Default for unbound "Month" combobox

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi,

How can I set an unbound combo-box to show the current month as default? (I
have a separate table with 12 months to populate this combo.)

I need the default value to automatically display the data of the current
month and also need to be able to choose another month of the year to show
the report for another month. (this concerns both my report and my form)

Please help me with some ideas?

Thank you.
Lana
 
Hi, Lana

I would use the OnOpen event of the form.

You didn't give the table structure or the field types, so choose one and
delete the other two examples in the code below:

'------------
Private Sub Form_Open(Cancel As Integer)
' bound column is a number
' for numeric month (11)
Me.cboMonth = Month(Date)

' bound column is text
' for Month abbreviation (Nov)
Me.cboMonth = Format(Month(Date), "mmm")

' for full Month name (November)
Me.cboMonth = Format(Month(Date), "mmmm")

End Sub
'------------

Or you could also try setting the 'Default Value' property of the combo box.

Use:
=Month(Date()) for month number
=Format(Date(),"mmm") for Month name abbreviation
=Format(Date(),"mmmm") for full month name


Steve
 
Hi Steve,

My Combo-box "MNo" displays text (Like "November"). The code you gave me
worked all right untill I involved my querry. It has the condition as follows:

WHERE (((Month([Reporting Date]))<(Val(Format(Forms!Main!MNo), "mm")+1)));

Before I used the text field with number to be inputed and the condition as
WHERE (((Month([Reporting Date]))<(Val(Forms!Main!MNo)+1)));
it worked all right before.
Now that I want the text to be displayed in the combo - I dont know what to
do with my query. :(

can I reffer somehow to the ID column of my combo (which is number)? may be
then the querry will work again?

Please help!

Lana
 
Lana,

We should be able to fix everything.

I need to know (for the combo box "MNo"):

What is the Record Source? (from properties/Data tab - please post the SQL)
What is the bound column? (from properties/Data tab)
What is the column count? (from properties/Format tab)
What is the column widths? (from properties/Format tab)

What is the table structure for the table that holds the month info?
Is it like this?

Field name - field type
------------------------------
MNumber - integer
MName - Text

WHERE (((Month([Reporting Date]))<(Val(Format(Forms!Main!MNo), "mm")+1)));
Click to expand...

Also, the Where clause will select all months less than or equal to the
month entered in the combo box for ALL years. If you had 5 years of data
(1999 - 2004) and you wanted months Jan - May, you would get Jan - May for
1999, Jan - May for 2000, ..., Jan - May for 2004. Maybe that is what you
want...??


Steve
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)


Lana said:
Hi Steve,

My Combo-box "MNo" displays text (Like "November"). The code you gave me
worked all right untill I involved my querry. It has the condition as follows:

WHERE (((Month([Reporting Date]))<(Val(Format(Forms!Main!MNo), "mm")+1)));

Before I used the text field with number to be inputed and the condition as
WHERE (((Month([Reporting Date]))<(Val(Forms!Main!MNo)+1)));
it worked all right before.
Now that I want the text to be displayed in the combo - I dont know what to
do with my query. :(

can I reffer somehow to the ID column of my combo (which is number)? may be
then the querry will work again?

Please help!

Lana
Click to expand...
 
for the combo box "MNo":
What is the Record Source?
Click to expand...
SELECT Months.ID, Months.MNo FROM Months;
(a separate table with 2 columns, ID & MNo - only 12 records for 12 months
with IDs 1-12)
What is the bound column? 1
What is the column count? 2
What is the column widths? 0cm;2.54cm

What is the table structure for the table that holds the month info?
Click to expand...

Field name - field type
------------------------------
Operation Date - Date/Time
Paid to - Text
Amount - Number
Reporting Date - Date/Time

I tried to change original
WHERE (((Month([Reporting Date]))<(Val(Format(Forms!Main!MNo), "mm")+1)));
Click to expand...
Click to expand...

to:
WHERE (((Month([Reporting Date]))<=([Forms]![Main]![MNo])));
Seems to work, but only with months 1-9. When comes to 10,11,12 - gives out
wrong data.
Also, the Where clause will select all months less than or equal to the
month entered in the combo box for ALL years. If you had 5 years of data
(1999 - 2004) and you wanted months Jan - May, you would get Jan - May for
1999, Jan - May for 2000, ..., Jan - May for 2004. Maybe that is what you
want...??
Click to expand...

Frankly speaking I am in a great mess right now! I have about 15 querries
which do the calculations I need. And lots of them depend on that MNo field.
One of them looks like:
WHERE (((Month([Operation Date]))<=(Forms!Main!MNo) And (Year([Operation
Date]))<((Year(Date()))+1)));
I guess this one also is not correct.

I would be happy to reduce the number of queries but don't know how. I need
sub-queries like "Sums" and "Totals" and I dont know any other way but to put
them into separate queries and then reffer to that queries. I didnt succeed
with the names of the queries so now I am completely lost in them now!
Probably will have to change everything.

Basically, I need 4 of them:
1) calculating "Previous" - the amount of money I have at the begginning of
the reporting month ([sum of received till date]-[sum of spent till date])
2) retrieve all records from "Received" for this month
3) retrieve all records from "Spent" for this month
4) get the amount of money "Hanging" (not reported) - this comes
complicated. I need to look for "Operation date" and "Reporting date". I
don't add to the "Hanging" amount those records where reporting was done
before the needed month. But I do add them if the reporting was done, but
later than this needed month. And here I also need to take the year into
consideration - cause nobody ever does reporting in time. (For received-spend
things I decided to start new database each year, only keeping the table with
"Hanging" amounts from previous.)

So for the "Hanging" I would like to have all months for the previous years
and all months for this year up to the end of the month I need. (E.g. since
Jan. 1, 1900 till May 31, 2004).

How can I achieve this if I enter only the Month into my combo-box and
assume the Year is (). I hope it will work all right even for retrieving the
last year records since I keep the new year ones in a separate DB?

One more question: I have put the following into the default value of my
combo-box as you advised:
=Format(Date(),"mmmm")
It displays "November" all right on open, but doesn't work to retrieve the
records for this month.
When I try to select value from the list, it sais "the value you entered is
not valid for this field". I click OK, then select from my list the same
"November" month, click "refresh" and all the records appear all right.
Why does this happen? Why it doesn't accept the default?

Lana
 
Lana,

Let's get the combo box fixed first. There are many ways to fix this problem
- this is the way I would do it.

Make sure you are working on a COPY!

First, for the table "Months", I would have two fields:
Field name - field type
------------------------------
m_ID - Integer (Primary key) (NOT autonumber)
m_name - Text

with the data (entry order into the table doesn't matter):
m_ID m_name
 
Hi, Steve,

the combo works all right now - thank you! the solution was so simple!

I have created the Yr and S_Date boxes as you advised. Seems to work fine,
but I have to change and check all my 15 querries now.....

Hope everything goes all right. If not - I guess I'd have to cry for help
again :)

Thank you so much for your help!

Lana
 
Back
Top