Stop duplication of appointment

[cheyanne]

Hi

I did not reply to your last post as I thought you had reached the end of your tether.
I am truly grateful for the time and patience you have showed. As you suggested I wrote
to Bob and am awaiting his reply.



I have used the code you suggested and put it on the BeforeUpdate event of lstTimes.

Unfortunately when I ran it an error message occured saying there was a syntax error (see screenshot)

I can confirm that the bound field is field 1 which is AppointmentTimeID. I trust I got the right field and Event!


Regards

Cheyanne

[jzwp11]

I copied the code to Word and then posted it. In doing so, some of the double quote marks changed & VBA does not recognize those quotes. I reworked the code in VBA, so this should not give you the syntax error

If DCount("*", "tblAppointments", "AppointmentDate=#" & Me.AppointmentDate & "# AND ApptTimeID=" & Me.lstTimes) > 0 Then

By the way, I do not think the Before Update event of the list box is the appropriate event since you have to select the specific time in order for the DCount() to have something to evaluate. I would guess it would be the After Update event of the list box, but I'll that for you to try out.

[cheyanne]

Hi

Thanks again. I read your last post and wondered why I had not just had the tblAppointments, rather than two. Much simpler!


I have now done so and made just one table and included a field AppointmentTime which is a Date/Time field formatted to short time.

The control source for lstTimes is AppointmentTime and its row source is a list of times "09.30;09.45;10.00;10.15;10.30;10.45;11.00;11.15;1 1.30;11.45;12.00; etc"

I have put this line of code on the AfterUpdate event of lstTimes - thought I had made the changes you suggested

If DCount("*", "tblAppointments", "AppointmentDate=#” & Me.AppointmentDate & “# AND AppointmentTime=#” & Me.lstTimes) >0 THEN

and still get an error.

Hopefully we are getting near a solution. I did notice that on this system when you change records there is no indication of what time has
been booked. Somehow I need to have a text field that shows this, otherwise users will not be able to check which appointment has been given to whom.
They will only see the form. Any ideas?

Regards

Cheyanne

[jzwp11]

Thanks again. I read your last post and wondered why I had not just had the tblAppointments, rather than two. Much simpler!

You still need both tblAppointments and tblAppointmentTimes, I did not say to get rid of any tables. It sounds like there was a misunderstanding.

The list box should still use tblAppointmentTimes. The form still needs to be bound to tblAppointments.

I would suggest you type the code directly rather than copying and pasting because of the issue I had mentioned with the double quotes (something with the font changes that VBA does not recognize)

[Bob Fitz]

Cheyanne and jzwp11
Do you still want me to look closer into the suggestion I made earlier? I'm happy to do that if it would help.

[jzwp11]

Sure Bob, any help would be appreciated.

[Bob Fitz]

OK. Iwill review the thread and see if I can come up with something.

[cheyanne]

Hi Bob

As our fellow contributor says - All help is appreciated.

I am really starting to get frustrated as when I "repair" one error I ger another.

I shall look forward to your constructive comments arriving in due course

Regards

Cheyanne

[jzwp11]

In the stripped down version of the database with only the appointment form and dummy data, I think I got Bob's code working.

[Bob Fitz]

This is the code I've used:

Private Sub cmbTimes_BeforeUpdate(Cancel As Integer)
  If Not IsNull(DLookup("AppointmentRef", "tblAppointments", "AppointmentDate = #" & _
      Format(Me.AppointmentDate, "mm/dd/yyyy") & "# And fkApptTimeID = " & Me.ActiveControl & _
      " AND AppointmentRef <> " & Me.AppointmentRef)) Then
    Cancel = True
    Me.ActiveControl.Undo
    MsgBox "The " & Me.ActiveControl.Column(1) & " appointment is already booked"
  End If
End Sub

The attached db has an example

[cheyanne]

Hi to you both

This is an example of why I am getting frustrated.

I re-designed tblAppointmentTimes (having erroneously deleted it) and now get the error message shown in the screenshot.

I simply cannot understand what it means as regards its relevance.

tblAppointmentTimes has only two fields

AppointmentTimeID which is the Primary Key - set to LongInteger and AutoNumber

AppointmentTime (which holds the values of the time slots e.g. 09,30,09.45, 10.00 etc - each slot has a corresponding numerical value i.e. 1= 09.30, 2 = 09.45 etc)
is a Date/Time field formatted to Short Date.

A relationship exists between tblAppointments.AppointmentTimeID and tblAppointmentTime.AppointmentTimeID (one to many) This is the first time I have had this error
and I wish I knew why so that I could fix it.

Any ideas guys

[Bob Fitz]

jzwp11

Made my last post before I noticed that you'd already got there. Could you post it in mdb format so that I can take a look at. I suspect your coding may be better than mine and I'm always ready to learn from something better.

[jzwp11]

Bob,

My code was pretty simple; it is as follows

Private Sub lstTimes_BeforeUpdate(Cancel As Integer)
If DCount("*", "tblAppointments", "AppointmentDate=#" & Me.AppointmentDate & "# AND ApptTimeID=" & Me.lstTimes) > 0 Then
    MsgBox "The appointment time for the indicated date has already been reserved"
    Cancel = True
End If
End Sub

I've also attached the example database in an Access 2000 format.

cheyanne,

You will have to make sure the field names of the new tblAppointments match what is in the DCount() statement. Also, did you remove my code in the after update event of the date textbox control?

[Bob Fitz]

jzwp11

Thanks for attaching the db. Unfortunately, the form has elements that A2003 does not recognise so I am unable to open it as it should be. Anyway, I just wondered if there would any benefit in using DCount instead of DLookup. I seem to remember reading somewhere that DCount was much faster than DLookup.

[jzwp11]

Bob, from what I have heard the domain aggregate functions overall are slower than a query or a recordset; I have not heard whether there is a difference between DLookup() and DCount() with respect to performance. I've attached the db in 2003 format. Sometimes going from 2007/2010 back to an earlier version can be problematic, so hopefully you will be able to open the form. The form defaults to datasheet view; I have been working with it in Form View.