Insertion operation in multiple tables holding many-to-many relationship

[Parminder]

Hello all



I am developing something in which I have to perform insertion operation on tables having many-to-many relation. How can I perform this?

As an example,

I have 3 tables like:
1st


2nd: Bridge table


3rd: ID table


I want to perform insertion operation on bridge table on selection of items from listbox like, suppose the ID is 2 and they can select any number of values from listbox and that selected items should be inserted into bridge table.
How can I perform this operation?
Will wait for some positive and useful reply.

Regards
Parminder

[June7]

User selects ID (from a combobox?) and then selects multiple items from a multi-select listbox? Then you want to write records to Bridge table? This will require code that loops through the items of the listbox and runs SQL INSERT action to create records.

Here is example of looping through listbox: http://bytes.com/topic/access/answer...-thru-list-box

Instead of opening a filtered report, you would run SQL statement.

CurrentDb.Execute "INSERT INTO Bridge(ID, sk_weather) VALUES(" & Me.ID & ", " & lst.ItemData(vItem) & ")"

[Parminder]

The problem I have is ID is auto generated and I want to take that value from 3rd ID table. Is there any way to do that?

[June7]

ID is autonumber in ID table and is PK?

This is saved as FK in Bridge table?

What I suggested should work.

The real trick is figuring out what event to put code into. If user is not selecting ID from a combobox, then where will it come from - how will it be selected/specified for use in writing records to Bridge?

[orange]

Parminder,

Can you forget the tables and Access for the moment, and tell readers in business terms, plain English
(no jargon) WHAT it is that you are trying to accomplish? There are some details, as June has mentioned, that are not clear, but it would be better if we understood your requirement.

[Parminder]

Yes sure,

In actual I want user to select multiple items and store those selected items somewhere, there is many to many relationship between two entities, as an example one book can have more than one author and one author can write more than one book and this can obtain duplicate entries. So in order to remove duplicacy, I want to create one table which will contain just two columns as an example bookID and aurthorID and the combination of these two columns will be unique.
I want to insert that information into table through user forms not manually.
The problems I am facing is:
1. I created one table, i.e staging table from where I am getting ID, which is a field in staging table and it is autonumber, I am confused that how can I get that information from table.
2. I am using listbox for multiple selections and they are associated with weather table and connected to sk_weather column.
So I want to get ID from staging table and it will be joined with sk_weather and the combination of these two will be unique and stored in another new table which is Bridge table.

I hope you guys understand the scenario, But still if you guys have any question feel free to ask.

Regards
Parminder

[June7]

Did you see post 4?

[Parminder]

Did you see post 4?

Yes, I did see the post. I am trying to figure out how can I retrieve value of ID from ID table. It is autogenerated and PK in ID table and referred as a foreign key in Bridge table.

[orange]

StagingTable(source of ID)--->BridgeTable(junction table)<----WeatherTable(rowsource of listbox)

I think post #2 identifies the solution.
You just have to refer to the fields by name in the SQL/vba. You do not have to be concerned with how the autonumber assignment occurs/occurred.

Please show us what you have done if there are still issues.

[June7]

Well, if doing data entry on form bound to ID table, code can be behind that form. The real trick is figuring out what event to put code into. The ID table record must first be committed to table before saving the related child records. Could be a button Click.

DoCmd.RunCommand acCmdSaveRecord
If IsNull(DLookup("ID", "Bridge", "ID=" & Me.ID)) Then
'looping code to read values from list box and save records
End If