how do I link info in table?

[abbazabba]

Hi!

I have a form at the moment with the fields 'student name' and 'course' (amongst others).

the field 'Student name' has a drop down menu linked to a table listing all the student names. On the same table is a column stating each students course.

How can I link this info together so that when you enter a student name on the form, their course appears in the 'course' field on the form?

I'm sure this is easily done but i'm stuck!

Cheers

Phil

 

[ethorn10]

If the drop down menu has that table as its RecordSource, you could try something like this to update the course field:

Private Sub student_fieldname_AfterUpdate()

If Me![student_fieldname].Column(1) <> &quot;&quot; Then
Me![course_fieldname] = Me![student_fieldname].Column(1)
End If

End Sub

--The number in parenthesis after Column specifies what column the course field is. If the course is in column 3 of the RecordSource, you would put .Column(3) and this should update your Course field.

Let me know if this does the trick.

 

[Clarkie001]

An idea would be to create another form containing the 'Course' information and any other data you need. Then attach it to your main form as a subform.

Allow access to launch the wizard and use this to create your links between the two forms.

Hope this is what you are looking for.......

Clarkie

 

[abbazabba]

Hi, thanks for replying..

I have been given two different options here and I'm trying the first (because it's the first! Thanks to both ethorn10 and clarkie001 for replying). Problem is I don't really know what i'm doing.

In response to ethorn10's suggestion;

I opened up the form in design view, and brought up the properties of the field 'student name'.

Went to the 'AfterUpdate' option, and selected macrobuilder, and for the action, selected runSQL. I then inserted your code. Is this totally the wrong approach as I am new to all this and am trying to muddle through!

When you insert a name in the form it comes up with this message:

Invalid SQL statement; expected 'DELETE', 'INSERT', 'PROCEDURE' 'SELECT', or 'UPDATE'.

Cheers

Phil

 

[ethorn10]

Instead of selecting macrobuilder, select Code Builder. Then insert that code and change the words inside the [ ] to match your field's names, respectively.

Hopefully this will eliminate the error you were getting.

Again, let me know how this works.

-ethorn10

 

[abbazabba]

Ok, I've done that and it's coming up with another error.

This is what i've put in the code builder:

Private Sub STUDENT_NAME_AfterUpdate()
If Me![STUDENT NAME(F/T)_fieldname].Column(3) <> &quot;&quot; Then
Me![COURSE_fieldname] = Me![STUDENT NAME(F/T)_fieldname].Column(3)

End Sub

'STUDENT NAME (F/T)' is the full name of the student names column and the COURSE column is the third.

When you enter a name it comes up with this error:

The expression After Update you entered as the event property setting produced the following error: Ambiguous name detected: DATE _DUE_BACK_SEARCH_Click.

Hmmm...'DATE DUE BACK SEARCH' is the name of one of my macros that is launched with a command button elsewhere on the form. Why is it coming up with this?

Cheers!

Phil

 

[ethorn10]

Ok...

You should replace &quot;STUDENT NAME(F/T)_fieldname&quot; with the name of the drop down field, not the table field. Assuming the name of the drop down menu is student_name (which it seems to be considering the name of the subroutine), you would put:

Private Sub STUDENT_NAME_AfterUpdate()
If Me![STUDENT_NAME].Column(3) <> &quot;&quot; Then
Me![COURSE] = Me![STUDENT NAME].Column(3)
End Sub

This is also assuming that the field for course is called &quot;course&quot;. You don't put &quot;fieldname&quot; after it, that was my way of letting you know what to put there.

--keep me informed.

ethorn10

 

[abbazabba]

HI THERE...

Private Sub STUDENT_NAME_AfterUpdate()
If Me![STUDENT_NAME].Column(3) <> &quot;&quot; Then
Me![COURSE] = Me![STUDENT NAME].Column(3)
End Sub

This is what I have at the mo and it's giving me this message:

Compile error: Block If without End If

Any ideas?

Cheers

Phil

 

[ethorn10]

Yes.

Throw an &quot;End If&quot; on the line above &quot;End Sub&quot; and that error should disappear.

-ethorn10

 

[abbazabba]

Private Sub STUDENT_NAME_AfterUpdate()
If Me![STUDENT_NAME].Column(3) <> &quot;&quot; Then
Me![COURSE]=Me![STUDENT_NAME].Column(3)End If
End Sub

This is what I have now and it either says 'expected: end of statement' or 'compile: syntax error'...

Any more ideas?

Thanks alot!

Phil.

 

[abbazabba]

End If Me![COURSE] = Me![STUDENT NAME].Column(3)

Ooops, think you might have meant stick it on the beginning like the above.

Still says 'expected: end of statement tho..'

Cheers!

Phil

 

[ethorn10]

Sorry I meant on the line immediately after that.

    Me![COURSE] = Me![STUDENT NAME].Column(3)
  End If
End Sub

This should solve that error.

 

[abbazabba]

Private Sub STUDENT_NAME_AfterUpdate()
If Me![STUDENT NAME].Column(3) <> &quot;&quot; Then
Me![COURSE] = Me![STUDENT NAME].Column(3)
End If
End Sub

Ok. This is what I've got now and it doesn't come up with any error messages, but doesn't update the course field. Should this code automatically run when I enter a name into the student name field, or do I need to create a macro to start it up?

Sorry if this is painful, but i'm in over my head!

Cheers

Phil

 

[ethorn10]

We might have to start this one over. Do me a favor and open the form with the two fields in question using design view. Double click on the student name combo box and if there is something in the &quot;Row Source&quot; property, copy it here. If there isn't, I'll need a little more information. Is the course field a combo box also?

Sorry, we'll get it worked out.

 

[abbazabba]

Hi there..

This is what is in the row source for the drop down menu entitled 'STUDENT NAME' on the form:

SELECT [STUDENT NAMES].[STUDENT NAME (F/T)] FROM [STUDENT NAMES];

And the 'course' field on the form isn't a drop down menu.

Cheers for your help!

Phil.

 

[ethorn10]

Ok, what is the 'course' field if it is not a drop down menu?

 

[abbazabba]

Hey there Ethorn10

The course field is a text box..

Cheers

Phil.

 

[ethorn10]

Ok, try setting the source for the Students field to:

SELECT [STUDENT NAMES].[STUDENT NAME (F/T)], [whatever the course field is called].[student name (f/t)] FROM [STUDENT NAMES];
[\code]

and then change the column in the other code to (2) instead of (3).  

See how that works.  If that doesn't do the trick, try changing the column to (1)

 

[abbazabba]

Hey there,

I tried this and it came up with a parameter query window when I clicked on the drop down menu?! The window says 'course.STUDENT NAME(F/T)'.

Now i'm confused??

I click ok and the drop down menu appears, so I select a name and no go..

Have you any ideas on this one?! If you get fed up of this problem at any stage i'd totally understand!

Cheers

Phil.

 

[ethorn10]

Another try...

SELECT [STUDENT NAME (F/T)].[STUDENT NAMES], [course].[STUDENT NAMES] FROM [STUDENT NAMES];

as the source. the syntax for the select is &quot;field.table&quot; so make sure that &quot;course&quot; is a field in the table &quot;student names&quot;