Write listbox selections to table.

[Icecream7]

Hi, I've been working on this database for someone for some time now and I have run stuck again. I do not totally understand how to get the multiple choice selections from a list box to a table. I've been able to get the other information to write but run stuck here.

I also want to find a way to check that a check box has been selected and run an error if both are unchecked.
Below is what I have so far. It's all being done at the save_click and writing to a table that has a column for each list box. There are a total of about 20 different list boxes but I'm just starting with a few to make sure I can get it first. The first one starts at column 7 on the table. Here's what I have so far....

Private Sub Save_Bid_Click()

Dim rs As New ADODB.Recordset
Dim varItem As Variant

On Error GoTo ErrorHandler

'Check to see if the location field is not blank
If IsNull(Me.txtLocation) Then
MsgBox "Please enter in a location for this bid.", vbInformation, "Missing Location!"
Me.txtLocation.SetFocus
GoTo Done
End If

'Check to see if the quote number has been entered
If IsNull(Me.txtQuote) Then
MsgBox "Please enter a Quote Number for this bid.", vbInformation, "Missing Quote Number!"
Me.txtQuote.SetFocus
GoTo Done
End If

'Open Bid table to write selections to
rs.Open "tblBid", CurrentProject.Connection, adOpenDynamic, adLockPessimistic

With rs
'Start loop to process all items selected in the list boxes
For Each varItem In Me.Create_Bid.ItemSelected
.AddNew
.Fields("QuoteNumber") = txtQuote
.Fields("CustomerName") = lstCust
.Fields("JobLocation") = txtLocation
.Fields("lstBathroom") = Me.lstBathroom.Column(6, varItem)
.Fields("lstToilets") = Me.lstToilets.Column(7, varItem)
.Fields("lstTub") = Me.lstTub.Column(8, varItem)
.Fields("lstVents") = Me.lstVents.Column(9, varItem)
.Update


End With
rs.Close

MsgBox "Your bid has been successfully created!", vbExclamation, "Bid Created"

DoCmd.Close acForm, Me.Name
GoTo Done

ErrorHandler:
MsgBox Err.Description
Done:

End Sub

 

[formerTexan]

Hello Icecream,


.Update
Next varItem 'ADD this line to loop through selected items

End With

---------

To validate or test the checkboxes, look at their values: -1 for false, 0 for true.

CHeers,
Bill

 

[Eupher]

Also, not sure if this is just a typo in your post, or if you cut and pasted your code:

For Each varItem In Me.Create_Bid.ItemSelected

should be...

For Each varItem In Me.Create_Bid.Item[b][red]s[/red][/b]Selected

HTH,

Ken S.

 

[Icecream7]

Thanks guys I'll try the suggestions. I was a bit nervy on the ItemsSelected because I didn't get the drop down suggestions and when I did it only had InSelection.

 

[Eupher]

Icecream7,

Are you sure you want to use multi-select listboxes? What if the user doesn't select the same number of items in each listbox? What will the saved record(s) look like in that case? Are you really using up to 10 columns in your listboxes?

Just some musings, sorta wondering where you are going with this...

Ken S.

 

[Icecream7]

Hi Eupher, I have about 20 listboxes on the form each with 2 columns. What the form is for is.....they have a business where they create job bids based on several different kits. The kits are for various things and from what they have told me they do use different kits under one category, example being a bathroom category will have a kit for toilets one for sink one for piping etc. I was thinking about doing a comma delimited column for each listbox category. I'm sure this all sounds kind of confusing so if you aren't totally clear on something please ask because I can use all the help possible right now.

I tried the suggestion from formerTexan and I received the following error:

**For Each can only iterate over a collection object or an array**

 

[formerTexan]

Icecream7,

Looking back over your original posting, I see what I missed.

For Each varItem In Me.Create_Bid.ItemsSelected
....
Next varItem

The contents of a listbox constitute a "collection object or an array", so Me.Create_Bid needs to be a reference to a list box.

I think what you did was try to concurrently loop through a group of list boxes rather than loop through one at a time. Each loop needs to be based on a single listbox. You can use your concurrent approach, but as Ken pointed out, you will run into a problem if the list boxes have different number of rows/items.

Try the following:

With rs
'Start loop to process all items selected in the list boxes
For Each varItem In Me.Create_Bid.ItemSelected
.AddNew

.Fields("lstBathroom") = Me.lstBathroom.Column(6, varItem)
.Update

Next varItem
End With

Using a delimited list as a Value List for a listbox's rowsource is fine as long as you anticipate a couple of potential issues.

1. A2K and earlier versions have a 2048 character limit. I believe later versions have increased this.

2. Since it sounds as if the data for your delimited list may potentially change, you will need to create some mechanism for allowing the user to handle this without having to open up the form and listbox in design view to change the rowsource.

Cheers,
Bill

 

[Eupher]

Hi, Icecream7,

If your listboxes only have 2 columns each, then your column syntax is incorrect. Further, I believe you need to rethink the logic of how your listboxes relate and how you will loop through them, as Bill suggested above:

.Fields("lstBathroom") = Me.lstBathroom.Column(6, varItem)

For instance, the syntax of the line quoted above says you want the data found in the 7th column of lstBathroom, from the same row as the one currently selected in Create_Bid! Do you see the problem(s) here?

Ken S.