×
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

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

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

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

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

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

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

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

(OP)

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

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

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

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

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

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