Dynamically setting the recordset for a combobox

[Dave]

I have a form that has two combo boxes on it. The combo boxes apply filters to the form's underlying recordset.

The first box has a list of states and the second has a list of customers.

When the user makes a selection in the state combobox I want the record set for the customer combo box to be filtered by that selection. (As welll as the form's recordset.)

For example, if the user selects California in the state combobox, the customer combobox should be restricted to show only customers in California as being available for selection.

I believe this can be done in the VBA code module. Can anyone give me an idea on how to approach this? IOW how do I dynamically set the record source for the combobox using the onchange event of another combobox?

Thanks

 

[strive4peace]

Hi Dave,

limit the customer combobox to specific records when it gets the focus,
show all records when you leave it -- do this so that when you are not
specifically dropping a list, all choices will be displayed

on the gotFocus event of the customer combobox, assign this:

=SetRowSourceCust(true)


on the lostFocus event of the customer combobox, assign this:

=SetRowSourceCust(false)


put this code behind the form/subform with the combobox -- and compile
it before testing

'~~~~~~~~~~~

private function SetRowSourceCust( _
pBooCriteria as boolean)

on error goto Err_proc

dim strSQL as string

strSQL = "SELECT CustID, CustName" _
& " FROM Tablename"

if pBooCriteria then

strSQL = strSQL _
& " WHERE (State = '" & me.state_comboname & "')"
end if

strSQL = strSQL & "ORDER BY CustName;"

'comment next line after debugged
debug.print strSQL

me.customer_controlname.RowSource = strSQL
me.customer_controlname.Requery

Exit_proc:
exit function

Err_proc:
msgbox err.description,, _
"ERROR " & err.number & " SetRowSourceCust"
'press F8 to step through code and fix problem
'comment next line after debugged
Stop: Resume

resume Exit_proc
End function

'~~~~~~~~

** debug.print ***

debug.print strSQL

--> this prints a copy of the SQL statement to the debug window (CTRL-G)

After you execute your code, open the Debug window
CTRL-G to Goto the debuG window -- look at the SQL statement

If the SQL statement has an error

1. Make a new query (design view)

2. choose View, SQL from the menu
(or SQL from the toolbar, first icon)

3. cut the SQL statement from the debug window
(select, CTRL-X)

4. paste into the SQL window of the Query
(CTRL-V)

5. run ! from the SQL window
-- Access will tell you where the problem is in the SQL

'~~~~~~~~~~~~~~

the debug window, also called the immediate window, is another good
resource. When you are executing code, you can query the value of any
variable, field, control, ...

? pSQL
and then press ENTER

You can also use the debug window to get help on a topic -- type or
paste a keyword into the window and press F1

'~~~~~~~~~ Compile ~~~~~~~~~

Whenever you change code or references, your should always compile
before executing.

from the menu in a module window: Debug, Compile

fix any errors on the yellow highlighted lines

keep compiling until nothing happens (this is good!)

'~~~~~~~~~~~~~~

Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*

 

[Dave]

strive4peace:

That works perfectly!

Thanks so much for your help.

Dave

 

[strive4peace]

you're welcome, Dave happy to help

Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*