CODEWITHSUNDEEP
ms-accessvba
Scott Jethro Hardwick
Scott Jethro Hardwick

Reputation: 65

why won't this allow update of a new record

I am trying to create a form for an employee to enter grooming times for an appointment.

Thought I had it working, until the same pet has an appointment on a new date, and then I am getting a Microsoft Visual Basic Run-Time Error 3022, and will not add the new entry.

Where am I missing what's going on here??

Private Sub cmdNew_Click()

' Error check for empty comboboxes
If IsNull(CmbPetName) = True Then
    MsgBox ("Pick A Pet For This Operation")
    Exit Sub
ElseIf IsNull(CmbPEmplye) = True Then
    MsgBox ("Pick A Prep Employee For This Operation")
    Exit Sub
ElseIf IsNull(CmbPTime) = True Then
    MsgBox ("Pick A Prep Time For This Operation")
    Exit Sub
ElseIf IsNull(CmbBEmplye) = True Then
    MsgBox ("Pick A Bath Employee For This Operation")
    Exit Sub
ElseIf IsNull(CmbBTime) = True Then
    MsgBox ("Pick A Bath Time For This Operation")
    Exit Sub
ElseIf IsNull(CmbDEmplye) = True Then
    MsgBox ("Pick A Dry Employee For This Operation")
    Exit Sub
ElseIf IsNull(CmbDTime) = True Then
    MsgBox ("Pick A Dry Time For This Operation")
    Exit Sub
ElseIf IsNull(CmbGEmplye) = True Then
    MsgBox ("Pick A Groom Employee For This Operation")
    Exit Sub
ElseIf IsNull(CmbGTime) = True Then
    MsgBox ("Pick A Groom Time For This Operation")
    Exit Sub
End If

'Set variables for error checking duplicates
Dim pid As String
Dim aptdate As Date
pid = CmbPetName.Column(2)
    'MsgBox (pid)
aptdate = txtAptDate.Value
    'MsgBox (aptdate)
Dim strCriteria As String

'Set Criteria for DCount()
strCriteria = "([PetID] = '" & pid & "') And ([ApptDate] = #" & aptdate & "#)"
    'MsgBox (strCriteria)

'Error checking for duplicates
If DCount("[PetID]", "[TimeLog]", strCriteria) > 0 Then
    MsgBox ("Record Already Exists")
    Exit Sub
End If

'Set variables to record entries
Dim rs As DAO.Recordset
Dim db As DAO.Database
Set db = CurrentDb
Set rs = db.OpenRecordset("TimeLog", dbOpenTable)

'Add entries to Table TimeLog
rs.AddNew
rs!PetID = CmbPetName.Value
rs!PetName = Me.CmbPetName.Column(0)
rs!ApptDate = txtAptDate.Value
rs!ptime = CmbPTime.Value
rs!PEmplyee = CmbPEmplye.Value
rs!btime = CmbBTime.Value
rs!BEmplyee = CmbBEmplye.Value
rs!dtime = CmbDTime.Value
rs!DEmplyee = CmbDEmplye.Value
rs!gtime = CmbGTime.Value
rs!GEmplyee = CmbGEmplye.Value

rs.Update

rs.Close
db.Close

Set rs = Nothing
Set db = Nothing

Me.CmbPetName = Null
Me.CmbPEmplye = Null
Me.CmbBEmplye = Null
Me.CmbDEmplye = Null
Me.CmbGEmplye = Null
Me.CmbPTime = Null
Me.CmbBTime = Null
Me.CmbDTime = Null
Me.CmbGTime = Null

End Sub

The run-time happens on the rs.update line.

Upvotes: 0

Views: 54

Answers (2)

Samuel
Samuel

Reputation: 1

rs!PetID = CmbPetName.Value Can't duplicate the key probably which create a conflict?

Upvotes: 0

J. D.
J. D.

Reputation: 1561

What is the primary key on this table? From the sounds of it, your primary key might just be on the pet, instead of being a composite key on pet and appointment date.

Upvotes: 1

Related Questions