Using combo box to select record source

[Guest]

Using Accss 2003, i know you can use the combo box wizard to select a record
source after the combo has been updated. when i use the wizard i get the
following code.

Private Sub Combo37_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[Employee ID] = " & Str(Nz(Me![Combo37], 0))
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub

the form where i placed the combo box is called "form2"

what i have done is placed form2 as a subfrm in my main form form1. (Form1
is the main form and Form2 is a subfrm of form1) The combo box Combo37 is
moved from the subform form2 to the main form form 1. i have a cmdbutton
button on form1 named "cmd1"

When i open form 1, form 2 is displayed showing the first record. what i
want to be able to do is select a record from combo37 on form 1 and then
click the cmdbutton also on form1, then form2 would change to show new
recordset.

Any ideas please anyone.

 

[Graham Mandeno]

First, to clear up some terminology, the "record source" is the query that
defines ALL the records in a form. I think you mean "select a record".

Your code below is using the keyword "Me", which is a reference to the form
which contains the code that is running (in this case Form2).

If you move the code to Form1, then "Me" will refer to Form1, not Form2. So
you need to get a reference to Form2 and use that instead of "Me".

A subform's Form object is referenced via the Form property of the subform
control, so you can do this:

Dim frm2 as Form
Set frm2 = Me![name of subform control].Form

Then replace every other instance of "Me" with "frm2"
Set rs = frm2.RecordsetClone
.... etc