using combobox on subform

[2whElr]

Hi All...need help with a subform issue...I've got 2 tables (Campaign & AdZone) with 1-many relationship created in relationships tool...need to be able to select multiple values from a combo box...



Challenges: When the combobox displays the values I want the user to select from for the AdZone, I can only select a single AdZone....most of the time I get a null value in the combo box as if I need to enter new data back to the AdZone table

Have found a few posts that attempt to resolve one or the other but not both together...have also tried to use a query, but that isn't working either

Any ideas/suggestions?

[TheShabz]

So you have your campaign table in a one to many with AdZone such that...
tblCampaign 1----M AdZone

Are you looking to select a campaign number and see what zones it was run in or select a zone and see what campaigns were run in it?

[2whElr]

Neither...need to identify multiple zones where a campaign has been run...not concerned about the reverse relationship...next steps after identifying what zones a campaign has been run is perform a couple of calculations, but I think that is the easy part

[TheShabz]

so your input will be a campaign, and your output will be zones that campaign has been run. If so, you populate your combobox with your campaign names/ids (whichever is the join) and then populate a subform with:
SELECT *
FROM AdZone Inner Join Campaign On AdZone.ID = Campaign.ID
WHERE Campaign.ID = Forms!formName!comboboxName

[2whElr]

so this just gave me a thought....should I remove the autonumbering of AdZones in that table? Right now, I have an autonumber ID field for each active AdZone

[TheShabz]

Do you have a better way in mind for uniquely identifying each AdZone record? If not, don't

[2whElr]

Yes could use just the adzone name instead of any kind of true numbering but need to update data on periodic basis as well as retain historical values...should I use compound key instead

[TheShabz]

Compound keys are usually used in a Many-to-Many junction table where you use each of the "parent" tables' PKs as a compound key. I dont really see the purpose of that here. But yes, if you have a truly unique way of identifying each record, then by all means go ahead and use it instead of an autonumber field. Just make sure to note it as the PK so Access can index it for you.