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_Cli ck()
Dim strsTime As Date
Dim strsDate As Date
Dim intX As Integer
strsTime = Me.txtsTime
strsDate = Me.txtsDate
intX = DCount("[AppointmentID]", "tblAppointment s", "[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 4 3011
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_Cli ck()
On Error GoTo Err_Generate_Cl ick
'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]", "tblAppointment s", "[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_C lick
Else
DoCmd.DoMenuIte m acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
MsgBox "Appointmen t added successfully!", vbOKOnly
DoCmd.GoToRecor d , , acNext
End If
Exit_Generate_C lick:
Exit Sub
Err_Generate_Cl ick:
Debug.Print
MsgBox Err.Description
Resume Exit_Generate_C lick
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********@lin ux.ca> wrote in message
news:zI******** *********@news0 1.bloor.is.net. cable.rogers.co m... 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_Cli ck()
Dim strsTime As Date Dim strsDate As Date Dim intX As Integer
strsTime = Me.txtsTime strsDate = Me.txtsDate
intX = DCount("[AppointmentID]", "tblAppointment s", "[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
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
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.
What you probably wanted in your code is:
intX = DCount("[AppointmentID]", "tblAppointment s", _
"[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********@lin ux.ca> wrote in message
news:zI******** *********@news0 1.bloor.is.net. cable.rogers.co m... 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_Cli ck()
Dim strsTime As Date Dim strsDate As Date Dim intX As Integer
strsTime = Me.txtsTime strsDate = Me.txtsDate
intX = DCount("[AppointmentID]", "tblAppointment s", "[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
This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics |
by: jdph40 |
last post by:
I posted this problem previously and received excellent help from
Wayne Morgan. However, I still have an unanswered question. My form
(frmVacationWeeks) is opened from the OnClick event of a button on
another form (frmEmpList) which has a list box that contains the names
of all employees. When a name is selected in the list box and the
button is clicked, frmVacationWeeks opens and is filtered using the
following: ". IN (" & Left(strSQL,...
|
by: Simon Matthews |
last post by:
Hope someone can help an Access beginner!
I've just started keeping my surgical logbook on access and it's a
simple flat-file affair. I have created several queries that will
list cases performed at different hospitals and reports based on the
queries to print out the relavent details.
What I would like to do is have a summary sheet in the Report Footer
section that lists a grid of each type of procedure performed as well
as the...
|
by: Will |
last post by:
Hi, I had a DCount within a module on records in a table where CustSuffix =
0. I now need to DCount where CustSuffix = 0 and the type of cost Suffix
(Suffix in table) = G. I can't get both requirements to work and have tried
a number of different formats. At the moment I am getting an object
required error message.
This is the current code:
Function UpdateDistributionCost(Suff As String) ' adds new record if none
exists, prompting...
|
by: Mike Conklin |
last post by:
This one really has me going. Probably something silly. I'm using
dcount for a report to determine the number of different types of
tests proctored in a semester.
My report is based on a parameter query which is the recordsource for
the report. The parameter is <=. The query returns the
correct amounts upto the date entered (no need for "between" dates
here).
There are 8 textboxes with dcounts; 2 other boxes Sum some of these
|
by: Paul T. RONG |
last post by:
Hi,
I have a problem with DCount, the following code doesn't work:
DCount("", "qryOrder", "( = Me! AND
= 'drink')" > 0
Please help.
Thank you.
| |
by: sara |
last post by:
Hi
I'm pretty new to Access here (using Access 2000), and appreciate the
help and instruction.
I gave myself 2.5 hours to research online and help and try to get this
one, and I am not getting it.
Simple database: I want to have a user enter Supply Orders (just for
tracking purposes) by Item. The user may also enter a new item - "new"
is a combination of Item, PartNumber and Vendor - they could have the
|
by: Kaspa |
last post by:
Hello I am trying to create dcount field but is not working I have
tried every way possible and I can't get it to work. here is my code:
=Dcount("","qryTotalscratched"," in (6,7,8,9) and
between #01/01/2001# and " & )
I also tried:
|
by: Wingz |
last post by:
Hiya,
Fairly new to Access and was wondering what the best way to perform Dcounts on groups in an Access report.
For example, I have 10 employees and the different instances of jobs they can perform are either Clean, Shampoo or Vacuum. I have a query called Details that shows a history of all the jobs they have performed. On my report I want a total for each employee of how many of each job they have performed.
This is what I know of...
|
by: dan.cawthorne |
last post by:
Need Some Help,
In Modifing this Bit of VBA Code,
This the code i use and it works but i want it to go one step further
in on the open event of my main start up form
If DCount("", "Qry_DueProjects") 0 Then
DoCmd.RunMacro "TodayProjectsEmail"
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look !
Part I. Meaning of...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
| |
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own....
Now, this would greatly impact the work of software developers. The idea...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms.
Adolph will...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert into image.
Globals.ThisAddIn.Application.ActiveDocument.Select();...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
|
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| |
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...
| |