ComboBox requery is erratic

Bill Grigg · Nov 20, 2003

I am using Access 2002 and ADO. My combobox, CBO, has either a table
or query as a datasource (which one does not affect the problem).
After I update the table, I do Me.Recalc, Me.Repaint and CBO.Requery.
CBO will sometimes show the added table entry, and sometimes it will
not. Generally, it seems to lag by one record, although occasionally
it will catch up. I have googled on variations of <<erratic combobox
behavior>> but none of the situation seems to quite match. By the way,
the form is unbound. Any thoughts?

TIA

Bill

Alick [MSFT] · Nov 21, 2003

Hi,

We need to make sure the new added records in the table that combo box
bounds to are saved, you can close the table before requery combo box every
time to check if the issue occurs. In addition, is there any other code
behind the combo Box? For test purpose, put a command button on the form
and run the code to see if it works?

Private Sub Command3_Click()
Combo0.Requery
Me.Requery
End Sub
Please feel free to reply to the threads if you have any concerns or
questions.

Sincerely,

Alick Ye, MCSD
Product Support Services
Microsoft Corporation
Get Secure! - <www.microsoft.com/security>

This posting is provided "AS IS" with no warranties, and confers no rights.

--------------------
| From: (e-mail address removed) (Bill Grigg)
| Newsgroups: microsoft.public.access.forms
| X-Tomcat-NG: microsoft.public.access.forms
|
| I am using Access 2002 and ADO. My combobox, CBO, has either a table
| or query as a datasource (which one does not affect the problem).
| After I update the table, I do Me.Recalc, Me.Repaint and CBO.Requery.
| CBO will sometimes show the added table entry, and sometimes it will
| not. Generally, it seems to lag by one record, although occasionally
| it will catch up. I have googled on variations of <<erratic combobox
| behavior>> but none of the situation seems to quite match. By the way,
| the form is unbound. Any thoughts?
|
| TIA
|
| Bill
|

Bill Grigg · Nov 21, 2003

Alick,

How do you "close the table"? Below is the code I am using. Actually,
it almost is working. It (the combo box, CBO) just does not update
occasionally now.

Code that updates the table that is the row source for CBO:

<<<<start code
Private Function addToTable(ByVal tblName As String, _
ByVal fldName As String, _
ByVal strVariable As String, _
ByVal strConnection As String) As Long

Dim cnn As New ADODB.Connection
Dim rs As New ADODB.Recordset
Dim strSQL As String
Dim returnVal As Long: returnVal = 0

cnn.Open strConnection

' Open the target table for updating
'
strSQL = "SELECT * FROM " & tblName
rs.Open strSQL, cnn, adOpenKeyset, adLockOptimistic
rs.AddNew
rs.Fields(fldName) = strVariable
returnVal = rs.Fields("ID")

rs.Update
rs.Close
cnn.Close
Set cnn = Nothing
End Function

.....

Me.Recalc
Me.Repaint
CBO.Requery
CBO.Value = strVariable

I have also found that the following OnEnter event code helps a lot:

<<<<start code

Private Sub CBO_Enter()
CBO.Requery
End Sub

I would say that the above makes CBO look correct 90% of the time. Any
more thoughts?

Bill

Alick [MSFT] · Nov 24, 2003

Alick [MSFT] · Nov 25, 2003

Hi

It seems you use some third-party dll/library files, when I open the form
frmtest, it shows dlls.dll, mztools.dll, clsurface.dll are missing.
However, I click OK to run the form, it seems the code below is never
executed:

Private Sub cboExternalDB_Enter()
cboFileNames.Requery

End Sub

Therefore, Requery method is never called.

We can add Requery method in the combo box's getfocus event, and this will
be called every time when we select combo box.

Private Sub cboFileNames_GotFocus()

cboFileNames.Requery

End Sub

To make sure it works you can also add the refresh method in afterupdate
event:

Private Sub cboFileNames_AfterUpdate()

cboFileNames.Requery

End Sub

Please feel free to reply to the threads if you have any concerns or
questions.

Sincerely,

Alick Ye, MCSD
Product Support Services
Microsoft Corporation
Get Secure! - <www.microsoft.com/security>

This posting is provided "AS IS" with no warranties, and confers no rights.

--------------------
| From: (e-mail address removed) (Bill Grigg)
| Newsgroups: microsoft.public.access.forms
| X-Tomcat-NG: microsoft.public.access.forms
|
| Alick,
|
| How do you "close the table"? Below is the code I am using. Actually,
| it almost is working. It (the combo box, CBO) just does not update
| occasionally now.
|
| Code that updates the table that is the row source for CBO:
|
| <<<<start code
| Private Function addToTable(ByVal tblName As String, _
| ByVal fldName As String, _
| ByVal strVariable As String, _
| ByVal strConnection As String) As Long
|
| Dim cnn As New ADODB.Connection
| Dim rs As New ADODB.Recordset
| Dim strSQL As String
| Dim returnVal As Long: returnVal = 0
|
| cnn.Open strConnection
|
| ' Open the target table for updating
| '
| strSQL = "SELECT * FROM " & tblName
| rs.Open strSQL, cnn, adOpenKeyset, adLockOptimistic
| rs.AddNew
| rs.Fields(fldName) = strVariable
| returnVal = rs.Fields("ID")
|
| rs.Update
| rs.Close
| cnn.Close
| Set cnn = Nothing
| End Function
|
| .....
|
| Me.Recalc
| Me.Repaint
| CBO.Requery
| CBO.Value = strVariable
|
|
| >>>>>stop code
|
| I have also found that the following OnEnter event code helps a lot:
|
| <<<<start code
|
| Private Sub CBO_Enter()
| CBO.Requery
| End Sub
|
| >>>>stop code
|
| I would say that the above makes CBO look correct 90% of the time. Any
| more thoughts?
|
| Bill
|
| >
| > We need to make sure the new added records in the table that combo box
| > bounds to are saved, you can close the table before requery combo box
every
| > time to check if the issue occurs. In addition, is there any other code
| > behind the combo Box? For test purpose, put a command button on the
form
| > and run the code to see if it works?
| >
|

Alick [MSFT] · Nov 27, 2003

Hi Bill,

I tried the following steps to reproduce the issue:

1. Open the database file.
2. Open the form frmTest.
3. Open the table tblFileNames, add a new record, close the table; this
makes sure the new record is saved to the table.
4. Select the Combo Box cboFileNames, it drops down with a list, the new
added item/record is there.
5. Repeat Step 3 and Step 4, I repeat 10 more times, every time the new
added items/records will be shown when I select the Combo Box cboFileNames.

The issue is not reproduced with the same database file on my side, please
feel free to let me know if there is misunderstood or the reproduce steps
are not correct.

Sincerely,

Alick Ye, MCSD
Product Support Services
Microsoft Corporation
Get Secure! - <www.microsoft.com/security>

This posting is provided "AS IS" with no warranties, and confers no rights.

--------------------
| From: (e-mail address removed) (Bill Grigg)
| Newsgroups: microsoft.public.access.forms
| Xref: cpmsftngxa07.phx.gbl microsoft.public.access.forms:244543
| X-Tomcat-NG: microsoft.public.access.forms
|

Alick,

Sorry, those dll's were just residue and not needed for the Sample mdb. In
the enclosed Sample.mdb I deleted them from the references. I also added
cboFileNames_GotFocus(), cboFileNames_Enter() and
cboFileNames_AfterUpdate() and it does seem make things better, but not
perfect. The problem is that while whatever is selected is ALWAYS added to
the table, it occasionally does NOT get added to the cboFileNames. Try it
out. It now takes me 6 selections until the incorrect behavior appears.
However, it does seem that if I select the record selector on the left side
of the form and then select cboFileNames that the latest entry does appear.

Regards,

Bill

PS: I appreciate the assistance

Bill Grigg · Nov 27, 2003

Alick,

The problem occurs when using the Browse... button to select a file
name to add to the table and combo box.

Bill

Alick [MSFT] · Nov 28, 2003

Hi Bill,

Thanks for the clarification.

I tried the following steps with the sample database you sent:

1. Open the database file.
2. Open the form.
3. Click the Browse¡ button,
4. Select a file and click Open.
5. The new selected file information is shown in the combo box immediately;
and the new record is added to the table.
6. Repeat Step 3 and Step 4, each time it shows the current new added item;
repeat 15 more times and it works fine.

The code should be fine; I think you can try to install the latest MDAC
version to see if it makes any difference. Please feel free to let me know
you have any concerns or questions.

Hope you had a wonderful Thanksgiving!

Sincerely,

Alick Ye, MCSD
Product Support Services
Microsoft Corporation
Get Secure! - <www.microsoft.com/security>

This posting is provided "AS IS" with no warranties, and confers no rights.

--------------------
| From: (e-mail address removed) (Bill Grigg)
| Newsgroups: microsoft.public.access.forms
| Xref: cpmsftngxa06.phx.gbl microsoft.public.access.forms:245654
| X-Tomcat-NG: microsoft.public.access.forms
|
| Alick,
|
| The problem occurs when using the Browse... button to select a file
| name to add to the table and combo box.
|
| Bill
|
|
|
| (e-mail address removed) (Alick [MSFT]) wrote in message
| > Hi Bill,
| >
| > I tried the following steps to reproduce the issue:
| >
| > 1. Open the database file.
| > 2. Open the form frmTest.
| > 3. Open the table tblFileNames, add a new record, close the table; this
| > makes sure the new record is saved to the table.
| > 4. Select the Combo Box cboFileNames, it drops down with a list, the
new
| > added item/record is there.
| > 5. Repeat Step 3 and Step 4, I repeat 10 more times, every time the new
| > added items/records will be shown when I select the Combo Box
cboFileNames.
| >
| > The issue is not reproduced with the same database file on my side,
please
| > feel free to let me know if there is misunderstood or the reproduce
steps
| > are not correct.
| >
| >
| > Sincerely,
| >
| > Alick Ye, MCSD
| > Product Support Services
| > Microsoft Corporation
| > Get Secure! - <www.microsoft.com/security>
| >
| > This posting is provided "AS IS" with no warranties, and confers no
rights.
| >
| >
| > --------------------
| > | From: (e-mail address removed) (Bill Grigg)
| > | Newsgroups: microsoft.public.access.forms
| > | Xref: cpmsftngxa07.phx.gbl microsoft.public.access.forms:244543
| > | X-Tomcat-NG: microsoft.public.access.forms
| > |
| >
| >
| > Alick,
| >
| > Sorry, those dll's were just residue and not needed for the Sample mdb.
In
| > the enclosed Sample.mdb I deleted them from the references. I also
added
| > cboFileNames_GotFocus(), cboFileNames_Enter() and
| > cboFileNames_AfterUpdate() and it does seem make things better, but
not
| > perfect. The problem is that while whatever is selected is ALWAYS added
to
| > the table, it occasionally does NOT get added to the cboFileNames. Try
it
| > out. It now takes me 6 selections until the incorrect behavior appears.
| > However, it does seem that if I select the record selector on the left
side
| > of the form and then select cboFileNames that the latest entry does
appear.
| >
| > Regards,
| >
| > Bill
| >
| > PS: I appreciate the assistance
|

Bill Grigg · Nov 28, 2003

Alick,

Thanks so much for your efforts. I will find the latest MDAC and
install it as you suggest.

Thanks again,

Bill

Hi Bill,

Thanks for the clarification.

I tried the following steps with the sample database you sent:

1. Open the database file.
2. Open the form.
3. Click the Browse¡ button,
4. Select a file and click Open.
5. The new selected file information is shown in the combo box immediately;
and the new record is added to the table.
6. Repeat Step 3 and Step 4, each time it shows the current new added item;
repeat 15 more times and it works fine.

The code should be fine; I think you can try to install the latest MDAC
version to see if it makes any difference. Please feel free to let me know
you have any concerns or questions.

Hope you had a wonderful Thanksgiving!

Sincerely,

Alick Ye, MCSD
Product Support Services
Microsoft Corporation
Get Secure! - <www.microsoft.com/security>

This posting is provided "AS IS" with no warranties, and confers no rights.

--------------------
| From: (e-mail address removed) (Bill Grigg)
| Newsgroups: microsoft.public.access.forms
| Xref: cpmsftngxa06.phx.gbl microsoft.public.access.forms:245654
| X-Tomcat-NG: microsoft.public.access.forms
|
| Alick,
|
| The problem occurs when using the Browse... button to select a file
| name to add to the table and combo box.
|
| Bill
|
|
|
| (e-mail address removed) (Alick [MSFT]) wrote in message
| > Hi Bill,
| >
| > I tried the following steps to reproduce the issue:
| >
| > 1. Open the database file.
| > 2. Open the form frmTest.
| > 3. Open the table tblFileNames, add a new record, close the table; this
| > makes sure the new record is saved to the table.
| > 4. Select the Combo Box cboFileNames, it drops down with a list, the
new
| > added item/record is there.
| > 5. Repeat Step 3 and Step 4, I repeat 10 more times, every time the new
| > added items/records will be shown when I select the Combo Box
cboFileNames.
| >
| > The issue is not reproduced with the same database file on my side,
please
| > feel free to let me know if there is misunderstood or the reproduce
steps
| > are not correct.
| >
| >
| > Sincerely,
| >
| > Alick Ye, MCSD
| > Product Support Services
| > Microsoft Corporation
| > Get Secure! - <www.microsoft.com/security>
| >
| > This posting is provided "AS IS" with no warranties, and confers no
rights.
| >
| >
| > --------------------
| > | From: (e-mail address removed) (Bill Grigg)
| > | Newsgroups: microsoft.public.access.forms
| > | Xref: cpmsftngxa07.phx.gbl microsoft.public.access.forms:244543
| > | X-Tomcat-NG: microsoft.public.access.forms
| > |
| >
| >
| > Alick,
| >
| > Sorry, those dll's were just residue and not needed for the Sample mdb.
In
| > the enclosed Sample.mdb I deleted them from the references. I also
added
| > cboFileNames_GotFocus(), cboFileNames_Enter() and
| > cboFileNames_AfterUpdate() and it does seem make things better, but
not
| > perfect. The problem is that while whatever is selected is ALWAYS added
to
| > the table, it occasionally does NOT get added to the cboFileNames. Try
it
| > out. It now takes me 6 selections until the incorrect behavior appears.
| > However, it does seem that if I select the record selector on the left
side
| > of the form and then select cboFileNames that the latest entry does
appear.
| >
| > Regards,
| >
| > Bill
| >
| > PS: I appreciate the assistance
|