Scripting conditional list box

[Guest]

I am trying to populate a drop box with time in increments of 15 min.
However, I want to be able to block (or delete) that time if it has already
been assigned to a worker on an assigned day.
ie:
Worker A tries to sign in on 5/2/06 at 7:15. If there is already an
appointment for 7:15, I want that time to NOT be a drop box option.

As I am new to this, I will need in depth direction. Will this be an easy do?
Thanks,
Vella

 

[J. Goddard]

Try this:
Create a small table with all possible appointment times, blocked or
not. Call it AppointmentTimes, for example, with a field called ApptTime

Then populate the combo box with SQL something like this:

Select ApptTime from AppointmentTimes where ApptTime not in (Select
AppointmentTable.ApptTime from AppointmentTable where
AppointmentTable.ApptDate = me!RequestedDate)

This assumes that the table of appointments contains only filled
appointment times.

This is what I might do; I'm sure there are other ways. MVP's?

John

 

[Guest]

john,
Great idea. That would save me from scripting for sure...I think? LOL.
I'll work on that one.
Thanks!

 

[Guest]

ok. I'm running into trouble and I think it is because I am not fully
understanding.
If I create a new table filled with only appt dates and times, what will
differentiate who has appts at what times?
Should I, instead, create a table for each employee that holds appts for
each? That seems a bit overkill.

 

[J. Goddard]

The new table I am suggesting serves only to provide a list if values
that the combo box can have, and it never needs to be changed. It
contains only one field.

The query to populate the combo box will list all the times from the
list, EXCEPT those which have already been taken on that date, and

Your main table of appointments contains all the other data, such as
names, phone numbers etc. The form used is based on the main table of
appointments.

Hope this makes it a bit clearer.

John

 

[Guest]

Awesome,
This works however, is there anyway to make it not pop up a box that asks
for the date?
Also, I need to compare names as well as dates/times. The current select
that was given above works fine i just need to add workers last name to that
too.

Oh boy...also, too, and... after each date entry, how do I make the entire
form refresh so that the correct times drop down in the next lines. I tried
this:

Private Sub Appointment_Time_AfterUpdate()
Me.Appointment_Time.Requery

It's not working well.

I really do appreciate all and any help.

 

[J. Goddard]

When does it ask for the date? From what I can see, the only reason it
might is that the SQL refers to a date field it can't find.

What do you want to do with the worker's last name in the SELECT? (make
sure they don't already have an appointment that day, for example?)

Oh boy...also, too, and... after each date entry, how do I make the entire
form refresh so that the correct times drop down in the next lines. I tried
this:

Private Sub Appointment_Time_AfterUpdate()
Me.Appointment_Time.Requery

Can you clarify what you mean by this? I don't think it will do much
this way. Is Appointment_Time the list of available times? If so, just
doing a requery won't work.

John

 

[Guest]

Hi John,
Yes, I need to compare names so that the same person does not have an
appointment time made available for a time he/she is already scheduled...just
as you said.

hrm. As far as the date, you may be right. I am so very new to scripting VB
that I put in:
SELECT ApptTime
FROM AppointmentTimes
WHERE ApptTime not in (Select
Table1.Appointment_Time from Table1 where
Table1.Date = me!Date);
(Date is a field on the form Schedule)

Should I have said something like
(Select
Table1.Appointment_Time from Table1 where
Table1.Date = Schedule.Date);
???

 

[J. Goddard]

Almost right!

SQL = "SELECT ApptTime
FROM AppointmentTimes
WHERE ApptTime not in (Select
Table1.Appointment_Time from Table1 where
Table1.Date = #" & me!Date & "#)";

Dates need to be delimited by # marks.

Just a note: Avoid giving date fields names like "Date"; Date is a
reserved word in MS Access, and using it can cause problems at times.

Can a person have two appointments on the same day? If the answer is
"Yes", then your code is OK.

If the answer is "No", then it becomes more complicated; you have to
first check if that person has an appointment on that day, at any time.

Also, do not use the worker last name to search - what if you have two
or more people with the same last name? Use a value you know is unique,
such as employee number.

John

 

[Guest]

Thanks John,
ok...
I tried the statement that you provided and got an error "Invalid SQL
statement: expected 'DELETE', 'INSERT'...ECT."
So, I'm thinking I needed to deliniate something in there? Feeling rather
silly.

As far as employee, you are right, employee number would be best to negate
redundancies. So, If I check for the employee name and time, then this should
be a perfect statement for my needs. Only, Im feeling slightly lost now.

 

[J. Goddard]

Hi -

It sounds like you may be using the SQL statement in a db.execute or
docmd.runsql or something like that. Can you post your code to show how
you are using it, please?

Thanks

John

 

[Guest]

hiyas John,
ok. at first I had set up a query called Time that executed when the list
box was clicked on... this didn't really work as it popped up a box asking
for the date before the results populated the list box.
Then you gave me this code:

SQL = "SELECT ApptTime
FROM AppointmentTimes
WHERE ApptTime not in (Select
Table1.Appointment_Time from Table1 where
Table1.Date = #" & me!Date & "#)";

I realized this had to be a VB code so I put this in under form properties
(code builder) as an On Focus for the list box ATime. Still, it wouldn't
populate the list box. I have to wonder if I didn't use the wrong syntax?
This VB coding is a bit harsh for a newbie.
So, I think I might need a bit more explicit (read dummy proof instruction?

 

[J. Goddard]

Hi Vella -

You said this:

I realized this had to be a VB code so I put this in under form properties
(code builder) as an On Focus for the list box ATime. ...

I'm not sure what you mean by "Form Properties ... as On Focus for the
list Box", but I think you just did not word it right. Anyway, try this:

In the properties for the List Box, put this code in the "on Got Focus"
event. The code SQL = .... has to be all on one line - your browser is
probably wrapping it so it looks like it is on several lines:


'========
Dim SQL as string

SQL = "SELECT ApptTime FROM AppointmentTimes WHERE ApptTime not in
Select Table1.Appointment_Time from Table1 where Table1.Date = #" &
me!Date & "#)";

me!atime.rowsource = sql
'========

Replace AppointmentTime with the name of the table containing the list
of times
Replace ApptTime with the name of the time field in that table (DON'T
call it "Time")
Replace Table1 with the name of your table of Appointments
Replace Date in Table1.date with the dame of the Appointment Date field
(Dont call it "Date")
Replace Date in me!date with the name of the form control containing the
requested date (Don't call it "date")

Before closing the code window, from the Menu, click Debug - Compile.
Any errors?

John

P.S.

...I had set up a query called Time that executed when the list
box was clicked on... this didn't really work as it popped up a box

asking for the date before the results populated the list box.

This might have been working, except that you called the query "Time".
Try renaming the query, because "Time" is an MS Access reserved word.

J

 

[Guest]

Hiyas John,
I did everything you said, right down to matching fields meticulously. For
some reason, the drop down box(ATime) won't populate. I wonder if the
Appt_Date field needs to be forced into Table1 before this will work? (or
does this happen dynamically?)

I'm a bit weary of adding any more script but I will have to add worker ID
to this as well. *sigh* I wish I knew where I was going wrong.