1) Yes, the DLookup should be in the Default Value of the ComboBox.
2) Do you have 1 as the value for the BoundColumn property? Even though the
comboBox maynot be bound to a field in the table (no value in the
ControlSource), in order to select a default value you have to designate
which column of the recordset will contains the value that the will be
considered as the combobox's value when an item is selected. Basically,
you're telling Access, when an item is selected I want you to use the value
in column [x] as the value for the control.
http://msdn.microsoft.com/en-us/library/aa286723(office.10).aspx
3) Try copying & pasting the Dlookup into the VBA Immediate Window to see
what value it returns. This'll confirm that the syntax for it is correct.
You'll need to enter a ? in front of the DLookup a in ...
?DLookUp("[TimesheetID]","[t_PayPeriod]","[PayPeriodFrom] >= Date() and
[PayPeriodTo] =< Date()")
Billiam said:
Hi David,
Thank yu for your help! By .defaultValue --does that mean the default value
area of the combobox? I have the select statement as the rowsource of the
combo box:
and the following in the default value of the property sheet under data.
Nothing is populating the combobox though(just the pay periods from the
rowsource...do I need an on open event, or got focus event?
Or have I missed what I am supposed to do?
(Only to make it a bit more readible for the post.)
BTW - The name of the table isn't required unless a field name could refer
to more than one table in the SQL statement.
SELECT [TimesheetID], [PayPeriodFrom], [PayPeriodTo] FROM t_PayPeriod ORDER
BY [PayPeriodFrom];
=DLookUp("[TimesheetID]","[t_PayPeriod]","[PayPeriodFrom] >= Date() and
[PayPeriodTo] =< Date()")
Thanks again for your help,
Billiam
:
Sounds like you'll need to set the .DefaultValue of the combobox to the value
returned by a DLookup.
It'll look something like...
.DefaultValue = DLookup("[PayPeriodId]","[PayPeriodsTableName]","[StartDate]
= Date() and [EndDate] =< Date()")
Basically you're telling Access to go out to the table that contains the pay
periods [PayPeriodsTableName] and retrieve the value [PayPeriodId] where the
[StartDate] in the record is equal to or greater than today's date and the
[EndDate] is equal to or less than today's date.
This is assuming that you're table has the following data
Id Start Date End Date
1000 10/1/2009 10/8/2009
1001 10/9/2009 10/15/2009
1002 10/16/2009 10/22/2009
It also assumes that the record source of the comboBox pulls directly from
the table with a RecordSource similar to Select [Id], [StartDate], [EndDate]
From PayPeriods ORDER by [StartDate].
To have the DLookup select a value in the comboBox, you *MUST* ensure that
the value returned by the DLookup matches a value in the bound column of the
comboBox.
Post back if you need some help.
Might have to use => and <=, I can never remember which language takes which.
:
the second option you suggested is what I am tryiing to do...I still want all
the payperiods to be available for choosing, just in case they need to do
vacation in the future, but I do want the value of the payperiod displayed
based on the system date.
I am looking to populate the combobox with the payperiod value (all
payperiods are in a table, and I also have them as a concatenated query)
based on the system date.
So if the system date is December 22, 2009,it searches the payperiods to
see which one it fits into, and then that payperiod is populated in the
combobox.
It is just a small timesaver for the enduser, so the pay period does not
always have to be selected, it populates automatically. Hope I am explainng
this better...sorry for the confusion!
Billiam
:
Are you looking to set a default value for the combobox or are you looking to
popuplate the combobox with specific values based on todays date? By the
later, I mean when the user expands the combobox the individual items are
based on the current date.
:
I have a form with a concatenated cboPayPeriod (based on a query of
t_PayPeriod) which I would like to have updated on Form Open or Load showing
the current pay period based on the computer system date.
In other words, I want the pay Period Combobox to be updated based on an
evaluation? of the system date. So if a Pay Period runs from October 5 to
October 18, and the system date is within, that range, it will display that
PayPeriod in the combobox.
Can this be done the way I have cboPayPeriod based on a concatenated query
(the cbo Payperiod was concatenated so that it says "October 5, 2009 TO
October 18", 2009, however, the table that the query is based on has seperate
fields PayPeriodFrom and PayPeriodTo).
Would really appreciate any help!
Thanks,
Billiam
