Look Up from one form to another

[Jim Normile]

(1) I have a table with approx 1000 clients, each with an autonumber primary
key. A simple form is used to look at, enter and update records in the client
table. Call it "Form A."

(2) I have a second table which lists transaction information for some of
the clients, and contains a look up field to link to a client when necessary.
EAchrecord carries its own autonumber , which is also the primary key. So
this is a related table.

(3) I have a query, including both tables , which shows related information
from both the client and the transaction tables. A form is built on this
query, which can view the related info for a client and is capable of
adjusting information in either table. Call the second form "Form B".

(4) My problem is as follows:

If I am looking at a record in form A, and I want to see the associated
record in Form B, how do I set up a button to jump me to the linked record in
Form B??. I have tried adding Buttons with the wizard, and writing a piece of
VB Code to do the job, but my code writing skills obviously arent up to
speed. Would appreciate any help

Jim Dublin

 

[Sprinks]

Hi, Jim.

Assuming the second table has a Number type field that contains the ClientID
defined in table 1, something like this ought to do it:

Private Sub cmdOpenFormB_Click()
On Error GoTo Err_cmdOpenFormB_Click

Dim stDocName As String
Dim stLinkCriteria As String

' The name of the form to open
stDocName = "FormB"

' A string representing a WHERE clause without the word WHERE
' See VBA Help on the OpenForm method for further explanation
stLinkCriteria = "[ClientID]=" & Me![YourClientIDControlOnFormA]

' Open the form with the specified criteria
DoCmd.OpenForm stDocName, , , stLinkCriteria

' Exit the subroutine
Exit_cmdOpenFormB_Click:
Exit Sub

' Handle and display any errors in a message box
Err_cmdOpenFormB_Click:
MsgBox Err.Description
Resume Exit_cmdOpenDistributorForm_Click

End Sub

HTH
Kevin Sprinkel