Two tables link on a form

[chessico]

I’m struggling to build an expression to link fields from two different tables via a form.
I have a membership form frmMember which displays and updates all data entered storing it in a table tblMembers.
In the tblMembers I have a field ‘LoyaltyID
My problem is I want to add a text field to the membership form by allowing the user to add the number of a loyalty card which will then be stored in a different table tbl LoyaltyCard. Also if the record is retrieved then the number should be saved and shown. some members may nnot yet qualify for this Loyalty card.
When I use properties I cannot obviously find the fields from the loyalty card because the main form source is the tblMembers.
I’ve tried different code and queries but get various errors from the text box basically saying cannot find the object.
I need to be able to enter a number – possibly with zeros to start i.e 000123 which will then be linked to the corresponding MemberID.
The Tables and their attributes are
tblMembers contains MemberID as the key field (autonumber) and LoyaltyID as field
tblLoyaltyCard contains LoyaltyID (autonumber), LNumber (number) to be entered from the membership form.
Any advice would be appreciated Thanks in advance.

[alansidman]

Why do you need to have the loyalty card number in a separate table. Since it only holds the Loyalty ID and the LNumber, why not just put the LNumber in your tblMembers table.

[June7]

Do what Alan suggests unless members can have more than one loyalty card, in which case you need the memberID in the loyalty table. Use a subform to enter the loyalty record.

[chessico]

Doh! I didn't think of the simple way! - does make sense I suppose to do away with LoyaltyId and just have a field LNumber in tblMembers. However its early days and we don't know how much we are going to do with the loyalty card. presently it indicates a discount for a paid up member and members can add money to the card and use the card to buy drinks- hence doing away with cash. however I have to assume people will lose cards or they may become warn and need replacing and I am unsure what the EPOS till system which reads the loyalty card will do regards replacing lost/defaced cards. Also I don't know yet what else maybe needed in the tblLoyatlyCard. This was only a tentative stab at establishing another procedure to the DB. So as you say I will start by doing as you suggested. Secondly I already have a subform on this Members form which is set to accept payments -so I'm running out of space hence I only wanted to add a field for loyalty entry. And more to the point I really would like to know CAN it be done. Someone must have the ability to devise this code! thanks anyway to both of you.

[June7]

You could use a tab control to expand the form 'real estate'. Put subform on each page of tab control.

The loyalty card table is useless without the member ID as foreign key.

[chessico]

Yes it is except I had initially put the LoyaltyID as the foreign key in tblMembers to link the tblLoyaltyCard to the tblMembers. The club has a family membership category and to save further alterations and complications I chose to have the tblLoyaltyCard incase the club decide to issue two loyalty cards to a family membership- i.e one for husband and one for wife. and yes they could be same loyalty card numbers - as yet I don't know if this is possible so I'm assuming that partners will each get their own loyalty card complete with capability of adding their own individual amounts of cash! I've also discovered over a period of time how many families change partners but because their child who plays rugby for the club retains the same biological parents then partners or ex partners remain tied to the same membership for the sake of the child If you think about supermarket loyalty cards you can add more than one card to a members account and these may contain different loyalty numbers. Sounds complicated but DB built up over a long period and adapted to meet differing situations. So yes its getting to the point it will be easier to rebuild, knowing the problems in hindsight, or were not evident or even existed when the DB was first built. So back to your suggestion I already have a second tab on the form to take the details of the partner of the member who holds the membership, and details of all the children who also play rugby or may want to attend club on a social level. So I'm trying to stop problems before they occur by using a table instead of adding field to membership table. I already have 2 tabs and 2 subforms on the membership form so its complicated enough as it is. Hence my plea for a simple txt box on the membership form.

[June7]

I had initially put the LoyaltyID as the foreign key in tblMembers to link the tblLoyaltyCard to the tblMembers

That arrangement sounds backwards unless the tables are a one-to-one relationship or the card number is not unique in the card table.

Either have the loyalty card number in members table or have a child table that allows multiple loyalty cards and use the member ID as foreign key in the loyalty card table. Those are the options I see. Regardless, if you put card info in a separate table, use a subform to create/edit card record. Surely the card table has more info about the card than just card number?

A textbox could be used like you want then need code that will save record to the card table. This will be a lot trickier than a subform.