Using Listbox value as a form or query filter

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

Guest

Hi. I have a form displaying a list of client details, at the moment, if I
want to move from the default client to a subsequent one, I have to use the
form navigation controls to scroll through them. I've added a subform that
lists all clients in the underlying tables (used in the query that controls
the form). I want to be able to highlight a client in the list box, and have
the form reopen or requery to show the client that I have highlighted.

But I cant get it to work!

Do I use the forms filter property somehow, or make a change to the
underlying query, to SELECT on the value of the listbox?
 
Let's say you have a control on the form for the user to select a piece of
data (a combobox or listbox). You can reference the value of the control in
a query's criteria to return results that contain that value via something
like:

[Forms]![FormName]![ComboBoxControl] - exact
Like "*" & [Forms]![FormName]![ComboBoxControl] & "*" - match similar

Then you can use the query to populate the form and when a different
selection is made, use the control's AfterUpdate event to run something like:

Me.Requery
 
I saw your posting looking for my own solution to a problem. Your idea of
having the subform to view the list of clients while having the main form
display a particular client's details is fine. I have several forms like this.

In the subform add this code - (modify ClientID to your field name)

Private Sub Form_Click()
Dim lngID As String
If IsNull(Me!ClientID) Then
Exit Sub
Else
lngID = Me!WkrID
Me.Parent!WkrID.SetFocus
DoCmd.FindRecord lngID
End If
End Sub

You will be able to click on arrow at the left side of the subform and your
main form will display that client and the rest of their details.
Hope that helps if you're still working on your database.


Agent Dagnamit said:
Thanks guys, I'm pretty new to this sort of thing, but I think I see where
I've gone wrong now. Also, I can see that learning more about recordsets
(than is in my "Dummies Guide to VBA) would be a great help!

kingston via AccessMonster.com said:
Let's say you have a control on the form for the user to select a piece of
data (a combobox or listbox). You can reference the value of the control in
a query's criteria to return results that contain that value via something
like:

[Forms]![FormName]![ComboBoxControl] - exact
Like "*" & [Forms]![FormName]![ComboBoxControl] & "*" - match similar

Then you can use the query to populate the form and when a different
selection is made, use the control's AfterUpdate event to run something like:

Me.Requery


Agent said:
Hi. I have a form displaying a list of client details, at the moment, if I
want to move from the default client to a subsequent one, I have to use the
form navigation controls to scroll through them. I've added a subform that
lists all clients in the underlying tables (used in the query that controls
the form). I want to be able to highlight a client in the list box, and have
the form reopen or requery to show the client that I have highlighted.

But I cant get it to work!

Do I use the forms filter property somehow, or make a change to the
underlying query, to SELECT on the value of the listbox?
Click to expand...
Click to expand...
Click to expand...
 
Back
Top