Multiple Selection List Box 2

[Smitch1]

Hi,
I want to have a multiple selection list box as a field in one of my tables. Even better would be a list box, which drops down to display multiple checkboxes that the user can select from.

How would I go about this?
Any help would greatly be appreciated.

Thanks in advance.
Smitch

 

[fneily]

Have you ever used the Toolbox? In design view of your form, click on the button that has a hammer and wrench - you should see the toolbox toolbar somewhere. Make sure you have the button that says Control Wizard pushed in. If you move your mouse over the icons, a screen tip will appear that says Listbox. Click on this button, move your mouse to your form, and draw a box. When you let go, the Listbox wizard starts up and then just follow the directions. When it's completed, click on the Properties button, and then click the ALL tab. Scroll down until you see Multi Select and change it to simple. This will allow for multiple choices.
You then have to use VBA code to evaluate the selection. If you do a search in the access forums, you'll find lots of code.
As for the checkboxes, using the Access listbox - forget it. Using MS Forms 2.0 ListBox you can but then it has problems with populating the list. I wouldn't advise it.

 

[dhookom]

fneily is correct (as usual) even thought Smitch1 asked for "multiple selection list box as a field in one of my tables". Field properties in tables is not where I store any information about the display/presentation of my values. Setting display properties is for form and report controls.

Scroll down in the Query FAQs to find lots of info on multi-select list boxes

http://www.tek-tips.com/faq.cfm?pid=701.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[Smitch1]

ok, that works well when designing a form but is it possible in a table.

In my table I have a field with about 10 different items. I need to be able to select any combination of those items. If I can't do this with a multiple selection list box how should I go about it? I understand that it can be done using a form, but can it be done using a table?

Thanks again,
Smitch

 

[dhookom]

You need to read up on normalization. Selecting any combination of 10 items suggests creating a child table. You haven't described anything about your table but consider an employee table and skills table. There are 10 different skills that an employee might have. He/she might have 3 skills or possibly all ten. You would need three tables:
[tt][blue]
tblEmployees
================
EmployeeID
FirstName
LastName
...
[/blue]
[green]
tblSkills
================
SkillID
SkillName
...
[/green]
tblEmployeeSkills
=================
[blue]EmployeeID[/blue]
[Green]SkillID[/green]
[/tt]
You use a form and subform to implement this design.

You will need to wait for the next version of Access to store multiple values (complex data) in pseudo single field. There are a high percentage of experienced developers that won't like the new "feature".

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[Smitch1]

ok, the mock up database you posted is very similar to how I have setup mine. At the moment my database has two table similar to the first two tables in your example.

From what you said though, it still seems impossible to store mutliple skills for each employee?

Thanks again
Smitch

 

[dhookom]

You can store as many skills as you want per employee. As I suggested, this is done in a junction table such as tblEmployeeSkills. Each employee will have one record in this table for every skill.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[Smitch1]

ok thankyou for your help. Much appreciated. Thanks

 

[Smitch1]

Hi, I have yet another question,

Ive set the tables an relationships up and Im now working on the Forms.

I have:
tblOrders
OrderID
CustomerID
Date
etc...

tblOrderdComponents
OrderID
ComponentID
QTY

tblComponents
CompID
CompDescription
CompPrice

My questions are:
1) I have started creating a form to enter new orders into the DB. On the form I have a multiple selection list box so I can select multiple components for each order. But how do I enter the quantity required for each chosen component?

2) Once Ive done that, how do I tell the form that it should create a new record in the tblOrderedComponents table for each orderd component, and where it should get the values from?

Thanks again
Mitch

 

[dhookom]

What you are attempting to do would take a bunch of code and wouldn't work very well. Just take a look at the Northwind sample database to see how to create an order and add products (order details).

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[Smitch1]

In my DB there are 4 types of customers as defined in the Customers table. Individual Business Government and Charity.

In the Orders table I record the Price charged for a particular order. It is linked to the Customers table through the CutomerID field.

How do I calculate how much of the total revenue was calculated by each customer type (Individual Business Government and Charity) and display it in a report?

Ive been stuffing around with queries and reports for ages but cannot figure it out.

Simplified down it looks like this:
tblCustomers
CustomerID
CustomerType (Individual Business Government or Charity)

tblOrders
OrderID
CustomerID
PriceCharged

Thanks heaps in advance
Mitch

 

[dhookom]

Create a totals query with the two tables properly joined on CustomerID. Add the CustomerType and PriceCharged fields to the grid. Group By CustomerType and Sum PriceCharged.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]