data not appearing in combo box

[czarjosh]

OK, I have a form with a dop down box on it that allows me to pick other people in a registration table to list them as a roommate. The events looks like this:
If Nz(Me.Roommate, 0) > 0 Then
strSQL = "UPDATE Registration SET RoomMate = " _
& Me.RecNumber & " WHERE RecNumber = " & Roommate

DoCmd.SetWarnings (False) 'turn off wanring prompt
DoCmd.RunSQL strSQL
DoCmd.SetWarnings (True) 'turn warnings back on

End If

and the row source is:
SELECT Registration.RecNumber, [First name] & " " & [last Name] AS [Full name]
FROM Registration;

On this form i also have a sub form that allows me to register a guest of that person. This is a one to one relationship in the same table, just like the roommate field is.

When I register a person as a guest, they do not appear in the roommate combo box. What is going on here?

 

[PHV]

Have you tried to play with the ReQuery method of the combo ?
BTW, have you verified that the Update query does the job as expected ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[czarjosh]

I have verified it and it does work. How do i make the combo box requery?

 

[czarjosh]

On the subform, this is the Event Procedure:

Private Sub Form_Current()

mySql = "SELECT RecNumber, [last Name] & ', ' & [First name] FROM Registration" & " WHERE RecNumber<>" & Me![RecNumber]
Me![cboRegistrants].RowSource = mySql
Me![cboRegistrants].Requery

End Sub