Conflicting Data Types & Linking Tables in a Form

[JKK]

Hey guys,

Some background:
I have a table named tblCampaigns (which has a list of campaigns offered, format is text); another table CompanyParticulars has a field named Campaign.

In the CompanyParticulars table, the Campaign field is set to lookup values from the tblCampaigns. It allows multiple values. For some reason it is a Number format ( I cannot change this).

I then have a form named frmCompanies which has a combobox for the field Campaign. When I click on the combobox, it drops down and I am able to view all campaigns with a checkbox next to them.

The default value stored in the combobox is the CampaignID from the tblCampaigns. When I select from the options in the combobox an error that the value isnt valid as it is an incorrect format.

My Issue:
What I would like is to select multiple campaigns in my form and have them stored in the CompanyParticulars table. If I have gone about this incorrectly, please advise.

I look forward to hearing your advice.

 

[boblarson]

You have several bad things going on here.

1. Using lookups at table level (see here for why that is a very BADDDDDD idea).

2. Using multivalue field for the Campaign field.


You aren't really going to get past this until you correct numbers one and two above.

 

[JKK]

Thanks Bob for your quick response.

I have done as you recommended, and all is in working order (no errors); however my issue is not altogether solved. How would I be able to allow the user to select multiple campaigns and capture the users choices?

I could use many checkboxes (one for each campaign), but this would make the form look messy.

 

[boblarson]

What you should do with that is create a junction table and then store the values there along with the related ID for the company (if I understand your setup correctly). And then you would use a SUBFORM to assign them.

 

[JKK]

I'm still a newbie here, would you mind dumbing your last statement down a little bit.

 

[boblarson]

You would build a junction table:
And you would select both of those fields as a composite key (so select both while in design view and click the KEY to set it as the primary key)

CompanyID - Long Integer
CampaignID - Long Integer

And then you would build a subform with just those two fields. You would really only be showing the CampaignID and you could use a combo box set up to select the Campaign and have it store the campaignID in the table but show the name.

The subform would be linked to the main form by the CompanyID using the Master/Child links of the subform.

Perhaps you might want to upload a copy of your database to here and one of us here can assist in getting that in.