Combo Box Help??


T

TotallyConfused

I have a form with a combo box . When you click on this combo box you choose
the month. Then you choose view rpt button which opens report in view form
for that particular month. This db only has 2009 dates. I inherited this db
and have been asked to add the ability to choose the year. 2010 dates will
be added to the db. What I though t to do is put another combo box for the
year. However, how do I get the "Year" combo box to synchronize with the
month combo box and then when clicking on the report command button only get
the correct year and month??? Below is a copy of the current combo box.
Thank you in advance for any help you can provide.

Private Sub Combo50_BeforeUpdate(Cancel As Integer)
If Me.Combo43 = "January" Then Me.Text32 =#1/1/2009#
If Me.Combo43 = "January" Then Me.Text32 =#1/20/2009#
If Me.Combo43 = "February" Then Me.Text32 =#2/1/2009#
If Me.Combo43 = "February" Then Me.Text32 =#2/15/2009#
 
Ad

Advertisements

D

Dale Fye

Confused:

Since every year has the same months, they don't necessarily need to be
"synchronized", unless you only want to display the years and months for
which a table contains data. What is important, for your report, is that you
set the appropriate critieria, so that only the data for "Jan 2009" or "Feb
2010" shows up in the report when you make those selections. The way you do
this will be determined by the data fields in your table, specificly the date
field.

In your case, you indicate you have a command button which opens the report
in preview mode. The code behind your command button should look something
like:

Private Sub cmdPreview_Click

dim strCriteria as string

'rename your controls so that they mean something
'rename combo43 to cbo_Month, text32 to txt_Year
if IsNull(me.cbo_Month) Then
msgbox "Select a month!"
Exit Sub
elseif isnull(me.txt_Year) Then
msgbox "Enter a valid year!"
Exit Sub
endif

'define the criteria that will limit your report to the
'appropriate month and year
strCriteria = "Format([DateField], "mmmm") = '" & me.cbo_Month & "' AND
" _
& "Format([DateField], "yyyy") = '" & me.txt_Year & "'"
docmd.openReport "ReportName", acViewPreview,,strCriteria, acDialog

End Sub
 
T

TotallyConfused

Thank you for responding, but I don't think this will work. Please
disregard the code below as it is incorrect. Let me explain. On opening db
there is a form where you choose region, depending on region it opens another
form where you choose month depending on the month then you choose to veiw
the report. Report will list only cases within that month. However,
depending upon the region, the dates will be diffent for the month. The form
has a combo box for the month and two unbound boxes for the dates. These
unbound boxes are not visible but are referenced in the code. Therefore,
if when I open the form I choose region 1 next form will open (same form for
both regions) and combo box for the month. Pick the month from the value
list (january, febraury, etc) then click on the command to open the report
and only the cases for that specific region and month will be listed. The
correct code now is the following with only 2009 dates. I need to add 2010
dates but want to give the user the choice to pick a specific year or both.
How can I accomplish this without rewriting everything. This may not be
needed in the near future. Just need a quick way to make this easier for the
user. But do not want to have to change the forms and fields. Thank you.

Private Sub Combo_BeforeUpdate(Cancel As Integer)
If Me.Combo = "january" Then Me.Text32 = #1/1/2009#
If Me.Combo = "january" Then Me.Text34 = #1/20/2009#
If Me.Combo = "february" Then Me.Text32 = #2/10/2009#
If Me.Combo = "february" Then Me.Text34 = #2/25/2009#

etc
Confused:

Since every year has the same months, they don't necessarily need to be
"synchronized", unless you only want to display the years and months for
which a table contains data. What is important, for your report, is that you
set the appropriate critieria, so that only the data for "Jan 2009" or "Feb
2010" shows up in the report when you make those selections. The way you do
this will be determined by the data fields in your table, specificly the date
field.

In your case, you indicate you have a command button which opens the report
in preview mode. The code behind your command button should look something
like:

Private Sub cmdPreview_Click

dim strCriteria as string

'rename your controls so that they mean something
'rename combo43 to cbo_Month, text32 to txt_Year
if IsNull(me.cbo_Month) Then
msgbox "Select a month!"
Exit Sub
elseif isnull(me.txt_Year) Then
msgbox "Enter a valid year!"
Exit Sub
endif

'define the criteria that will limit your report to the
'appropriate month and year
strCriteria = "Format([DateField], "mmmm") = '" & me.cbo_Month & "' AND
" _
& "Format([DateField], "yyyy") = '" & me.txt_Year & "'"
docmd.openReport "ReportName", acViewPreview,,strCriteria, acDialog

End Sub

----
HTH
Dale



TotallyConfused said:
I have a form with a combo box . When you click on this combo box you choose
the month. Then you choose view rpt button which opens report in view form
for that particular month. This db only has 2009 dates. I inherited this db
and have been asked to add the ability to choose the year. 2010 dates will
be added to the db. What I though t to do is put another combo box for the
year. However, how do I get the "Year" combo box to synchronize with the
month combo box and then when clicking on the report command button only get
the correct year and month??? Below is a copy of the current combo box.
Thank you in advance for any help you can provide.

Private Sub Combo50_BeforeUpdate(Cancel As Integer)
If Me.Combo43 = "January" Then Me.Text32 =#1/1/2009#
If Me.Combo43 = "January" Then Me.Text32 =#1/20/2009#
If Me.Combo43 = "February" Then Me.Text32 =#2/1/2009#
If Me.Combo43 = "February" Then Me.Text32 =#2/15/2009#
 
Ad

Advertisements

J

Jinjer

Are you able to change the list in the combo box so the user can choose
January 2009, January 2010, February 2009, February 2010, etc.?

--
Jinjer


TotallyConfused said:
Thank you for responding, but I don't think this will work. Please
disregard the code below as it is incorrect. Let me explain. On opening db
there is a form where you choose region, depending on region it opens another
form where you choose month depending on the month then you choose to veiw
the report. Report will list only cases within that month. However,
depending upon the region, the dates will be diffent for the month. The form
has a combo box for the month and two unbound boxes for the dates. These
unbound boxes are not visible but are referenced in the code. Therefore,
if when I open the form I choose region 1 next form will open (same form for
both regions) and combo box for the month. Pick the month from the value
list (january, febraury, etc) then click on the command to open the report
and only the cases for that specific region and month will be listed. The
correct code now is the following with only 2009 dates. I need to add 2010
dates but want to give the user the choice to pick a specific year or both.
How can I accomplish this without rewriting everything. This may not be
needed in the near future. Just need a quick way to make this easier for the
user. But do not want to have to change the forms and fields. Thank you.

Private Sub Combo_BeforeUpdate(Cancel As Integer)
If Me.Combo = "january" Then Me.Text32 = #1/1/2009#
If Me.Combo = "january" Then Me.Text34 = #1/20/2009#
If Me.Combo = "february" Then Me.Text32 = #2/10/2009#
If Me.Combo = "february" Then Me.Text34 = #2/25/2009#

etc
Confused:

Since every year has the same months, they don't necessarily need to be
"synchronized", unless you only want to display the years and months for
which a table contains data. What is important, for your report, is that you
set the appropriate critieria, so that only the data for "Jan 2009" or "Feb
2010" shows up in the report when you make those selections. The way you do
this will be determined by the data fields in your table, specificly the date
field.

In your case, you indicate you have a command button which opens the report
in preview mode. The code behind your command button should look something
like:

Private Sub cmdPreview_Click

dim strCriteria as string

'rename your controls so that they mean something
'rename combo43 to cbo_Month, text32 to txt_Year
if IsNull(me.cbo_Month) Then
msgbox "Select a month!"
Exit Sub
elseif isnull(me.txt_Year) Then
msgbox "Enter a valid year!"
Exit Sub
endif

'define the criteria that will limit your report to the
'appropriate month and year
strCriteria = "Format([DateField], "mmmm") = '" & me.cbo_Month & "' AND
" _
& "Format([DateField], "yyyy") = '" & me.txt_Year & "'"
docmd.openReport "ReportName", acViewPreview,,strCriteria, acDialog

End Sub

----
HTH
Dale



TotallyConfused said:
I have a form with a combo box . When you click on this combo box you choose
the month. Then you choose view rpt button which opens report in view form
for that particular month. This db only has 2009 dates. I inherited this db
and have been asked to add the ability to choose the year. 2010 dates will
be added to the db. What I though t to do is put another combo box for the
year. However, how do I get the "Year" combo box to synchronize with the
month combo box and then when clicking on the report command button only get
the correct year and month??? Below is a copy of the current combo box.
Thank you in advance for any help you can provide.

Private Sub Combo50_BeforeUpdate(Cancel As Integer)
If Me.Combo43 = "January" Then Me.Text32 =#1/1/2009#
If Me.Combo43 = "January" Then Me.Text32 =#1/20/2009#
If Me.Combo43 = "February" Then Me.Text32 =#2/1/2009#
If Me.Combo43 = "February" Then Me.Text32 =#2/15/2009#
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top