Solved Update Query

[Momma]

I need some help again, please.
I want to insert a record into tblDogCheckList . If a record for the DogID does not exist do an Insert. If a record with the same DogID already exists then do an Update only. MCTrfLodged is a date field.
When I run it and I check the table, nothing happened.
I know I must have something wrong.
Thank you, everyone!!

Private Sub cmdMCTrfLodged_Click()
   If Me.Dirty Then Me.Dirty = False 'This line Saves the Data just entered

    Dim rnSQL As String
    Dim varItem As Variant

    Dim db As DAO.Database
    Set db = CurrentDb
    varItem = Me.lstPuppies.Column(0, varItem)

    If DCount("DogID", "tblDogsCheckList", "DogID=" & varItem) = 0 Then

        rnSQL = "INSERT INTO tblDogsChecklist (DogID, MCTrfLodged)" _
            & " Values (p1, p2);"

        For Each varItem In Me.lstPuppies.ItemsSelected

        With db.CreateQueryDef("", rnSQL)
            .Parameters("p1") = varItem
            .Parameters("p2") = Me!TxtMCTrfLodged
            .Execute dbFailOnError
        End With
       
    Next varItem
   
    Else
   
        rnSQL = "UPDATE tblDogsChecklist SET MCTrfLodged = Me!TxtMctrflodged" _
            & " WHERE DogID = " & varItem & ";"
           
        For Each varItem In Me.lstPuppies.ItemsSelected
        varItem = Me.lstPuppies.Column(0, varItem)

        Next varItem
   
    End If

    Set db = Nothing
   
End Sub

 

[June7]

Concatenate Me!TxtMctrflodged

Code does not Execute the UPDATE sql.

 

[arnelgp]

you can also use a Recordset:

Private Sub cmdMCTrfLodged_Click()
   If Me.Dirty Then Me.Dirty = False 'This line Saves the Data just entered

    'Dim rnSQL As String
    'Dim varItem As Variant
    Dim puppy, puppy_id
    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    
    If Me.lstpubblies.ItemsSelected.Count <> 0 Then
        Set db = CurrentDb
        Set rs = db.OpenRecordset("tblDogsCheckList", dbOpenDynaset)
        
        With rs
            For Each puppy In Me.lstPuppies.ItemsSelected
                puppy_id = Me.lstPuppies.ItemData(puppy)
                
                .FindFirst "DogID = " & puppy_id
                
                If Not .NoMatch Then
                    .Edit
                Else
                    .AddNew
                    !dogid = puppy_id
                End If
                
                !MCTrfLodged = Me!TxtMCTrfLodged
                
                .Update
            Next dog
            .Close
        End With
            
    End If
    Set rs = Nothing
    Set db = Nothing

End Sub

 

[Momma]

you can also use a Recordset:

Private Sub cmdMCTrfLodged_Click()
   If Me.Dirty Then Me.Dirty = False 'This line Saves the Data just entered

    'Dim rnSQL As String
    'Dim varItem As Variant
    Dim puppy, puppy_id
    Dim db As DAO.Database
    Dim rs As DAO.Recordset
   
    If Me.lstpubblies.ItemsSelected.Count <> 0 Then
        Set db = CurrentDb
        Set rs = db.OpenRecordset("tblDogsCheckList", dbOpenDynaset)
       
        With rs
            For Each puppy In Me.lstPuppies.ItemsSelected
                puppy_id = Me.lstPuppies.ItemData(puppy)
               
                .FindFirst "DogID = " & puppy_id
               
                If Not .NoMatch Then
                    .Edit
                Else
                    .AddNew
                    !dogid = puppy_id
                End If
               
                !MCTrfLodged = Me!TxtMCTrfLodged
               
                .Update
            Next dog
            .Close
        End With
           
    End If
    Set rs = Nothing
    Set db = Nothing

End Sub

Hi Arnel, I've copied your code which creates a record but does not update the MCTrfLodged field, which is a date.

 

[arnelgp]

there is Wrong spelling on my part on the list name:

If Me.lstpubblies.ItemsSelected.Count <> 0 Then


should be:

lstpuppies

 

[Momma]

there is Wrong spelling on my part on the list name:

If Me.lstpubblies.ItemsSelected.Count <> 0 Then


should be:

lstpuppies

I saw that and I fixed it. There was another one as well, Next Dog should've been Next Puppy.
So those are not the problem.

 

[arnelgp]

what is the recordsource of your listbox and what is the Bound column number?
because obviously on my demo it is working, unless your bound column is not 1?

 

[Momma]

what is the recordsource of your listbox and what is the Bound column number?
because obviously on my demo it is working, unless your bound column is not 1?

When I read your message I knew exactly what the problem is. Thanks for pointing it out. It's working
Thank you so much, Arnel