Look Up from one form to another

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

(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
 
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
 
Back
Top