Code not working

[cheyanne]

A few days ago a couple of helpful readers gave me some code to place on the BeforeUpdate event of the lstTimes list box.

It worked perfectly but suspect I may have erroneously overwrote it with an older database copy and, if so, of course it no longer exists.
I re-introduced it as shown below and it doesn't work. I then changed the line containing Format(Me.AppointmentDate, "mm/dd/yyyy") to read
Format(Me.txtAppointmentDate, "mm/dd/yyyy"), and then tried Format(AppointmentDate, "dd/mm/yyyy") but none work and I cannot see why.

Basically I have two tables (listed below are the pertinent fields

tblAppointments

AppointmentRef - AutoNumber, Long Integer, Promary Key
AppointmentDate - Date Time set to Short Date
AppointmentTimeID Foreign key from tblAppopintmentTimes - Long Integer

tblAppointmentTimes

AppointmentTimeID - AutoNumber, Long Integer, Promary Key
AppointmentTime - Date Time set to Short Time

What I am trying to achieve is to have code on the BeforeUpdate event to ensure that appointments are not doubled booked.
When the user tries to schedule an appointment, for a time that has already been booked on that day, they should receive an error message and the event is cancelled.


The code I was given is

Private Sub lstTimes_BeforeUpdate(Cancel As Integer)
If DCount("*", "tblAppointments", "AppointmentDate=#" & Format(Me.AppointmentDate, "mm/dd/yyyy") & "# AND ApptTimeID=" & Me.lstTimes) > 0 Then (NB Me.AppointmentDate is formatted to dd/mm/yy on the form)
MsgBox "The appointment time for the indicated date has already been reserved"
Cancel = True
End If
End Sub

On the attached form the relative control names are

txtAppointmentRef
txtAppointmentDate
lstTimes - list box containing the times

I should add that Fecha means Date and Tiempo de Cita is Appointment Time whose drop down list is lstTimes.
You can see from theb datasheet below that currently times are being multi booked.

Any assistance greatly appreciated



Regards

Cheyanne

[orange]

I haven't looked deeply into this, but I see a potential issue with this code

"AppointmentDate=#" & Format(Me.AppointmentDate, "mm/dd/yyyy") & "#

Format changes things to strings. So the # signs which enclose dates would not be correct (in my view).
I would try ' to enclose your value.

[ssanfu]

Looking at your posts from 5-June-2012 (https://www.accessforums.net/forms/s...tml#post123370), this is the code that seemed to work:

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

When I opened the attached A2K3 mdb, the message was displayed when I selected duplicate times.
Is this what you are searching for?

[cheyanne]

Hi Steve

Thanks for your reply.

Where diod you find the A2K3 mdb? I found an examples one and an appointment one.

I shall try the code again but was advised that sometimes cut and paste doesn't work.


I shall not give up, having had it work once. I said I may have copied old code over it but I do not remember doing so.
I am confused as to how code that was working suddenly stopped. Maybe I inadvertently changed something.


Regards
Cheyanne

[ssanfu]

Where diod you find the A2K3 mdb? I found an examples one and an appointment one.

Open the above link (my post #3 this thread) and go to post #43 or #45 by jzwp11. Attached are example MDBs of your database. They do not appear to be the full database, but they do have the code for the list box. The code seems to work for the list box.

I shall try the code again but was advised that sometimes cut and paste doesn't work.

I cut an paste code all of the time - no problems.

[cheyanne]

Hi Steve

I found the example code but still cannot get it to work.
It is getting late here (21.30) so I think it is best that I call it a day anf
look at it again with fresh eyes in the morning.


Thanks again and who knows I might still be struggling tomorrow!


Regards

Cheyanne

[cheyanne]

Hi

I have semi got the code to work in that there are now no syntax errors. However, using this code

If DCount("*", "tblAppointments", "AppointmentDate =#" & Me.AppointmentDate & "# AND AppointmentTimeID=" & Me.lstTimes > 0) Then
MsgBox "This appointment time has already been allocated. Please offer the Client another time", vbOKOnly
Cancel = True
Else
MsgBox "Your appointment is confirmed", vbOKOnly
End If

The event considers every appointment made to have already been allocated - see error messages - it does never goes to
MsgBox "Your appointment is confirmed", vbOKOnly
, even if no appointment has been previously made for that time slot.

The table and field names are correct. I did try making Me.AppointmentDate into Me.txtAppointmentDate (which is its control name - rather than field name) but
it still did not functi0on correctly.

I suspect that there is a small error in either the code or logic.

I have attached two screenshots to show what is happening. Error shows an error that I got, but do not get now since I re-entered the code.
Error2 shows that the event suggest that an 11.00 appointment has already been allocated when clearly it has not been.

Any help much appreciated

Regards

Cheyanne

[Bob Fitz]

In your earlier tread I attached an example db that I thought did what you required. If you are still having problems you may like to take another look. It is attached to this post.

[cheyanne]

Hi Bob

Thanks for your reply.

I did try your solution and it worked (once amended for my own field names=. However I could
not find a way to format the appointment time to read as hh:mm rather that the presentation
your code gives i.e. hh:mm:ss.

Whilst trying to resolve this "small issue" our American contributor came up with code based on the
DCount function which worked without actually njeeding to show the time of the appointment, which meant I did
not need to find a formatting solution to the time matter.


In fairness that was working fine until something happened, either I overwrote the code or onhe of several recent crashes
had corrupted the code. In Spain the electricity suppliers regularly just cut the power to a whole area and if you
are working in software it can often cause problems by not closing the programme down properly.

Is there a way to format your code to read hh:mm? The field in question is formatted as short time.

Regards

Cheyanne


PS I have a daughter who lives outside Braintree.

[cheyanne]

Hi Bob

Have managed to resolve the time formatting matter. I used this code and it works perfectly

Private Sub lstTimes_BeforeUpdate(Cancel As Integer)
If Not IsNull(DLookup("AppointmentRef", "tblAppointments", "AppointmentDate = #" & _
Format(Me.AppointmentDate, "mm/dd/yyyy") & "# And AppointmentTimeID = " & Me.ActiveControl & _
" AND AppointmentRef <> " & Me.AppointmentRef)) Then
Cancel = True
Me.ActiveControl.Undo
MsgBox "El " & Format(ActiveControl.Column(1), "hh:mm") & " cita ya ha sido asignado"
Else
MsgBox "Su " & Format(ActiveControl.Column(1), "hh:mm") & " appoinment ha sido confirmado"
End If
End Sub

(Sorry it is in Spanish!)


Thanks for your help I shall mark post as solved.

Regards

Cheyanne