question on accessing data using table relationships

[Brad Pears]

I have two tables that contain a CustomerID field. One of the tables (Table
A) contains the Customers name and address etc.. info - the other table
(Table B) simply carries the customer ID as well as other data...

Is there a better way to display the new customer name info from Table A
when a user selects a different record in Table B than what I am doing right
now which is using a function call (I pass in the customerID) which then
uses SQL to get the record from the table and returns the name)??

With relationships set up is there not a way that as the TABLE B pointer
changes, it also changes the pointer in TABLE A so that you can access the
correct table A data without having to run a function??

I used to program in Dbase/Clipper years ago, and they had that capability.
It was really slick ... As one record pointer moved, so did the associated
"related" record pointer...

Does anyone have any suggestions?

Thanks,

Brad

 

[Rob Oldfield]

You mean that you want, as you view records from Table B on a form, that it
also displays the associated Table A record? If that's right then you just
need to set up a standard Form/Subform setup... just with the tables the
other way around from the usual. The main form is based on Table B, the
subform is based on Table A, and the master/child fields are the your FK/PK.

 

[Brad Pears]

Yes, correct, I never even thought of that... I could add the subform and
hide it because I want that cust data to be displayed inside of a completely
different subform... But that would allow me to directly access it ...

Thanks for that!

 

[Albert D. Kallal]

Yes, correct, I never even thought of that... I could add the subform and
hide it because I want that cust data to be displayed inside of a
completely different subform... But that would allow me to directly
access it ...

You can always disable the sub form allow edits. I have some screen shots,
and show how this can be done here:

http://www.members.shaw.ca/AlbertKallal/Articles/fog0000000005.html

And, if you remeber the "browse" command in FoxPro, then you will like the
follwing screen shots of grids in ms-access:

http://www.members.shaw.ca/AlbertKallal/Articles/Grid.htm

 

[Vincent Verheul]

Brad,

You can create a query that joins table B with table A via the customerID
(Select * from B and just add the CustName field from A). Use this query as
the basis for your form that will edit records of table B. You now simply
add the CustName field onto your form. Preferably make the name read only
(lock it on the form).

Vincent