Link Multiple fields of two forms cascade update a 2nd tables

[NanciMc]

I used the procedure shown in another thread and am receiving a Compile error. Syntax Error.
I am trying to link to forms, and two tables by 2 fields for which both are the primary key in the secondary table. I am also trying to accomplish cascading update to the 2nd table. The command button is currently only linked by the Contract field and isn't updating the SITECMF field. Consequently the second form is showing all Records for that contract.
I tried to change the VB code to account for both fields and am getting an error: Compile Error. Syntax Error.
My fields are
SITECMF - Text Field
CONTRACT - Number Field

Here is the code:
Private Sub SITEDET_CMD_Click()
On Error GoTo Err_SITEDET_CMD_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "frmSite"

stLinkCriteria = "[SITECMF]= '" & Me!SITECMF& "'AND [CONTRACT]="& Me![CONTRACT]
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_SITEDET_CMD_Click:
Exit Sub

Err_SITEDET_CMD_Click:
MsgBox Err.Description
Resume Exit_SITEDET_CMD_Click

End Sub

Help!
Nanci
[sig][/sig]

 

[Lightning]

Nanci

Try replacing the Me keyword with the the full form name

e.g.

stLinkCriteria = "[SITECMF]= " & Forms!YourForm!SITECMF & "AND [CONTRACT]= "& Forms!YourForm![CONTRACT]
    DoCmd.OpenForm stDocName, , , stLinkCriteria

Hope this helps

Lightning

[sig][/sig]

 

[NanciMc]

Do I want the form that the link is coming from? (frmContracts) or the link is going to? (frmSite)
I get the following error both ways:
Microsoft can't find the form 'frmSite' referred to in a macro expression or Visual Basic Code.

Microsoft can't find the form 'frmContracts' referred to in a macro expression or Visual Basic Code.


Here is my code now:
Private Sub SITEDET_CMD_Click()
On Error GoTo Err_SITEDET_CMD_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "frmSite"

stLinkCriteria = "SITECMF= '" & Forms!frmContracts!SITECMF& '" AND "[CONTRACT] = " & Forms!frmContracts![CONTRACT]
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_SITEDET_CMD_Click:
Exit Sub

Err_SITEDET_CMD_Click:
MsgBox Err.Description
Resume Exit_SITEDET_CMD_Click

End Sub

Thanks

[sig][/sig]

 

[Lightning]

Nanci

Try using this code

    Dim stDocName As String
    Dim stLinkCriteria As String
    
    stDocName = "frmSite"
    
    stLinkCriteria = "[Sitecmf]=" & "'" & Me![Sitecmf] & "'" 
    DoCmd.OpenForm stDocName, , , stLinkCriteria

I tried this in a very simple forms setup, and it does work. If it doesn't work for you, post your e-mail address and I'll send you the forms to play with.

Lightning
[sig][/sig]

 

[NanciMc]

Hi,
I agree that your code works, although the problem I'm having is that I need to include the Contract field as well. The Primary Key is the unique combination of both Contract and Site CMF. So your code only passes the Site CMF and not the Contract field.
My email address is gmeaa2@yahoo.com.
Help!
Thanks,
Nanci [sig][/sig]