Open form based on subform record

[JoeM]

I have written a macro to open a new form when the primary
key field of a subform is double clicked. The new form
would be based upon the primary key selection.
Unfortunately, I cannot get this to work as the system
does not regnize the subform record selection and filter.
Any help would be appreciated.

 

[David Ehrenreich]

I have a similar setup. This is how I did it. I used
social security numbers to bring the records up, but I am
about to change to an auto number. Anyway. First I set my
tables like this:
Tables

IDtbl:
Key
LookupID

InfoTbl:
ID (Key Feild)
Fname
Lname
Date
Time
Etc.

Queries

lookupQry:
ID
Fname
Lname

DataEntryQry
Fname
Lname
Date
Time
etc.

Now I made a form based on the IDtbl (just the LookupID
feild) We will call this form Main

Now make unbound subform If the wizard comes up just
cancel. Now goto the subform properties and make the
control source LookupQry. Now add the lookup feilds make
sure you add the ID feild. This subform will let you see
the records in the table.

Now make another subform this time go through with
wizard. You will click on - Use exsisting Tables and
queries. The Select the DataEntryQry. Select all the
feilds(Making sure ID is selected. Then hit next. Now in
this screen use the pulldown on the left side and select
LookupID then on the right side select ID. Hit next and
name the subform and hit next. This is how it should work
you will see current records in that unbound subform.
Enter an ID in to the LookupID feild(Getting the number
form the unbound subform). Now what ever record belongs
to that ID appears in the Data Entry Subform. Here are
some suggestion the unbound subform wont refresh
automatically so creat a button to refresh the data.
Also, you will want another form for inputing the data.
I would base that form off the infoTbl or A qry based off
the infoTbl. You shou be able to complete this with no VB
skills. You will need to learn some VB to customize the
forms through. Good Luck.

Sorry if this doesnt make sense.

Also, for each feild in the LookupQry Subform make this
the On Click expression:

Private Sub Fname_Click()
Me.Parent.LookupID = Me.LookupID


End Sub

Private Sub Lname_Click()
Me.Parent.LookupID = Me.LookupID


End Sub

Etc. Do this for the rest of your feilds in the LookupQry
Subform


David Ehrenreich