Data entry form informs if record is already in table

[Joan]

Hi,
I have a data entry form where the user can add a booking for a litter of
dogs. Breeders call in with the Breed and Whelp Date information and a
booking is added to the Bookings table. The Bookings table has an
auto-number, Booking Number, as it's primary key. Consequently, a new
Booking Number comes up automatically when the user starts adding a new
Booking entry.

What I need my form to do is:

(After the user has entered a record with a Breeder, Breed and [Whelp Date]
that is the same as a record already in the Bookings table), the forms
displays a message saying: "A record with this Breeder,Breed, and [Whelp
Date] is already in the Bookings table. Would you like to add a another
litter with the same [Whelp Date], Breeder, and Breed or edit the existing
record in the table?"

There would be an "Add" button and an "Edit" button on this dialog form
also, where if the user selects "Add" , it will let him finish adding the
record. If the user clicks "Edit", the EditBooking form opens at the
desired record.

How do I code the AfterUpdate event of the [Whelp Date] textbox to do this?
I haven't yet done anything like this.

Joan

 

[Joan]

Hi Rick,
I tried the code you had in your reply on the Before Update event of the
Whelped Date textbox but then I get a Run-time error '2001': "You canceled
the previous operation." When I debugg, the line: If DCount("*",
"Bookings", "......) is highlighted. I'm not sure why it is doing this.???

I then tried putting the code in the After Update event of the Whelped Date
textbox. Then I get a Run-time error ' -2147352567 (80020009)': "The
field is too small to accept the amount of data you attempted to add. Try
inserting or pasting less data. When I debug, the line: Cancel = True
is highlighted.

On my Bookings form, I have entered a Breeder in the Breeder's control, and
a Breed in the Breed's control and a Whelped Date in the Whelped Date's
control. A new Booking Number automatically comes up in the Booking Number
control and today's date in the [Enter Date] control.

What am I doing wrong here?

Joan

Hi,
I have a data entry form where the user can add a booking for a litter of
dogs. Breeders call in with the Breed and Whelp Date information and a
booking is added to the Bookings table. The Bookings table has an
auto-number, Booking Number, as it's primary key. Consequently, a new
Booking Number comes up automatically when the user starts adding a new
Booking entry.

What I need my form to do is:

(After the user has entered a record with a Breeder, Breed and [Whelp Date]
that is the same as a record already in the Bookings table), the forms
displays a message saying: "A record with this Breeder,Breed, and [Whelp
Date] is already in the Bookings table. Would you like to add a another
litter with the same [Whelp Date], Breeder, and Breed or edit the existing
record in the table?"

There would be an "Add" button and an "Edit" button on this dialog form
also, where if the user selects "Add" , it will let him finish adding the
record. If the user clicks "Edit", the EditBooking form opens at the
desired record.

How do I code the AfterUpdate event of the [Whelp Date] textbox to do this?
I haven't yet done anything like this.

I would use BeforeUpdate as that event can be cancelled.

If DCount("*", "Bookings", "[Breeder] = '" & Me![Breeder] & "' AND [Breed] = '" &
Me![Breed] & "' AND [Whelp Date] = #" & Me![Whelp Date] & "#") > 0 Then
If MsgBox("This looks like a duplicate. Create another?",vbYesNo) = vbNo Then
Cancel = True
End If

 

[Rick Brandt]

Comments interspersed:

Hi Rick,
I tried the code you had in your reply on the Before Update event of the
Whelped Date textbox but then I get a Run-time error '2001': "You canceled
the previous operation." When I debugg, the line: If DCount("*",
"Bookings", "......) is highlighted. I'm not sure why it is doing this.???

Are you letting the form save naturally or are you using a custom save button? If
the latter and if that button is using the DoCmd. function, then anything that causes
the save to cancel will in fact look like an error to the code in your button. You
just need to trap for error number 2501 in the code for the button and ignore it.

On Error GoTo ErrHandler

DoCmd.RunCommand acCmdSaveRecord

Egress:
Exit Sub

ErrHandler:
Select Case Err.Number
Case 2501
'ignore
Case Else
(normal error handling code)
End Select
Resume Egress
End Sub

I then tried putting the code in the After Update event of the Whelped Date
textbox. Then I get a Run-time error ' -2147352567 (80020009)': "The
field is too small to accept the amount of data you attempted to add. Try
inserting or pasting less data. When I debug, the line: Cancel = True
is highlighted.

AfterUpdate does not have a Cancel argument. That is why it is a bad chice for doing
validation stuff.

 

[Joan]

Rick,
Since my last post, I've been trying different things with my code and the
code below seems to be working except for a type mismatch error. Instead of
using : Cancel = True, I'm using : Me.Undo as it will undo any entries
entered in the form and they will not appear in the table. If the user
answers yes to the question: "Would you like to edit the already existing
record?", then I am attempting to open a form in edit mode at the record
that the user was attempting to add but is already in the table. I use the
same form for both data entry and to edit a record, I just change the data
entry argument in the DoCmd.OpenForm line of code: Now I am wondering why
I am getting a type mismatch error on the line: stCriteria =
CStr("[BreederCode]''" &.........) ??? I thought that CStr would convert
everything in () to a string. [BreederCode] and [Breed Code] are both text
fields, however [Whelped Date] is of course a date data type. Also is my
syntax correct in the stCriteria = CStr("[BreederCode].......) line of
code?

Below is my code:

Private Sub Whelped_Date_AfterUpdate()
Dim stCriteria As String
Dim Myrs As DAO.Recordset

If DCount("*", "Bookings", "[BreederCode]='" & Me![cboBreedersCode] &
"' AND [Breed Code]='" & Me![Breed Code] & "' AND [Whelped Date]=#" &
Me![Whelped Date] & "#") > 0 Then
If MsgBox("A record with this breeder, breed and whelped date is
already recorded. Would you like to add another litter with the same
breeder, breed and whelped date?", vbYesNo) = vbNo Then
Me.Undo
If MsgBox("Would you like to edit the already existing record?",
vbYesNo) = vbYes Then
stCriteria = CStr("[BreederCode]='" & Me![cboBreedersCode] & "'"
And "[Breed Code]='" & Me![Breed Code] & "'" And "[Whelped Date]=#" &
Me![Whelped Date] & "#")

DoCmd.Close acForm, "Bookings Form"
DoCmd.OpenForm "Bookings Form", acNormal, , stCriteria,
acFormEdit

End If
End If
End If
End Sub


Thanks for all of your help.

Joan

 

[Rick Brandt]

Rick,
Since my last post, I've been trying different things with my code and the
code below seems to be working except for a type mismatch error. Instead of
using : Cancel = True, I'm using : Me.Undo as it will undo any entries
entered in the form and they will not appear in the table. If the user
answers yes to the question: "Would you like to edit the already existing
record?", then I am attempting to open a form in edit mode at the record
that the user was attempting to add but is already in the table. I use the
same form for both data entry and to edit a record, I just change the data
entry argument in the DoCmd.OpenForm line of code: Now I am wondering why
I am getting a type mismatch error on the line: stCriteria =
CStr("[BreederCode]''" &.........) ??? I thought that CStr would convert
everything in () to a string. [BreederCode] and [Breed Code] are both text
fields, however [Whelped Date] is of course a date data type. Also is my
syntax correct in the stCriteria = CStr("[BreederCode].......) line of
code?

Your "ands" need to be inside the quotes. You have them outside of them. A tip I
use in these situations is to add a line immediately after assigning a value to a
variable like stCriteria of...

Debug.Print stCriteria

Then you can look in the debug window and examine the string to see what is wrong
with it after it has been assembled.

Below is my code:

Private Sub Whelped_Date_AfterUpdate()
Dim stCriteria As String
Dim Myrs As DAO.Recordset

If DCount("*", "Bookings", "[BreederCode]='" & Me![cboBreedersCode] &
"' AND [Breed Code]='" & Me![Breed Code] & "' AND [Whelped Date]=#" &
Me![Whelped Date] & "#") > 0 Then
If MsgBox("A record with this breeder, breed and whelped date is
already recorded. Would you like to add another litter with the same
breeder, breed and whelped date?", vbYesNo) = vbNo Then
Me.Undo
If MsgBox("Would you like to edit the already existing record?",
vbYesNo) = vbYes Then
stCriteria = CStr("[BreederCode]='" & Me![cboBreedersCode] & "'"
And "[Breed Code]='" & Me![Breed Code] & "'" And "[Whelped Date]=#" &
Me![Whelped Date] & "#")

DoCmd.Close acForm, "Bookings Form"
DoCmd.OpenForm "Bookings Form", acNormal, , stCriteria,
acFormEdit

End If
End If
End If
End Sub


Thanks for all of your help.

Joan

Comments interspersed:

this.???

Are you letting the form save naturally or are you using a custom save button? If
the latter and if that button is using the DoCmd. function, then anything that causes
the save to cancel will in fact look like an error to the code in your button. You
just need to trap for error number 2501 in the code for the button and ignore it.

On Error GoTo ErrHandler

DoCmd.RunCommand acCmdSaveRecord

Egress:
Exit Sub

ErrHandler:
Select Case Err.Number
Case 2501
'ignore
Case Else
(normal error handling code)
End Select
Resume Egress
End Sub



AfterUpdate does not have a Cancel argument. That is why it is a bad chice for doing
validation stuff.

 

[Joan]

Rick,

I finally got it to work! Your comment about the line in the Debug window :
([BreederCode] = '' And [Breed Code] = '' and [Whelped Date] = ## means all
of the references to the form controls are returning Null and your question
about if I have values in all of the controls,.....made me go back and
examine my code. I had the following excerpt in my code:

If MsgBox("A record with this breeder, breed and whelped date is already
recorded. Would you like to add another litter with the same breeder, breed
and whelped date?", vbYesNo) = vbNo Then
Me.Undo
If MsgBox("Would you like to edit the already existing record?", vbYesNo)
= vbYes Then .....


As you can see, the line Me.Undo was taking my values out of the controls.
By changing the above line to:

If MsgBox("A record with this breeder, breed and whelped date is already
recorded. Would you like to add another litter with the same breeder, breed
and whelped date?", vbYesNo) = vbYes Then
Exit Sub


Below is the code that did everything I wanted it to.

Private Sub Whelped_Date_AfterUpdate()
Dim stCriteria As String

EarlyBonusCalc
If DCount("*", "Bookings", "[BreederCode]='" & Me![cboBreedersCode] & "'
AND [Breed Code]= '" & Me![Breed Code] & "' AND [Whelped Date]=#" &
Me![Whelped Date] & "#") > 0 Then
If MsgBox("A record with this breeder, breed and whelped date is
already recorded. Would you like to add another litter with the same
breeder, breed and whelped date?", vbYesNo) = vbYes Then
Exit Sub
Else
If MsgBox("Would you like to edit the already existing record?",
vbYesNo) = vbYes Then
stCriteria = "[BreederCode]= '" & Me![cboBreedersCode] & "' And
[Breed Code]='" & Me![Breed Code] & "' And [Whelped Date]=#" &
Format(Me.[Whelped Date], "mm\/dd\/yyyy") & "#"
Debug.Print stCriteria
DoCmd.OpenForm "Edit Bookings", acNormal, , stCriteria,
acFormEdit, acWindowNormal
DoCmd.Close acForm, "Bookings Form"
Else
Me.Undo
End If
End If
End If
End Sub

Thanks, Rick, for all of your help. You pointed me in the right direction.

Joan

Rick,

I tried putting Debug.Print stCriteria in my code immediately after
assigning a value to the variable stCriteria. Below is what appeared in the
Immediate window:

[BreederCode]='' And [Breed Code]='' And [Whelped Date]=##

[BreederCode]='' And [Breed Code]='' And [Whelped Date]=##

[BreederCode]=

[BreederCode]= '' And [Breed Code]='' And [Whelped Date]=##



I got the same results both before I changed the stCriteria = .... line
and after I tried to put the "ands" within the quotes like you suggested. I
am not sure what you mean by putting the "ands" within the quotes?
Following is the stCriteria = .... line after I tried putting the "ands"
inside the quotes: [snip]

Please help me figure this out. It is giving me fits. Do I need to add
quotes to my previous stCriteria that I used? The "ands" are between the
first quotes (that are before [BreederCode] and the last quotation mark of
the line.

stCriteria = "[BreederCode]= '" & Me![cboBreedersCode] & "' And [Breed
Code]='" & Me![Breed Code] & "' And [Whelped Date]=#" & Format(Me.[Whelped
Date], "mm\/dd\/yyyy") & "#"

This last one looks good. How is it printing in the Debug window? If it looks
like...

[BreederCode]= '' And [Breed Code]='' And [Whelped Date]=##

..then that means all of the references to your form controls are returning
Null. Do you have values in all of the controls? Are you sure you are spelling
the control names correctly?

 

[Rick Brandt]

Rick,

I finally got it to work! Your comment about the line in the Debug window :
([BreederCode] = '' And [Breed Code] = '' and [Whelped Date] = ## means all
of the references to the form controls are returning Null and your question
about if I have values in all of the controls,.....made me go back and
examine my code.

Glad you got it figured out.