I've been trying to prevent dupicate entries into a linked form by using the above advice but without success.
My form (frmVisit) is autopopulated by clicking a command button (New visit) on the main form (frmPatient).
I want to show a message if a duplicate value for the combination of patient ID + visit date is created by clicking on command button a second time.
I'm attaching by database below. Any help would be much appreciated.
Thanks.
I would check it in the new visit button so that you do not force them to create a new record only to find out it is a duplicate for today. Then let them go and change the date if they want.
Code:
Private Sub CmdNewVisit_Click()
Dim rtn As Long
If Me.Dirty = True Then Me.Dirty = False
If DCount("*", "TblVisit", "patientID = " & Me.PatientID & "AND visitDate = #" & Format(Date, "MM/dd/YYYY") & "#") > 0 Then
rtn = MsgBox("The patient already has a created visit on this date. If you continue you must change the visit date. Do you want to continue?", vbYesNo, "Date Exists")
If rtn = vbYes Then
DoCmd.OpenForm "frmVisit", , , , acFormAdd
Forms("frmVisit")!PatientID = Me!PatientID
End If
Else
DoCmd.OpenForm "frmVisit", , , , acFormAdd
Forms("frmVisit")!PatientID = Me!PatientID
End If
End Sub
Then in the form I changed it a little. I think the problem is you have to delimit your date with #mm/dd/yyyy# so the check was failing.
Code:
Private Sub Form_BeforeUpdate(Cancel As Integer)
If DCount("*", "TblVisit", "patientID = " & Me.PatientID & "AND visitDate = #" & Format(Me.VisitDate, "MM/dd/YYYY") & "#") > 0 Then
MsgBox "Visit already exists. Please Change date, or undo record"
Cancel = True
If MsgBox("Do you want to remove this duplicate?", vbYesNo, "Delete?") = vbYes Then
Me.Undo
DoCmd.Close acForm, Me.Name
End If
End If
End Sub
I would check it in the new visit button so that you do not force them to create a new record only to find out it is a duplicate for today. Then let them go and change the date if they want.
Code:
Private Sub CmdNewVisit_Click()
Dim rtn As Long
If Me.Dirty = True Then Me.Dirty = False
If DCount("*", "TblVisit", "patientID = " & Me.PatientID & "AND visitDate = #" & Format(Date, "MM/dd/YYYY") & "#") > 0 Then
rtn = MsgBox("The patient already has a created visit on this date. If you continue you must change the visit date. Do you want to continue?", vbYesNo, "Date Exists")
If rtn = vbYes Then
DoCmd.OpenForm "frmVisit", , , , acFormAdd
Forms("frmVisit")!PatientID = Me!PatientID
End If
Else
DoCmd.OpenForm "frmVisit", , , , acFormAdd
Forms("frmVisit")!PatientID = Me!PatientID
End If
End Sub
Then in the form I changed it a little. I think the problem is you have to delimit your date with #mm/dd/yyyy# so the check was failing.
Code:
Private Sub Form_BeforeUpdate(Cancel As Integer)
If DCount("*", "TblVisit", "patientID = " & Me.PatientID & "AND visitDate = #" & Format(Me.VisitDate, "MM/dd/YYYY") & "#") > 0 Then
MsgBox "Visit already exists. Please Change date, or undo record"
Cancel = True
If MsgBox("Do you want to remove this duplicate?", vbYesNo, "Delete?") = vbYes Then
Me.Undo
DoCmd.Close acForm, Me.Name
End If
End If
End Sub
I tried using above code in another part of my project and am failing.
I have a form which feeds back data about patient visits to a table (tblVisit). Only single visit can be available for one patient perday.
I need to have a provision in my form to also enter past visit details for the particular patient (i.e. visit dates which are different fro mthe current date). For this I have created following code:
Code:
Private Sub cmdNewPastVisit2_Click()
Dim rtn As Long
If DCount("*", "TblVisit", "patientID = " & Me.PatientID & "AND visitDate = #" & Format(Date, "MM/dd/YYYY") & "#") > 0 Then
rtn = MsgBox("Visit already exists")
Else
DoCmd.GoToRecord acDataForm, "frmPastIx2", acNewRec
If Me.NewRecord Then
PatientID = PatientID.Tag
End If
End If
End Sub
Private Sub Form_BeforeUpdate(Cancel As Integer)
PatientID.Tag = PatientID.Value
End Sub
However when I enter a past date, even when particular date is not present for that patient in the table, I get the message "Visit already exists".
I can figure out what im doing wrong.
For starters, it is poor practice to dirty a record before the user dirties it. Instead of opening the form and dirtying it, use the where argument of the open form method to pass the ID of the parent record using the OpenArgs. Then in the BeforeInsert event, copy the ID from the OpenArgs and put it in the FK field.
This method doesn't create an empty record as the form opens so you are less likely to end up saving empty records, although, you should have validation code in the form's BeforeUpdate event to prevent this anyway. In the Form's BeforeUpdate event or in the visit date control's BeforeUpdate event, you can check to see if there is already a visit for that date to prevent duplicates. Normally I do all validation in the Form's BeforeUpdate event just to keep it all in one place but when I have validation that will prevent the user from saving the record such as this, I will do that check as soon as possible. VisitDate should be the first field on the form that the user fills so if the validation rule fails, he hasen't wasted his time filling out all the other fields before I decide that he can't save the record he just created.
This method also allows you to create multiple records in the popup form and still populate the FK in the pop up form correctly.
I found another error that needs correcting. In the click event of the New button, you need to make sure that there is a PatientID already entered and then you need to save the current record if it is dirty BEFORE you attempt to open the New visit form. This is a common problem whenever you open formB from formA or ReportC from FormA, ALWAYS ensure that formA has a PK value AND that it is saved. Otherwise you can run into trouble with trying to create child records because the parent has never been saved and so doesn't exist yet or you might see old data on a report if you haven't saved the changes to the current form prior to opening a report based on that data.
Thanks @Pat Hartman for the advise. I have put in the validation code to the on change event of the visit date field bit still end up wit hthe same problem.
I have attached my database with dummy data to make it more clear.
From Welcome I enter the relevant patient by double clicking on patientID.
In Patient form I use the past visit combobox to reach pastvisit form.
On this form I click new past visit to reach frmPastIx2.
I need this form to autopopulate with current patient ID which is happening with the current code. I need to display a message when I change the date to say visit already exists, if a visit of the same date already exists for the same patient. However with the current set up it displays the message even when the visit does not exist. Strange since the same bit of code is working when I click new visis button on patient form.
I am stumped. Please help.
That is NOT where I said to put it. Each event has a purpose. Use them correctly and they will work for you. Use them incorrectly and they won't. The on Change event is only useful for one very specific type of validation. That being, editing a control character by character as the user types. It also requires referencing the .text property of the control rather than the default property which is .value because Access doesn't move the typed characters from the .text buffer to the .value buffer until entry is completed.
I need this form to autopopulate with current patient ID which is happening with the current code.
There is nothing wrong with putting something into an UNBOUND control so the user thinks the form is live if you think he will be confused if the form opens to an empty record. But do not populate bound controls until the record is dirty.
The error I get happens because you are trying to calculate birth year in the form's load event. If you are entering a NEW record, there is NO DATA present in the form's Load event. Form events are not arbitrary. They are designed to be used at specific points in time. A more appropriate place to calculate Age is the Form's BeforeUpdate event AND you need to validate the components to avoid getting errors.
Code:
Private Sub Form_BeforeUpdate(Cancel As Integer)
If IsDate(Me.txtVisitDate Then
If IsDate(Me.txtBirthYear Then
Me.txtAge = Year(Me.txtVisitDate) - Me.txtBirthYear
Else
Msgbox "Please enter Birth Year", vbOKOnly
Cancel = True
Me.txtBirthYear.SetFocus
Exit Sub
End If
Else
Msgbox "Please enter the Visit Date", vbOKOnly
Cancel = True
Me.txtVisitDate.SetFocus
Exit Sub
End If
End Sub
1. Use the proper reference style to get intellisense -- Me.ControlName
2. Cancel the event when you find an error to avoid saving invalid data. This doesn't undo the data entry but it does leave the form dirty. Therefore, you can't get past this BeforeUpdate event UNLESS you fix ALL the errors OR you use Esc Esc to back out of the update. I don't recommend using Me.Undo UNLESS, you put a button on the form so the user KNOWS that he is backing out all the changes he made. And I only use Me.ControlName.Undo when the user is NOT allowed to make changes to a specific control.
3. You have an awful lot of code. None of it is properly formatted making it hard to read. Plus I don't see any validation code so you are saving lots of bad data and incomplete records.
4. Rather than using ElseIf's, use a Case statement. It is easier to read and expand.
5. You are using variables for no apparent reason. There is no reason to move a form control's value to a variable before using it for something. Maybe if you were going to use the value in a loop that would execute a 1000 times, there might be some speed gain since it would take less time to execute if referencing a local variable than a form control. However, using the proper format to reference a form control also increases the efficiency since the interpreter knows immediately where the referenced field is defined and doesn't have to search alll the loaded libraries to find the definition. The other downside is that you will bet errors if you attempt to move a null value to any variable with a non-variant data type and you certainly do NOT want to define your variables as variants.
6. Using "ID" as the PK name simply obfuscates the relationships.
7. All relationships should enforce RI. If you can't enforce RI on a table that contains data, it means that you already have bad data in the table so you need to fix that first.
8. Seems to me that all those columns referencing tmpRange and the other fields that go with them are actually a repeating group and should be placed in a many-side table so that they are a single row each rather than a single(multiple) column each.
That is NOT where I said to put it. Each event has a purpose. Use them correctly and they will work for you. Use them incorrectly and they won't. The on Change event is only useful for one very specific type of validation. That being, editing a control character by character as the user types. It also requires referencing the .text property of the control rather than the default property which is .value because Access doesn't move the typed characters from the .text buffer to the .value buffer until entry is completed.
There is nothing wrong with putting something into an UNBOUND control so the user thinks the form is live if you think he will be confused if the form opens to an empty record. But do not populate bound controls until the record is dirty.
The error I get happens because you are trying to calculate birth year in the form's load event. If you are entering a NEW record, there is NO DATA present in the form's Load event. Form events are not arbitrary. They are designed to be used at specific points in time. A more appropriate place to calculate Age is the Form's BeforeUpdate event AND you need to validate the components to avoid getting errors.
Code:
Private Sub Form_BeforeUpdate(Cancel As Integer)
If IsDate(Me.txtVisitDate Then
If IsDate(Me.txtBirthYear Then
Me.txtAge = Year(Me.txtVisitDate) - Me.txtBirthYear
Else
Msgbox "Please enter Birth Year", vbOKOnly
Cancel = True
Me.txtBirthYear.SetFocus
Exit Sub
End If
Else
Msgbox "Please enter the Visit Date", vbOKOnly
Cancel = True
Me.txtVisitDate.SetFocus
Exit Sub
End If
End Sub
1. Use the proper reference style to get intellisense -- Me.ControlName
2. Cancel the event when you find an error to avoid saving invalid data. This doesn't undo the data entry but it does leave the form dirty. Therefore, you can't get past this BeforeUpdate event UNLESS you fix ALL the errors OR you use Esc Esc to back out of the update. I don't recommend using Me.Undo UNLESS, you put a button on the form so the user KNOWS that he is backing out all the changes he made. And I only use Me.ControlName.Undo when the user is NOT allowed to make changes to a specific control.
3. You have an awful lot of code. None of it is properly formatted making it hard to read. Plus I don't see any validation code so you are saving lots of bad data and incomplete records.
4. Rather than using ElseIf's, use a Case statement. It is easier to read and expand.
5. You are using variables for no apparent reason. There is no reason to move a form control's value to a variable before using it for something. Maybe if you were going to use the value in a loop that would execute a 1000 times, there might be some speed gain since it would take less time to execute if referencing a local variable than a form control. However, using the proper format to reference a form control also increases the efficiency since the interpreter knows immediately where the referenced field is defined and doesn't have to search alll the loaded libraries to find the definition. The other downside is that you will bet errors if you attempt to move a null value to any variable with a non-variant data type and you certainly do NOT want to define your variables as variants.
6. Using "ID" as the PK name simply obfuscates the relationships.
7. All relationships should enforce RI. If you can't enforce RI on a table that contains data, it means that you already have bad data in the table so you need to fix that first.
8. Seems to me that all those columns referencing tmpRange and the other fields that go with them are actually a repeating group and should be placed in a many-side table so that they are a single row each rather than a single(multiple) column each.
Thank you for taking your time to help me.
I am preparing this database only for my personal use. It's my first one. I have very rudimentary knowlege of VBA and actually up until one month back I had not even opened up access on my computer.
Your advises are immensely helpful to me. I will try to change my database along your lines of thought.
Cheers
