DLookup Issue

[sdel_nevo]

Hi All

I have a problem that i can't seem to get my head around

i have a basic for that i am using to record bookings on, what i want to do is this

when the end user clicks the save button, i need the code to see if another booking is happening at the same time (these are recorded in a start date field ) and to provide a msgbox saying "you have a booking at this time already, do you want to continue" if the user click yes the form saves if not it cancels the msgbox.

im fine with the msgbox syntax i just cant workout the criteria for the Dlookup

form fields are

AppointmentID
starttime
endtime
notes



many thanks

Steve

[orange]

I think you might want the BeforeUpdate event.
Form BeforeUpdate sample at
http://msdn.microsoft.com/en-us/libr.../ff822421.aspx

You could try a DCount() and check for DCount() >0
which implies a record exists.
DCount info at http://www.techonthenet.com/access/f...ain/dcount.php

[sdel_nevo]

Hi

Thanks for getting back

been looking at the reference you pointed me to but im stuck

i have been using this code


Private Sub Close_Click()


If Me.txtAppointmentID > 0 Then 'if function returns value >0 then

If DLookup("ApptStart", "tblAppointments", "ApptID") = Me.ApptStart Then ' then show message box

Beep
MsgBox "ERROR. This new appointment overlaps an existing appointment, Please amend dates and or times and try again.", vbCritical + vbYesNo, "Invalid Appointment Times"
Exit Sub

End If

with both Dlookup and Dcount but no joy.

im stuck now lol

Steve

[sdel_nevo]

Hi

I have been trying the references that Orange gave me but to no joy

im struggling with this and im not even sure that a dcount or dlookup is what i need

i need to see if a date and time entered into a field called Apptstart on a form called "Add New Booking" is the same as another time and date within the table called "tblAppointments" and if so show a message box

the dlookup and dcount i have been using is this

DLookup("Apptstart", "tblAppointments", "ApptID") but i don't know what the criteria will be as im not looking for the ApptID but a time and date in the "Appstart" Field

any help would be most welcome

many thanks

Steve

[rzw0wr]

You can use the table for this.
I assume you have a date and a time for the appointment.

In the table design view you can set Indexs for what you do not want to duplicate.

If it is a time and a date then in the index sheet Type an Index name like appointment.
select the date field then the time field.
Select the Index Name and set the unique property to yes.

Access will tell you if you try to add a record with the same date and time.


Dale

[sdel_nevo]

Hi Dale

Thanks for the speedy reply, thats a great solution and 1 that i over looked to be honest Opps!!

how can i code this for the same result in a form, so the end user has the option of entering the date even if it conflicts with another one?

many thanks

Steve

[rzw0wr]

Try something like this in the BeforeUpdate event.

if not isnull(dlookup("[AppointmentID]","tblAppointmentst","AppointmentID =" & AppointmentID)) then
Response= Msgbox("Would you like to delete this appointment?t", vbYESNO)
If Response = vbYes then
Me.undo
End if
end if

This is air code. May or may not work first time.

Good idea to have Option Explicit at the top of your code window. (Editor)
Under Debug in editor select Compile before you run code.
That will give you an idea if the code will run. If it will not compile, it will not run.
Fix the errors.

Let us know if we can help further.

Dale

[sdel_nevo]

Hi Dale

that's for the pointer about Option Explicit i never knew about that, many thanks and i have now added that to my code.

i have not got anywhere with the form as i can't for the love of it get the dlookup to work.

it's pritty straight forward looking for a value based on a ID but i think my issue is this

i need to look into the tblAppointment to check a field called ApptStart and see if a value in there matches the value in the Apptstart field on the Add Appointment Form. if it does i would like it t show a message box

i just can't seam to figure this out so i may well opt for the indexes option

many thanks for your help and the pointer about the code

Steve

[rzw0wr]

if not isnull(dlookup("[AppointmentID]","tblAppointmentst","AppointmentID =" & AppointmentID)) then
Response= Msgbox("Would you like to delete this appointment?t", vbYESNO)
If Response = vbYes then
Me.undo
End if
end if

I assume that startdate is a date type.

if dlookup("[startdate]","tblAppointmentst") then
     Response= Msgbox("Would you like to delete this appointment?t", vbYESNO)
        If Response = vbYes then 
          Me.undo
        End if
end if

You may need to change startdate to #startdate#
Dale

[sdel_nevo]

Hi Dale

After hours of head scratching failing to get this code to run , set about a new task for a new form.

on doing this i discovered that the macro editor was not working, by that i mean when i selected "macro" for a button on click action i could not select any marcos as the designer screen was completely blank!!!!

i imported my database into a new clean database that resolved the macro issue, but magically also resolved the issue i had with the original post

many thanks for the pointers the code you posted works really well

if dlookup("[startdate]","tblAppointmentst") then Response= Msgbox("Would you like to delete this appointment?t", vbYESNO) If Response = vbYes then Me.undo End ifend if Just thought i would let you know that all is working as expected.

once again

Many Thanks

Steve

[rzw0wr]

Thank you.

Glad we could help.

Dale