467,161 Members | 933 Online
Bytes | Developer Community
Ask Question

Home New Posts Topics Members FAQ

Post your question to a community of 467,161 developers. It's quick & easy.

Requerying ComboBox in NotInList event

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 = strMiddleInitial
!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.Requery

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.
Mar 4 '08 #1
  • viewed: 4681
Share:
4 Replies
On Tue, 4 Mar 2008 11:09:22 -0800 (PST), EManning
<ma**********@hotmail.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 = strMiddleInitial
!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.Requery

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.
Mar 5 '08 #2
The way it works for me is typing:

ComboBox.Requery

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.Requery

- GL
On Mar 5, 1:37*am, Tom van Stiphout <no.spam.tom7...@cox.netwrote:
On Tue, 4 Mar 2008 11:09:22 -0800 (PST), EManning

<manning_n...@hotmail.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 = strMiddleInitial
* *!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.Requery
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 -
Mar 5 '08 #3
On Mar 5, 8:57*am, Guillermo_Lopez <g.lo...@iesdr.comwrote:
The way it works for me is typing:

* * *ComboBox.Requery

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.Requery

- GL

On Mar 5, 1:37*am, Tom van Stiphout <no.spam.tom7...@cox.netwrote:
On Tue, 4 Mar 2008 11:09:22 -0800 (PST), EManning
<manning_n...@hotmail.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 = strMiddleInitial
* *!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.Requery
>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.
Mar 5 '08 #4
EManning wrote:
On Mar 5, 8:57 am, Guillermo_Lopez <g.lo...@iesdr.comwrote:
>>The way it works for me is typing:

ComboBox.Requery

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.Requery

- GL

On Mar 5, 1:37 am, Tom van Stiphout <no.spam.tom7...@cox.netwrote:

>>>On Tue, 4 Mar 2008 11:09:22 -0800 (PST), EManning
>>><manning_n...@hotmail.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 = strMiddleInitial
!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.Requery
>>>>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_NotInList works just fine.
Private Sub Combo0_NotInList(NewData As String, Response As Integer)
Dim rst As Recordset
Set rst = CurrentDb.OpenRecordset("Table1", dbOpenDynaset)

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

'Combo3_NotInList 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_NotInList(NewData As String, Response As Integer)
Dim rst As Recordset
Set rst = CurrentDb.OpenRecordset("Table1", 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 = acDataErrContinue
End If
rst.Close
Set rst = Nothing
End Sub

Fur
http://www.youtube.com/watch?v=BPv0qCg4so8
Mar 5 '08 #5

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

2 posts views Thread by Mark Kola | last post: by
2 posts views Thread by whilstiwait@gmail.com | last post: by
14 posts views Thread by Thelma Lubkin | last post: by
1 post views Thread by Stephen.Hunter@dumgal.gov.uk | last post: by
12 posts views Thread by eetarnold@kc.rr.com | last post: by
2 posts views Thread by steph | last post: by
5 posts views Thread by genojoe | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.