ComboBox2 Clear On Update of ComboBox1

[StevePrefontaine]

Hi,

I have a simple form with 3 Combo Boxes, all unbound. The first lists Months
(1 through 12), the second lists Years (2007 through 2010), and the third
lists Week Endings. The Week Ending combo box is populated using a select
query based on the selection in the other 2 combo boxes. I have simple
requeries on the after updates of the month and year combo boxes, which
successfully repopulate the week ending rows when month and/or year are
updated.

I have a button that generates a report based on this week ending. I now
would like to display an error message if the user clicks this button, but
has not make a selection from the week ending combo box. I have been able to
successfully do this if the user has not selected the week ending at any
previous point. I think this is because I've set the default value of week
ending to "". However, if the user has previously selected a week ending, and
then updated the month and/or year, the week ending is now blank, but no
error message is displayed. Here is a copy of the form code:

Private Sub cmbmonth_AfterUpdate()

Me.week_end.Requery
Me.week_end = ""

End Sub

Private Sub cmbyear_AfterUpdate()

Me.week_end.Requery
Me.week_end = ""

End Sub

Private Sub Command0_Click()
On Error GoTo Err_Command0_Click


DoCmd.Close

Exit_Command0_Click:
Exit Sub

Err_Command0_Click:
MsgBox Err.Description
Resume Exit_Command0_Click

End Sub
Private Sub Command1_Click()
On Error GoTo Err_Command1_Click

If Me.week_end = "" Then

x = MsgBox("Week Ending Required!", , "ERROR!")

Else
Dim stDocName As String

stDocName = "rptWeekly_Timesheets2"
DoCmd.OpenReport stDocName, acPreview

End If

Exit_Command1_Click:
Exit Sub

Err_Command1_Click:
MsgBox Err.Description
Resume Exit_Command1_Click

End Sub

Keep in mind, I'm fairly new at VBA and not all of the code shown here was
done by me, but I have a basic understanding of it. Thank you.

 

[DStegon via AccessMonster.com]

First off... I hate this:

x = MsgBox("Week Ending Required!", , "ERROR!")

ahhhh.... just use

MsgBox "Week Ending Required!", , "ERROR!"

ok.. now that the pet peeve is done lets look at your other issue... well
almost Move all dim statements to the top of the sub or function for clarity
and useablity. Ok... now...

What you should be doing it testing for the length of of the value in the cmb

If len(Me.week_end & "") = 0 Then

MsgBox "Week Ending Required!", , "ERROR!"

Else
Dim stDocName As String

stDocName = "rptWeekly_Timesheets2"
DoCmd.OpenReport stDocName, acPreview

End If

This will assure that some value has been choosen in the week_end combo by
turing the value of the combo into a string and testing that there is a
length.

There are other ways to do this but this is a simple way to test for blank
values in a combo box.

Also, why not have the program dropdown the week_end box after requery so the
user knows they have to pick from something???

Private Sub cmbyear_AfterUpdate()

Me.week_end.Requery me.week_end.setfocus
me.week_end.dropdown

End Sub

 

[StevePrefontaine via AccessMonster.com]

That worked great! Thank you for the added tips too. Every little bit helps.

First off... I hate this:

x = MsgBox("Week Ending Required!", , "ERROR!")

ahhhh.... just use

MsgBox "Week Ending Required!", , "ERROR!"

ok.. now that the pet peeve is done lets look at your other issue... well
almost Move all dim statements to the top of the sub or function for clarity
and useablity. Ok... now...

What you should be doing it testing for the length of of the value in the cmb

If len(Me.week_end & "") = 0 Then

[quoted text clipped - 7 lines]

End If

This will assure that some value has been choosen in the week_end combo by
turing the value of the combo into a string and testing that there is a
length.

There are other ways to do this but this is a simple way to test for blank
values in a combo box.

Also, why not have the program dropdown the week_end box after requery so the
user knows they have to pick from something???

Private Sub cmbyear_AfterUpdate()

Me.week_end.Requery me.week_end.setfocus
me.week_end.dropdown

End Sub

Hi,

[quoted text clipped - 68 lines]

Keep in mind, I'm fairly new at VBA and not all of the code shown here was
done by me, but I have a basic understanding of it. Thank you.

 

[StevePrefontaine via AccessMonster.com]

Ok, I got that to work fine. Right now my Week Ending combo box is populated
by a table. This table has 3 fields: Month, Year, and Week Ending. The Week
Ending Dates are all Sundays. As mentioned before, the Week Ending row source
is a select query based on the selection of the month and year combo boxes.
Is there any way I could populate the Week Ending combo box with only Sundays
in the selected month and year without using a table that lists every Sunday
for years on end?

I guess my main obstacle is that I'm unable to have it recognize days of the
week. Once I get that down, I should be able to fight my way through the rest.
Thank you.

First off... I hate this:

x = MsgBox("Week Ending Required!", , "ERROR!")

ahhhh.... just use

MsgBox "Week Ending Required!", , "ERROR!"

ok.. now that the pet peeve is done lets look at your other issue... well
almost Move all dim statements to the top of the sub or function for clarity
and useablity. Ok... now...

What you should be doing it testing for the length of of the value in the cmb

If len(Me.week_end & "") = 0 Then

[quoted text clipped - 7 lines]

End If

This will assure that some value has been choosen in the week_end combo by
turing the value of the combo into a string and testing that there is a
length.

There are other ways to do this but this is a simple way to test for blank
values in a combo box.

Also, why not have the program dropdown the week_end box after requery so the
user knows they have to pick from something???

Private Sub cmbyear_AfterUpdate()

Me.week_end.Requery me.week_end.setfocus
me.week_end.dropdown

End Sub

Hi,

[quoted text clipped - 68 lines]

Keep in mind, I'm fairly new at VBA and not all of the code shown here was
done by me, but I have a basic understanding of it. Thank you.

 

[DStegon via AccessMonster.com]

Sure. In your select query and another day value that is filtered on the
DayPart and have it equal 1.

Example:

SELECT Orders.OrderDate, Year([OrderDate]) AS GivesUsTheYear, Month(
[OrderDate]) AS GivesUsTheMonth, Day([OrderDate]) AS GivesUsTheDay, Weekday(
[OrderDate]) AS GivesUsTheWeekDay1_To_7
FROM Orders WHERE (((Weekday([OrderDate]))=1));

So in the above, you can set the where clause on the Weekday("Date") to equal
Sunday which is 1.

If you already have the year and month filtered in the where clause then just
add the weekend part and you would only get the days in the month and year
that were Sundays.

Ok, I got that to work fine. Right now my Week Ending combo box is populated
by a table. This table has 3 fields: Month, Year, and Week Ending. The Week
Ending Dates are all Sundays. As mentioned before, the Week Ending row source
is a select query based on the selection of the month and year combo boxes.
Is there any way I could populate the Week Ending combo box with only Sundays
in the selected month and year without using a table that lists every Sunday
for years on end?

I guess my main obstacle is that I'm unable to have it recognize days of the
week. Once I get that down, I should be able to fight my way through the rest.
Thank you.

First off... I hate this:

[quoted text clipped - 40 lines]