Change rowsource on combobox in OnEnter event

[Doug]

I have a combo box on a tab object of an Access 2002 form that contains a lot
of records (30000+). To increase the form speed, I set the original
rowsource for the combo box to only the record bound to that control so only
1 record is retrieved (because it is rarely changed after it is added). If
the user enters the combobox again, I have code in the On Enter event to
select from the DB again for all the records so the user can select any
record they would like.

My OnEnter event is as follows:
Private Sub cbobillclientcode_Enter()
strsql = "SELECT tblClient.clientcode, tblClient.lastname + ', ' +
tblClient.firstname AS client, tblCompany.intname " & _
"FROM tblClient INNER JOIN " & _
"tblCompany ON tblClient.companycode = tblCompany.companycode "
& _
"WHERE tblclient.status = 'Active' " & _
" ORDER BY Client "
Me.cbobillclientcode.RowSource = strsql

End Sub

I would expect the rowsource to change and the combobox to requery but it is
not changing. It still only contains the one record. I have checked the sql
in query analyzer and that works fine.

Any suggestions?

 

[PJFry]

You also need to tell the combo box to requery to load the new values.

Just add Me.cbobillclientcode.Requery to the end of your code and you should
be set.

 

[Dirk Goldgar]

You also need to tell the combo box to requery to load the new values.

Just add Me.cbobillclientcode.Requery to the end of your code and you
should
be set.

I believe you're mistaken on this point. Setting the RowSource property of
a combo or list box always forces a requery -- no other action should be
required. I don't think this can be Doug's problem, though I'm not sure
what the answer is.

 

[Dirk Goldgar]

I have a combo box on a tab object of an Access 2002 form that contains a
lot
of records (30000+). To increase the form speed, I set the original
rowsource for the combo box to only the record bound to that control so
only
1 record is retrieved (because it is rarely changed after it is added).
If
the user enters the combobox again, I have code in the On Enter event to
select from the DB again for all the records so the user can select any
record they would like.

My OnEnter event is as follows:
Private Sub cbobillclientcode_Enter()
strsql = "SELECT tblClient.clientcode, tblClient.lastname + ', ' +
tblClient.firstname AS client, tblCompany.intname " & _
"FROM tblClient INNER JOIN " & _
"tblCompany ON tblClient.companycode = tblCompany.companycode
"
& _
"WHERE tblclient.status = 'Active' " & _
" ORDER BY Client "
Me.cbobillclientcode.RowSource = strsql

End Sub

I would expect the rowsource to change and the combobox to requery but it
is
not changing. It still only contains the one record. I have checked the
sql
in query analyzer and that works fine.

Any suggestions?

Can you verify that the code is executing? Is the On Enter event property
for the combo box set to "[Event Procedure]"? Set a breakpoint in the code,
then put the form into form view and see if the breakpoint is triggered when
you enter the combo box. If it is, step through the code to see what
happens.

 

[PJFry]

Dirk is correct, the On Enter will force a requery.

Make sure that your Row Source Type is set to Table/Query. If it is set to
Value List then the combo box will not update.

Other than that, Dirk's suggestion about checking to see the if the code is
executing is the next step.
--
Regards,

PJ
Please rate this post using the vote buttons if it was helpful.

 

[Doug]

I have a combo box on a tab object of an Access 2002 form that contains a
lot
of records (30000+). To increase the form speed, I set the original
rowsource for the combo box to only the record bound to that control so
only
1 record is retrieved (because it is rarely changed after it is added).
If
the user enters the combobox again, I have code in the On Enter event to
select from the DB again for all the records so the user can select any
record they would like.

My OnEnter event is as follows:
Private Sub cbobillclientcode_Enter()
strsql = "SELECT tblClient.clientcode, tblClient.lastname + ', ' +
tblClient.firstname AS client, tblCompany.intname " & _
"FROM tblClient INNER JOIN " & _
"tblCompany ON tblClient.companycode = tblCompany.companycode
"
& _
"WHERE tblclient.status = 'Active' " & _
" ORDER BY Client "
Me.cbobillclientcode.RowSource = strsql

End Sub

I would expect the rowsource to change and the combobox to requery but it
is
not changing. It still only contains the one record. I have checked the
sql
in query analyzer and that works fine.

Any suggestions?

Can you verify that the code is executing? Is the On Enter event property
for the combo box set to "[Event Procedure]"? Set a breakpoint in the code,
then put the form into form view and see if the breakpoint is triggered when
you enter the combo box. If it is, step through the code to see what
happens.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

Dirk,
I have verified that the code is executing and also tried it with and
without the .requery.
Neither seems to work. I do this on other combo boxes and they work great.
Not sure why this one doesn't work.

 

[Dirk Goldgar]

I have verified that the code is executing and also tried it with and
without the .requery.
Neither seems to work. I do this on other combo boxes and they work
great.
Not sure why this one doesn't work.

Hmm, it may be an error in the SQL statement, causing Access to refuse to
apply it. Instead of this:

Try this:

" ORDER BY tblClient.lastname, tblClient.firstname "