Look up a related record in a second form

[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

 

[John Vinson]

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.

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

 

[Brett Collings [429338]]

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

Cheers,
Brett