Developer Forum

Hi George,


limit the combobox to specific records when it gets the
focus, show all records when you leave it

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

=SetRowSource(true)


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

=SetRowSource(false)


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

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

private function SetRowSource(pBooCriteria as boolean)

on error goto Err_proc

dim s as string, mRecordID as long

s = "SELECT StoreID, StoreName " _
& " FROM Stores"

if pBooCriteria then
mRecordID = nz(me.parent.RecordID_controlname )
if mRecordID <> 0 then
s = s & " WHERE (RecordID_fieldname =" _
& mRecordID & ") "
end if
end if

s = s & "ORDER BY StoreName;"

debug.print s

me.combobox_controlname.RowSource = s
me.combobox_controlname.Requery

exit function

Err_proc:
msgbox err.description,, _
"ERROR " & err.number & " SetRowSource"
'press F8 to step through code and fix problem
Stop
Resume
End function

'~~~~~~~~

Warm Regards,
Crystal
Microsoft Access MVP 2006

*
Have an awesome day ;)

remote programming and training
strive4peace2006 at yahoo.com

*