multiselect list box

[GirlBlonde]

hi

i am trying and have been for awhile to create a multiselect list box that saves my selected data into a table so i can use that data later. Fields are simple CostID, RCN,and department name.

seems simple but just can not get it.

thanks

Energy & persistance conquer all things!

 

[TheAceMan1]

How are ya GirlBlonde . . .

Here's the basic idea:

[blue]   Dim LBx As ListBox, SQL As String, itm
   
   Set LBx = Me![purple][b][i]ListboxName[/i][/b][/purple]
   
   For Each itm In LBx.ItemsSelected
      SQL = "INSERT INTO tblCustomer (CostID, " & _
                                     "RCN, " & _
                                     "[Department Name]) " & _
            "Values (" & LBx.Column(0, itm) & ", " & _
                   "'" & LBx.Column(1, itm) & "', " & _
                   "'" & LBx.Column(1, itm) & "');"
   Next

   Set LBx = Nothing[/blue]

Further changes may be required depending if the primary key is autonumber. Also you'll have to double check the data types (string/numeric), I'm posting a guess.

[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

Be sure to see thread181-473997
Also faq181-2886

 

[GirlBlonde]

hi aceman

i have the below code i have gotten from the net and just manipulated to suite. unfortunately my VB is not that great so i know i am close but i am getting the message
'error number 3134; description syntax error in insert into statement.

the fields in the code are what is in the tables.
fields are;ID, costid,CostCentreName,rcn & prefix so i dont understand i have noted them all??

help

many thanks to you for your help

Dim strSQL As String
Dim lst As Access.ListBox
Dim varItem As Variant
Dim intColumn As Integer
Dim lngID As Long

Set lst = Me![Listgroup]

'Clear old temp table
DoCmd.SetWarnings False
strSQL = "DELETE * from Tbl_TempMthlyFiguresOrder"
DoCmd.RunSQL strSQL

'Check that at least one contact has been selected


If lst.ItemsSelected.Count = 0 Then
MsgBox "Please select at least one item"
lst.SetFocus
Exit Sub
End If

For Each varItem In lst.ItemsSelected

DoCmd.SetWarnings False

'All information is present; write a record to the temp table
lngID = lst.Column(0, varItem)

Debug.Print "Selected ID: " & lngID

strSQL = "INSERT INTO Tbl_TempMthlyFiguresOrder (ID, CostID, rcn, CostCentreName, Prefix )" _
& "SELECT ID, CostID, rcn, CostCentreName, Prefix,FROM TBl_RCNforAllDrivers " _
& "WHERE ID = " & lngID & ";"
DoCmd.RunSQL strSQL
Next varItem


Energy & persistance conquer all things!

 

[TheAceMan1]

There's a spacing missing in [red]red[/red]:

[blue]"INSERT INTO Tbl_TempMthlyFiguresOrder (ID, CostID, rcn, CostCentreName, Prefix )[COLOR=black red] [/color]" _[/blue]

See Ya! . . . . . .

Be sure to see thread181-473997
Also faq181-2886

 

[GirlBlonde]

hi aceman

still giving me the same message??? any other ideas?

Energy & persistance conquer all things!

 

[HarleyQuinn]

You need to take out the comma between prefix and FROM

Prefix FROM TBl_RCNforAllDrivers

Hope this helps

HarleyQuinn
---------------------------------
The most overlooked advantage to owning a computer is that if they foul up there's no law against wacking them around a little. - Joe Martin

Get the most out of Tek-Tips, read FAQ222-2244 before posting.

 

[GirlBlonde]

Hi Thanks that worked.

I am trying to do the same thing but with a text field and i am getting the regno coming up in a pop up box saying enter parameter. i have changed all the tables etc see below code. sorry to bug but i can not get it. its using the same data in the cbobox and that of the tbl below?

i have put IngId as string

strSQL = "INSERT INTO Tbl_TempMthlyFiguresOrder (regno, driversurname, driverfirstname, owner ) " _
& "SELECT regno, driversurname, driverfirstname, owner FROM Tbl_DriversDetails " _
& "WHERE regno = " & lngID & ";"
DoCmd.RunSQL strSQL
Next varItem


Energy & persistance conquer all things!

 

[PHV]

If regno is not defined as numeric in Tbl_TempMthlyFiguresOrder then you have to quote the value:

& "WHERE regno = '" & lngID & "';"

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[GirlBlonde]

Hi

Does this mean i have to enter each rego no separately? when it asks me for parameter it shows me the rego no. there are thousands of them how am i going to do this when more will keep getting added. can i not do this with a text?

Energy & persistance conquer all things!

 

[ck1999]

If it is asking your for the regno then most likely it is not a field in the table if you have tried both of PHV suggestion. You post in your code regno but in discussion use rego no what is the actual field name in the table.

ck1999