Combo box requery help needed

[AnthonyL]

I have a form (with help desk call info) that uses a query to populate a combobox with the firstname, lastname and company data from a table full of contact data. I allow the user to open a new form on a double click event in the combo box, so that the user can add new contacts to the contact table that the query draws its fields from.

When the user is done, he closes the form for adding data and is again seeing the open form he had been on.

I can't seem to get the newly added data to show in the combo box without closing and reopening the first form, which loses data that is already in the form.

How do I do this??? I have run out of ideas as the requery command doesnt work for me.

Thanks for any help you can give.

[This message has been edited by AnthonyL (edited 10-05-2001).]

 

[jwindon]

Try

DoCmd.RunCommand acCmdRefresh

 

[Elana]

For this purpose, I usually place a command on the "On Activate" event for the form:

me.yourcombobox.requery

Hope this helps!

 

[Alexandre]

My two cents:

You should try to avoid using the On current event if you can use an event more specifically related to your context. The On current event is triggered under various situations, which can lead to various executions of the code. You may find that it slows down significantly your app.

The on close even of your new data entry form, may be a good choice to use .refresh

Alex

[This message has been edited by Alexandre (edited 10-05-2001).]

 

[AnthonyL]

Thanks. It's working using the on activate event for the form.

[This message has been edited by AnthonyL (edited 10-08-2001).]

 

[R. Hicks]

If you are only adding the entry to the lookup table and this table only consists on a single field, you can use the code below to do this. The combo will automatically be updated when the code executes:

Private Sub YourCombo_NotInList(NewData As String, Response As Integer)
On Error GoTo Err_YourCombo_NotInList
Dim ctl As Control
Dim strSQL As String

' Return Control object that points to combo box.
Set ctl = Me!YourCombo
' Prompt user to verify they wish to add new value.
If MsgBox("Item is not in list. Add it?", vbOKCancel) = vbOK Then
&nbsp ' Set Response argument to indicate that data is being added.
&nbsp Response = acDataErrAdded
&nbsp ' Add string in NewData argument to products table.
&nbsp NewData = StrConv(NewData, 3)
&nbsp strSQL = " INSERT INTO tblYourTable ( YourField ) SELECT '" & NewData & "'"
&nbsp DoCmd.SetWarnings False
&nbsp DoCmd.RunSQL strSQL
&nbsp ctl.Value = NewData
&nbsp DoCmd.SetWarnings True
Else
&nbsp ' If user chooses Cancel, suppress error message and undo changes.
&nbsp Response = acDataErrContinue
&nbsp ctl.Undo
End If

Exit_YourCombo_NotInList:
&nbsp Exit Sub

Err_YourCombo_NotInList:
&nbsp MsgBox Err.Description
&nbsp Resume Exit_YourCombo_NotInList
End Sub

HTH
RDH