Look up a related record in a second form

  • Thread starter Thread starter Jim Dublin
  • Start date Start date
J

Jim Dublin

I have a "Clients" table with about 1000 records. Each
record has an autonumber ID which is also the primary key.
I have built a simple "Clients" form on the table for
normal viewing and updating. Call this form "Form A"

I have a second "Transactions" table which records
specific transactions with the clients in the first table.
Each transaction has an autonumber ID/Primary key. In
addition it has a lookup field which looks up the ID of
the Client from the "Client" Table, and creates a
relationship with the Client Table.

I have a Query which includes both of the above related
tables, showing required information from the "Client"
Side and from the "Transaction side". On top of this query
I have built a second form, which allows me to view/update
individual client and transaction details. Call this
form "Form B.

When I look at Client records in Form A, I can see if
there are associated records in the Transaction table,
through a small subform. I need to be able to click a
button on form A which will open Form B, and take me to
the related transaction record on Form B.

Using the form wizard I can easily open Form B, but I have
not been able to automatically go to the correct linked
record in form B. I expect I need to write a piece of VBA
code for the "On Click" event on the button on form A, but
I haven't managed to write the correct code.

Can anybody out there advise me:
(1) Is VBA code attached to the "On-Click" event on
the button on form A the correct approach??........IF not,
what is the correct approach??
(2) Can you help me with the code please ??

My thanks in anticipation


Jim Dublin
03 November 2004
 
Using the form wizard I can easily open Form B, but I have
not been able to automatically go to the correct linked
record in form B. I expect I need to write a piece of VBA
code for the "On Click" event on the button on form A, but
I haven't managed to write the correct code.
Click to expand...

Why not put FormB on FormA as a Subform? No code needed at all; just
use the ClientID as the master/child link field, and it will keep the
two tables in synch.

If you don't have enough screen space, put a Tab Control on the form.
Copy and paste (*don't* drag, it won't work) all your existing
controls onto the first tab page, and put the Subform on the second.

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps
 
Is this the same thread that has been going on for some time where the
developer wants to open FormB, select a record and have the Parent
FormA find the related record?

If it is, the thread went on for days telling the questioner that the
solution lay in a different direction ... that of opening FormA and
having the SubformB display the detailed Many records. We never got a
critical response from the questioner

Brett

Why not put FormB on FormA as a Subform? No code needed at all; just
use the ClientID as the master/child link field, and it will keep the
two tables in synch.

If you don't have enough screen space, put a Tab Control on the form.
Copy and paste (*don't* drag, it won't work) all your existing
controls onto the first tab page, and put the Subform on the second.

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps
Click to expand...

Cheers,
Brett
 
Back
Top