Help with List box and subform

  • Thread starter Thread starter Al
  • Start date Start date
A

Al

How can I make a sub form go a record based on the selection made in a list
box in the main form?
thanks
 
The simplest way would be to bind your subform to the mainform using the Link
Child and Link Master fields on the subform controls data property tab. If
that won't work for you, for some reason (if for example the subform is
already bound by some other field, and you are using the listbox to expand on
that feature), try:

Private Sub lst_Test_Click()

Dim rs As dao.Recordset
Dim frm As Form

Set frm = Me.sub_MySub.Form
Set rs = frm.RecordsetClone

rs.FindFirst "[Numbers] = " & Me.lst_Test.Value
If Not rs.NoMatch Then frm.Bookmark = rs.Bookmark

rs.Close
Set rs = Nothing

End Sub

Note:
1. the "sub_MySub" text on the line that starts "Set frm = " should be
replaced by the name of the subform control on your main form, not the name
of the form being used as the subform.
2. "lst_Test" should be replace with the name of the listbox control on
your form.

HTH
Dale
 
Al said:
How can I make a sub form go a record based on the selection made in a list
box in the main form?
Click to expand...


Here's one way to do that using the list box's AfterUpdate
event:

With Me.yoursubformcontrolname.Form.Recordset
.FindFirst "[your PK field] = " & Me.[thelistboxname]
End With
 
Marshall,

If you "know for certain" that there is a record in the subform that matches
the value you are looking for this works great.

But if you are not sure, and the FindFirst method doesn't find the value you
are looking for, it moves the record pointer off of the record that was
currently selected in the subform to the first record in the subform.

I'm not sure I would want that result.

Dale
--
Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.



Marshall Barton said:
Al said:
How can I make a sub form go a record based on the selection made in a list
box in the main form?
Click to expand...


Here's one way to do that using the list box's AfterUpdate
event:

With Me.yoursubformcontrolname.Form.Recordset
.FindFirst "[your PK field] = " & Me.[thelistboxname]
End With
Click to expand...
 
Thank you, both ways work
Al

Marshall Barton said:
Al said:
How can I make a sub form go a record based on the selection made in a list
box in the main form?
Click to expand...


Here's one way to do that using the list box's AfterUpdate
event:

With Me.yoursubformcontrolname.Form.Recordset
.FindFirst "[your PK field] = " & Me.[thelistboxname]
End With
Click to expand...
 
Thanks Dale

Dale Fye said:
Marshall,

If you "know for certain" that there is a record in the subform that matches
the value you are looking for this works great.

But if you are not sure, and the FindFirst method doesn't find the value you
are looking for, it moves the record pointer off of the record that was
currently selected in the subform to the first record in the subform.

I'm not sure I would want that result.

Dale
--
Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.



Marshall Barton said:
Al said:
How can I make a sub form go a record based on the selection made in a list
box in the main form?
Click to expand...


Here's one way to do that using the list box's AfterUpdate
event:

With Me.yoursubformcontrolname.Form.Recordset
.FindFirst "[your PK field] = " & Me.[thelistboxname]
End With
Click to expand...
Click to expand...
 
Dale said:
Marshall,

If you "know for certain" that there is a record in the subform that matches
the value you are looking for this works great.

But if you are not sure, and the FindFirst method doesn't find the value you
are looking for, it moves the record pointer off of the record that was
currently selected in the subform to the first record in the subform.

I'm not sure I would want that result.
Click to expand...


You're right, Dale.

I don't think the issue will arise in this case, but safe is
better than sorry.
 
Back
Top