VBA not working for a Multiselect Listbox

S

Skip Bisconer

Who Me?
Local time
Today, 13:14
Joined
Jan 22, 2008
Messages
285
I am trying to append selected records from one table to another but my code doesn't do the trick. The table structures are identical. These are inventory items and I am trying to select certain Vcodes and have those records append to the second table. The rowsource is the Vcode from the first table. The listbox is unbound. There many records per Vcode hence the SELECT DISTINCT on form load.

Any suggestions are appreciated. THanks for looking.

Code: 
Private Sub Form_Load()
Me.List41.RowSource = "SELECT DISTINCT Vcode FROM tblInventoryAnalysisLoc1 "
End Sub
 
Private Sub Command6_Click()
 Dim strSQL        As String
  Dim db            As DAO.Database
  Dim rs            As DAO.Recordset
  Dim ctl           As Control
  Dim varItem       As Variant
  On Error GoTo ErrorHandler
  Set db = CurrentDb() [COLOR=green]'tblInventoryAnalysisLoc1[/COLOR]
  Set rs = db.OpenRecordset("tblReviewInventoryByMultibleVcode", dbOpenDynaset, dbAppendOnly)
  'make sure a selection has been made
  If Me.List41.ItemsSelected.Count = 0 Then
    MsgBox "Must select at least 1 position"
    Exit Sub
  End If
 
  'add selected value(s) to table
  Set ctl = Me.List41
  For Each varItem In ctl.ItemsSelected
    rs.AddNew
    rs!VCode = ctl.ItemData(varItem)
    rs!Part = Me.Part
    rs!Description = Me.Description
    rs!WhsLocation = Me.WhsLocation
    rs!VendorID = Me.VendorID
    rs!VendUOM = Me.VendUOM
    rs!VendItemMinOrd = Me.VendItemMinOrd
    rs!InventoryCost = Me.InventoryCost
    rs!InventoryList = Me.InventoryList
    rs!QtyOnOrder = Me.QtyOnOrder
    rs!QtyOnBackOrder = Me.QtyOnBackOrder
    rs!QtyOnHand = Me.QtyOnHand
    rs!QtyMin = Me.QtyMin
    rs!QtyMax = Me.QtyMax
    rs!AvgMo = Me.AvgMo
    rs!ListMargin = Me.ListMargin
    rs!LeadTime = Me.InventoryCost
    rs!ReviewCycle = Me.ReviewCycle
    rs!CarryCost = Me.CarryCost
    rs!ReplenishmentCosts = Me.ReplenishmentCosts
    rs!SA = Me.SA
    rs!SAPcnt = Me.SAPcnt
    rs!SP = Me.SP
    rs!EOQ = Me.EOQ
    rs!Calc1 = Me.Calc1
    rs!Calc2 = Me.Calc2
    rs!Calc3 = Me.Calc3
    rs!LP = Me.LP
    rs!OP = Me.OP
    rs!OverSP = Me.OverSP
    rs!OverInvDollars = Me.OverInvDollars
    rs!OverMax = Me.OverMax
    rs!TQA = Me.TQA
  Next varItem
ExitHandler:
  Set rs = Nothing
  Set db = Nothing
  Exit Sub
ErrorHandler:
  Select Case Err
    Case Else
      MsgBox Err.Description
      DoCmd.Hourglass False
      Resume ExitHandler
  End Select
End Sub
 
I'm no good with DAO but, for starters, isn't it necessary to call

rs.Update

after each entry?
 
I'm also a little skeptical about this code:

rs!VCode = ctl.ItemData(varItem)

because I seem to recall that ItemData works like this (with zero-based indexes).

ctl.ItemData(columnNumber, rowNumber)

which in your case would probably be:

rs!VCode = ctl.ItemData(0, varItem)

since your listbox only has one column. For testing, maybe output the above into a messagebox to make sure I am advising you correctly.
 
Thanks for looking Jal.

I am using this code in another process and it works fine there. That process lets the user pick multiple employees to assign a specific training process and appends the data to a training log table. The column count in the the process is 4. That form layout is a little different that what I am trying to do here. I am trying to pull some lines of data off another table and append them to another table using the Vcode as a filter to get the data I need.

Maybe there is a better way to do this than what I am trying to do now.

rs!VCode = ctl.ItemData(0, varItem) gives me a wrong number of arguments error. When I stick rs.update before "Next varItem" it only updates the table with the first record in the rs and it is one of the Vcodes I selected in the list box. (That's more than it was doing) Maybe my On load rowsource is incorrect? The row source is coming from the table I am trying to get the data from.

I only know enough here to get me into trouble and needless to say most of my code comes from this site. I am trying to make this form work like an append query while letting the user select all products related to the 3 digit text Vcode and append them to the destination table. I may have up to 200 products per vendor code.

There are no fields on the from except the Vcode listbox but I have the form data source set to the originating table. I want to eventually bring in the destination table as a subform.
 
Sorry, I got this:

ctl.ItemData(columnNumber)

mixed up with this:

ctrl.Column(columnNumber, rowNumber)

Try it that way instead, that is, try


ctl.Column(0, VarItem)
 
You said:

The table structures are identical.
Click to expand...

Copying records from one table to an identical table is usually pretty straightforward. Generally it only takes one line of code:

DoCmd.RunSQL "INSERT INTO DestinationTable SELECT * FROM SourceTable WHERE VCode = '" & Vcode & "'"
 
Where would you suggest I stick that code so it would represent the listbox selections?
 

The simplest way is this:

For Each varItem In ctl.ItemsSelected
DB.Execute "INSERT INTO tblReviewInventoryByMultibleVcode SELECT * FROM tblInventoryAnalysisLoc1 WHERE VCode = '" & ctl.Column(0, varItem) & "'"
Next varItem


But personally I would prefer a left join (use it the same way as above) because it insures, even if the user clicks the INSERT button repeatedly, that the same record won't be inserted multiple times. Of course, if you have established your primary key correctly, there won't be any danger of re-inserting the same records. Anyway, if you decide there is such a danger, a left join would look something like this:

'Assumes that each partNo-Vcode combo is unique.
INSERT INTO tblReviewInventoryByMultibleVcode
SELECT LeftTable.* FROM tblInventoryAnalysisLoc1 as LeftTable
LEFT JOIN tblReviewInventoryByMultibleVcode as RightTable
ON LeftTable.Vcode = RightTable.Vcode
AND LeftTable.PartNo = RightTable.PartNo
WHERE RightTable.Vcode IS NULL
AND LeftTable.Vcode = '" & Vcode & '"

 
When I use your simple way it adds the data lines to the table but I get Update or Cancelupdate without AddNew statement.

As it could become an issue of clicking the button twice I copy/pasted your SQL but I get a compile syntax error with the text in red font as shown below.

Code: 
Set ctl = Me.List41
  For Each varItem In ctl.ItemsSelected
  db.Execute "INSERT INTO tblReviewInventoryByMultibleVcode"
  [COLOR=red]SELECT LeftTable.* FROM tblInventoryAnalysisLoc1 as LeftTable
  LEFT JOIN tblReviewInventoryByMultibleVcode as RightTable
  ON LeftTable.Vcode = RightTable.Vcode
  AND LeftTable.PartNo = RightTable.PartNo[/COLOR]
  WHERE RightTable.VCode Is Null
 [COLOR=red] AND LeftTable.Vcode = '" & Vcode & '"
[/COLOR]
 
Skip:

Try doing this instead:

Code: 
Dim strSQL As String

strSQL = "INSERT INTO tblReviewInventoryByMultibleVcode " & _
         "SELECT LeftTable.* FROM tblInventoryAnalysisLoc1 as LeftTable " & _
         "LEFT JOIN tblReviewInventoryByMultibleVcode as RightTable " & _
         "ON LeftTable.Vcode = RightTable.Vcode " & _
         "AND LeftTable.PartNo = RightTable.PartNo " & _
         "WHERE RightTable.VCode Is Null " & _
         "AND LeftTable.Vcode = '" & Vcode & '"

Set ctl = Me.List41
  For Each varItem In ctl.ItemsSelected
  CurrentDb.Execute strSQL, dbFailOnError
  Next varItem
 
Hi Bob The strSQL= statement comes up with a compile syntax error. Is there something wrong with my dims I have the following:
Dim strSQL As String
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim ctl As Control
Dim varItem As Variant
 
Vcode is a field in the originating table and the destination table. In fact when I created the destination table is just a copy of the the origination table.
 
Well this line:
"AND LeftTable.Vcode = '" & Vcode & '"

seems to make it think it is a variable.
 
Yes Bob you're right on both counts. It was supposed to be a variable. I think we could maybe do this:



For Each varItem In ctl.ItemsSelected

strSQL = "INSERT INTO tblReviewInventoryByMultibleVcode " & _
"SELECT LeftTable.* FROM tblInventoryAnalysisLoc1 as LeftTable " & _
"LEFT JOIN tblReviewInventoryByMultibleVcode as RightTable " & _
"ON LeftTable.Vcode = RightTable.Vcode " & _
"AND LeftTable.PartNo = RightTable.PartNo " & _
"WHERE RightTable.VCode Is Null " & _
"AND LeftTable.Vcode = '" & ctl.Column(0, VarItem) & "'"
CurrentDb.Execute strSQL, dbFailOnError
Next varItem


Ok, I hope I edited it right.
 
Last edited:
woops, i'lll have to edit that post
 
This is how I placed the code and I get a Compile syntax error on the strSQL = statement.
Code: 
Private Sub Command6_Click()
 Dim strSQL        As String
  Dim db            As DAO.Database
  Dim rs            As DAO.Recordset
  Dim ctl           As Control
  Dim varItem       As Variant
 
  On Error GoTo ErrorHandler
  Set db = CurrentDb()
  Set rs = db.OpenRecordset("tblReviewInventoryByMultibleVcode", dbOpenDynaset, dbAppendOnly)
  'make sure a selection has been made
  If Me.List41.ItemsSelected.Count = 0 Then
    MsgBox "Must select at least 1 position"
    Exit Sub
  End If
 
[COLOR=red]strSQL = "INSERT INTO tblReviewInventoryByMultibleVcode " & _[/COLOR]
[COLOR=red]       "SELECT LeftTable.* FROM tblInventoryAnalysisLoc1 as LeftTable " & _[/COLOR]
[COLOR=red]       "LEFT JOIN tblReviewInventoryByMultibleVcode as RightTable " & _[/COLOR]
[COLOR=red]       "ON LeftTable.Vcode = RightTable.Vcode " & _[/COLOR]
[COLOR=red]       "AND LeftTable.PartNo = RightTable.PartNo " & _[/COLOR]
[COLOR=red]       "WHERE RightTable.VCode Is Null " & _[/COLOR]
[COLOR=red]       "AND LeftTable.Vcode = '" & Vcode & '"[/COLOR]
Set ctl = Me.List41
  For Each varItem In ctl.ItemsSelected
  CurrentDb.Execute strSQL, dbFailOnError
 
  [COLOR=green]'Set ctl = Me.List41[/COLOR]
[COLOR=green] 'For Each varItem In ctl.ItemsSelected[/COLOR]
 
 ' [COLOR=green]db.Execute "INSERT INTO tblReviewInventoryByMultibleVcode SELECT * FROM tblInventoryAnalysisLoc1 WHERE VCode = '" & ctl.Column(0, varItem) & "'"[/COLOR]
[COLOR=green]   'rs.AddNew[/COLOR]
    rs!VCode = ctl.ItemData(varItem)
    rs!Part = Me.Part
    rs!Description = Me.Description
    rs!WhsLocation = Me.WhsLocation
    rs!VendorID = Me.VendorID
    rs!VendUOM = Me.VendUOM
    rs!VendItemMinOrd = Me.VendItemMinOrd
    rs!InventoryCost = Me.InventoryCost
    rs!InventoryList = Me.InventoryList
    rs!QtyOnOrder = Me.QtyOnOrder
    rs!QtyOnBackOrder = Me.QtyOnBackOrder
    rs!QtyOnHand = Me.QtyOnHand
    rs!QtyMin = Me.QtyMin
    rs!QtyMax = Me.QtyMax
    rs!AvgMo = Me.AvgMo
    rs!ListMargin = Me.ListMargin
    rs!LeadTime = Me.InventoryCost
    rs!ReviewCycle = Me.ReviewCycle
    rs!CarryCost = Me.CarryCost
    rs!ReplenishmentCosts = Me.ReplenishmentCosts
    rs!SA = Me.SA
    rs!SAPcnt = Me.SAPcnt
    rs!SP = Me.SP
    rs!EOQ = Me.EOQ
    rs!Calc1 = Me.Calc1
    rs!Calc2 = Me.Calc2
    rs!Calc3 = Me.Calc3
    rs!LP = Me.LP
    rs!OP = Me.OP
    rs!OverSP = Me.OverSP
    rs!OverInvDollars = Me.OverInvDollars
    rs!OverMax = Me.OverMax
    rs!TQA = Me.TQA
    rs.Update
 
  Next varItem
DoCmd.Requery[List41]
ExitHandler:
  Set rs = Nothing
  Set db = Nothing
  Exit Sub
ErrorHandler:
  Select Case Err
    Case Else
      MsgBox Err.Description
      DoCmd.Hourglass False
      Resume ExitHandler
  End Select
End Sub
 
I find that if I remark out the & ctl.Column(0, varItem) & '" the SQL passes the debug.
 
The code you have in red will error because of the lack of an opening quote here:

"AND LeftTable.Vcode = '" & Vcode & "'"
 
You must log in or register to reply here.

Similar threads

C
Working in 32 bit but not in 64.
Replies
19
Views
1,150
Catalina
C
R
  • Question Question
Using vba to select a particular record in a combo box is not working properly
Replies
4
Views
476
cheekybuddha
cheekybuddha
N
Getting Opening Balance for a Ledger by using Open Record Set NOT Dsum
Replies
18
Views
1,187
Minty
Minty
N
VBA for populating a subform from data in a listbox
Replies
12
Views
701
June7
June7
L
Message to inform a record already exists
Replies
2
Views
855
arnelgp
arnelgp

Users who are viewing this thread

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