Solved Update Query (1 Viewer)

Momma

Member
Local time
Today, 12:56
Joined
Jan 22, 2022
Messages
70
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!!

Code:
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
 
Last edited:

June7

AWF VIP
Local time
Yesterday, 18:56
Joined
Mar 9, 2014
Messages
4,450
Concatenate Me!TxtMctrflodged

Code does not Execute the UPDATE sql.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 10:56
Joined
May 7, 2009
Messages
16,788
you can also use a Recordset:
Code:
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

Member
Local time
Today, 12:56
Joined
Jan 22, 2022
Messages
70
you can also use a Recordset:
Code:
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

..forever waiting... waiting for jellybean!
Local time
Today, 10:56
Joined
May 7, 2009
Messages
16,788
there is Wrong spelling on my part on the list name:

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


should be:

lstpuppies
 

Momma

Member
Local time
Today, 12:56
Joined
Jan 22, 2022
Messages
70
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

..forever waiting... waiting for jellybean!
Local time
Today, 10:56
Joined
May 7, 2009
Messages
16,788
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?
 

Attachments

  • snoopDog.accdb
    672 KB · Views: 150

Momma

Member
Local time
Today, 12:56
Joined
Jan 22, 2022
Messages
70
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 😊
 

Users who are viewing this thread

Top Bottom