MS Access Listbox won't synch

[Guest]

I have a subform called frmVisits that displays the details of a visit. On
the form I have a listbox that displays the date and type of visit. I want
the user to be able to dblclck on a particular record in the listbox and then
have the frmVisits display the visit details. both the form and the listbox
are based on the same query.
Pls help

 

[Rob Parker]

There's several ways this could be done. One of the simplest (since both
the list box and the subform have the same recordsource) is to simply filter
the subform by the selected item in the list box.

Private Sub ListBoxName_DblClick(Cancel As Integer)
Me.SubformControlName.Form.Filter = "KeyField = " & Me.ListBoxName.Value
Me.SubformControlName.Form.FilterOn = True
End Sub

This assumes that your keyfield is a number; if it's text, you'll need
string delimiters, thus:
Me.SubformControlName.Form.Filter = "KeyField = """ & Me.ListBoxName.Value
& """"
(using a pair of double-quote characters will prevent this failing if the
text string contains a single-quote (apostrophe) character).

Substitute the correct names for your listbox, subform control, and
keyfield. Make sure that the listbox's bound field is the keyfield, display
whichever field(s) you want, and set the list-box's Multi Select property to
None.

HTH,

Rob

 

[Guest]

thanks for your response, I'm not sure what you mean by the following;
Me.SubformControlName. What do you mean when you say control? (field, form?)

Thanks for your help

RobUCSD

 

[Rob Parker]

A control is an object on a form (or report). Forms do not contain fields -
the content of a field (in the form's recordset) is displayed, and can be
entered/edited, in a control such as a textbox or combobox or ... which is
bound to that field. Forms also do not contain other forms - they contain
subform controls; the subform control is the container for a form. Your
subform control may have the same name as the form which it contains, or it
may have a completely different name (eg "Child3") - it will initially
depend on how you created the subform control on your form.

The important thing in the syntax I gave is to use the subform control name,
and not the name of the form which the subform contains (if they are
different).

HTH,

Rob

 

[Guest]

Here's what I tried. I get a Compile Error Msg, "Method or data Member not
found"

Private Sub List101_DblClick(Cancel As Integer)

Me.frmVisitNewEdit.Form.Filter = "VisitNo = " & Me.List101.Value
Me.frmVisitNewEdit.Form.FilterOn = True
End Sub

frmVisitNewEdit is the subform on which the listbox is located and that also
displays the details of the visit.

I'm not sure what's wrong

RobUCSD

 

[Rob Parker]

Sorry, I got the wrong impression of your set-up when I replied - I took it
that the listbox was on the main form, rather than on the subform. If the
listbox is on the subform then all you need is:

Private Sub List101_DblClick(Cancel As Integer)
Me.Filter = "VisitNo = " & Me.List101.Value
Me.FilterOn = True
End Sub

Rob

 

[Guest]

Rob, thanks it finally works. Hope you have a great day down under and if you
surf I hope you score.

Thanks again, RobUCSD