Can't make Combo Box refresh after adding

[StarfishJoe]

I am trying to make a ComboBox in Access 2000 refresh after I add a record
on the fly to the underlying table that is the source for the Combobox list.

To start with, I have a form for new jobs. The customers or CLIENTS of
these jobs are entered in a combobox bound to a ClientTable.

The main form; ProjectList Form is bound to a ProjectListTable.

Most of the projects are repeat business for the same clients, but there are
also new clients being added all the time.

I need to "limit to list" the client name in the Client combo box on the
Project List form. (done that-no problem)

I also need to set it up so that if the user types in a client name that is
not in the list, a message box pops up with Yes/No buttons and a message:"
"This client name is not in list." & vbCrLf & "Do you want to add this name
to the Client list?"

The No button Cancels the action. (That works OK.)
The Yes Button opens a NewClient form that adds the new client to the
ClientTable. When the NewClient form is closed the Focus returns to the
Client combo Box on the Project List Form.

The problem is that although I have sussessfully added the New Client Name
to the Client list., The Combo Box has not been refreshed and still prompts
me to add new client or pick from list. The only way around this is to Clear
the new name from the Combo Box, close the ProjectListForm and reopen it.

I have tried Refresh and Requery but without success.

How do I refresh, requery, or repopulate the Combo Box without closing and
reopening the form?

The following is the code I have in the combo Box's NotInList event
----------------------------------------------------------------------------
-----
Private Sub Client_NotInList(NewData As String, Response As Integer)
Dim Msg, Style, Title, YesNoResponse
Msg = "This client name is not in list." & vbCrLf & "Do you want to add this
name to the Client list?"
Style = vbYesNo
Title = "Client Not Found"
Response = 0
YesNoResponse = MsgBox(Msg, Style, Title)
If YesNoResponse = vbYes Then
NewData = Me.cboClient.Text
On Error GoTo Err_AddNewClientForm

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "AddNewClientForm"
DoCmd.OpenForm stDocName, , , stLinkCriteria
Form_AddNewClientForm.txtCompany = NewData
Form_AddNewClientForm.SetFocus

'Exit_AddNewClientForm:
Exit Sub

Err_AddNewClientForm:
MsgBox Err.Description
Resume Next 'Exit_AddNewClientForm
Else
'MsgBox "You have selected No. Pick from list", vbOKOnly,
"CANCEL--LIMIT TO LIST"
cboClient.SetFocus
cboClient.Undo
End If
End Sub

 

[Guest]

You say you have tried Requery, but I don't see it in your code. Have you
tried:
Me.cboClient.SetFocus
Me.cblClient.Requery

Put it right after:
Form_AddNewClientForm.SetFocus

 

[StarfishJoe]

You say you have tried Requery, but I don't see it in your code.
I had placed it in and removed it in a couple places because it did not
produce the desired action.
However, I did have it coded in the cboClient's Got Focus event:
Private Sub cboClient_GotFocus()
Me.cboClient.Requery
End Sub

Have you tried:
Me.cboClient.SetFocus
Me.cblClient.Requery

Put it right after:
Form_AddNewClientForm.SetFocus

I just did this and it did not change anything.
I stepped through the procedure and after inserting your code where you
said, The error handler pops up with a message "You must save the current
field before you run the Requery action." with an OK button only.

The Combo box still has not been refreshed, and the newly added data is
still added to the New Client list but not updated in the Combo Box.

The only change here is the error message mentioned above.