Dcount Help

[Classified]

Hey There,

Have a problem, hopefully someone out here can lend me a hand.

Working on an appointment databases, haven't used VBA in ages and I am find
a lot of rust everywhere.

What I'm trying to do, search the databases using Dcount for any records
that match the Date and time of the appointment prior to it being added to
the database, however have not been successful. Moreover I was looking for
an easy way out, that's the reason behind choosing DCount, however if anyone
else can offer a better suggestion I'll be listening. Below is my code,
don't laugh but offer suggestions :) Once I can get beyond the checking to
see if an appointment already exists for that date and time I can move
forward, but I've fallen and cannot get up and need some help!

Private Sub cmdGenerate_Click()

Dim strsTime As Date
Dim strsDate As Date
Dim intX As Integer

strsTime = Me.txtsTime
strsDate = Me.txtsDate

intX = DCount("[AppointmentID]", "tblAppointments", "[sTime] = strsTime AND
[sDate] = #strsDate#")

If intX > 0 Then

MsgBox "Error! An appointment already exists with that time. Choose
another date or time.", vbOKOnly, "McGill University Health Centre:
Appointment Database"

Else
End If

End Sub

[Classified]

Ok,

I've figured out the problem, all is working properly now.. with the
exception of the following. I'll repaste the code I'm using, as it's been
modifed..

Private Sub cmdGenerate_Click()
On Error GoTo Err_Generate_Click

'declare some vars
Dim strsTime As Date
Dim strsDate As Date
Dim intX As Integer

'check to see if an appointment exists for the date/time inputed.

intX = DCount("[sDate]", "tblAppointments", "[sDate] = #" & txtsDate & "#
AND [sTime] = #" & txtsTime & "#")

If intX > 0 Then
MsgBox "Error! An appointment already exists with that time. Choose
another date or time.", vbOKOnly, "McGill University Health Centre:
Appointment Database"
GoTo Exit_Generate_Click
Else
DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
MsgBox "Appointment added successfully!", vbOKOnly
DoCmd.GoToRecord , , acNext
End If

Exit_Generate_Click:
Exit Sub

Err_Generate_Click:
Debug.Print
MsgBox Err.Description
Resume Exit_Generate_Click

End Sub
The problem is, even though after it finds that there is a duplicate record
with that exact date and time, it still add's another one despite the exit
sub command being used after ther error message box was has me completely
lost. Any thoughts would be greatly appericated.

Thanks,

Steve

sturner AT linux dot ca
"Classified" <cl********@linux.ca> wrote in message
news:zI*****************@news01.bloor.is.net.cable .rogers.com...

Hey There,

Have a problem, hopefully someone out here can lend me a hand.

Working on an appointment databases, haven't used VBA in ages and I am find a lot of rust everywhere.

What I'm trying to do, search the databases using Dcount for any records
that match the Date and time of the appointment prior to it being added to
the database, however have not been successful. Moreover I was looking for
an easy way out, that's the reason behind choosing DCount, however if anyone else can offer a better suggestion I'll be listening. Below is my code,
don't laugh but offer suggestions :) Once I can get beyond the checking to see if an appointment already exists for that date and time I can move
forward, but I've fallen and cannot get up and need some help!

Private Sub cmdGenerate_Click()

Dim strsTime As Date
Dim strsDate As Date
Dim intX As Integer

strsTime = Me.txtsTime
strsDate = Me.txtsDate

intX = DCount("[AppointmentID]", "tblAppointments", "[sTime] = strsTime AND [sDate] = #strsDate#")

If intX > 0 Then

MsgBox "Error! An appointment already exists with that time. Choose
another date or time.", vbOKOnly, "McGill University Health Centre:
Appointment Database"

Else
End If

End Sub

[Pieter Linden]

If you store the date as a general date, you'll just have both the
date and time in the same field... then it's simple.

Public Function ApptCount(ByVal dtDateStamp As Date)
ApptCount = DCount("[ApptDateTime]", "tblAppts",
"[ApptDateTime]=#" & dtDateStamp & "#")
End Function
Of course, if you have regular appointment intervals, then you could
do something like set the {PersonID, ApptDate} to be unique (set as
primary key), and the table will do the work for you. But then you
CAN NOT override the rule.

HTH,
Pieter

[Pieter Linden]

Move all the code into the BeforeInsert event of the form, and if your
count is already at your limit, set Cancel=True. Then no insert
happens.

[Van T. Dinh]

What you probably wanted in your code is:

intX = DCount("[AppointmentID]", "tblAppointments", _
"[sTime] = " & Format(strsTime, "\#hh:nn:ss\#") & _
" AND [sDate] = " & Format(strsDate, "\#mm/dd/yyyy\#") )

However, I am not of the logic you are using since the above will only find
exact matches. The most obvious problem is that of the overlapping
appointments.

--
HTH
Van T. Dinh
MVP (Access)


"Classified" <cl********@linux.ca> wrote in message
news:zI*****************@news01.bloor.is.net.cable .rogers.com...

Hey There,

Have a problem, hopefully someone out here can lend me a hand.

Working on an appointment databases, haven't used VBA in ages and I am find a lot of rust everywhere.

What I'm trying to do, search the databases using Dcount for any records
that match the Date and time of the appointment prior to it being added to
the database, however have not been successful. Moreover I was looking for
an easy way out, that's the reason behind choosing DCount, however if anyone else can offer a better suggestion I'll be listening. Below is my code,
don't laugh but offer suggestions :) Once I can get beyond the checking to see if an appointment already exists for that date and time I can move
forward, but I've fallen and cannot get up and need some help!

Private Sub cmdGenerate_Click()

Dim strsTime As Date
Dim strsDate As Date
Dim intX As Integer

strsTime = Me.txtsTime
strsDate = Me.txtsDate

intX = DCount("[AppointmentID]", "tblAppointments", "[sTime] = strsTime AND [sDate] = #strsDate#")

If intX > 0 Then

MsgBox "Error! An appointment already exists with that time. Choose
another date or time.", vbOKOnly, "McGill University Health Centre:
Appointment Database"

Else
End If

End Sub