A little Listbox Problem

[2003Learner]

I am currently writing a recipe database and I have drawn a blank when trying to resolve an issue with data to and from list boxes.

My form includes two list boxes. One contains a list of available recipes and the other holds the selected recipes. But here is the twist. When I click on a button to add a recipe from list box 1 to list 2, I have a text box that collects a quantity value and I want to add this value to the end of the data taken from list box 1 before I add this to list box 2.

So the concept = List box 1 (Item) + quantity added to list box 2

I created a query AddMeuToList and changed list box 2 rowsourcetype to Table/Query and rowsource to the query I created. The properties on list box 2 have been changed to allow multiple columns and I have set column widths etc. When I run the code as a test, all I get is the column headers (taken from the query) and none of the data. I changed the query just to try to get the basic information without the quantity value but it still does not work. The code for the query is as follows:-

SELECT RECIPE.RID AS Record, RECIPE.RNAME AS Recipe, RECIPE.RCATEGORY AS Category, RECIPE.RVEGETARIAN AS ['Vegetarian?']
FROM RECIPE
WHERE (((RECIPE.RID)=Forms!MakeMenu!ListRecipeList.ListIndex+1));

Any idea how I can make list box 2 accept the values from list box 1 and is there a way to get the quantity value appended to the end of the data from list box 1?

 

[vbaInet]

There's no way you can get that Quantity value from the textbox unless there's a field in the related table that will hold this value.

The only other way is to have an unbound listbox (i.e. RowSourceType = Value List) and Add the item to it.

Me.Listbox[COLOR=Red]2[/COLOR].AddItem Me.Listbox[COLOR=Red]1[/COLOR] & ";" Me.QuantityTextbox, [I][optional - Row Index][/I]

 

[2003Learner]

Thank you vbaInet for your suggestion.

I tried it but I'm still not getting the values to be added to my second listbox. So I think I'm going to try to convert values from the first listbox, append ; to each value including the quantity value and insert this into listbox 2. I wish Microsoft had made the listbox a little more flexible.

 

[vbaInet]

What did you try? The extra quantity field in the table?

It's not automatically going to appear in the table so you have to run an append query to save it into the table at the corresponding record before requerying the listbox.

docmd.execute "UPDATE TableName SET QuantityField = " & Me.QuantityTextbox & " WHERE SomeID = " & Me.SomeIDtextbox & ";"

Oh, by the way, the code I provided was missing an ampersand:

Me.Listbox2.AddItem Me.Listbox1 & ";"[COLOR=Red][B] &[/B][/COLOR] Me.QuantityTextbox, [I][optional - Row Index][/I]

 

[smig]

to use the AddMeuToList your list must be a List of Values. it can't be a Table/Query
it should be a long string that will look like (for two columns list):
MyList.Rowsource = Var0Col0 & "," & Var0Col1& "," & Var1Col0& "," & Var1Col1& "," & Var2Col0& "," & Var2Col1

 

[2003Learner]

vbaInet I tried your suggested code

Me.Listbox2.AddItem Me.Listbox1 & ";" & Me.QuantityTextbox, [optional - Row Index]

and yes I did spot the missing & character. I now realise what you meant by adding the columns from the first list box to the second by using delimiters. I now have the values added to the second list box. This is what I did

'Made sure I selected something from first list box
If Me.LstRecipeList.ItemsSelected.Count > 0 Then
intColCount = Me.LstRecipeList.ColumnCount
For intI = 0 To intColCount - 1
strResult = strResult & Me.LstRecipeList.Column(intI) & ";"
Next intI
'picks up the quantity value
strResult = strResult & Str(Me.txtQty.Value)
LstMenuList.RowSourceType = "Value List"
LstMenuList.RowSource = strResult
LstMenuList.Requery
End if

I'm sure I can tidy up this code a bit.

Thanks smig for your suggestion also. Yes I did try to get the results of a query to populate the second list box, clearly I was looking at the wrong concept.

I remember early versions of Excel VBA and they allowed you to use the lstBox.List(index) property and that allowed you to easily copy items from one list box to another.

 

[vbaInet]

Happpy to hear you've got this resolved.

I remember early versions of Excel VBA and they allowed you to use the lstBox.List(index) property and that allowed you to easily copy items from one list box to another.

If both listboxes have a Row Source Type of Table/Query then you can copy the Row Source of one listbox onto the other. If the Row Source Type is Value List then again you can copy the Row Source from one to the other. In this case you're dealing with different Row Source Types so it had to be done one by one.