Assigning a value to mulitple records

[bigalpha]

Three tables:
Inventory
InventoryPK

ShowLink
ShowFK
InventoryFK

Shows
ShowPK

There's an inventory of products that I might take to a show (convention). What's a good way to associate the show with the product, and store that relationship in the ShowLink table?

I could make a datasheet with one of the fields a combo box, but this would get tedious if I had 100 products. I figure a better way would be to have a multiselect list box that I select all the products, then have one combo box to associate a show to it.

Not really sure, though.

 

[pbaldy]

Would this work to add the initial records?

http://www.baldyweb.com/MultiselectAppend.htm

 

[bigalpha]

I don't quite get how I can add both sets of information.

So this will let me add all the products to the ShowLink table - but I don't understand how I can associate the Shows with those records at the same time.

 

[pbaldy]

Given your description, I would expect a combo or textbox for show and a listbox for products. The code would add each product chosen along with the show to your junction table.

 

[bigalpha]

OhhhhHHHHHHhhhh. It took me a minute to go through the sample DB.

I'm going to set up a test and see if I can get it to work.

 

[pbaldy]

Okay, post back if you get stuck. It sounds like a good fit for the initial adding of records.

 

[bigalpha]

Ah ha! That works like a champ! That was significantly easier than I anticipated.

Thanks for your help and time! You've helped me immensely.

 

[pbaldy]

No problem!

 

[bigalpha]

No problem!

Out of curiosity - do you have a quick reference to opening a form that contains a subform to a specific subform record? If not, I'll start a new thread.

 

[pbaldy]

Sounds like you could adapt this to refer to the subform:

http://www.baldyweb.com/Bookmark.htm

 

[bigalpha]

Thanks, I'll take a look at this method. I recall reading that the subform is opened and 'filtered' before the main form. I have a search form that opens the main form - so could i open the main and subform simultaneously with the subform's criteria?

 

[pbaldy]

I believe you're correct, the subform loads first. I was thinking you wanted to

1) Open form containing subform
2) Navigate subform to specific record

 

[bigalpha]

I believe you're correct, the subform loads first. I was thinking you wanted to

1) Open form containing subform
2) Navigate subform to specific record

Yes, that's what I want to do, except when the form loads, the subform should load to the record that was selected from the search form.

 

[pbaldy]

Then like I said, I think that code will work, changing the recordset and bookmark form references to point to the subform instead of the main form. If you have trouble and can post a sample db, I'll play with it.

 

[bigalpha]

Then like I said, I think that code will work, changing the recordset and bookmark form references to point to the subform instead of the main form. If you have trouble and can post a sample db, I'll play with it.

Ok, let me play with this for a bit and see if I can get it to work.

 

[bigalpha]

Ok, so I can't quite seem to get this to work completely.

I have the search result window in frmSearch set to open the inventory form on keypress.

I set the code to open the main form and set the subform to the record I'm looking for. It pops an error that it cannot find my subform. Thoughts?