Forms: linking child/master fields on a non-embedded subform

[DMcDaniel]

So super easy to link more than one field between a child and master when you
embed a subform within a form....
But anybody out there know how to string link more than one field (ie:
certificate type and certificate ID) between a master and child form; when
opening a subform through a 'command' button and utlizing the command 'open
form'?
.....all efforts only allow me link between one field....not both.

driving me nutz, a savior of sanity would leave me forever in your debt!

 

[Jeanette Cunningham]

Hi D,
if you are using open form, then why bother about the link master and child
fields?
Put code in the OpenForm to open with the correct link.

Dim strWhere As String
strWhere = "[CertificateType] = " & Me.CertificateType & " And
[CertificateID] = " & Me.CertificateID

DoCmd.OpenForm FormName, , , strWhere


If either fields are text type, use 3 double quotes instead of one double
quote for that particular field.


Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

 

[Ken Sheridan]

Also put the following in the Current event procedure of the first form to
keep the second form in sync should the record pointer in the first form be
moved while the second form is open:

Dim frm As Form
Dim strFilter As String

On Error Resume Next
Set frm = Forms("NameOfSecondForm")

If Err.Number = 0 Then
strFilter= "[Certificate Type] = " & _
Nz(Me.[Certificate Type],0) & _
" And [Certificate ID] = " & Nz([Me.Certificate ID],0)

frm.Filter = strFilter
End If


If Certificate Type is a text data type use:

strFilter= "[Certificate Type] = """ & _
Me.[Certificate Type] & _
""" And [Certificate ID] = " & Nz(Me.[Certificate ID],0)

If Certificate ID is also text data type use:

strFilter= "[Certificate Type] = """ & _
Me.[Certificate Type] & _
""" And [Certificate ID] = """ & Me.[Certificate ID] & """"

The Nz function is called in the first example as if the data type is a
number and the first form is moved to an empty new record then the values
will be Null, so a zero is substituted to prevent the string expression
evaluating incorrectly. Where the data type is text this is unnecessary as
the delimiting quotes characters ensure that a zero-length string is included
in the expression rather than a Null.

Ken Sheridan
Stafford, England

 

[Bob Quintal]

So super easy to link more than one field between a child and
master when you embed a subform within a form....
But anybody out there know how to string link more than one field
(ie: certificate type and certificate ID) between a master and
child form; when opening a subform through a 'command' button and
utlizing the command 'open form'?
....all efforts only allow me link between one field....not both.


driving me nutz, a savior of sanity would leave me forever in your
debt!

Not too difficult with Access <=2003.I've never played with 2007 so
no guarantees.

When you open the Command Click event, you should see some code
resembling
'...........................
Private Sub Command8_Click()
On Error GoTo Err_Command8_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "Certificates"

stLinkCriteria = "[Certificate ID]=" & Me![Certificate ID]
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_Command8_Click:
Exit Sub

Err_Command8_Click:
MsgBox Err.Description
Resume Exit_Command8_Click

End Sub
'...........................

All that you need to change is the line that begins with
stLinkCriteria =
so that you have the second field as well, so
"[CertificateI D]=" & Me![Certificate ID] becomes
"[Certificate ID]=" & Me![Certificate ID] & " AND [Certificate type]
" & me.[certificate type]
'That's all on 1 line.

Note that if your type or id is string and not numeric as in my
example, you'll need extra quotes
"[Certificate ID]=""" & Me![Certificate ID] & """ AND [Certificate
type] """ & me.[certificate type] & """"

 

[DMcDaniel]

Jeanette, Ken, Bob,

a.) ...I've got to go back to class

b.) much ALOoooooooooooooooHA, and many, many, mahalo's (thanks) for your
help with this one!

final event procedure (for the simplistic purposes of this MDB):

stLinkCriteria = "[Certificate_ID]=" & Me![Certificate_ID] & " AND
[Certificate_Type] = """ & Me.[Certificate_Type] & """"

....it was that darn """ on my [Cert_Type], as it was text!

you all are the best!