Full Version: ListBox Control Source
UtterAccess Forums > Microsoft® Access > Access Forms
StephG123
I was wondering if there is a way to have more than one field selected in the Control source for a Listbox? and if so how do I do it??
fkegley
Yes, set the Multiselect property to Simple or Extended. I believe Simple allows only Ctrl or Shift to select multiple, cannot remember which, but you can test it, Extended allows Ctrl and Shift Select both. The challenge comes when you try to use the selections.
Edited by: fkegley on Tue May 16 15:36:10 EDT 2006.
StephG123
I know how what you are explaining works that was not my question though
ontrol Source when I click on the down arrow the options I get are the columns that I bound to the Form
now lets say i have 6 of them in my list box something like this
RefKey | PartNo | OE_Number |FMSI| ... ect
what I want is the data that is in those fields to be sent to their proper columns in the table that is bound to the Form which is called NewCatalogTable
to do this there is a place in the listbox properties that says control source
however it only allows me to select one column
do you know how I would build multiple columns??
fkegley
Yes, I know how to do this. What I would do is give them the list box of course, multiselect extended so they could select as many rows as they wanted, but suit yourself on this. Then a button to click.
On the click event of the button some code to root thru the ItemsSelected collection of the list box and append the values found via an append SQL statement. I can't write all of the code for you, I have a terrible memory for code, so I just look it up as often as I need to, but I can start it for you.
Dim var As Variant
Dim J As Integer
For Each var in Me.NameOfListBox.ItemsSelected
For J = 0 To Me.NameOfListBox.ColumnCount - 1
THere you would put your append SQL statement, it would fetch the contents of the columns right out of the list box.
You refer to a particular column, row like this:
Me.NameOfListBox.Column(J, var) J represents the column number, 0 based, var is the row number
I think I have it right but it may be var, J
Next J
Next var
fkegley
I have attached a simple example of how to do this.
StephG123
ok thanks that helped big time laugh.gif only problem I have now is that some of my fields will be empty however I still need to select them
keep getting an error message saying this once i get to the empty field:
Runtime Error 3061
Too Few Parameters Expected 1
StephG123
I also have another little problem is that some of my var's have spaces between them
Like this ALFA ROMEO and I get another error on this
fkegley
You need to enclose in [] those that have spaces in their names. It might be a good idea to do this for all of them.
fkegley
I don't understand. I just changed the source table so that it now has some empty fields in it. It still works just fine. However the fields are type TEXT which can tolerate a NULL value better than some of the other types.
It any rate, the Nz function might be a way around this. Wrap the field name in the Insert statement in the Nz function:
Nz([FieldName], Value To Use If Null)
It would be up to you what to use.
Oh, yes, if the field is required in the destination table and the value in the source table is Null you'll also get this error.
StephG123
ok thanks for all your help laugh.gif
fkegley
You're welcome. I am glad I could help.
StephG123
sorry to bother you again :S
I've tried enclosing [] in a ton of spots just not sure where to do it lol
I've tried reading up on it too but there isn't much info on it
the Nz also failed told me i had an error in my Insert Into statement
StephG123
This is what i have now column 3,4 and 5 are the ones giving problems

strSQL = "INSERT INTO newCatalogTable ( BaseVehicleID, AppRefNo, Year, Make , Model, Notes ) VALUES ("

For Each var In Me.euroapps1.ItemsSelected
strSQLWork = strSQL & Me.euroapps1.Column(0, var) & ", " & Me.euroapps1.Column(1, var) & " ," & Me.euroapps1.Column(2, var) & " , " & Me.euroapps1.Column(3, var) & " , " & Me.euroapps1.Column(4, var) & " , '" & Me.euroapps1.Column(5, var) & "')"
Thanks
fkegley
The [] would only need to be around field or table names that contain embedded spaces. You don't appear to have any of that.
If the Make, Model, and Notes columns are required columns then they won't accept a NULL value. Then Nz would go around the values you are INSERTING:
strSQL = "INSERT INTO newCatalogTable ( BaseVehicleID, AppRefNo, Year, Make , Model, Notes ) VALUES ("
For Each var In Me.euroapps1.ItemsSelected
strSQLWork = strSQL & Me.euroapps1.Column(0, var) & ", " & Me.euroapps1.Column(1, var) & " ," & Me.euroapps1.Column(2, var) & " , " & Nz(Me.euroapps1.Column(3, var), 0) & " , " & Nz(Me.euroapps1.Column(4, var), '') & " , '" & Nz(Me.euroapps1.Column(5, var), '') & "')"
StephG123
i have another question I have 3 different ones that are working great now I have a problem with only one of these
It gives me a run-time error 3061 Im not quite sure why
this is my code:
Private Sub Command89_Click()
Dim var As Variant
Dim strSQL As String
Dim strSQLWork As String

strSQL = "INSERT INTO newCatalogTable ( BrakeSys,BrakeABS ) VALUES ("

For Each var In Me.brake.ItemsSelected
strSQLWork = strSQL & Trim(Me.brake.Column(0, var)) & ", '" & Trim(Me.brake.Column(1, var)) & "')"
Debug.Print strSQLWork
CurrentDb.Execute strSQLWork
Next var

End Sub
this is what the results give me :
INSERT INTO newCatalogTable ( BrakeSys,[BrakeABS] ) VALUES (Manual, 'Non-ABS')
Please help
StephG123
Dim strSQL As String
Dim var As Variant


strSQL = "INSERT INTO newCatalogTable ( BaseVehicleID, AppRefNo, Year, Make, Model, Notes, RefKey, PartNo, OE_Number,FMSI,Position,L,R,Qty ) VALUES ("

For Each var In Me.euroapps1.ItemsSelected
strSQL = strSQL & Me.euroapps1.Column(0, var) & ", '" & Chr(34) & Me.euroapps1.Column(1, var) & Chr(34) & "', " & Chr(34) & Me.euroapps1.Column(2, var) & Chr(34) & ", '" & Chr(34) & Me.euroapps1.Column(3, var) & Chr(34) & "', '" & Chr(34) & Me.euroapps1.Column(4, var) & Chr(34) & "', '" & Chr(34) & Me.euroapps1.Column(5, var) & Chr(34) & ",')"

Next var

For Each var In Me.europarts1.ItemsSelected

strSQL = strSQL & Me.europarts1.Column(0, var) & ", '" & Chr(34) & Me.europarts1.Column(1, var) & Chr(34) & "', '" & Chr(34) & Me.europarts1.Column(2, var) & Chr(34) & "','" & Chr(34) & Me.europarts1.Column(3, var) & Chr(34) & "','" & Chr(34) & Me.europarts1.Column(4, var) & Chr(34) & "','" & Chr(34) & Me.europarts1.Column(5, var) & Chr(34) & "','" & Chr(34) & Me.europarts1.Column(6, var) & Chr(34) & "','" & Chr(34) & Me.europarts1.Column(7, var) & Chr(34) & "')"

Next var
strSQL = Left$(strSQL, Len(strSQL) - 2) & ""
CurrentDb.Execute strSQL
Me.euroapps1 = Null
Me.europarts1 = Null
fkegley
If you are using Chr(34) then you don't need the ' marks do you?
StephG123
I've removed the ' and it still gives the same error :S
StephG123
I think it doesnt like the $
fkegley
It should work without the $ too. Have you tried that?
StephG123
it says access cannot find the field 'Left' reffered to in your expression
fkegley
Could all this be a missing reference? Open a code window, click Tools--->References, examine the list that appears make a note of the names of any marked MISSING. If so, uncheck the MISSING ones, scroll down the list and check the one for your version of Access. It is OK to check MORE than you think you need.
StephG123
the ones for my access are checked and still get the same error
fkegley
Then it has to be something else. I have looked at the syntax and can't find an error. That doesn't mean there isn't one, tho.
When you start putting references to form controls into the SQL you can really get some hairy syntax, almost impossible to figure out.
Try this:
rewrite your insert into statement so that it inserts one field at a time into the table. As one works, put another field into the statement, etc. When it quits working, then you have found the syntax error. It has to be syntax, doesn't it? I can't think of anything else.
StephG123
this is all i have left
Dim strSQL As String
Dim var As Variant


strSQL = "INSERT INTO newCatalogTable ( BaseVehicleID ) VALUES ("

For Each var In Me.euroapps1.ItemsSelected
strSQL = strSQL & Char(34) & Me.euroapps1.Column(0, var) & Char(34) & ","

Next var
strSQL = Left$(strSQL, Len(strSQL) - 2) & ")"
CurrentDb.Execute strSQL
Me.euroapps1 = Null
It chokes on the Yellow part this is the error given :
Sub or Function not defined
fkegley
It's not Char(34) it's Chr(34)!

P.S. Yellow doesn't show up too good.

Edited by: fkegley on Thu Jun 1 15:42:54 EDT 2006.
This is a "lo-fi" version of UA. To view the full version with more information, formatting and images, please click here.