Confirming selection from a listbox 1

[ghobbit]

Hi there

This is what I'm trying to acheive and I'm sure its possible but cant quite get my head around it to do it.

I have a form with a multi select list box from which our customers can select a number of items they would like added to their worklist for the day. They then click on a command button which then sends those items to a table from which our employees then generates a worklist.

However what I would like is that once the customer has entered their selections I would like them to be able to click another command button before submitting the work to the table which would then show them the selections they have made before the info is submitted to the table. They would then review what they've selected and make additions or subtractions as neccessary.

I'm not sure whether or not I have to create two tables the first of which would hold their initial selections and then the first button would then run a query based on that first table and then once they click the submit button it sends that info to another table from which the days worklist can be drawn.

Another question is what if once they've reviewed what they've selected and wish to remove something? If selections go to a temporary table and then they unslected an item will that remove it from the table or would it create a new record which is not what I want.

If you could help that would be most aprreciated. I know very little in the way of coding so any explicit instructions would be most welcomed

regards

Steve

 

[greely]

One way to do what you want is with an "ok" or "cancel" MessageBox:

You can put this code on the existing order button...but towards the top of the procedure before the data is saved to the table.

When the order button is clicked, a messagebox will display the items selected from the listbox. If cancel is clicked, the customer will return to the form. If ok is clicked, the order will be processed.

======
Dim ctl, varItm, TheSelectedItems, response

Set ctl = Me!yourlistbox

For Each varItm In ctl.ItemsSelected
TheSelectedItems = TheSelectedItems & Me!yourlistbox.Column(0, varItm) & vbCrLf
Next varItm

response = MsgBox("You have selected: " & vbCrLf & TheSelectedItems, vbOKCancel, "Your Selections")
If response = vbCancel Then
Exit Sub
End If
=====

replace YourListBox with the name of your list box. Also, the code assumes that the data displayed to your customers is in the first column of the listbox...Column(0). If it's in another column, you will have to change the column number. Of course, code provided as-is.

 

[ghobbit]

Thanks Greely

Thats fantastic - works like a charm


much appreciated

regards

Steve

 

[ghobbit]

Hi Greely or anyone else who may read this and can help

The above code works fine but I'd like to get a bit more adventurous

I have two list boxes one called StainsList and the other ImmunoList. I've put in your code above and made the following changes to reflect my listbox

Dim ctl, varItm, TheSelectedItems, response

Set ctl = Me!StainsList

For Each varItm In ctl.ItemsSelected
TheSelectedItems = TheSelectedItems & Me!StainsList.Column(1, varItm) & vbCrLf
Next varItm

response = MsgBox("You have selected: " & vbCrLf & TheSelectedItems, vbOKCancel, "Your Selections")
If response = vbCancel Then
Exit Sub
End If

Then what I did was to paste this code in again and this time changed the name of the listbox to point to the other listbox. When I clicked on the command button I got an error and so after mucking about with the code in the second pasting (and not being conversant with code I'm sure I did it wrong)I ended up with

Dim ctl2, varItm2, TheSelectedItems2, response2

Set ctl2 = Me!ImmunoList

For Each varItm2 In ctl2.ItemsSelected
TheSelectedItems2 = TheSelectedItems2 & Me!ImmunoList.Column(1, varItm2) & vbCrLf
Next varItm

response2 = MsgBox("You have selected: " & vbCrLf & TheSelectedItems2, vbOKCancel, "Your Selections")
If response2 = vbCancel Then
Exit Sub
End If

Now when I click the command button I get no errors and I get the first message box asking tme to check my selection in the StainsList and then I get another box appear asking me to check what I've selected in the ImmunoList and I click ok. I then get a box up saying its adding the selected items to the table. It does add the selected items from the first listbox but not the second. I'm sure its more to do with the code relating to adding the data to the tables rather than what I've done above as I get the right boxes etc asking if my selections are correct.

Is there another way I can perform the above i.e just have one box listing all the selections from both Listboxes at once rather than having two separate :Check your selections" boxes??

Sorry for the length of this.

regards

Steve

 

[greely]

Hi Steve,

Looks as if you have done most of it correctly

The following code generates the list of items from theStainsList. What it's doing is looping through each selected item in the list (ctl.itemsselected) and then adding them together with a line feed (vbcrlf) between each item:

=======
Set ctl = Me!StainsList

For Each varItm In ctl.ItemsSelected
TheSelectedItems = TheSelectedItems & Me!StainsList.Column(1, varItm) & vbCrLf
Next varItm
=======

The code you wrote will generate another list from immunolist:

=======
Set ctl2 = Me!ImmunoList

For Each varItm2 In ctl2.ItemsSelected
TheSelectedItems2 = TheSelectedItems2 & Me!ImmunoList.Column(1, varItm2) & vbCrLf
Next varItm2
=======

Now you want to put them together, and add some description. Its done like this (you can change or add to the text between the quotes):

===========
TheCompleteList = "You have selected:" & vbcrlf & TheSelectedItems & TheSelectedItems2
===========

The next section of code displays the messagebox. If the response is cancel (vbcancel) then no more code is run. If the response is ok, the rest of the code is run.

=======
response = MsgBox("You have selected: " & vbCrLf & TheCompleteList, vbOKCancel, "Your Selections")
If response = vbCancel Then
Exit Sub
End If
=======

To put it all together:

=======
Dim ctl, varItm, TheSelectedItems, response
Dim ctl2, varItm2, TheSelectedItems2
Dim TheCompleteList

Set ctl = Me!StainsList

For Each varItm In ctl.ItemsSelected
TheSelectedItems = TheSelectedItems & Me!StainsList.Column(1, varItm) & vbCrLf
Next varItm

Set ctl2 = Me!ImmunoList

For Each varItm2 In ctl2.ItemsSelected
TheSelectedItems2 = TheSelectedItems2 & Me!ImmunoList.Column(1, varItm2) & vbCrLf
Next varItm2

TheCompleteList = "You have selected:" & vbcrlf & TheSelectedItems & TheSelectedItems2

response = MsgBox(TheCompleteList, vbOKCancel, "Your Selections")
If response = vbCancel Then
Exit Sub
End If
========

You now have one messagebox which lists the items from both listboxes.

 

[ghobbit]

Many thanks for explaining that step by step - you made it very clear and easy to follow and I'm slowly getting a better grasp of whats going on and how to put it all together.

The code works very well - now I just have to work out why its putting the selections from stainsList into the table but not the ImmunoList.....


many thanks again

regards

Steve