Date combo box question

[Jeffrey K. Ries]

I am working on a timesheet form in access. One of the combo boxes I am
trying to setup is for the "Week of:". This should show the Monday starting
the work week. What I would like to happen is to have this combo box show
the currents weeks Monday with a drop down for the last half-dozen or so
Mondays.

Is the best way to do this to link the combo box to a query?
If so, could someone give me some direction to get the query started? I
understand the basics of querys to get selected info from a table, but I
have never used one to pull info from the current date.

Thanks,
Jeffrey K. Ries

 

[Duane Hookom]

You could set the row source type to value list and use code in the on open
of the form:
Private Sub Form_Open(Cancel As Integer)
Dim datMonday As Date
For datMonday = DateAdd("d", 2 - Weekday(Date), Date) To _
Date - 50 Step -7
Me.cboDates.RowSource = Me.cboDates.RowSource & _
Format(datMonday, "mm/dd/yyyy") & ";"
Next
End Sub

 

[Jeffrey K. Ries]

That worked beautifully! Thank you.

Now how do I make the first entry in the list come up as the default? Would
that be an addition to the code you provided? ...or do I need some other
code in the 'Default' property?

Jeffrey K. Ries

 

[Duane Hookom]

Set the value property of the combo box.
Me.cboDates =DateAdd("d", 2 - Weekday(Date), Date)

 

[Jeffrey K. Ries]

I don't see a 'value' property of the combo box so I assume you mean the
'default value'. I placed the code there and I get nothing. So I took the
code and fit it into the original code you provided for the 'OnOpen'
property of the form itself.

This worked, ...kinda. It does place the correct date into the 'default
value' property, but then this date is translated into 12/30/1899. What
gives? The 'format' property of the combo is set to Short Date; isn't that
enough?

Also, the original code for the 'OnOpen' property of the form does one thing
which I find troubling. It add the eight dates exactly like it should,
however the next time I open the form it adds the eight again onto the end
of the original list. This happens each time the form is run which results
in an eventual monster of a list. Is there a way to clear the list before
populating it each time?

Sorry to drag out this subject. I am trying desperately to learn this
program, but the VB code is the most difficult and, of course, most
important part.

Thanks again,
Jeffrey K. Ries

 

[Jeffrey K. Ries]

OK, I got the default value to work using =[cboDates].[ItemData]("0"). It
looks like the help files are helpful after all, who knew?

The multiple list entries are still a mystery tho if you have the time.

Thanks,
Jeffrey K. Ries

 

[Duane Hookom]

When the form closes, the Row Source property should go back to nothing
unless you had saved it. You could use
Private Sub Form_Open(Cancel As Integer)
Dim datMonday As Date
Me.cboDates.RowSource = ""
For datMonday = DateAdd("d", 2 - Weekday(Date), Date) To _
Date - 50 Step -7
Me.cboDates.RowSource = Me.cboDates.RowSource & _
Format(datMonday, "mm/dd/yyyy") & ";"
Next
Me.cboDates =DateAdd("d", 2 - Weekday(Date), Date)
End Sub

--
Duane Hookom
MS Access MVP

OK, I got the default value to work using =[cboDates].[ItemData]("0"). It
looks like the help files are helpful after all, who knew?

The multiple list entries are still a mystery tho if you have the time.

Thanks,
Jeffrey K. Ries

I don't see a 'value' property of the combo box so I assume you mean the
'default value'. I placed the code there and I get nothing. So I took the
code and fit it into the original code you provided for the 'OnOpen'
property of the form itself.

This worked, ...kinda. It does place the correct date into the 'default
value' property, but then this date is translated into 12/30/1899. What
gives? The 'format' property of the combo is set to Short Date; isn't that
enough?

Also, the original code for the 'OnOpen' property of the form does one thing
which I find troubling. It add the eight dates exactly like it should,
however the next time I open the form it adds the eight again onto the end
of the original list. This happens each time the form is run which results
in an eventual monster of a list. Is there a way to clear the list before
populating it each time?

Sorry to drag out this subject. I am trying desperately to learn this
program, but the VB code is the most difficult and, of course, most
important part.

Thanks again,
Jeffrey K. Ries

the

on boxes combo
box half-dozen
or table,
but

 

[Jeffrey K. Ries]

That did it. Thank you.

Jeffrey K. Ries

When the form closes, the Row Source property should go back to nothing
unless you had saved it. You could use
Private Sub Form_Open(Cancel As Integer)
Dim datMonday As Date
Me.cboDates.RowSource = ""
For datMonday = DateAdd("d", 2 - Weekday(Date), Date) To _
Date - 50 Step -7
Me.cboDates.RowSource = Me.cboDates.RowSource & _
Format(datMonday, "mm/dd/yyyy") & ";"
Next
Me.cboDates =DateAdd("d", 2 - Weekday(Date), Date)
End Sub

--
Duane Hookom
MS Access MVP

OK, I got the default value to work using =[cboDates].[ItemData]("0"). It
looks like the help files are helpful after all, who knew?

The multiple list entries are still a mystery tho if you have the time.

Thanks,
Jeffrey K. Ries

I don't see a 'value' property of the combo box so I assume you mean the
'default value'. I placed the code there and I get nothing. So I

took
the

code and fit it into the original code you provided for the 'OnOpen'
property of the form itself.

This worked, ...kinda. It does place the correct date into the 'default
value' property, but then this date is translated into 12/30/1899. What
gives? The 'format' property of the combo is set to Short Date; isn't that
enough?

Also, the original code for the 'OnOpen' property of the form does one thing
which I find troubling. It add the eight dates exactly like it should,
however the next time I open the form it adds the eight again onto the end
of the original list. This happens each time the form is run which results
in an eventual monster of a list. Is there a way to clear the list before
populating it each time?

Sorry to drag out this subject. I am trying desperately to learn this
program, but the VB code is the most difficult and, of course, most
important part.

Thanks again,
Jeffrey K. Ries


Set the value property of the combo box.
Me.cboDates =DateAdd("d", 2 - Weekday(Date), Date)

--
Duane Hookom
MS Access MVP


That worked beautifully! Thank you.

Now how do I make the first entry in the list come up as the default?
Would
that be an addition to the code you provided? ...or do I need some
other
code in the 'Default' property?

Jeffrey K. Ries


You could set the row source type to value list and use code in

the

on
open
of the form:
Private Sub Form_Open(Cancel As Integer)
Dim datMonday As Date
For datMonday = DateAdd("d", 2 - Weekday(Date), Date) To _
Date - 50 Step -7
Me.cboDates.RowSource = Me.cboDates.RowSource & _
Format(datMonday, "mm/dd/yyyy") & ";"
Next
End Sub

--
Duane Hookom
MS Access MVP


I am working on a timesheet form in access. One of the combo boxes
I
am
trying to setup is for the "Week of:". This should show the Monday
starting
the work week. What I would like to happen is to have this combo
box
show
the currents weeks Monday with a drop down for the last half-dozen
or
so
Mondays.

Is the best way to do this to link the combo box to a query?
If so, could someone give me some direction to get the query
started?
I
understand the basics of querys to get selected info from a table,
but
I
have never used one to pull info from the current date.

Thanks,
Jeffrey K. Ries