can I populate multiple tables in one go, from a single form?

[chris davies]

Hi Folks,
I am creating a database for a friend, the database has four main tables and and one intermediary table ( to remove any many to many relationships) I have popluated the DB with some test data manually and my queries thus far appear to be working correctly. What I need to know now is, is is possible to populate all these tables using one form?

For example, one table consists of four foriegn keys, all concatenated to form one primary key, to take care of any possible data redundancy or duplication in any of the other four tables.

My friend collect star wars autographs, so if you can imagine that one actor can play more than one role in more than one film and that different actors can play the same character as someone else in the same film.

So my immediate problem is to populate the four main tables and each primary key from each table to the intermediary table. If this is possible, couls someone please give me a pointer on how to handle inserting the the main data and primary keys into each table using a command button please?

I have posted a copy of my database if this will make things clearer, bearing in mind it is only populated with a small amount of test data. You can see from the Character Query that the results appear quite corrrectly. It's the VBA that I need to get to grips with.

I am hoping there is an intelligent individual here somewhere, from whom I could I gain some advice on this.

Thanks to all in advance, any and all replies accepted gratefully.

 

[Fifty2One]

Make a sql statement on the onclick of a button.

Dim strSQL1 as String
strSQL1 = "INSERT INTO tblOtherTable1 (Field1, Field2) VALUES ('" & Me.tbxValue1 & "', '" & Me.tbxValue2 & "');"
DoCmd.RunSQL strSQL1

Change the tblOtherTable1 to your table name and Field1, Field2 is substitute for the fields in that table, tbxValue1, tbxValue2 is substitute for the boxes on your form. You can use strSQL1, strSQL2, etc for more then one table

 

[chris davies]

Hi There,
Thank you very much for the reply, this looks like exactly what I need. One more question please if I may, while I now see that the table fields are now being populated directly from the fields on the form, how do I populate the fields in the intermediary table using the primary keys that are generated from the main tables?

My meaning here is, when I populate the tables from the form, the primary keys are auto-generated since they have been set to autonumber. What I would also like to achieve is to populate the intermediary table (there are four foriegn keys in this table) with the four primary keys from each of the main tables simultaneously.

Is it just a case of writing separate consecutive sql statements for each write to the database?I am familiar with SQL ( but by no means an expert), when I use PHP there is a specific method for inserting that last generated primary into another table for referential purposes.

Coming to think of it, how would I know the value of the primary key that is being generated, since these values are auto-generated? This key is going to be different for each and every table. Is there a MAX function I can use to find these values and would Access allow me to insert these values? I have noticed that Access throws a wobbly when manually inserting data if there is referential integrity involved.
Again thanks for your reply.

 

[Pat Hartman]

The problem with the form is that it is not bound. bind the form to the relation table. Then bind each of the combos to a field in the form so that it can be used to select the desired data.

FYI - it is best to avoid the use of spaces and special characters in you object names.

 

[chris davies]

If I bind the controls to the form, this wouldn't solve the problem, I can bind the control to the actual tables, but, correct me if I am wrong, but this wouldn't affect the intermediary table as nothing is bound to this table. From the solution from the previous post, I can now write the SQL to populate my main tables, but I have no way to post the primary keys to the intermediary table.

Please, If I have this completely wrong, please explain why so I can understand what you mean. I appreciate your help, but I need to understand why your solution would work, which at this moment in time I do not.

I do not expect a complete answer, that way I would understand what I need to do, and understanding is the way to learn. I now written queries to retrieve the highest ( max(ID)) fomr the main table. what I dont understand is how to write these ID's to the intermediary table, espedcially the ID's where the user makes a selection fromt eh combo boxes.

I hope you understand what I mean. Anyways, thanks for your reply, it has given me something else to think about and consider.

 

[boblarson]

You bind it to, what you are calling the intermediate table, the junction table. The combos select from the other tables.

I changed your form to pull it all together.

 

[chris davies]

Hi,
It's late here now, past midnight, will look at this tomorrow, thanks, could you tell me how how BOUND the controls. I thought they were already bound when I created the form and the wizard asked what data source to connect to.

cheers chris

 

[boblarson]

I think you are mistaking the bound controls on a form, with the bound column of a combo box. A combo box has something called a bound column - that which returns the value for whatever selection is made. It really is separate from the bound fields on the form. The fields on the form are bound by setting the control's recordsource to a particular field. And, in the case of a combo box, you select the recordsource to be one of the fields in the underlying query or table of the form and it will store the value of the bound column into the field of the form's recordsource.

I think if you compare the version you posted with the version I posted you might see what I'm talking about. Especially if you select one of the combo boxes on your original form, you will see that the control source property was not selected, but it is in mine.

 

[chris davies]

Hi Bob,
The problem with this solution is that, with the add person data form, the user wants to add the persons details manually and not be selecting them from a combo box, as they will be populating the database. The reason that the film and role combo's exist is to save the user from entering the film and role data. If the user need to add a specific role or film this will be done as a separate event on a separate form.

My aim with this form is to let the user select a film and role from the combo boxes, then add the specific person details via text boxes, click the add record button which will then populate the Person, Character and the Person/Character/Role/Film (the latter being the intermediary table) tables respectively. At this moment in time, I cannot figure out how to populate the intermediary table with the primary keys from all the other tables. Ouch my head is hurting again.

Any further input in this will be greatly appreciated, thank you.

 

[boblarson]

I think you are trying too hard here. This may not be exactly what you were originally thinking but using subforms is the way to go and would solve your insert problem because you don't need to do it. See this revision of what I originally gave you.

 

[chris davies]

ok, I been having another go at this problem, and and followed the above instructions regarding subforms etc,while the relationships look ok to me, and appear to work well if the data is entered manually, if I add a another entry for a person already existing in the database, the database add the persons name again. this is now puzzling me, all I want to be able to do is save a roleID, personID, FilmID and characterID so that none of the data is replicated. If somone could please help you would be held in superhero status by me.

 

[chris davies]

OK peeps, I'e nailed the problem. After taking a long hard gander at the northwind database (an excellent piece of work that is I must say), I notice that some of the foriegn keys, which were numbers, didn't actually display as numbers, but as the actual records (eg product as opposed to productid) themselves.
having sussed this bit out, I changed the format of the Id's in the intermediary table, and hey presto, add a new record without adding any redundant data by just adding an entry to the intermediary table via the use of four combo boxes and it works a treat. Now where did I put my beer....

BTW, if you'd like to see what I've done, post here and I'll post a smple of the DB.

thanks for all your help anyways, it has opened my eyes to other problems and possible solutions.