Listbox not updating table (1 Viewer)

Momma

Member
Local time
Today, 19:19
Joined
Jan 22, 2022
Messages
114
I have a listbox from where I select records, I enter the data and then update the table with the data entered.
It runs through without any errors, getting the message "All Medical Details Inserted" but none of the data have been written to the table.
Anyone who can see what I do wrong?
Is there a better way to do this?
Any help will be highly appreciated!

Code:
Private Sub cmdInsert_Click()

      If Me.Dirty Then Me.Dirty = False 'This line Saves the Data just entered

      Dim rnSQL As String
      Dim varItem As Variant
      Dim lngTreatmentID As Long
      Dim lngTreatmentTypeID As Long
      Dim dteTreatmentDate As Date
      Dim lngVetID As Long
      Dim strWeight As String
      Dim dteFollowUpDate As Date
      Dim lngTreatedByID As Long
      Dim strAssessment As String


      lngTreatmentID = Me.txtTreatmentID
      lngTreatmentTypeID = Me.txtTreatmentTypeID
      dteTreatmentDate = Me.TreatmentDate
      lngVetID = Nz(Me.txtVetID, 0)
      strWeight = Nz(Me.txtWeight, 0)
      dteFollowUpDate = Nz(Me.txtFollowUpDate, 0)
      lngTreatedByID = Me.txtTreatedByID
      strAssessment = Nz(Me.txtAssessment, 0)

      For Each varItem In Me.lstPuppies.ItemsSelected
        
      varItem = Me.lstPuppies.Column(0, varItem)

      rnSQL = "INSERT INTO tblMedicalTreatments (DogID, TreatmentID, TreatmentTypeID, TreatmentDate, VetID, Weight, FollowupDate, TreatedByID, Assessment)" _
        & " Values ( " & varItem & ", " & Me.txtTreatmentID & "," & Me.txtTreatmentTypeID & "," & Format(Me.TreatmentDate, "\#dd\-mmm\-yyyy\#") & " , " _
        & IIf(IsNull(txtVetID), "Null", txtVetID) & ",'" & IIf(IsNull(txtWeight), "Null", txtWeight) & "', " & Format(Me.txtFollowUpDate, "\#dd\-mmm\-yyyy\#") & " , " _
        & Me.txtTreatedByID & ",'" & IIf(IsNull(txtAssessment), "Null", txtAssessment) & "' )"

     Debug.Print rnSQL
     CurrentDb.Execute rnSQL
     Next varItem
     MsgBox "All Medical Details Inserted", vbInformation

End Sub
 

GPGeorge

Grover Park George
Local time
Today, 01:19
Joined
Nov 25, 2004
Messages
1,776
I have a listbox from where I select records, I enter the data and then update the table with the data entered.
It runs through without any errors, getting the message "All Medical Details Inserted" but none of the data have been written to the table.
Anyone who can see what I do wrong?
Is there a better way to do this?
Any help will be highly appreciated!

Code:
Private Sub cmdInsert_Click()

      If Me.Dirty Then Me.Dirty = False 'This line Saves the Data just entered

      Dim rnSQL As String
      Dim varItem As Variant
      Dim lngTreatmentID As Long
      Dim lngTreatmentTypeID As Long
      Dim dteTreatmentDate As Date
      Dim lngVetID As Long
      Dim strWeight As String
      Dim dteFollowUpDate As Date
      Dim lngTreatedByID As Long
      Dim strAssessment As String


      lngTreatmentID = Me.txtTreatmentID
      lngTreatmentTypeID = Me.txtTreatmentTypeID
      dteTreatmentDate = Me.TreatmentDate
      lngVetID = Nz(Me.txtVetID, 0)
      strWeight = Nz(Me.txtWeight, 0)
      dteFollowUpDate = Nz(Me.txtFollowUpDate, 0)
      lngTreatedByID = Me.txtTreatedByID
      strAssessment = Nz(Me.txtAssessment, 0)

      For Each varItem In Me.lstPuppies.ItemsSelected
       
      varItem = Me.lstPuppies.Column(0, varItem)

      rnSQL = "INSERT INTO tblMedicalTreatments (DogID, TreatmentID, TreatmentTypeID, TreatmentDate, VetID, Weight, FollowupDate, TreatedByID, Assessment)" _
        & " Values ( " & varItem & ", " & Me.txtTreatmentID & "," & Me.txtTreatmentTypeID & "," & Format(Me.TreatmentDate, "\#dd\-mmm\-yyyy\#") & " , " _
        & IIf(IsNull(txtVetID), "Null", txtVetID) & ",'" & IIf(IsNull(txtWeight), "Null", txtWeight) & "', " & Format(Me.txtFollowUpDate, "\#dd\-mmm\-yyyy\#") & " , " _
        & Me.txtTreatedByID & ",'" & IIf(IsNull(txtAssessment), "Null", txtAssessment) & "' )"

     Debug.Print rnSQL
     CurrentDb.Execute rnSQL
     Next varItem
     MsgBox "All Medical Details Inserted", vbInformation

End Sub
Change this line:
CurrentDb.Execute rnSQL

To this line:
CurrentDb.Execute rnSQL, dbFailOnError

That should display an error message if there is something wrong in the SQL which prevents it from inserting the record.

The date formatting for the treatment and followup dates looks a bit hinky to me, though. I'd be particularly interested in seeing the result of the line:
Debug.Print rnSQL

Also, why are you inserting the text string "Null" into the Assessment field?
 

theDBguy

I’m here to help
Staff member
Local time
Today, 01:19
Joined
Oct 29, 2018
Messages
21,358
Can't tell what is wrong without being able to run and trace it. However, I think you may be inserting your date data incorrectly. I understand your regional settings may not be US, but you still need to format the date data as either US or ISO.

Edit: Oops, too slow...
 

Momma

Member
Local time
Today, 19:19
Joined
Jan 22, 2022
Messages
114
Change this line:
CurrentDb.Execute rnSQL

To this line:
CurrentDb.Execute rnSQL, dbFailOnError

That should display an error message if there is something wrong in the SQL which prevents it from inserting the record.

The date formatting for the treatment and followup dates looks a bit hinky to me, though. I'd be particularly interested in seeing the result of the line:
Debug.Print rnSQL

Also, why are you inserting the text string "Null" into the Assessment field?
The code was given to me by someone else. Not having much experience with vba code I just don't know how to fix it.
I've added dbFailOnError and I'm getting this error:
Data type mismatch in criteria expression.
This is what I get in the Immediate Window.
INSERT INTO tblMedicalTreatments (DogID, TreatmentID, TreatmentTypeID, TreatmentDate, VetID, Weight, FollowupDate, TreatedByID, Assessment) Values ( 1181, 8,1,#31-Jan-2022# , 1,'Null', #28-Feb-2022# , 7,'Null' )
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 16:19
Joined
May 7, 2009
Messages
19,169
Code:
Private Sub cmdInsert_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

    rnSQL = "INSERT INTO tblMedicalTreatments (DogID, TreatmentID, TreatmentTypeID, TreatmentDate, VetID, Weight, FollowupDate, TreatedByID, Assessment)" _
    & " Values ( p0, p1, p2, p3, p4, p5, p6, p7, p8, p9);"

    For Each varItem In Me.lstPuppies.ItemsSelected

        varItem = Me.lstPuppies.Column(0, varItem)

        with db.CreateQuerydef("", rnSQL)
            .Parameters("p1")=varItem
            .Parameters("p2")=Me!txtTreatmentID
            .Parameters("p3")=me!txtTreatmentTypeID
            .Parameters("p4")=me!TreatmentDate
            .Parameters("p5")=me!txtVetID
            .Parameters("p6")=me!txtWeight
            .Parameters("p7")=me!txtFollowUpDate
            .Parameters("p8")=me!txtTreatedByID
            .Parameters("p9")=me!txtAssessment

            .Execute dbFailOnError
        end with


    Next varItem
    Set db=Nothing
End Sub
 
Last edited:

Momma

Member
Local time
Today, 19:19
Joined
Jan 22, 2022
Messages
114
Code:
Private Sub cmdInsert_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

    rnSQL = "INSERT INTO tblMedicalTreatments (DogID, TreatmentID, TreatmentTypeID, TreatmentDate, VetID, Weight, FollowupDate, TreatedByID, Assessment)" _
    & " Values ( p0, p1, p2, p3, p4, p5, p6, p7, p8, p9);"

    For Each varItem In Me.lstPuppies.ItemsSelected

        varItem = Me.lstPuppies.Column(0, varItem)

        with db.CreateQuerydef("", rnSQL)
            .Parameters("p1")=varItem
            .Parameters("p2")=Me!txtTreatmentID
            .Parameters("p3")=me!txtTreatmentTypeID
            .Parameters("p4")=me!TreatmentDate
            .Parameters("p5")=me!txtVetID
            .Parameters("p6")=me!txtWeight
            .Parameters("p7")=me!txtFollowUpDate
            .Parameters("p8")=me!txtTreatedByID
            .Parameters("p9")=me!txtAssessment

            .Execute dbFailOnError
        end with


    Next varItem
    Set db=Nothing
End Sub
Code:
Private Sub cmdInsert_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

    rnSQL = "INSERT INTO tblMedicalTreatments (DogID, TreatmentID, TreatmentTypeID, TreatmentDate, VetID, Weight, FollowupDate, TreatedByID, Assessment)" _
    & " Values ( p0, p1, p2, p3, p4, p5, p6, p7, p8, p9);"

    For Each varItem In Me.lstPuppies.ItemsSelected

        varItem = Me.lstPuppies.Column(0, varItem)

        with db.CreateQuerydef("", rnSQL)
            .Parameters("p1")=varItem
            .Parameters("p2")=Me!txtTreatmentID
            .Parameters("p3")=me!txtTreatmentTypeID
            .Parameters("p4")=me!TreatmentDate
            .Parameters("p5")=me!txtVetID
            .Parameters("p6")=me!txtWeight
            .Parameters("p7")=me!txtFollowUpDate
            .Parameters("p8")=me!txtTreatedByID
            .Parameters("p9")=me!txtAssessment

            .Execute dbFailOnError
        end with


    Next varItem
    Set db=Nothing
End Sub
Thank you so much ArnolGP, it's working! I might get there one day 😊
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 04:19
Joined
Feb 19, 2002
Messages
42,981
Dim rnSQL As String
Dim varItem As Variant
Dim lngTreatmentID As Long
Dim lngTreatmentTypeID As Long
Dim dteTreatmentDate As Date
Dim lngVetID As Long
Dim strWeight As String
Dim dteFollowUpDate As Date
Dim lngTreatedByID As Long
Dim strAssessment As String


lngTreatmentID = Me.txtTreatmentID
lngTreatmentTypeID = Me.txtTreatmentTypeID
dteTreatmentDate = Me.TreatmentDate
lngVetID = Nz(Me.txtVetID, 0)
strWeight = Nz(Me.txtWeight, 0)
dteFollowUpDate = Nz(Me.txtFollowUpDate, 0)
lngTreatedByID = Me.txtTreatedByID
strAssessment = Nz(Me.txtAssessment, 0)

1. Doesn't seem to be any point to this code since you are not using it in the insert.
2. It is more efficient to disambiguate your control references using Me. You do that in the unused code but not in the insert code.
3. The original code is strange. It looks like you are picking dogs from a listbox and then inserting a record with duplicate values for all the dogs chosen. Sort of like a bulk update. Do you really have that many dogs with the same symptoms and treatments all at the same time?

I don't know what was wrong with your original code. We could have determined that if you had put a stop in the code after the Insert string was built and printed the string, but the rewritten method is not better or more correct. It is simply different. Whatever, the conversion did, it eliminated the bug in your logic.
 

Momma

Member
Local time
Today, 19:19
Joined
Jan 22, 2022
Messages
114
1. Doesn't seem to be any point to this code since you are not using it in the insert.
2. It is more efficient to disambiguate your control references using Me. You do that in the unused code but not in the insert code.
3. The original code is strange. It looks like you are picking dogs from a listbox and then inserting a record with duplicate values for all the dogs chosen. Sort of like a bulk update. Do you really have that many dogs with the same symptoms and treatments all at the same time?

I don't know what was wrong with your original code. We could have determined that if you had put a stop in the code after the Insert string was built and printed the string, but the rewritten method is not better or more correct. It is simply different. Whatever, the conversion did, it eliminated the bug in your logic.
Hi Pat, as I said in my previous comment, the code was given to me and it didn't really do what I was hoping.
This is a bulk update. I select all puppies from a specific litter to update wormings and vaccinations which are the same for all of them. It saves time rather than updating them one by one. For all the rest of the dogs, I use a different form where I enter data for one dog only.
 

GPGeorge

Grover Park George
Local time
Today, 01:19
Joined
Nov 25, 2004
Messages
1,776
The particular set of delimiters is sort of unusua
Hi Pat, as I said in my previous comment, the code was given to me and it didn't really do what I was hoping.
This is a bulk update. I select all puppies from a specific litter to update wormings and vaccinations which are the same for all of them. It saves time rather than updating them one by one. For all the rest of the dogs, I use a different form where I enter data for one dog only.
The specific error you reported "Data Type mismatch", suggests that I was possibly on the right track in calling out the text string 'null' as the value to be inserted in fields "Weight" and "Assessment". "Null" when delimited as you do with the single quotes is a string. I'm guessing the Weight and Assessment fields are numeric and expect either numbers or Null, i.e "nothing", "unknown", "no value given". Those are all ways of saying "I'm not putting anything here because it's Null." Null is a specific concept that must be understood to be used properly.

See a fuller explanation of Null in relational database applications.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 04:19
Joined
Feb 19, 2002
Messages
42,981
If the replacement code works, please mark the thread solved.
 

Users who are viewing this thread

Top Bottom