List Box Problem

[campo88]

Hi

I am a relatively new vba user.


Problem:
I need to produce a form that lists 7 subject options.
Each option is either 10 or 20 points.
The user needs to choose two or three options, so that the total number of points is 30.

Can any1 help solve this problem?


My Idea
It is important that I record both the options chosen and the number of points each option is so that I can validate whether the total number of points = 30. I do not know how to do this.

My initial idea would be to use three seperate list boxes.

 

[MarkK]

I would start with the tables. Do you have any work done already in this regard?
You'll need an Options table, something like...

[B]tOption[/B]
OptionID
Description
Points

Then maybe a UserSelection table, where the choices a user makes are stored...

[B]tUserSelection[/B]
SelectionID
OptionID
UserID

Not shown is a User table so you can distinguish one user from another. Then, you sum the existing points using SQL like ...

SELECT SUM(Points) As TotalPoints FROM tOption WHERE OptionID In 
( SELECT OptionID FROM tUserSelection WHERE UserID = <your current user's ID> )

... and handle that however you see fit. Limit the list to only those options that are still available given the current points, or any number of ways to limit the user`s options at some point.
Cheers,

 

[campo88]

Hi Again

Sorry, I need to do this problem using vba.

I have now made the tables tOption and tSelection. I also have a user table containing name and userID.

My Form has a UserID, first name and surname, option1, option2 and option3. The options are list boxes listing all the option descriptions from the Option table (tOption). The For has a submit button at the bottom.

Should I put UserID into the UserSelection table?

Once a User has clicked submit I want to be able to validate that the total points = 30 and that replica options are not chosen. If this is not the case then error messages should show up and the data is not exported onto the selections table.

In addition, it would be very useful if the list was limited as to the options that are still available.

I hope you can help.

 

[MarkK]

I wouldn't wait for the user to click submit. I'd handle the listbox click events and calculate points and options and so on as the user interacts with the form. Only enable the submit button when it all adds up.
Maybe use multi select listboxes? Does the user know about the points thing and do you display how many points an option is worth?

 

[campo88]

Ok - that makes good sense.

The User should know about the points system, but I would need to assume that they don't. Maybe I should display the number of points next to the selection on the form if i can do this.

I am not sure if multi select listboxes is a better idea. How do I do this?

How do i calculate the points as the user interacts with the form?

 

[MarkK]

- A concern I'd have is that if the user is not aware of his point limit or the points per choice then he might not understand why certain options become unavailable or why his choices are limited.
- What might make sense is to have two list boxes, say ListA for 10 point options and ListB for 20 point options. Then it's a simple instruction to inform him to pick one option from each list, or three options from ListA. And then those are the only conditions you need to validate.
- ListBox properties you'll want to explore to make this work include MultiSelect and ItemsSelected.
- Code that might be useful...

private sub ListA_Click()
  SubmitButtonEnabler
end sub

private sub ListB_Click()
  SubmitButtonEnabler
end sub

private sub SubmitButtonEnabler
[COLOR="Green"]  'submit button enabled according to the following logic[/COLOR]
  cmdSubmit.Enabled = _
    (me.ListA.ItemsSelected.Count = 3 AND IsNull(me.ListB)) _
    OR _
    (me.ListA.ItemsSelected.Count = 1 AND Not IsNull(me.ListB))
end sub

 

[campo88]

Ok thanks - having seperate lists for 10 and 20 points makes good sense

I will work on this and get back to you over the next few days if I have any problems.

 

[MarkK]

You bet, I'll be around.

 

[dfenton]

I wouldn't wait for the user to click submit. I'd handle the listbox click events and calculate points and options and so on as the user interacts with the form.

Why the listbox's Click() event instead of the AfterUpdate()? Seems to me that is the better event.

 

[MarkK]

Why the listbox's Click() event instead of the AfterUpdate()? Seems to me that is the better event.

Why AfterUpdate()? It seems to me the Click() is better.
One of us needs to make a better argument.

 

[dfenton]

Why AfterUpdate()? It seems to me the Click() is better.
One of us needs to make a better argument.

Well, the OnClick() event can fire for actions that don't update the values in the listbox. Since you don't care about any events that don't update the listbox, AfterUpdate() is the correct event.

 

[MarkK]

I see your argument. Thanks for elaborating.
In my experience--and for lists and combos--the Click and AfterUpdate events either both fire or they both don't fire so I treat them as synonymous.
Cheers,

 

[dfenton]

In my experience--and for lists and combos--the Click and AfterUpdate events either both fire or they both don't fire so I treat them as synonymous.

Fair enough, if you're limiting it to combo boxes and listboxes. For all other control types, not so much (maybe option groups are the same, but they have multiple OnClick events so AfterUpdate of the main option group is the obvious one).

I think, though, that given that they have the same result in listboxes and combo boxes, but different results in all other control types, it would be better to use AfterUpdate all the time when you're wanting to know the value of the control. It just makes it easier so you don't have to remember where one is the appropriate event and where it's not.

 

[smig]

I'm trying to understand what you realy need here.

why not simply put 7 combo boxes with the options of 0, 10, 20 (default = 0)
a calculated field that will show the total, and will be updated after every change to each of the combo (AfterChange event)

after that store all these combo data into the table.

 

[campo88]

Hi Again

I have created two seperate lists for 10 and 20 points.

How can I capture into a table from my form which people have chosen which options, as I will need to record everyones selections.

Also, what does SubmitButtonEnabler do for the code you gave me?

 

[MarkK]

How can I capture into a table from my form which people have chosen which options, as I will need to record everyones selections.

In my post #6 use the tUserSelection table or one with a similar structure. You want to save one record with the UserID for each OptionID selected in the form. It appears that you'd want to do this in response to a click on the Submit button which ....

Also, what does SubmitButtonEnabler do for the code you gave me?

It enables or disables the Submit button based on the logic encapsulated in the expression. In the given code the submit button will be enabled if ...

three items are selected in ListA AND none in ListB 
OR
one item is selected in ListA and one in ListB

If that expression is true then the button will be enabled, otherwise the button will be disabled.

 

[campo88]

You want to save one record with the UserID for each OptionID selected in the form

I am slightly confused by what you have said here. Can I confirm that in my UserSelection table shown below I will have multiple records for one UserID?

Also I do not know how i can use selectionID?

[B]tUserSelection[/B]
SelectionID
OptionID
UserID

My database also has a "User" table with details such as first name, surname, email-address etc and I want to have some of these details on my form as well as capturing the options. I therefore made a query for the record source on my form, however once selected, my form turns blank. Do you know why this is?



The SubmitButtonEnabler is brilliant thanks!

 

[MarkK]

SelectionID is like the 'T' in 'Colbert'; it is silent. In this current case you may not directly use it, but it provides a unique ID for each record in the table, which is good practice. Some database tools cannot update a record if there is not a unique primary key.

Yes, tUserSelection will have multiple records for a single user, and very probably multiple records for a single selection.

If you join the user to the selections in a query and there are no selections, then there can be no user either. The thing becomes a "UserSelection" object. It requires at least one user and at least one selection to have any meaning.

I would be most likely to create a User form that has a Selection subform. This preserves the reality of the One-To-Many relationship between the one user and his many selections.

Cheers,

 

[campo88]

So I set selection ID as a primary key and do I set it as an AutoNumber? Also do I set both user ID as a primary key in both user and selection tables?

If you join the user to the selections in a query and there are no selections, then there can be no user either. The thing becomes a "UserSelection" object. It requires at least one user and at least one selection to have any meaning.

I have made a query by linking User ID in both "user" and "selection" tables. What fields do I need in this query?

Thanks for your help!

 

[MarkK]

Can you post your database? I think it'll take less time for me to just implement it, and then you can then look at how it was done, and others might find it interesting too.
Cheers,