Help with creating a form that calculates

[JP]

Good day,

Firstly, I'm quite the novice with this software. I'd like
to create a form, that enable a user to make multiple
selections from a list. Will this require multiple drop
down boxes? Or could one dropbox be configured to do this?

Also, I need for each choice to be assigned a numeric
value, and have that value automatically calculated
somewhere as items are being selected, and displayed
somewhere on the form. (perhaps even displayed as a
color). Can I do this in Access, or is Excel required for
this task?

Many thanks for any and all help!

JP

 

[Roxie Aho]

The List Box control has a MultiSelect property under the
Other tab on Properties. Help has a good explanation of
how to use it. Select Simple or Extended. My example
works with both.

In the example I added a text box named ListAdd to the
form. The name of the list box is ListBox because that's
what I was able to grab quickly. It just displays name
and a primary key from a table. In the ListBox After
Update event I added the following code:
Private Sub ListBox_AfterUpdate()
Dim ctlList As Control, varItem As Variant
Dim a As Long
' Return Control object variable pointing to list box.
Set ctlList = Me.ListBox
' Enumerate through selected items.
For Each varItem In ctlList.ItemsSelected
' Print value of bound column.
'Debug.Print ctlList.ItemData(varItem)
a = a + ctlList.ItemData(varItem)
Next varItem
'Debug.Print a
ListAdd.Value = a
End Sub

The user can hold down the Control Key while clicking to
select the items he or she wants. At the same time the
Text Box ListAdd shows the sum of the primary key values
as the user selects.

The Text Box control has many properties you can set in
code for display purposes.

The form and example I created has no real purpose. It is
only to show you some capabilities. The code also comes
from Visual Basic Help: Item Data Property and example.

Roxie Aho
(e-mail address removed)

 

[Greg Kraushaar]

The short answer is yes.
The long answer requires more info from you.
Are all the selections dependent on each other. For example,
If you define Post(Zip) code then Town and state follow automatically
(Yes I know this example doesn't hold up in the real world)

In htis case, use a single drop down
or are they independent of one another
eg Sex = Male/Female/NotTelling
Salute = Mr/Mrs/Dr/Ms/Sir/Prof/

In which case multiple dropdowns are the way to go unless the choices
are really limited

You can refer to the control using
cboMyDropdown.Column(1) to get info out

It is generally a good idea to use numbers as codes and display a more
user friendly label

HTH

 

[JP]

The form will be used as a daily briefing sheet for a
maritime port. The choices from a list would be events
that may occur in the port, i.e. inbound oil tanker (say 1
point), small oil spill (1 point), large hazmat spill (5
points), etc. etc. So, one day there may be only 3 choices
to select, the next day 15. The total number of choices
available would be around 25-30. The numeric values would
be tallied to automatically provide a quick risk analysis,
such as a green-amber-red system. What I thought would be
cool originally, was to have a drop down box with all the
choices availabale, and if a selection was made, another
identical box would appear enabling the user to make
another selection, and have this continue until all
choices needed were selected. But that, if possible, would
exceed my novice abilities.

 

[PC Datasheet]

First you need a table that contains all possible choices and the corresponding
value of each choice. On your form you need a multi-select listbox based on the
table that allows you to make multiple choices. (Look that up in the Help file).
Also on your form you need a text box to hold the sum of the values of the
choices you make. Then you need a little VBA code in the AfterUpdate event of
the listbox that adds the values as you make choices and subtracts the values as
you remove choices. Finally, you need to use conditional formatting on the
textbox to change the backcolor of the textbox for various levels of total vaue.
(you can also look up conditional formatting in the help file)

 

[JP]

Eureka! It worked! Although, I have no idea how it worked,
or what any of that stuff in code means (newbie idiot,
remember) but it worked.

Many thanks! This clears hurdle one of about 4!

JP