Populating 2 tables the same time with a form

[WayneSteenkamp]

Is it possible to Auto Populate two tables with one form?


i.e If I enter a record, the record goes into two separate tables with the same headings

I thought a relationship did this but mine isn't working.

[John_G]

No, you can't do this. A control on a form can be bound to only one field in a table or (updateable) query. You would have to select one table to populate with the form, and populate the other with VBA. A Select query (which is the only kind that a form can have as a recordsource) cannot be used to add records.

Why would you need to do this at all, especially if the two tables have the same field names? That is not good database practice.

John

[WayneSteenkamp]

Hey John,

It's quite complicated in explaining it but i'll try.

Table (A) has a same Field as Table (B). i.e SimCardNumber
Table (A) is the PhoneUnit table
Table (B) is the SimCard table (Mainly consisting of two fields SimcardNumber and Status i.e Assigned/Unassigned)
A Form is to Populate Table (A), and a Simcardnumber is entered to "assign" it to the Phone.
Was wondering if the SimcardNumber entered in Table A could also copy itself into the same field in Table B.

That Way The data doesnt have to be entered twice
In this case a Phone will always have a SimCard but a Simcard wont always have a Phone. I will be having more Simcards than phones.

Any of this makes sense?

[John_G]

Hi -

Yes, I see what you are aiming at.

Since you will be assigning a Simcard to a phone when you enter the phone data, then I think it makes more sense to have the list (table) of Simcards already populated, then select from that list when the phone data is entered. A SimCard could be marked as "Assigned" by having a field in that table to indicate which phone it is in. An analogy would be a student enrollment system - a student cannt be enrolled in a course that does not already exist.

This arrangement records the "current status" only - if you want to track the history of which Simcard was in which phone, and when, then it is more complicated. You then have a many-to-many relationship, which requires a third table. (You have another thread dealing with that)

HTH

John