List of options

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a list of 4 items. When the user selects the items they could be
selecting 1 of the options, 2 of the options, etc... What is the best way to
incorporate this into a form?
Thanks
 
Hi.

The most common approach is to use a multi-select list box because it's so
flexible. If more items need to be added to the list, a new record is
inserted into the source table. A query can retrieve all records, in
whatever sort order or grouping desired, and then the RowSource Property of
the list box can be set to this query. Any added records will be picked up
by the same query, so it's easily maintainable in that the query and list box
need not be altered any further. Use VBA code to determine which items are
selected by checking the Selected or ItemsSelected Properties.

One may use option buttons, but it's less flexible when changes need to be
made and may take up more space on the form.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address so that a message will
be forwarded to me.)
- - -
If my answer has helped you, please sign in and answer yes to the question
"Did this post answer your question?" at the bottom of the message, which
adds your question and the answers to the database of answers. Remember that
questions answered the quickest are often from those who have a history of
rewarding the contributors who have taken the time to answer questions
correctly.
 
Thanks for the info. I think I left out a few things that I need to do with
this list box. First, I need to take the values selected and put them in a
"form letter". Second, one of the values (if selected) needs to combine the
value plus a field from one of the tables. Lastly, I don't need to save the
data, it's just used temporarily to fill out a "form letter", the the record
is deleted.

On a side note, I created a listbox with the multiselect property at simple.
The control is bound to a field a table. When I select the value, it
doesn't show up in the table at all. I read the help on the selected
property, but I didn't really understand it.
Thanks!
 
Hi.
I think I left out a few things that I need to do with
this list box.
Click to expand...

I foresee a bright future for you in management. :-)
First, I need to take the values selected and put them in a
"form letter".
Click to expand...

I can envision many different ways to do that. Perhaps you've already set
this up? If not, the most common approach would probably be using a Word
template. (This would definitely _NOT_ be my choice, so if you pick it and
need help, please post a new question on how to do so.)
Second, one of the values (if selected) needs to combine the
value plus a field from one of the tables. Lastly, I don't need to save the
data, it's just used temporarily to fill out a "form letter", the the record
is deleted.
Click to expand...

Create a text box on the form that is bound to that "field from one of the
tables." Here's some code that iterates through the list box, determines
which items are selected, compares the value with some "special" value and
concatenates it when the value matches, and then saves this value in a table.

Private Sub StoreLstBxSelBtn_Click()

On Error GoTo ErrHandler

Dim idx As Long
Dim sValue As String

For idx = 0 To (Me!lstList.ListCount - 1)
If (Me!lstList.Selected(idx)) Then
sValue = Me!lstList.ItemData(idx)

If (sValue = "special") Then
sValue = sValue & Me!txtSpecialValue.Value
End If

CurrentDb().Execute "INSERT INTO tblStuff (SomeValue) " & _
"VALUES ('" & sValue & "');", dbFailOnError

End If
Next idx

Exit Sub

ErrHandler:

MsgBox "Error in StoreLstBxSelBtn_Click( ) in" & vbCrLf & _
Me.Name & " form." & vbCrLf & vbCrLf & _
"Error #" & Err.Number & vbCrLf & Err.Description
Err.Clear

End Sub

.. . . where lstList is the name of the list box, txtSpecialValue is the name
of the text box bound to the "field in one of the other tables," tblStuff is
the name of the table to store this temporary data, and SomeValue is the name
of the field to store the temporary data in.
When I select the value, it
doesn't show up in the table at all.
Click to expand...

If the code above doesn't shed some light, then please post your code and
we'll see whether we can help fix it.
I read the help on the selected
property, but I didn't really understand it.
Click to expand...

If the listbox's row's Selected Property = TRUE, that means the row is
highlighted. FALSE means it's not highlighted. For example:

Me!lstList.Selected(4) = TRUE

This means that the 5th row displayed in the list box will be highlighted
when this code is executed. (Count in many collections starts at 0, not 1.)

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address so that a message will
be forwarded to me.)
- - -
If my answer has helped you, please sign in and answer yes to the question
"Did this post answer your question?" at the bottom of the message, which
adds your question and the answers to the database of answers. Remember that
questions answered the quickest are often from those who have a history of
rewarding the contributors who have taken the time to answer questions
correctly.
 
Thanks!

'69 Camaro said:
Hi.


I foresee a bright future for you in management. :-)


I can envision many different ways to do that. Perhaps you've already set
this up? If not, the most common approach would probably be using a Word
template. (This would definitely _NOT_ be my choice, so if you pick it and
need help, please post a new question on how to do so.)


Create a text box on the form that is bound to that "field from one of the
tables." Here's some code that iterates through the list box, determines
which items are selected, compares the value with some "special" value and
concatenates it when the value matches, and then saves this value in a table.

Private Sub StoreLstBxSelBtn_Click()

On Error GoTo ErrHandler

Dim idx As Long
Dim sValue As String

For idx = 0 To (Me!lstList.ListCount - 1)
If (Me!lstList.Selected(idx)) Then
sValue = Me!lstList.ItemData(idx)

If (sValue = "special") Then
sValue = sValue & Me!txtSpecialValue.Value
End If

CurrentDb().Execute "INSERT INTO tblStuff (SomeValue) " & _
"VALUES ('" & sValue & "');", dbFailOnError

End If
Next idx

Exit Sub

ErrHandler:

MsgBox "Error in StoreLstBxSelBtn_Click( ) in" & vbCrLf & _
Me.Name & " form." & vbCrLf & vbCrLf & _
"Error #" & Err.Number & vbCrLf & Err.Description
Err.Clear

End Sub

. . . where lstList is the name of the list box, txtSpecialValue is the name
of the text box bound to the "field in one of the other tables," tblStuff is
the name of the table to store this temporary data, and SomeValue is the name
of the field to store the temporary data in.


If the code above doesn't shed some light, then please post your code and
we'll see whether we can help fix it.


If the listbox's row's Selected Property = TRUE, that means the row is
highlighted. FALSE means it's not highlighted. For example:

Me!lstList.Selected(4) = TRUE

This means that the 5th row displayed in the list box will be highlighted
when this code is executed. (Count in many collections starts at 0, not 1.)

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address so that a message will
be forwarded to me.)
- - -
If my answer has helped you, please sign in and answer yes to the question
"Did this post answer your question?" at the bottom of the message, which
adds your question and the answers to the database of answers. Remember that
questions answered the quickest are often from those who have a history of
rewarding the contributors who have taken the time to answer questions
correctly.
Click to expand...
 
Back
Top