UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Multi-select Listbox Posting Same Value, Access 2013    
 
   
rpdorn
post Sep 6 2017, 10:30 PM
Post#1



Posts: 12
Joined: 7-February 09



I have a problem with the ID field not being inserted for the records selected, and instead a different record ID (but the same for all) is used for each selected record.

I have a form with a multi-select listbox. Once the specific records are chosen, the user fills in several unbound fields for the disposition of the selected records. The information in these fields is then posted to a table when the user clicks the Run button. The items selected for this example:

CollectionID LastName FirstName CollectionType Status
------------------------------------------------------------------------------------------------------------
4612 Law Johnny Desktop Completed
4613 Law Johnny Network Completed
4614 Law Johnny E-mail Completed

This is one that is not selected:
4575 Tester Willy Other In Progress


Here is my code:

CODE
Private Sub Command88_Click()
On Error GoTo Err_Command88_Click

Dim strSql As String
Dim db As DAO.Database
Dim rs As Recordset
Dim ctl As Control
Dim varItem As Variant
    
Set db = CurrentDb()
Set rs = db.OpenRecordset("tblDispo", dbOpenDynaset, dbAppendOnly)

If List97.ItemsSelected.Count = 0 Then
    MsgBox "There are no Collections selected", vbInformation, "Error"
    Exit Sub
End If
    
DoCmd.SetWarnings False

Set ctl = Me!List97
For Each varItem In ctl.ItemsSelected


    rs.AddNew
    rs!CollectionID = Me.List97.Column(0, varItem)
    strSql = "INSERT INTO tblDispo (CollectionID, DispoDate, DispoType, DispoLocation, DispoProcSize, DispoHash, DispoUser, DispoMachine, DispoESI, DispoEncryptionKey, DispoEncryptionSoft, DispoShipVendor, DispoTrackingNumber, DispoMediaTypeID, DispoModel, DispoSerialNumber)"
    strSql = strSql & "VALUES ('" & Me.CollectionID.value & "', '" & Me.txtDispoDate.value & "', '" & Me.txtDispoType.value & "', '" & Me.txtLocation.value & "', '" & Me.txtProcessedSize.value & "', '" & Me.txtHash.value & "', '" & Me.txtOSUser.value & "', '" & Me.txtMachine.value & "', '" & Me.txtEmployee.value & "', '" & Me.txtKey.value & "', '" & Me.txtSoftware.value & "','" & Me.txtVendor.value & "','" & Me.txtTracking.value & "','" & Me.txtMedia.value & "','" & Me.txtModel.value & "','" & Me.txtSerialNumber.value & "')"
    DoCmd.RunSQL strSql
Next varItem
    
    
    DoCmd.SetWarnings True
    
    MsgBox "You have successfully added " & Me.Text105 & " records.", vbOKOnly, "Success"
    Me.Command88.Enabled = False

    

Exit_Command88_Click:
Exit Sub

Err_Command88_Click:
MsgBox Err.Description
Resume Exit_Command88_Click
End Sub


The code enters the correct number of entries that were selected from the listbox, but all the Collection ID's that are posted to the table are the same number:

DispoID CollectionID Date Employee etc
-----------------------------------------------------------------------------------------
1 4675 01/01/2010 Anders
2 4675 01/01/2010 Anders
3 4675 01/01/2010 Anders

Everything works as it should - if 5 records are selected, 5 are pasted into the tblDispo. The problem is just with the CollectionID being the same. I have tried several things and can't seem to get it working. I am sure there is a simple explanation but I have spent two days trying different things and can't get it!
Go to the top of the page
 
DanielPineault
post Sep 7 2017, 05:23 AM
Post#2


UtterAccess VIP
Posts: 5,453
Joined: 30-June 11



In your Insert statement, should
CODE
Me.CollectionID.value

not also be
CODE
Me.List97.Column(0, varItem)


Okay after reviewing your code closer I think this would be more what you are after
CODE
Private Sub Command88_Click()
    On Error GoTo Error_Handler
    Dim db                    As DAO.Database
    Dim sSQL                  As String
    Dim varItem               As Variant

    Set db = CurrentDb()

    If Me.List97.ItemsSelected.Count = 0 Then
        MsgBox "There are no Collections selected", vbInformation, "Error"
        Exit Sub
    End If

    For Each varItem In Me.List97.ItemsSelected
        sSQL = "INSERT INTO tblDispo (CollectionID, DispoDate, DispoType, DispoLocation, DispoProcSize, DispoHash, DispoUser, DispoMachine, DispoESI, DispoEncryptionKey, DispoEncryptionSoft, DispoShipVendor, DispoTrackingNumber, DispoMediaTypeID, DispoModel, DispoSerialNumber)"
        sSQL = sSQL & " VALUES ('" & Me.List97.Column(0, varItem) & "', '" & Me.txtDispoDate.Value & "', '" & Me.txtDispoType.Value & "', '" & Me.txtLocation.Value & "', '" & Me.txtProcessedSize.Value & "', '" & Me.txtHash.Value & "', '" & Me.txtOSUser.Value & "', '" & Me.txtMachine.Value & "', '" & Me.txtEmployee.Value & "', '" & Me.txtKey.Value & "', '" & Me.txtSoftware.Value & "','" & Me.txtVendor.Value & "','" & Me.txtTracking.Value & "','" & Me.txtMedia.Value & "','" & Me.txtModel.Value & "','" & Me.txtSerialNumber.Value & "')"
        db.Execute sSQL
    Next varItem

    MsgBox "You have successfully added " & Me.Text105 & " records.", vbOKOnly, "Success"
    Me.Command88.Enabled = False

Error_Handler_Exit:
    On Error Resume Next
    If Not db Is Nothing Then Set db = Nothing
    Exit Sub

Error_Handler:
    MsgBox "The following error has occured" & vbCrLf & vbCrLf & _
           "Error Number: " & Err.Number & vbCrLf & _
           "Error Source: Command88_Click" & vbCrLf & _
           "Error Description: " & Err.Description & _
           Switch(Erl = 0, "", Erl <> 0, vbCrLf & "Line No: " & Erl) _
           , vbOKOnly + vbCritical, "An Error has Occured!"
    Resume Error_Handler_Exit
End Sub



Your code seemed confused. You started using the recordset directly, but then switch to executing an INSERT SQL statement. Both will work, but you need to use one, not both.

Make sure you always add proper error handling (which return meaningful information) and cleanup after your variables.

Lastly, try ensuring your always give your controls good names, meaningful names. List97 means nothing and when you look back at your code in 3 months you'll be scratching your head. Instead a name like lst_CollectionId or lst_Collection and so on would help you easily know exactly what the control was.

--------------------
Daniel Pineault (2010-2017 Microsoft MVP)
Professional Help: http://www.cardaconsultants.com
Free MS Access Code, Tips, Tricks and Samples: http://www.devhut.net

* Design should never say "Look at me". It should always say "Look at this". -- David Craib
* A user interface is like a joke, if you have to explain it, it's not that good! -- Martin LeBlanc


All code samples, demonstration databases, links,... are provided 'AS IS' and are to be used at your own risk! Take the necessary steps to check, validate ...
Go to the top of the page
 
bouncee
post Sep 7 2017, 05:29 AM
Post#3



Posts: 73
Joined: 5-August 05
From: Eastbourne, UK


You are inserting Me.CollectionID.value but you should use rs!CollectionID, which you have set to Me.List97.Column(0, varItem).

If that doesn't work, use Me.List97.ItemData(varItem).
Go to the top of the page
 
rpdorn
post Sep 7 2017, 08:51 AM
Post#4



Posts: 12
Joined: 7-February 09



Thank you both - Daniel's code worked and I see where I was getting confused! Still in the learning phase, and apparently will be for some time!
Go to the top of the page
 
DanielPineault
post Sep 7 2017, 09:27 AM
Post#5


UtterAccess VIP
Posts: 5,453
Joined: 30-June 11



thumbup.gif

I've been at this for 15+ years and I'm still learning daily! Access is immense!

--------------------
Daniel Pineault (2010-2017 Microsoft MVP)
Professional Help: http://www.cardaconsultants.com
Free MS Access Code, Tips, Tricks and Samples: http://www.devhut.net

* Design should never say "Look at me". It should always say "Look at this". -- David Craib
* A user interface is like a joke, if you have to explain it, it's not that good! -- Martin LeBlanc


All code samples, demonstration databases, links,... are provided 'AS IS' and are to be used at your own risk! Take the necessary steps to check, validate ...
Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    16th December 2017 - 09:21 PM