Combobox Values (1 Viewer)

[DanG]

Hello,

I have posted this problem in the past and just haven't quite got it down yet. I think primarily because the way i explained the issue, so I going to try starting over...

I have 2 tables:
tblMSREntry
* This has a field in it called "MSRCampaignID" (which is a number datatype)

tblCampaigns
* has 2 fields...CampID (autonumber) and CampName (text)
* All campaigns start with the year & then campaign name (2010 Super Sale...)

In the MSREntry form I have a combobox that draws it's values from tblCampaigns and when a campaign is picked stores the the "campID" into the MSRCampaignID field in tblMSREntry. All of this works as it should.

The problem is that I want to limit the values one can "choose" from on the list to the current year (say to 2010 campaigns only). But when I do this I can not see the campaign name displayed for previouse years (say 2009...) on the data enry form the combobox is in.

I would like to have the combobox do three thins (if possible)
1.) Contain a list of values (campaings) to choose from
2.) Store the campaign ID into the tblMSREntry
3.) Display the "name" of the campaign as I move from record to record. Which I would assume would have to come from somewhere other than the pick values?

Man I hope I did a better job of explainign this!

Thank you guys!

 

[vbaInet]

The problem is that I want to limit the values one can "choose" from on the list to the current year (say to 2010 campaigns only). But when I do this I can not see the campaign name displayed for previouse years (say 2009...) on the data enry form the combobox is in.

Your query criteria should for the campaigns date should be:

WHERE Year([CampaingDate]) <= Year(Date())

I would like to have the combobox do three thins (if possible)
1.) Contain a list of values (campaings) to choose from

I think you've handled this already.

2.) Store the campaign ID into the tblMSREntry

Use an Append query. You would probably just need to run an INSERT statement. Here's a link:
http://www.techonthenet.com/sql/insert.php

This is how you run the query:

Docmd.setwarnings false
Docmd.runsql "INSERT INTO ..."
Docmd.setwarnings true

3.) Display the "name" of the campaign as I move from record to record. Which I would assume would have to come from somewhere other than the pick values?

Use the Control Source property for that.

 

[DanG]

This works fine:

Your query criteria should for the campaigns date should be:

WHERE Year([CampaingDate]) <= Year(Date())

This is handled:

I think you've handled this already.

The combobox is already doing this, I have the control source on the combobox set to "MSRCampaignID" which as I undestand it is what takes the "campID" value from the campaign table and moves it to the "MSRCampaignID" in to tblMSREntry (?)

Use an Append query. You would probably just need to run an INSERT statement. Here's a link:
http://www.techonthenet.com/sql/insert.php

This is how you run the query:

Docmd.setwarnings false
Docmd.runsql "INSERT INTO ..."
Docmd.setwarnings true

See note above about my understanding of control source.
If the control sourse isn't taking the value from the pick list and moving it to the field referenced in the control source, what is???

Use the Control Source property for that.

I think my understanding of the control source may be my problem???

Thanks again for the help!

 

[vbaInet]

See note above about my understanding of control source.
If the control sourse isn't taking the value from the pick list and moving it to the field referenced in the control source, what is???

I think my understanding of the control source may be my problem???

I don't follow you here. Elaborate.

 

[DanG]

I don't follow you here. Elaborate.

Sorry it took so long to get back (they're killin' me here!).

A combobox has a control source, I thought the control source could do 2 different things...
1.) point to the field you want to store the data in once you pick it from the list in the combobox.
2.) have a function like "date()" and display the result.

So it was my thought that I wanted to pick a campaign from a list and store the campaignID into the field in the control source?

Again, I think I just don't understand just what the control source does completely.

Thank you again!