Combo Box help (repost)

[TotallyConfused]

As I mentioned before, I inherited a db and I am trying to figure it out.
Not sure I did a good job of explaining myself the first time. But I really
need help with this. The one change I have to make is to add 2010 dates and
let user choose 2009 or 2010 or both. This db only has 2009 dates. I tried
to add 2010 dates and although it lets me enter 2010 dates it does not show
2010 dates when I run the report.

The first form (frm1) opens it has two command buttons. You choose one or
the other than another form (frm 2) opens. When this forms opens,it opens
with a single combo box where you can pick any month from January through
December. Once you choose the month, command buttons appear. One to view
report. When you click on the view report command you can view all of the
dates for the month you choose.

Frm 2 has the following code that call the combo box and (invisible) text
boxes in this form. This code houses the dates as follows:

Private Sub Comp50 BeforeUpdate(Cancel as Integer)
If Me.Combo50 = "Janaury" Then Me.Text23 = #01/01/2009#
If Me.Combo50 = "Janaury" Then Me.Text43 = #01/22/2009#
If Me.Combo50 = "February" Then Me.Text23 = #02/10/2009#
If Me.Combo50 = "February" Then Me.Text43 = #02/22/2009#

(and so forth through December)

End Sub

Text23 and Text43 are the invisible text boxes in form2 these correspond to
the choice you make in Frm1.

I tried adding 2010 dates but the report will not pick up 2010 dates ony
2009.

The report code is the following:
Private Sub Detail Form(Cancel As Integer, FormatCount As Integer)
If Me.Group_name>0 Then Me.Detail.BackColor = 14408667 Else
Me.Detail.BackColor = 16777215
If Me.[Type] = "something" Then Me.Label24.Visible = False

End Sub

Private Sub PageheaderSection_Format(Cancel As Integer, FormatCount As
Integer)
If Me.[Type] = "something" Then Me.Label24.Visible = False Else
Me.Label24.Visible = True

End Sub

This is all the code in the report. My understanding of this is it is only
the formatting for the report headers. However, the report's source is a
query. The query's sql:

SELECT TableOne.State, TableOne.[Location],
TableOne.[DocName],TableOne.[DOCID],TableOne.ID,TableOne.[Group],TableOne.[MeetingDate],TableOne.[Type],IIF([Location]="New
York","NY" (etc Locations) too many to list
FROM TableOne
WHERE (((TableOne.State)=Forms!Frm1.State) AND ((TableOne.[MeetingDate])
Between forms!Frm2.Text23 AND forms!Frm2.Text43))

Can someone please help me how to have the report show 2010 dates. I don't
want to change much on this db as there is a possibiity that it may not be
used after 1st quarter of 2010. However, for now users need to see 2009 or
2010 or both. Thank you very much for any help I can get. As this is need
soon.

 

[Mr. B]

For a quick fix in your application, you could add a combo box to your form
and name the new combo box: cboYear. Set the “Row Source Type†property to
“Value List†and enter “2009;2010†(without the quotes) in then Row Source
property. This will allow you to select 2009 or 2010 from the new combo box.

With this new combo box added changes made, you can change each line of your
existing code:
If Me.Combo50 = “January†then Me.Text23 = #01/01/2009â€
By replacing the “2009†with " & Me.cboYear & "#"

You will need to make the same change at the end of each line so the line
will look like:
If Me.Combo50 = “January†then Me.Text23 = "#01/01/" & Me.cboYear & "#"
If Me.Combo50 = "Janaury" Then Me.Text43 = #01/22/" & Me.cboYear & "#"

This will duplicate what you are currently getting in your Text23 and Text43
text boxes with the exception that it will now have the year you have
selected from your new combo box.

Please understand that there are other ways to make this situation much more
efficient, but you wanted a quick fix. If you find that you need this to be
made to be better for use later, you can address other issues later.
-----
HTH
Mr. B
http://www.askdoctoraccess.com/
Doctor Access Downloads Page:
http://www.askdoctoraccess.com/DownloadPage.htm

As I mentioned before, I inherited a db and I am trying to figure it out.
Not sure I did a good job of explaining myself the first time. But I really
need help with this. The one change I have to make is to add 2010 dates and
let user choose 2009 or 2010 or both. This db only has 2009 dates. I tried
to add 2010 dates and although it lets me enter 2010 dates it does not show
2010 dates when I run the report.

The first form (frm1) opens it has two command buttons. You choose one or
the other than another form (frm 2) opens. When this forms opens,it opens
with a single combo box where you can pick any month from January through
December. Once you choose the month, command buttons appear. One to view
report. When you click on the view report command you can view all of the
dates for the month you choose.

Frm 2 has the following code that call the combo box and (invisible) text
boxes in this form. This code houses the dates as follows:

Private Sub Comp50 BeforeUpdate(Cancel as Integer)
If Me.Combo50 = "Janaury" Then Me.Text23 = #01/01/2009#
If Me.Combo50 = "Janaury" Then Me.Text43 = #01/22/2009#
If Me.Combo50 = "February" Then Me.Text23 = #02/10/2009#
If Me.Combo50 = "February" Then Me.Text43 = #02/22/2009#

(and so forth through December)

End Sub

Text23 and Text43 are the invisible text boxes in form2 these correspond to
the choice you make in Frm1.

I tried adding 2010 dates but the report will not pick up 2010 dates ony
2009.

The report code is the following:
Private Sub Detail Form(Cancel As Integer, FormatCount As Integer)
If Me.Group_name>0 Then Me.Detail.BackColor = 14408667 Else
Me.Detail.BackColor = 16777215
If Me.[Type] = "something" Then Me.Label24.Visible = False

End Sub

Private Sub PageheaderSection_Format(Cancel As Integer, FormatCount As
Integer)
If Me.[Type] = "something" Then Me.Label24.Visible = False Else
Me.Label24.Visible = True

End Sub

This is all the code in the report. My understanding of this is it is only
the formatting for the report headers. However, the report's source is a
query. The query's sql:

SELECT TableOne.State, TableOne.[Location],
TableOne.[DocName],TableOne.[DOCID],TableOne.ID,TableOne.[Group],TableOne.[MeetingDate],TableOne.[Type],IIF([Location]="New
York","NY" (etc Locations) too many to list
FROM TableOne
WHERE (((TableOne.State)=Forms!Frm1.State) AND ((TableOne.[MeetingDate])
Between forms!Frm2.Text23 AND forms!Frm2.Text43))

Can someone please help me how to have the report show 2010 dates. I don't
want to change much on this db as there is a possibiity that it may not be
used after 1st quarter of 2010. However, for now users need to see 2009 or
2010 or both. Thank you very much for any help I can get. As this is need
soon.

 

[TotallyConfused]

Thank you Mr. B for responding. Very much appreciated as this is driving me
crazy. I followed your instructions to the T. But I get the following
message when I run form/report:

"This expression is typed incorrectly, or it is too complex to be
evaluated. For example a numeric expression may contain too m any
complicated elements. Try simplying the expression by assigning parts of the
expression to variables."

I created the cboYear box - no problem. I add the cboYear to the code as:

If Me.Combo50 = "January" Then Me.Text23 = "#01/10/" & Me.cboYear & "#"
If Me.Combo50 = "January" Then Me.Text43 = "#01/20/" & Me.cboYear & "#"
If Me.Combo50 = "January" Then Me.Text23 = "#01/01/" & Me.cboYear & "#"
If Me.Combo50 = "January" Then Me.Text43 = "#01/22/" & Me.cboYear & "#"
If Me.Combo50 = "February" Then Me.Text23 = "#02/13/" & Me.cboYear & "#"
If Me.Combo50 = "February" Then Me.Text43 = "#02/22/" & Me.cboYear & "#"
If Me.Combo50 = "February" Then Me.Text23 = "#02/10/" & Me.cboYear & "#"
If Me.Combo50 = "February" Then Me.Text43 = "#02/22/" & Me.cboYear & "#"

There are two entries for each month for each year. A total of 4 entries
for each month for two years. Not sure if this is why I got the message.

It was also requested that I give the user a choice of picking the year or
picking both years. Can this be accomplished? Yes I do not want to revamp
the db because there is a chance that it may not be used after 1st quarter
2010. So far now if you can just help me fix with what is available it would
be very much appreciated. Thank you.

For a quick fix in your application, you could add a combo box to your form
and name the new combo box: cboYear. Set the “Row Source Type†property to
“Value List†and enter “2009;2010†(without the quotes) in then Row Source
property. This will allow you to select 2009 or 2010 from the new combo box.

With this new combo box added changes made, you can change each line of your
existing code:
If Me.Combo50 = “January†then Me.Text23 = #01/01/2009â€
By replacing the “2009†with " & Me.cboYear & "#"

You will need to make the same change at the end of each line so the line
will look like:
If Me.Combo50 = “January†then Me.Text23 = "#01/01/" & Me.cboYear & "#"
If Me.Combo50 = "Janaury" Then Me.Text43 = #01/22/" & Me.cboYear & "#"

This will duplicate what you are currently getting in your Text23 and Text43
text boxes with the exception that it will now have the year you have
selected from your new combo box.

Please understand that there are other ways to make this situation much more
efficient, but you wanted a quick fix. If you find that you need this to be
made to be better for use later, you can address other issues later.
-----
HTH
Mr. B
http://www.askdoctoraccess.com/
Doctor Access Downloads Page:
http://www.askdoctoraccess.com/DownloadPage.htm

As I mentioned before, I inherited a db and I am trying to figure it out.
Not sure I did a good job of explaining myself the first time. But I really
need help with this. The one change I have to make is to add 2010 dates and
let user choose 2009 or 2010 or both. This db only has 2009 dates. I tried
to add 2010 dates and although it lets me enter 2010 dates it does not show
2010 dates when I run the report.

The first form (frm1) opens it has two command buttons. You choose one or
the other than another form (frm 2) opens. When this forms opens,it opens
with a single combo box where you can pick any month from January through
December. Once you choose the month, command buttons appear. One to view
report. When you click on the view report command you can view all of the
dates for the month you choose.

Frm 2 has the following code that call the combo box and (invisible) text
boxes in this form. This code houses the dates as follows:

Private Sub Comp50 BeforeUpdate(Cancel as Integer)
If Me.Combo50 = "Janaury" Then Me.Text23 = #01/01/2009#
If Me.Combo50 = "Janaury" Then Me.Text43 = #01/22/2009#
If Me.Combo50 = "February" Then Me.Text23 = #02/10/2009#
If Me.Combo50 = "February" Then Me.Text43 = #02/22/2009#

(and so forth through December)

End Sub

Text23 and Text43 are the invisible text boxes in form2 these correspond to
the choice you make in Frm1.

I tried adding 2010 dates but the report will not pick up 2010 dates ony
2009.

The report code is the following:
Private Sub Detail Form(Cancel As Integer, FormatCount As Integer)
If Me.Group_name>0 Then Me.Detail.BackColor = 14408667 Else
Me.Detail.BackColor = 16777215
If Me.[Type] = "something" Then Me.Label24.Visible = False

End Sub

Private Sub PageheaderSection_Format(Cancel As Integer, FormatCount As
Integer)
If Me.[Type] = "something" Then Me.Label24.Visible = False Else
Me.Label24.Visible = True

End Sub

This is all the code in the report. My understanding of this is it is only
the formatting for the report headers. However, the report's source is a
query. The query's sql:

SELECT TableOne.State, TableOne.[Location],
TableOne.[DocName],TableOne.[DOCID],TableOne.ID,TableOne.[Group],TableOne.[MeetingDate],TableOne.[Type],IIF([Location]="New
York","NY" (etc Locations) too many to list
FROM TableOne
WHERE (((TableOne.State)=Forms!Frm1.State) AND ((TableOne.[MeetingDate])
Between forms!Frm2.Text23 AND forms!Frm2.Text43))

Can someone please help me how to have the report show 2010 dates. I don't
want to change much on this db as there is a possibiity that it may not be
used after 1st quarter of 2010. However, for now users need to see 2009 or
2010 or both. Thank you very much for any help I can get. As this is need
soon.