Hi everyone,

This is a really simple problem, but I I've only just started using Visual Basic this week so I don't know how to solve it.

I want to update a field in my main table using a value that I have selected from another table using a series of combo boxes. I choose a value (eg surname) in combo box 1, which in turn filters my second table so that in combo box 2 I have a choice of values (eg firstname). I would then like to write a module so that after update of firstname the primary key (foreign key in my main table) nameid is selected and is inserted into a textbox (nameid) bound to my main table.

I have the following code which shows what I want to do, but obviously it doesn't work because the UPDATE query contains a string...

Private Sub cbofirstname_AfterUpdate()

Set nameid As String

nameid = "SELECT tblname.nameid FROM tblename WHERE tblname.surname = '" & Me.cbosurname.Text & "' AND tblename.firstname = '" & Me.cbofirstname.Text & "';"

DoCmd.RunSQL (UPDATE tblmain SET tblnameid = & "nameid" & WHERE personid = " & Me.personid.Value & ";"

End Sub

Any ideas anyone?

Does the first command put the string you want in nameid?

The main problem I see is your second command (specifically where you put the quotes around nameid). It should be:
DoCmd.RunSQL (UPDATE tblmain SET tblnameid = '" & nameid & "' WHERE personid = '" & Me.personid.Value & "';"