add multiple records at once to a child table from a form (1 Viewer)

hubcap750

Registered User.
Local time
Today, 16:10
Joined
Jun 27, 2013
Messages
34
Hi folks,

I have an unbound new contact data entry form that adds records to many different tables. I have no problems adding one record to a child table at a time, but one of the things we are collecting is a list of online networks a person belongs to, and I want to be able to stick a "check all that apply box" on my form and have it add multiple records to the child table. Here's the basic set up, I have a parent table with the main contact information with a primary key field "IID", a lookup table with "Facebook, LinkedIn, Twitter..." etc in it with a primary key field "online_id", and a bridge table to link the two which should have multiple entries for IID, one for each online_id. I want to use a listbox (or something like it) that the user can select multiple online networks and then have records added to the bridge table. I can figure out how to add the listbox on the form, what I can't figure out is how to get the values out of the listbox. This seems like an operation that would be fairly common to do for a database that is properly normalized, but I haven't been able to find an answer. :banghead:
 

Isskint

Slowly Developing
Local time
Today, 23:10
Joined
Apr 25, 2012
Messages
1,302
You would need to use VBA to do this. I would have a listbox of the on line networks (multiselect) and a command button to trigger the VBA. Once the user has selected all the relevant networks, they click the button.The vba would need to loop through all the entries in the list box and identify the SELECTED records. For each record, create a string variable like strSQL = "INSERT INTO
[Field1], [Field2], [Field3]" then use the DoCmd.RunSQL function.
 

hubcap750

Registered User.
Local time
Today, 16:10
Joined
Jun 27, 2013
Messages
34
Hi Isskint, thanks for responding. I've seen that solution before, and I seem to be missing something. If I create a new variable for each selection, then how do I add them all to the same field in the child table? Should I have a child table with one field for each online network instead? I seem to be missing something very basic here, its been a brain fart kind of day so please forgive me if it is something obvious.
 

Isskint

Slowly Developing
Local time
Today, 23:10
Joined
Apr 25, 2012
Messages
1,302
Let me just check something. I would expect to see;

Contact table

  • ContactID - PK
  • .....other fields
Networks Table

  • NetworkID - PK
  • .....other fields
Contacts_Networks table (to use your terminology a bridge table)

  • TblID - PK
  • ContactID - FK
  • NetworkID - FK

So on a form displaying a single contact record, you would have the listbox of Networks.
The code then loops through the contents of the listbox looking for selected items (have a look here about using the SELECTED property). When it finds a selected item create the string "INSERT INTO [Contact_Networks] " & [ContactID] & " , " & [NetworkID].
You would have to name [ContactID] & [NetworkID] accordingly eg ME.ContactID and me.listboxname.column(0,row)
 

hubcap750

Registered User.
Local time
Today, 16:10
Joined
Jun 27, 2013
Messages
34
Yes, you have the structure correct. All the examples I have seen have been using the multi-select listbox to add selections to the WHERE clause in a select query, so they just keep adding the values to the string. I think what I might be missing is that I need to run the sql with each loop. Is that it?
 

Isskint

Slowly Developing
Local time
Today, 23:10
Joined
Apr 25, 2012
Messages
1,302
Yes, that is right. You can not build 1 string to do it all, you need to add each selected item one at a time. So once you have the string EG "INSERT INTO [Contact_Networks] 2,5" you run that as SQL.

So with 3 networks selected (say 1,2,3) on the record for Contact 1, you would get

"INSERT INTO [Contact_Networks] 1,1" - essentially Insert [ContactID] = 1, [NetworkID] =1
"INSERT INTO [Contact_Networks] 1,2" - essentially Insert [ContactID] = 1, [NetworkID] =2
"INSERT INTO [Contact_Networks] 1,3" -essentially Insert [ContactID] = 1, [NetworkID] =3
 

hubcap750

Registered User.
Local time
Today, 16:10
Joined
Jun 27, 2013
Messages
34
That did it! Who knew that making things easier for users would require so much code. Thanks!
 

MarkK

bit cruncher
Local time
Today, 16:10
Joined
Mar 17, 2004
Messages
8,178
I would do this with a subform, a datasheet view of the Contacts_Networks table, with one visible column being a combobox on the NetworkID field. Set that combo to have to two columns, the first one the hidden NetworkID--which would be the bound column--and then the visible column is the Network Name from the Networks table.

The other two columns can be hidden. The TblID, if it's an autonumber, will be filled in automatically by the table when records are added, and the ContactID foreign key would be filled in automatically by the subform control to match that of the ContactID in the parent record.

You can read up on the LinkMasterFields and LinkChildFields properties of the subform control, but Access also sets these properties automatically when you drop the subform on the main form in design view. Using a subform you can create that list without code, and edits and insertions are automatically linked, immediately viewable, and so on . . .

As an option . . .
 

hubcap750

Registered User.
Local time
Today, 16:10
Joined
Jun 27, 2013
Messages
34
Thanks MarkK, I think I'm doing something like that elsewhere in my data entry form, using a combo box to add a single record to a different linked table. I'm not seeing how your sub forms would allow the user to select more than one item at one time though, which is what I was shooting for. If you could explain that part a little better then I'd go for it rather than add more code to a database someone else will have to deal with down the road.
 

MarkK

bit cruncher
Local time
Today, 16:10
Joined
Mar 17, 2004
Messages
8,178
I'm not seeing how your sub forms would allow the user to select more than one item at one time though, which is what I was shooting for. If you could explain that part a little better . . .

Here's a fast and dirty database that demonstrates what I'm talking about. The tables called tLeft, tCenter and tRight are in a many to many relationship. Open the fLeft form and use the subform to add attributes from the tRight table by adding records to the join table, tCenter. There is no code, but there is a lot of functionality.

Does that do what you want? If so, there is an example of how to do it.
 

Attachments

  • db.zip
    18.6 KB · Views: 377

hubcap750

Registered User.
Local time
Today, 16:10
Joined
Jun 27, 2013
Messages
34
Thanks MarkK, that is a neat trick. I'm guessing it works because of the way the relationships are defined. I'm using Access as a front end though, my data actually resides in SQL server, so I will need to see if adding cascade update to the relationships there will work. Even if it doesn't I will have some tables in this database and that will be a useful trick.
 

MarkK

bit cruncher
Local time
Today, 16:10
Joined
Mar 17, 2004
Messages
8,178
It doesn't work because cascade update is true on the relationships. It works because the subform control mediates the relationship between the subform and the mainform. As I mentioned in a previous post, check out the LinkMasterFields and LinkChildFields properties of the subform control. The matching fields between parent form and subform are defined there, and synchronizing is done on those fields for filtering the subform, and for new records in the subform.

Also this is one of the things that makes Access so easy to use, that it can display these parent/child relationships between your tables so easily.
 

hubcap750

Registered User.
Local time
Today, 16:10
Joined
Jun 27, 2013
Messages
34
I look forward to investigating this further. Unfortunately, I think the reason it didn't work when I tried to replicate your example with my tables is because my computer has other issues suddenly, it isn't recognizing me as an administrator anymore. That maybe be why my table wasn't updating in SQL. I'll let you know if that was the issue once I get things running correctly again and am done thanking the backup gods. Thanks again both of you for your help!
 

hubcap750

Registered User.
Local time
Today, 16:10
Joined
Jun 27, 2013
Messages
34
So, it did turn out to be a Window's issue, some corrupt files so that SQL was no longer recognizing me and instead of sending me the typical error message it just disconnected everything. Once we got all that fixed you method worked great! Thanks again for your help.
 

Users who are viewing this thread

Top Bottom