Requerying ComboBox in NotInList event

[EManning]

I have a combobox whose rowsource is a union query. This query
displays a person's name in "lastname, firstname" format and in
"firstname lastname" format. The query results look like this:

Mouse, Mickey
Mickey Mouse

When a person is added, the querys' underlying recordset is updated in
the NotInList event. I can't figure out how to refresh the combobox
to display the new person. I get the standard error message that the
item is not in the list. Here's my code:

....<in the NotInList event>...
With rst
.AddNew
!FirstName = strFirstName
!MiddleInitial = strMiddleInitia l
!LastName = strLastName
.Update
End With

Response = acDataErrAdded

I have to refresh the query somehow so that it displays in the combo
box. If I add the code:

Me.ComboBox.Req uery

I get the standard error message that it must be saved.

Maybe there's a better way to do this? Thanks for any help or advice.

[Tom van Stiphout]

On Tue, 4 Mar 2008 11:09:22 -0800 (PST), EManning
<ma**********@h otmail.comwrote :

Your code, minus the Requery, looks correct. Perhaps you have On Error
Resume Next and you are suppressing errors?

-Tom.

>I have a combobox whose rowsource is a union query. This query
displays a person's name in "lastname, firstname" format and in
"firstname lastname" format. The query results look like this:

Mouse, Mickey
Mickey Mouse

When a person is added, the querys' underlying recordset is updated in
the NotInList event. I can't figure out how to refresh the combobox
to display the new person. I get the standard error message that the
item is not in the list. Here's my code:

...<in the NotInList event>...
With rst
.AddNew
!FirstName = strFirstName
!MiddleInitial = strMiddleInitia l
!LastName = strLastName
.Update
End With

Response = acDataErrAdded

I have to refresh the query somehow so that it displays in the combo
box. If I add the code:

Me.ComboBox.Req uery

I get the standard error message that it must be saved.

Maybe there's a better way to do this? Thanks for any help or advice.

[Guillermo_Lopez]

The way it works for me is typing:

ComboBox.Requer y

I don't use the me. part because im running the code in the form
already.

Instead of using the requery on the combo box, try requerying the
From.

CurrentForm.Req uery

- GL
On Mar 5, 1:37*am, Tom van Stiphout <no.spam.tom7.. [e-mail]:

On Tue, 4 Mar 2008 11:09:22 -0800 (PST), EManning

<manning_n...@h otmail.comwrote :

Your code, minus the Requery, looks correct. Perhaps you have On Error
Resume Next and you are suppressing errors?

-Tom.

I have a combobox whose rowsource is a union query. *This query
displays a person's name in "lastname, firstname" format and in
"firstname lastname" format. *The query results look like this:

* * Mouse, Mickey
* * Mickey Mouse

When a person is added, the querys' underlying recordset is updated in
the NotInList event. *I can't figure out how to refresh the combobox
to display the new person. *I get the standard error message that the
item is not in the list. *Here's my code:

...<in the NotInList event>...
With rst
* *.AddNew
* *!FirstName = strFirstName
* *!MiddleInitial = strMiddleInitia l
* *!LastName = strLastName
* *.Update
End With

* * Response = acDataErrAdded

I have to refresh the query somehow so that it displays in the combo
box. *If I add the code:

* * Me.ComboBox.Req uery

I get the standard error message that it must be saved.

Maybe there's a better way to do this? *Thanks for any help or advice.-Hide quoted text -

- Show quoted text -

[EManning]

On Mar 5, 8:57*am, Guillermo_Lopez <g.lo...@iesdr. comwrote:

The way it works for me is typing:

* * *ComboBox.Reque ry

I don't use the me. part because im running the code in the form
already.

Instead of using the requery on the combo box, try requerying the
From.

* * *CurrentForm.Re query

- GL

On Mar 5, 1:37*am, Tom van Stiphout <no.spam.tom7.. [e-mail]:

On Tue, 4 Mar 2008 11:09:22 -0800 (PST), EManning

<manning_n...@h otmail.comwrote :

Your code, minus the Requery, looks correct. Perhaps you have On Error
Resume Next and you are suppressing errors?

-Tom.

>I have a combobox whose rowsource is a union query. *This query
>displays a person's name in "lastname, firstname" format and in
>"firstname lastname" format. *The query results look like this:

* * Mouse, Mickey
* * Mickey Mouse

>When a person is added, the querys' underlying recordset is updated in
>the NotInList event. *I can't figure out how to refresh the combobox
>to display the new person. *I get the standard error message that the
>item is not in the list. *Here's my code:

>...<in the NotInList event>...
>With rst
* *.AddNew
* *!FirstName = strFirstName
* *!MiddleInitial = strMiddleInitia l
* *!LastName = strLastName
* *.Update
>End With

* * Response = acDataErrAdded

>I have to refresh the query somehow so that it displays in the combo
>box. *If I add the code:

* * Me.ComboBox.Req uery

>I get the standard error message that it must be saved.

>Maybe there's a better way to do this? *Thanks for any help or advice..- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -

Any attempt to requery the combobox gives me an error that the field
must be saved, whether or not the "Me." is used. The form is unbound
so requerying it wouldn't do any good. Thanks for replying.

[Salad]

EManning wrote:

On Mar 5, 8:57 am, Guillermo_Lopez <g.lo...@iesdr. comwrote:

>>The way it works for me is typing:

ComboBox.Requer y

I don't use the me. part because im running the code in the form
already.

Instead of using the requery on the combo box, try requerying the
From.

CurrentForm.Req uery

- GL

On Mar 5, 1:37 am, Tom van Stiphout <no.spam.tom7.. [e-mail]:

>>>On Tue, 4 Mar 2008 11:09:22 -0800 (PST), EManning

>>><manning_n.. [e-mail] ote:

>>>Your code, minus the Requery, looks correct. Perhaps you have On Error
Resume Next and you are suppressing errors?

>>>-Tom.

>>>>I have a combobox whose rowsource is a union query. This query
displays a person's name in "lastname, firstname" format and in
"firstnam e lastname" format. The query results look like this:

>>> Mouse, Mickey
Mickey Mouse

>>>>When a person is added, the querys' underlying recordset is updated in
the NotInList event. I can't figure out how to refresh the combobox
to display the new person. I get the standard error message that the
item is not in the list. Here's my code:

>>>>...<in the NotInList event>...
With rst
.AddNew
!FirstName = strFirstName
!MiddleInitial = strMiddleInitia l
!LastName = strLastName
.Update
End With

>>> Response = acDataErrAdded

>>>>I have to refresh the query somehow so that it displays in the combo
box. If I add the code:

>>> Me.ComboBox.Req uery

>>>>I get the standard error message that it must be saved.

>>>>Maybe there's a better way to do this? Thanks for any help or advice.- Hide quoted text -

>>>- Show quoted text -- Hide quoted text -

- Show quoted text -

Any attempt to requery the combobox gives me an error that the field
must be saved, whether or not the "Me." is used. The form is unbound
so requerying it wouldn't do any good. Thanks for replying.

Here's some code I wrote for 2 comboxes. I didn't have a recordsource
for the form using the code for Combo0. I added a recordsource for the
code in Combo3.

'Combo0_NotInLi st works just fine.
Private Sub Combo0_NotInLis t(NewData As String, Response As Integer)
Dim rst As Recordset
Set rst = CurrentDb.OpenR ecordset("Table 1", dbOpenDynaset)

If MsgBox("Make this a new entry?", vbYesNo, "Confirm Add") = vbYes
Then
rst.AddNew
rst!TextF = NewData
rst.Update
Response = acDataErrAdded
Else
Response = acDataErrContin ue
End If
rst.Close
Set rst = Nothing
End Sub

'Combo3_NotInLi st works just fine.
'Add record by entering firstname space lastname
'I use a query for the combo's rowsource. The combo has 2 columns; ID
'and Fullname. Fullname is a concatenation of first and last names.
'The SQL is
'SELECT DISTINCTROW Table1.ID, [FirstName] & " " & [LastName] AS
FullName FROM Table1;

Private Sub Combo3_NotInLis t(NewData As String, Response As Integer)
Dim rst As Recordset
Set rst = CurrentDb.OpenR ecordset("Table 1", dbOpenDynaset)
Dim sF As Variant
Dim sL As Variant
Dim iP As String
iP = InStr(NewData, " ")
If iP 0 Then
sF = Left(NewData, iP - 1)
sL = Mid(NewData, iP + 1)
Else
sF = NewData
End If

If MsgBox("Not in list. Make this a new entry?", vbYesNo, "Confirm
Add") = vbYes Then
rst.AddNew
rst!FirstName = sF
rst!LastName = sL
rst.Update
Response = acDataErrAdded
Else
Response = acDataErrContin ue
End If
rst.Close
Set rst = Nothing
End Sub

Fur
http://www.youtube.com/watch?v=BPv0qCg4so8