SetValue Difficulties in a Macro

[John Hannah]

My Database is for daily allocation of glaziers to execute
repair tasks. A Main Form frmGlazingOrderDetails contains
details of the tasks, with a start date and a finish date,
and it has a subform where the user selects glaziers on a
day-by-day basis. This works perfectly. There is a pop-up
form called frmHolidaysToday which uses a parameter Expr1:
[Select Date] to establish whether the date selected lies
<Between> the start date <And> the finish date, and warns
the user if any of his staff is unavailable on that
particular day. This also works very well when the user
enters the date parameter manually. I am trying to create
a macro to automate this task, called from the After
Update property of the [ServiceDate] control in the
Subform, and I am having no success at all with the
SetValue Action. I feel I am making a silly mistake with
the syntax somewhere, and would be most grateful for any
guidance.
Thanks in advance
John

 

[Steve Schapel]

I, for one, would be happy to offer some help with this. Just let us
know what the macro you have tried is, and how the ServiceDate is
supposed to relate to the Select Date, and we'll see what we can do.

 

[John Hannah]

Steve: Thank you for your reply.

This database is designed to allocate tasks to Glaziers.
Tables are GlazingOrders, Services, Employees, and
Holidays, and a GlazingOrderDetails table to link the
first three above.
The user browses frmGlazingOrderMaintenance (continuous
records), clicks on a record, then on a Command Button
which opens frmGlazingOrderDetails at that record. This
form has a subform <frmGlazingOrderDetails Subform>.
Because the job might take several days, the user enters a
start date and a finish date in the main form, then in the
subform (continuous) enters the Glazier's Name and the
working date <tbxServiceDate>, Control Source ServiceDate.
If the job needs more than one Glazier, he might enter
other names and dates at this time. This data is used in
forms and reports for planning daily and weekly
operations, and incidentally provides a history of the
execution of the job. At the moment I have provided a
Command Button on the main form, which runs a parameter
query, qryHolidays, on tblHolidays. The user has to enter
the date [Select Date] on which he is planning the job,
and the query uses an IIF/BETWEEN/AND function on the
HolidayStart and HolidayEnd fields. This is still named
Expr1 in the query. True results appear in a Popup form,
frmHolidaysToday, giving the user a list of any Glaziers
who may be unavailable on that date. All this works quite
well, but it is clumsy, I think. I was trying to clean
this up by calling a macro from the AfterUpdate property
of the tbxServiceDate, which would use as its parameter
the date just entered, and open the Popup form
frmHolidaysToday. This is where I am stumbling. What do
you think is my fundamental mistake?

Many thanks for your interest
John Hannah/Glass Northampton Ltd

-----Original Message-----
I, for one, would be happy to offer some help with this. Just let us
know what the macro you have tried is, and how the ServiceDate is
supposed to relate to the Select Date, and we'll see what we can do.

--
Steve Schapel, Microsoft Access MVP

My Database is for daily allocation of glaziers to execute
repair tasks. A Main Form frmGlazingOrderDetails contains
details of the tasks, with a start date and a finish date,
and it has a subform where the user selects glaziers on a
day-by-day basis. This works perfectly. There is a pop- up
form called frmHolidaysToday which uses a parameter Expr1:
[Select Date] to establish whether the date selected lies
<Between> the start date <And> the finish date, and warns
the user if any of his staff is unavailable on that
particular day. This also works very well when the user
enters the date parameter manually. I am trying to create
a macro to automate this task, called from the After
Update property of the [ServiceDate] control in the
Subform, and I am having no success at all with the
SetValue Action. I feel I am making a silly mistake with
the syntax somewhere, and would be most grateful for any
guidance.
Thanks in advance
John

.

 

[Steve Schapel]

John,

Thanks for the further information.

It seems to me that you need to replace the parameter prompt, which you
apparently have in the criteria of the qryHolidays query, with a
reference directly to the tbxServiceDate control, using syntax such as...
[Forms]![frmGlazingOrderDetails]![frmGlazingOrderDetails
Subform].[Form]![tbxServiceDate]
.... and then you can use an OpenForm action in the macro on the
AfterUpdate event of tbxServiceDate to open the frmHolidaysToday form.