Query, form and report help

[Harley]

Can someone help me do this in a better way? I have built the two queries below to give me a montly sum of some church contributions. On the "Reportsfrm" form I have two combo boxes to choose the month. One of the combo boxes is setup to choose the months of the year names. This is used merely to put the name of the month on the Monthly Report. The other combo box chooses a number from 1-12 which is used in the first query below to choose the month for the query. This works fine, but makes the DB user use two combo boxes. Does anyone know of a way that they can just choose the month by name? Thanks in advance for your help.

SELECT MemInfotbl.ContribDate, MemInfotbl.ContribAmt, Month([ContribDate]) AS ContribMonth
FROM MemInfotbl
WHERE (((Month([ContribDate]))=[Forms]![Reportsfrm]![Monthcbo]));

SELECT Sum(Monthlyqry.ContribAmt) AS SumOfContribAmt
FROM Monthlyqry;

 

[Dennisk]

Create a table containing 2 columns, 1st column is the month number 1 - 12 and set it to be the primary key. The second column contains the name of the month. Use this query as the datasource for the combo box and hide the first column. Then when a month is selected you can reference the month number by referring to the combo box name and to reference the name of the month use the column method of the combo box.

 

[Harley]

That sounds good. I will try it tonight.
Thanks for helping.

 

[Harley]

I don't get it. I am missing something somewhere. Can you give me a little more help?
Thanks

 

[Dennisk]

what are you missing.
you have a combo box on a form.
the datasource is set to the month table.
Number of columns =2
column width = 0;2
bound to column = 1

when you click on the combo box you see a list of months Jan-Dec

 

[Dennisk]

what are you missing.
you have a combo box on a form.
the datasource is set to the month table.
Number of columns =2
column widths = 0;2
bound to column = 1

when you click on the combo box you see a list of months Jan-Dec

 

[Harley]

That is working. Now how do I get the name of the month on the Report? I am getting the month # instead of the name. Sorry but I have never used a multiple column table in a combo box before. I do appreciate the help.

 

[Dennisk]

a combo box has a method called column where n is the number of the column and is called like this

cboMyComboBox.Column(1) or (2) etc. Set up an unbound text box and set its control source to =cboMyComboBox.Column(1)
and change the index number to see how to access different columns.
Hint: normally you would would set this text box so it was disabled.

All this is in help along with examples

 

[Harley]

Thanks for your help. I evidently didn't look in the right place in Help the first time. Your help is much appreciated. Here is what I needed in the Control Source of the Text Box.
=[Forms]![Reportsfrm]!Combo37.Column(1)