Listbox not updating table

M

Momma

Member
Local time
Tomorrow, 02:52
Joined
Jan 22, 2022
Messages
130
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
 
Momma said:
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
Click to expand...
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?
 
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...
 
GPGeorge said:
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?
Click to expand...
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' )
 
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:
arnelgp said:
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
Click to expand...
arnelgp said:
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
Click to expand...
Thank you so much ArnolGP, it's working! I might get there one day 😊
 
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)
Click to expand...

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.
 
Pat Hartman said:
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.
Click to expand...
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 particular set of delimiters is sort of unusua
Momma said:
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.
Click to expand...
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.
 
If the replacement code works, please mark the thread solved.
 
You must log in or register to reply here.

Similar threads

C
Solved Listbox selection does not update form
Replies
6
Views
726
ClaraBarton
C
E
Append xls to access table
Replies
1
Views
1,376
Guus2005
Guus2005
K
Using Access w/Oracle to update Access Linked table
Replies
0
Views
3,289
kfschaefer
K

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom