Filtered Control on a Continouous Form

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a filtered control on a continouous form where the condition for the
control is based off of a value (varies) from another field in the same row
of data. When the value for the filtered field is selected it is displayed
fine, but when the user goes to the next row of data to make a selection the
data from the previous row disappears unless the user places there cursor
back on that row.

Any suggestions on how to make the data display always without having your
cursor on that row of data only?

Thanks for any suggestions!

Sarah
 
Hi Sarah,

when you talk about a "filtered field" ... do you mean a combobox?

If so, here is a generic example that perhaps you can draw an analogy to:

on the gotFocus event of the Colllection combobox, assign this:
=SetCollectionSource(true)


on the lostFocus event of the Colllection combobox, assign this:
=SetCollectionSource(false)


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

'~~~~~~~~~~~~~~`
private function SetCollectionSource(pBooCriteria as boolean)

on error goto SetCollectionSource_error

dim s as string, mBrandID as long

s = "SELECT pkCollectionID, Collection, tkBrandID " _
& " FROM tblCollections "

if pBooCriteria then
mBrandID = nz(forms!frmNewBrands!txtPKBrandID)
if mBrandID <> 0 then
s = s & " WHERE (tkBrandID=" & mBrandID & ") "
end if
end if

s = s & "ORDER BY tblCollections.Collection;"

debug.print s

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

exit function

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


Warm Regards,
Crystal
*
(: have an awesome day :)
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*
 
you're welcome, Sarah ;) happy to help


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

sounds like the LostFocus event is not reassigning the combo to show
everything...

put a temporary label (which doesn't take the focus) on your form

use this for OnClick code

'~~~~~
MsgBox combo_controlname.rowsource
'~~~~~

it should show a filtered SQL statement when you are in the combo and an
SQL statement with no criteria when you are not...

to help you understand Access (and SQL statements) a bit better, send me
an email and request my 30-page Word document on Access Basics (for
Programming) -- it doesn't cover VBA, but prepares you for it because it
covers essentials in Access.

Be sure to put "Access Basics" in the subject line so that I see your
message...

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