Open a filter and default value

[margaret]

So, once again for all you who are reading today, I have a form ... frmSales
that is based an a qrySales from tblSales, it includes a field "fairdate". I
would like the control tblsales.fairdate to read from tblControlFile!fairdate
AND filter any data to equal the tblControlFile!fairdate. On the "on open"
property I have

Me.fairdate.DefaultValue = "'" & DLookup("FairDate", "TblControlFile") & "'"
Me!fairdate = tables!tblcontrolfile.fairdate



And, it's not working. Can anyone help?

Margaret

 

[tina]

well, to achieve your stated objective, setting a default value has no
purpose, so you can lose that line of code. ditto setting a fairdate value,
also: 1) in the Open event, i don't think the records are available yet,
and 2) if they were, you would be setting the value of the fairdate field
(editing data) in the first record in the forms' recordset - regardless of
what record that might be, and regardless of what value might already be in
that field in that record.

what you want to do is *filter* the records in the form's recordset. first
of all, does TblControlFile only have one record in it? if not, how are you
going to choose which FairDate in the table you want? one common method in a
user interface would be to have a combobox control on a form, that's bound
to TblControlFile, so the user has a list of fair dates to choose from
before opening frmSales. but there are many ways to give the user the
opportunity to choose from multiple FairDate values - it all depends on the
setup of the user interface and the needs of the process flow.

next, once a FairDate has been identified, you can set the form's Filter
property, as

Me.Filter = "FairDate = #" & <your date value here> & "#"
Me.FilterOn = True

the proper syntax for filling in <your date value here> will depend on how
you get that date.

or you can add a WHERE clause to the OpenForm argument, to set criteria on
the FairDate field; again, proper syntax will depend on how you get the
date.

another method is to set criteria in qrySales, on the FairDate field, to the
FairDate field; and again, proper syntax depends...

hth