Choosing more than one list value

[dulla]

is there any way to choose more than one value in a combo box that would be recorded in 1 field? as in holding down the ctl key and choosing more than one option? or would i just have to create different fields for this? thanks.

ameen

 

[SgtJarrow]

you can't do this with a combobox, but you can use a multiselect list box....

****************************
When the human body encounters disease it raises its temperature making it uncomfortable not only for the body but also for the disease. So it global warming the Earth's way of saying we are not wanted?

Robert L. Johnson III
MCSA, CNA, Net+, A+
w: rljohnso@stewart.com
h: wildmage@tampabay.rr.com

 

[SyBeerianTyGRRRR]

Hi dulla wot u need is something like this:

Insert a list box on to your form and then click the properties button on the toolbar, choose the "Other" tab and then "Multi-Select", choose "Extended" from the drop down list.

This will enable you to press Ctrl or Shift etc and left click multiple selections. Of course what you then do with these selections is another issue requiring more code.

I have a form with a multi-select list box on my db that i use to select and print out multiple mailing labels from my Contacts table/form. Let us know if this is anything like what u require and i'll put the code up. I do however acknowledge that i obtained the code from someone else in a book on Access 2002 but it works great.

Ok

Stu
UK

 

[dulla]

when i mark the multiselect property to either simple or extended (which should allow multiple values to be highlighted) i can only visually see them highlighted. when i check the tables or come back to the form, the values are not being recorded. is there something i have to do in conjunction with changing the property value from 'none' to either 'simple' or 'extended'? thanks.

ameen

 

[dulla]

thank you stu. yes i would love to see the code. all i can do now is select the values, but they do not record. thanks.

ameen

 

[SyBeerianTyGRRRR]

Hi again i'm not sure i'm totally with you, but i think that u r selecting the values in the multi-select box and expecting the same selections to be picked up in your form/table. Am i right?? If so i think it goes back to what i was saying about what you want to do with the multiple selections from the list.

Can you elaborate on what u want to do with these selections or tell me a bit more about the layout and purpose of the db. Hope i can help some more although bear in mind i am only an intermediate Access/VBA user so please don't expect the earth!!!

The label printing form if use is a simple form with a multi select list box and a print labels command button on it. I have code attached to the button that takes the selections from the ms list box links to a query that then populates a temporary table from the main table and creates a report with the labels on it ready for printing.

This is probably nothing like what u need/want but some of the code may be useful with some adaptation to achieve what u want.

Glad to be able to help if i can.

Stu
UK

 

[dulla]

stu, i am just trying to select multiple values from my listbox. on the form when i highlight any number of values (1 or more) nothing gets recorded in the tables UNLESS i change the multiselect property to "none". when i choose simple/extended for the multiselect property, no values get saved in the table. i hope this makes sense. the purpose is just for recording attributes of clients, and possibly spitting back this values in a report. thanks.

ameen

 

[SyBeerianTyGRRRR]

Hi mate,

Yes i think i now see wot u r looking for. Here is the code attached to my PrintLabels cmd button that uses a query called qryMergeContacts to populate a temporary table called tblContactsForLabels. This temp table gets its field values from the main table called tblContacts. Hope this makes sense when u see the code. Here goes:

""""""""""""""""""""""""""""""""""""""""""""""""""

Private Sub cmdPrintLabels_Click()

On Error GoTo ErrorHandler

Dim strSQL As String
Dim lst As Access.ListBox
Dim strName As String
Dim strAddress As String
Dim strJobTitle As String
Dim strTestFile As String
Dim varItem As Variant
Dim intIndex As Integer
Dim intCount As Integer
Dim intRow As Integer
Dim intColumn As Integer
Dim strTest As String
Dim i As String
Dim lngID As Long

Set lst = Me![lstSelectContacts]

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

'Check that at least one contact has been selected
If lst.ItemsSelected.Count = 0 Then
MsgBox "Please select at least one contact"
lst.SetFocus
Exit Sub
End If

For Each varItem In lst.ItemsSelected
'Check for required address information
strTest = Nz(lst.Column(5, varItem))
Debug.Print "Street address: " & strTest
If strTest = "" Then
Debug.Print "Can't send letter -- no street address!"
Exit Sub
End If

strTest = Nz(lst.Column(6, varItem))
Debug.Print "City: " & strTest
If strTest = "" Then
Debug.Print "Can't send letter -- no city!"
Exit Sub
End If

strTest = Nz(lst.Column(8, varItem))
Debug.Print "Postal code: " & strTest
If strTest = "" Then
Debug.Print "Can't send letter -- no postal code!"
Exit Sub
End If


'All information is present; write a record to the temp table
lngID = lst.Column(0, varItem)
Debug.Print "Selected ID: " & lngID
strSQL = "INSERT INTO tblContactsForLabels (ContactID, FirstName, " _
& "LastName, Salutation, StreetAddress, Town, City, StateOrProvince, " _
& "PostalCode, Country, CompanyName, JobTitle )" _
& "SELECT ContactID, FirstName, LastName, Salutation, " _
& "StreetAddress, Town, City, StateOrProvince, PostalCode, Country, " _
& "CompanyName, JobTitle FROM tblContacts " _
& "WHERE ContactID = " & lngID & ";"
DoCmd.RunSQL strSQL
Next varItem

'Print report
DoCmd.OpenReport reportname:="rptContactLabels", View:=acViewPreview

ErrorHandlerExit:
Exit Sub

ErrorHandler:
MsgBox "Error No: " & Err.Number & "; Description: " & Err.Description
Resume ErrorHandlerExit

End Sub

"""""""""""""""""""""""""""""""""""""""""""

I can obviously explain "most" of the components of the code and how they relate to my forms/tables/reports etc. But i'm afraid u will have 2 wait till tommorow nite as i am off to bed now, sorry i have early start 4 work tommorow. Meantime u can maybe play around with the code and see if u can get it to do what u want.

Best of Luck mate, will check in again tommorow night.

C ya

Stu
UK

PS: Thanks to Helen Feddema for the original code in the book.