×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Linking forms using multiple fields

Linking forms using multiple fields

Linking forms using multiple fields

(OP)
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.

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.

The onclick event procedure is

Private Sub Visits_Click()
On Error GoTo Err_Visits_Click

    Dim stDocName As String
    Dim stLinkCriteria As String

    stDocName = "Visits"
    
    stLinkCriteria = "[Client ID]=" & "'" & Me![Client ID] & "'"
    DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_Visits_Click:
    Exit Sub

Err_Visits_Click:
    MsgBox Err.Description
    Resume Exit_Visits_Click
    
End Sub

Can I add the other fields somehow into the stLinkCriteria.

I have tried several ways but get Type Mismatch and other error messages.

Please Help!

RE: Linking forms using multiple fields

Is this your scenario?
----------
Location - Text Field
Year - Text Field
Client ID - Number or Autonumber Field

If so use this:

stLinkCriteria = "[Location] = '" & Me!Location & "' AND [Year] = '" & Me!Year & "' AND [Client ID]= " & Me![Client ID]


If the fields are not as listed above, let us know what they are.
    

Jim Lunde
compugeeks@hotmail.com
CompuGEEKS
Custom Application Development

RE: Linking forms using multiple fields

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 = "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

RE: Linking forms using multiple fields

Try this:

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

Jim Lunde
compugeeks@hotmail.com
CompuGEEKS
Custom Application Development

RE: Linking forms using multiple fields

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

Help!!

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login


Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close