Display Specific Form and Subform Record

[bgreenspan]

I need some help, please...

I have constructed a series of dynamic queries (query by form) to
identify specific records in my one-to-many-to-many database. The
sequential queries successfully eliminate duplicates and I can display
the uniques results on a form. What I want to do is extract
identifiers for a record and use them to open the record in my original
data input form.

Some pertinent naming:
Tables:
tblDockets > tblFamilyMembers > tblActions
1:Many 1:Many
Keys:
docketRecID FamilyRecID ActionRecID

My query construction was solved with the help of several of you kind
experts and I have displayed the results on a form that looks like my
input form but because it is linked to the successive queries, it fails
to update the data tables when changed.

I have a an EDIT button that loads the input for the resulting
DocketRecID into my input form

myrec = "[DocketRecID]= " & DocketRecID
DoCmd.OpenForm "frmMainInput", acNormal, , myrec

and it works fine, displaying the Docket selected in the query.

On the input form, FamilyMember information is handled with a subform
(which functions very well during input). It is Named
subfrmFamilyMembers and its Source is frmFamilyMembers

As it stands now, I must scroll thru the records in the subform to find
the one I want despite knowing the value of the unique FamilyRecID

What I would like is for the subform to go to the specific Family
record identified in my qurey according to FamilyRecID

How can I do this? THANK YOU VERY MUCH in advance.

- Bernie

(extra note: The subform frmFamilyMembers contains a subform within it
to display the related entries in tblActions. This is why the
subfrmFamilyMembers is named annd sourced as described above)

 

[Albert D. Kallal]

myrec = "[DocketRecID]= " & DocketRecID

DoCmd.OpenForm "frmMainInput", acNormal, , myrec

Perfectly clear...

But, when, or where, or how do you ask, or get the FamilyRecID??

Were does this value come from? I assume that the main docketrecid form
opens, and you
have a nice neat sub-form that lists all of the Families that belong to this
docket....

You could restricted the sub-form to that main input family id (if the main
form does have a family id).

for the child link/master fields, you can define more then one link field,
just separate them with a ;

It is not clear if you want to the sub-form to display all of the
entries..but just have the cursor on the family id, or
you just want the sub-form to ONLY display the one family id?

 

[bgreenspan]

OKAY... Maybe I didn't search hard enough for the answer before asking.
I finally found a snippet in a 2003 posting to this group which
worked. (Thanks to Dirk Goldgar and Nick Mirro)

Here's my code with hopes it may help someone else. Comments on
improving it are greatly appreciated actually a little
explanation for me on what the .bookmark lines are doing would help me
in the future.

======
Private Sub cmdEditRecord_Click()
On Error GoTo Err_cmdEditRecord_Click

Dim myrec As String ' specific query string for the Docket and
Family ID
Dim myrec2 As String
Dim frm As Form

'for diagnostics prove that I have the proper info... Comment out later
prompt = MsgBox("DocketID is " & DocketRecID & " Family RecID No
is " & eFamilyRecID, vbOKOnly)

'Capture the record identifiers in strings
myrec = "[DocketRecID]= " & DocketRecID
myrec2 = "[FamilyRecID] = " & FamilyRecID

' Close the search results form.
DoCmd.Close acForm, "qryDynamic_Docket", acSaveYes

'Open the Input form to edit the selected data
DoCmd.OpenForm "frmMainInput", acNormal, , myrec

'Address the subform
Set frm = Forms!frmMainInput!subfrmFamilyMembers.Form

'Locate and display the desired record
With frm.RecordsetClone
.FindFirst myrec2
If Not .NoMatch Then
frm.Bookmark = .Bookmark
End If
End With

Set frm = Nothing

Exit_cmdEditRecord_Click:
Exit Sub

Err_cmdEditRecord_Click:
MsgBox Err.Description
Resume Exit_cmdEditRecord_Click

End Sub
============

 

[bgreenspan]

Hi Albert,

I just posted some code I found that makes it work.
To answer one of your questions, FamilyRecID comes from the same place
as DocketRecID. I have no trouble cpaturing and using the values.

The code I posted (probably below your message) loads ALL of the family
records with the desired one displayed. Your raise an interesting
possibility (and a desiable one) that ONLY the speific record can be
made available in the subform. How can I modify my code to do that?
This could keep users from moving about when editing the records.

Thanks!

myrec = "[DocketRecID]= " & DocketRecID
DoCmd.OpenForm "frmMainInput", acNormal, , myrec

Perfectly clear...

But, when, or where, or how do you ask, or get the FamilyRecID?? ..
..
..
It is not clear if you want to the sub-form to display all of the
entries..but just have the cursor on the family id, or
you just want the sub-form to ONLY display the one family id?

 

[Albert D. Kallal]

The code I posted (probably below your message) loads ALL of the family
records with the desired one displayed. Your raise an interesting
possibility (and a desiable one) that ONLY the speific record can be
made available in the subform.

Well, in fact, perhaps you don't even need to show the docket form..but jump
right to the
FamilyRecID in tblFamilyMembers ???

This would suggest that you don't use a docket form + sub-form..but jump
right to the tblFamilymemebers form...

How can I modify my code to do that?
This could keep users from moving about when editing the records.

Well, simply modify the link master/child field setting for the sub-form
(you do have that now..right??).

For link fields in the sub-form control, you likely have

master field: DocketRecID
child field : DocketRecID

change above to

master field: DocketRecID;FamilyRecID
child field : DocketRecID;FamilyRecID

You could also consider a design where you display all of the family for the
docket...but "move/place" the cursor on the
correct record in the sub-form.

regardless of the above two choices, you could also place a button on the
sub-form to open up the family member edit form.

If you look at the screen shots here...you can see that I OFTEN put a button
on the continuous form to open up the details...

http://www.members.shaw.ca/AlbertKallal/Articles/Grid.htm
(scroll down a bit.....to see some more ideas in the above).

The code behind those buttons is just like always...such as

docmd.openform "formdetails",,,"id = " & me!id

(you would change "id" in above to whatever the pk used).

 

[bgreenspan]

Thanks for the link to your website and for the ideas. It will take me
some time to go through the myriad of possibilities. I appreciate your
help.

- Bernie