You can use the GotFocus event of the combo to rebuild the SQL for the
RowSource to include criteria
Then, in the LostFocus event for the combo, rebuild the SQL for the
RowSource to show everything
here is an example you can pattern after:
'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
limit the combobox to specific records when it gets the focus, show all
records when you leave it
on the gotFocus event of the combobox, assign this:
=SetRowSource(true)
on the lostFocus event of the combobox, assign this:
=SetRowSource(false)
put this code behind the form/subform with the combobox -- and compile
it before testing
'~~~~~~~~~~~
private function SetRowSource( _
pBooCriteria as boolean)
on error goto Err_proc
dim strSQL as string
strSQL = "SELECT SomeID, SomeName" _
& " FROM Tablename"
if pBooCriteria then
strSQL = strSQL _
& " WHERE (Active_YN = true)"
end if
strSQL = strSQL & "ORDER BY SomeName;"
debug.print strSQL
me.combobox_controlname.RowSource = strSQL
me.combobox_controlname.Requery
Exit_proc:
exit function
Err_proc:
msgbox err.description,, _
"ERROR " & err.number & " SetRowSource"
'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
'~~~~~~~~~~~~~~
Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*
