Junction Tables and combobox to remove multiselect fields

[vb9063]

Hi Guys,
I have been trying to create a junction table to remove the need to use multiselect fields (as they are not very transferable to anything else!) and have got a bit mixed up as to how to do it and whether I need to have a unique ID for the junction table and how the foreign key relationship should work between my supplier and the container.

The example I have been using is to remove the container combobox from the services page on the supplier details form and put it on its own page "containers" in the form of a set of comboboxes with a datasheet view so that the user can chose multiple containers . I have tried this but am getting confused with the rowsources and control sources as I keep selecting the container in the combobox (which is in its own table and not reditable from this form ie the user should only be able to edit the container types from a separate form) and trying to put the resulting value somewhere but I am unsure as to how and where to save the selection as I keep getting a message in the bottom window saying "control cant be edited its bound to the expression [tblCont]![cont_ID].

So I am not sure whether my new junction table tblCont is actually what I should be using..

I have attached the database if anyone has any ideas as to how to fix it i would be very grateful!!
Thank you,
VB

 

[vbaInet]

I'm not clear on what you want to do but I've removed the multivalue fields and created joins with the relevant tables. Having a junction table would depend on what the circumstances are. Rethink your structure, look at the Relationships (which I've rearranged) and come up with a plan.

These links are a good read too:

http://r937.com/relational.html
http://support.microsoft.com/kb/100139
http://www.databasedev.co.uk/database_normalization_process.html

 

[vb9063]

Hi vbaInet,
Apologies I should have deleted the non relevant stuff because I am trying to get away from using the "services" page so have deleted it. The example of the multiple combobox is now on the container page but I am stumped as to how to actually store the chosen container as I cant store it as an ID. Thanks for the links they are great, thank ou for all your help!Cheers VB

 

[vb9063]

Hi vbaInet,
I have managed to create what I think is the substitute for a multivalued field on each page of my supplier subform!!! WHoopeee!! I read that long tutorial which I think helped!

Do you have any idea how i could restrict the fields for each supplier to not allow duplicate values for each supplier? If I give the example of the postcode fields I am able to choose "AB" more than once, which isnt ideal. I tried changing the indexing but that causes problems because it interferes with other supplier post codes...

I have also started looking at my "search criteria" form again which you helped me with before. I have the recordsource set to retrieve the info for the postcode, supplier, wastetype and container but when I do a search it returns what should be 1 or 2 suppliers but am getting 36 because it is outputting the supplier each time (because of each container, wastetype and postcode that it is associated with). The multiselect of each of the comboboxes on the search form arent working either. IS there a better way to fill in the recordsource??


Thanks v much for all your help!!!

 

[vbaInet]

Hello there, I've had a look and I think it's not right and I noticed you removed the Services table. At this stage you would be better off getting your relationships right before thinking about anything to do with your search form.

My advise would be that you post a new thread in the General section of the forum listing out your tables and fields and explaining what kind of database you're working towards. That way we get a clearer picture and we can advise better.

Once that's cleared up then we can think about the form.

 

[vb9063]

HI vbaInet , thanks for the quick response, did you see the latest post with new database, I thought I had got it the relationships to work
:-( what would you say is wrong in the relationships, i thought that it was a M-M but after reading that tutorial thought that it was a 1-M so is this wrong ??
Thaank uuu..VB

 

[vbaInet]

I'm not entirely sure but some of your joins are not justified and going through them one by one will take forever and we may just end up doing a round robin Which was why I suggested you post a new thread with your table structure so we have a clearer understand.

 

[vb9063]

Oh dear, that sounds terrible...what do you mean by not justifying joins? Is that in the queries?