Linking forms using multiple fields

[MickO]

Hi, I am developing a client database that starts on a client form that has a command button to update client visits. The wizard only lets me link the forms with one field for matching.<br><br>I have a client number consisting of 3 fields: Location, year and client ID. Need to link the forms with all three to show the correct visit info for clients.<br><br>The onclick event procedure is<br><br>Private Sub Visits_Click()<br>On Error GoTo Err_Visits_Click<br><br>&nbsp;&nbsp;&nbsp;&nbsp;Dim stDocName As String<br>&nbsp;&nbsp;&nbsp;&nbsp;Dim stLinkCriteria As String<br><br>&nbsp;&nbsp;&nbsp;&nbsp;stDocName = &quot;Visits&quot;<br>&nbsp;&nbsp;&nbsp;&nbsp;<br>&nbsp;&nbsp;&nbsp;&nbsp;stLinkCriteria = &quot;[Client ID]=&quot; & &quot;'&quot; & Me![Client ID] & &quot;'&quot;<br>&nbsp;&nbsp;&nbsp;&nbsp;DoCmd.OpenForm stDocName, , , stLinkCriteria<br><br>Exit_Visits_Click:<br>&nbsp;&nbsp;&nbsp;&nbsp;Exit Sub<br><br>Err_Visits_Click:<br>&nbsp;&nbsp;&nbsp;&nbsp;MsgBox Err.Description<br>&nbsp;&nbsp;&nbsp;&nbsp;Resume Exit_Visits_Click<br>&nbsp;&nbsp;&nbsp;&nbsp;<br>End Sub<br><br>Can I add the other fields somehow into the stLinkCriteria.<br><br>I have tried several ways but get Type Mismatch and other error messages.<br><br>Please Help!

 

[jimmythegeek]

Is this your scenario?<br>----------<br>Location - Text Field<br>Year - Text Field<br>Client ID - Number or Autonumber Field<br><br>If so use this:<br><br><b>stLinkCriteria = &quot;[Location] = '&quot; & Me!Location & &quot;' AND [Year] = '&quot; & Me!Year & &quot;' AND [Client ID]= &quot; & Me![Client ID]</b><br><br><br>If the fields are not as listed above, let us know what they are.<br>&nbsp;&nbsp;&nbsp;&nbsp; <p>Jim Lunde<br><a href=mailto:compugeeks@hotmail.com>compugeeks@hotmail.com</a><br><a href=

http://www.cgeek.com>CompuGEEKS</a><br>Custom

Application Development

 

[NanciMc]

I used this procedure 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 = &quot;frmSite&quot;

stLinkCriteria = &quot;[SITECMF]= '&quot; & Me!SITECMF& &quot;'AND [CONTRACT]=&quot;& 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]

 

[jimmythegeek]

Try this:

stLinkCriteria = &quot;((([SITECMF])= '&quot; & Me!SITECMF & &quot;') AND (([CONTRACT])= '&quot; & Me![CONTRACT] & &quot;'))&quot;

[sig]<p>Jim Lunde<br><a href=mailto:compugeeks@hotmail.com>compugeeks@hotmail.com</a><br><a href=

http://www.cgeek.com>CompuGEEKS</a><br>Custom

Application Development[/sig]

 

[NanciMc]

Hi,
I got an error:
The OpenForm Action was cancelled

Help!! [sig][/sig]