Hi Graham, thanks for the options.
I think I just about have a modified option 2 in place already.
1. This form opens as a filter from the main Project form, so you don't
have
the option to see data for other stores from here.
2. I have a combo box that searches for the employees of the particular
store and shows all of their transactions in the subform.
3. I only need the calendar to filter the subform by the selected date.
Thanks a million for all of your efforts.
--
Thanks for the brainwaves!
CJ
I blame the parents........
:
Hi CJ
Unbound controls do not appear in the link fields wizard, but they can
be
used (for master only, not child). I'm not sure if an unbound
calendar
control can be used in LinkMasterFields (I've never tried) but if it
can't
then a textbox certainly can. You can set the ControlSource of a
textbox
to
=[CalCtl1] and then use the textbox as your LinkMasterFields
component.
The approach you should use here depends on your needs. You can
either
have:
a) a subform showing ONLY the transactions for a given employee,
project,
and date (all three must be selected)
or
b) what I call a "progressive filter" - all transactions are shown,
but
when
an employee is selected from a combo box (or a project, or a date) the
list
is filtered accordingly. So you could show all transactions for a
given
ProjectID, or for a given EmployeeID on a given date, or whatever.
The first option simply uses a subform with three paired fields as a
link.
This is simple, but I think it is not very flexible or user-friendly
as
all
three criteria need to be specified.
The second option doesn't even require a subform - just a continuous
form -
and the AfterUpdate events for each of your three "selection controls"
builds a filter string and applies it to the form.
I'll give you some more details on the second option if you're
interested
in
that approach.
--
Good Luck!
Graham Mandeno [Access MVP]
Auckland, New Zealand
Hi:
Sorry, I had a couple of requery statements that were causing
grief.....they
are all gone now....no more error messages.
OK, the calendar is not bound to a field in the datasource for the
subform.
I just wanted to use it for locating transactions somehow.
At the moment, with your code in place, when I click on a date in
the
calendar not a single thing happens. I was thinking that the date
from
the
calendar would have to be criteria in the subform query, but the
Calendar
Control does not show up when I use the build button to add criteria
for
the
date field.
I guess I am expecting the transactions for the current employee to
filter
to the date that I am selecting with the calendar.
I also thought I should clarify, the calendar is on the main form,
should
it
be on the sub?
Does that help?
--
Thanks for the brainwaves!
CJ
I blame the parents........
:
Hi CJ
What this means is that the value of lngProjectID in the current
record
of
your subform is Null. If this is the case, then surely it is
meaningless
to
try to relocate it?
I'm further puzzled, because I understand your subform lists all
transactions for a given employee (lngEmpID) in a given store
(lngProjectID)
on a given day (CalCtl). If this is the case, then I cannot see
*how*
lngProjectID could be null (unless perhaps your subform allows
additions
and
you are on a new record).
Further, if you have just changed the date in the calendar then
surely
the
record which was current on the subform before (corresponding to
the
old
date) will no longer be included in the list for the newly selected
date,
so
trying to locate the old current record seems pointless.
There is something I'm missing here - could you please dispel my
confusion?
--
Thanks,
Graham Mandeno [Access MVP]
Auckland, New Zealand
Hi Graham, thanks for the response.
I added your code to the Click event of the calendar but I am
getting
"Invalid use of null"
A few details that might help you out.
1. The subform is continuous, it basically show all of the
transactions
an
employee made for the day at a particular store. Since an
employee
can
work
at different branches, I have used lngProjectID and lngEmpID as
my
link
fields to the main Store form.
2. The calendar is unbound. I was hoping to use it just to make
finding
a
date easier for the end users. If there is a better way, then
please
let
me
know.
Here is the code that I have in place now.
Private Sub CalCtl1_Click()
Dim sbf As Form, SavedID As Long
Set sbf = Me!fsubDailyBoxes.Form
SavedID = sbf!lngProjectID <This is the break point>
sbf.Requery
With sbf.RecordsetClone
.FindFirst "lngProjectID=" & SavedID
If Not .NoMatch Then sbf.Bookmark = .Bookmark
End With
End Sub
--
Thanks for the brainwaves!
CJ
I blame the parents........
:
Hi CJ
As you have discovered, a Requery will always reset to the first
record.
What you need to do is store the primary key value of the
subform's
current
record before the requery, then do a FindFirst to reposition the
record
after the requery. Something like this:
Dim sbf as Form, SavedID as Long
Set sbf = Me!fsubDailyBoxes.Form
SavedID = sbf!ID
sbf.Requery
With sbf.RecordsetClone
.FindFirst "ID=" & SavedID
If not .NoMatch then sbf.Bookmark = .Bookmark
End With
--
Good Luck!
Graham Mandeno [Access MVP]
Auckland, New Zealand
This was originally posted yesterday, November 16. However,
due
to
the
unhelpful response I received it looks like my questions were
answered.
My
questions were not answered so here I go again....... Thanks
for
your
patience.
Hi groupies.
I have a subform tied to a query. The date of the query uses
the
date
on
the
calendar of my main form as criteria.......my thought being
that
when
the
date on the calendar changes then the date of the query
changes
as
well.
I added a requery statement to the On Updated event of the
calendar,
