Stop duplication of appointment

[cheyanne]

Hi
I have a form named frmAppointments - currently it uses tblAppointments as its control source, but I could change to a query if it helps.

Amongst the fields I have for this form are txtAppointmentDate which is a date field, formatted to short date, and a list box named lstTimes

whose control source is tblAppointmentTimes.lstTimes. (This table has two fields an ID field and one for times at quarter hour intervals)


The idea is that the member of staff, when booking an appointment, first selects the date and then an appointment time for that date.
The system works as intended but currently there is a potential danger of duplicate appointments What I need is some VBA code, possibly on the OnClick event
of lstTimes that checks to see whether, on that particular date, another appointment has been scheduled for the same time. If so this would then have a
MsgBox advising of the same and asking them to select a new appointment time. Presumably followed by a set focus returning to lstTimes.

Can anyone assist me to overcome this problem?

Any assistance would be greatly appreciated.

Regards



Cheyanne

[Bob Fitz]

My suggestiom would be to use the Before Update event of the list box. Use the DLookup() funtion with the date and time as criteria. If it returns a record, the update can be cancelled and a msg box shown to the user.

[jzwp11]

Why can't you change the row source of the list box to a query that will remove already used appointment times from the list that way the user can select from only available appointment times.

[Bob Fitz]

Why can't you change the row source of the list box to a query that will removes already used appointment times from the list that way the user can select from only available appointment times.

That sounds like a much better idea.

[jzwp11]

How are you capturing the appointment time in tblAppointments? Are you capturing the actual time or the ID field value?

I would probably have some code in the after update event of the date textbox that creates/filters the row source of the list box based on the entered date. The following assumes that the ID field corresponding to the time is what is stored in the appointment table and that the ID field is the bound field of the list box.

Dim mySQL as string
mySQL= "SELECT tblAppointmentTimes.ID, tblAppointmentTimes.lstTimes FROM tblAppointmentTimes WHERE tblAppointmentTimes.ID NOT IN (SELECT foreignkeytotimefield FROM tblAppointments WHERE datefield = #" & me.datecontrolname & "#")
me.lstTimes.rowsource=mySQL

[cheyanne]

Hi Bob

Thanks for your prompt reply.

Unfortunately I am still learning to code with VBA coding and still find difficulty implementing it as required.
I have introduced this code on the BeforeUpdate event of the lstTimes list box.


Private Sub lstTimes_BeforeUpdate(Cancel As Integer)
Dim Duplicate As Integer
Duplicate = DLookup("[AppointmentTime]", "tblAppointments", "[AppointmentTime]=forms!frmAppointments![lstTimes]")
If True Then
MsgBox "True", vbOKonly
End If
End Sub (The two pertinent fields in tblAppointments are AppointmentDate and AppointmentTime

If I try to duplicate a record I now get the MsgBox saying "True" which presumably means it has evaluated that another 12.00 appointment (or whatever) has already been allocated. (Is this assumption correct?)

Howver if I choose another time, say 11.30 (which is free on 4th June 2012) I am advised that this line

Duplicate = DLookup("[AppointmentTime]", "tblAppointments", "[AppointmentTime]=forms!frmAppointments![lstTimes]") produces an invalid use of Null.

Would this code, if nearly correct, find an appointment for "4th June 2012" or for any 12.00 appointment?

I have never tried to use DLookup with two criteria before.

Regards

Cheyanne

[Bob Fitz]

Did you see what jzwp11 suggested in post #3. This seems like a much better idea to me.

[cheyanne]

Hi jzwp11

You are clearly much further up the Access motorway than I am. If only I could have thought of a solution like that!

I have attached two screenshots that show a dataview shot of the tblAppointments and a formview of frmAppointments which will hopefully
assist your thoughts.

The ID field in tblAppointments is AppointmentRef and the two pertinent fields are AppointmentDate and AppointmentTime. The first is formatted
as a short date and the latter as a short time. The list box lstTimes is bound to the field AppointmentTime.


If it were possibly to run a query that would only permit the user to offer an "available" date for that day would be superb.

So far I have got as far as


Private Sub lstTimes_AfterUpdate()
Dim mySQL As String
mySQL = "SELECT tblAppointments.AppointmentRef, tblAppointment.lstTimes FROM tblAppointment WHERE tblAppointment.AppointmentRef NOT IN (SELECT foreignkeytotimefield FROM tblAppointments WHERE appointmentDate = #" & me.txtAppointmentDate & "#"))
Me.lstTimes.RowSource = mySQL
End Sub

I do not understand what you meran by the forereign key to time field. Maybe the screenshots will help you.

I would much value your assistance if possible.

Regards

Cheyanne

[jzwp11]

First, regarding the code, it should be in the After Update event of the date textbox not the list box.

I do not understand what you meran by the forereign key to time field.

The time field in your tblAppointments is essentially acting as a foreign key but it is generally prefered to reference the ID that goes with that time rather than the time value itself. First, I would recommend that you change the field name of the ID field of tblAppointmentTimes to something more descriptive such as ApptTimeID. Then I would reference this field (not the time) in tblAppointments. By the way, your ClientRef field in tblAppointment referes to a client that is listed in another table--ClientRef is a foreign key.

tblAppointments
-AppointmentRef primary key, autonumber
-AppointmentDate
-fkApptTimeID foriegn key to tblAppointmentTimes; this field would need to be a long integer number datatype assuming that the ID/ApptTimeID is autonumber.
-ClientRef foreign key to your client table (I assume)

[jzwp11]

Attached is an example database that shows how I would structure the foreign key field for the time in tblAppointments.

By the way, the way you reference the client and their pet may also not be correct. If a client can have several pets, then that describes a one-to-many relationship.

tblClients
-pkClientID primary key, autonumber
-txtSurname
-txtFirstName

tblPets
-pkPetID primary key, autonumber
-fkClientID foreign key to tblClients
-txtPetName

You would then just need to referenct pkPetID as a foreign key in tblAppointments. You would not include the name fields in the appointment table since doing so duplicates what is already in the client and pet tables. I have included the above structure in the attached database as well

[cheyanne]

Hi again

I think I have done as requested but am still havings errors. The code I introduced is

Private Sub txtAppointmentDate_AfterUpdate()
Dim mySQL As String
mySQL= "SELECT tblAppointmentTimes.ApptTimeID, tblAppointmentTimes.lstTimes FROM tblAppointmentTimes WHERE tblAppointmentTimes.AppTimeID NOT IN (SELECT ApptTimeID FROM tblAppointments WHERE txtAppointmentDate = #" & me.txtAppointmentDate & "#")
Me.lstTimes.RowSource = mySQL
End Sub

This gives a syntax error but I know not where.

Also I now have the AppTimeID field is showing a value in the datasheet of tblAppointments but it no longer shoes the actual time (see record 2) and this will be
needed to print on the daily appointments schedule. I have attached a screenshot of the table. Note record was introduced when I had the source control of lstTimes
set to AppointmentTime rather than now which is set to ApptTimeID.
field

If I want to incorporate your code into a If Else, End If how is this best done?

Ideally I would like the code to run and if it finds a duplcated record, for the msgbox to say "This time has already been allocated. Please offer the Client another date", vbOKOnly.

If there is "no duplicated" appointment, then for the MsgBox to say "Appointment confirmed", vbOKOnly.

I understand the If,Else, Enf If but need to know how to integrate your code. Is it simply

If
Your code
MsgBox "An appointment has already been made for this time. Please offer the Client another date", vbOKOnly.
Else
MsgBox ""Appointment confirmed", vbOKOnly.
End If

Sorry to be a pain but this is above my head at my current level of knowledge.

I note your comments about putting "duplicated fields" in tables, but I struggle to find a way to have this information available for reports e.g. Surname and PetName.
Also when you return to the record the information is often missing. Should I use a query? (instread of the table as the control source)

Regards

Cheyanne

[jzwp11]

With my approach, you do not need a message warning of a duplicate because my approach will not allow a record with a duplicate time to be created because an already used time will not show up in the list box.

Is there any chance that you can zip and post a copy of your database (with any sensitive data removed or alterd)? There might be other issues with your tables that might be causing problems.

[cheyanne]

Hi

How will the user know that there is already a record with that appointment date? Or does your coding design only show those appointments which are still available
on that day in lstTimes?

The information, in the database, is not highly sensitive, merely names and addresses and their pets. I am sure that I can trust you not to abuse the information and
to destroy it once your kind assistance has finished.

I'll zip the database and send it to you.

Regards

Cheyanne

[jzwp11]

The subquery shown in red below is what finds those appointment times for the date entered that have already been assigned

"SELECT tblAppointmentTimes.ApptTimeID, tblAppointmentTimes.lstTimes FROM tblAppointmentTimes WHERE tblAppointmentTimes.AppTimeID NOT IN (SELECT ApptTimeID FROM tblAppointments WHERE txtAppointmentDate = #" & me.txtAppointmentDate & "#")

I have downloaded the database, it would be best to remove the attachment from your earlier post if it contains names and addresses of real people.

[cheyanne]

Hi

I have never deleted an attached file before as you suggest. I wouldf be happier doing it but cannot find how to.


Regards


Cheyanne