Another Multi-Select question

  • Thread starter Thread starter CY
  • Start date Start date
C

CY

I am really new to VBA but have been using Access for a while, although I am
bit rusty as I haven't used it consistently lately...

My question...I would like to use a multi select list box on a form, which I
have made so that it LOOKS correct, but I can't make it actually store the
data in the underlying table. I am pretty sure I will need to write code to
do it, but I am not sure if this is really the solution I am looking for...

I need the user to be able to select categories, and then create a report
that lists every business that is using that category. So for example...

ABC company will have 3 categories: Recreation, Children's Products and
Childrens Parties.
XYZ Company will have 2 categories: Recreation, Mom's Products
123 Company will have 4 categories: Home Products, Safety, Business
Solutions and Health

Then the report that I will create from the table will list as follows:

Recreation:
ABC Company
XYZ Company

Childrens Products:
ABC Company

Children's Parties:
ABC Company

Mom's Products:
XYZ Company

etc etc etc

After I hit a block there I created a field for every category and just made
it Yes/No on the table, but this is going to cause me problems when I try to
make a report and if the user wants to add categories, not to mention it's
sloppy. Looks good on the form, but that's about it. So if anyone has any
ideas that I can use to accomplish this I would really appreciate it.
Hopefully it's not really simple and I have missed it!

Hope this makes sense...can anyone help?

TIA

CY
 
Presumably you have 3 tables:
1. Company table, with CompanyID primary key;
2. Category table, with CategoryID primary key;
3. CompanyCategory table, with fields like this:
CompanyID relates to Company.CompanyID
CategoryID relates to Category.CategoryID

So, if company ABC has 3 categories, they have 3 *records* in the 3rd table.

You interface this with:
- a main form bound to the Company table, and
- a subform bound to the CompanyCategory table.
The subform has a combo for selecting the categories.
You enter as many rows as you need in the 3rd table for the company in the
main form.

You can now create the report you want. Create a query that uses all 3
tables, and use it as the source for your report.

Once you get that working, you can use a multi-select list box to filter the
report if you wish. Details in:
Use a multi-select list box to filter a report
at:
http://allenbrowne.com/ser-50.html

However, you do not use a multi-select list box as the interface for the
form where users to add/remove/edit the data.
 
My question...I would like to use a multi select list box on a form, which I
have made so that it LOOKS correct, but I can't make it actually store the
data in the underlying table. I am pretty sure I will need to write code to
do it, but I am not sure if this is really the solution I am looking for...
Click to expand...

As Allen suggests, a Subform is a better tool for entering data in this
instance. If you really prefer the listbox look&feel, it is possible to use it
for data entry; here's some code I used in a similar instance. It will need
some study and considerable editing to apply it to your database but hopefully
it will give you a start. It's the Click event of a "Process" button on the
form to add HealthID values to the AnimalCondition table, a junction table
linking Animals to HealthIssues.

Private Sub cmdProcess_Click()
' Comments : Update the AnimalCondition table based on the selections in
' the unbound multiselect listbox lstHealthIssues.
' Newly selected rows will be added, newly cleared
' rows will be deleted.
' Parameters: None
' Modified : 01/29/02 by JWV
'
' --------------------------------------------------
' Populate the AnimalCondition table with the selected issues
On Error GoTo PROC_ERR

Dim iItem As Integer
Dim lngCondition As Long
Dim db As DAO.Database
Dim rs As DAO.Recordset

' save the current record if it's not saved
If Me.Dirty = True Then
Me.Dirty = False
End If
Set db = CurrentDb
' Open a Recordset based on the table
Set rs = db.OpenRecordset("AnimalCondition", dbOpenDynaset)
With Me!lstHealthIssues
' Loop through all rows in the Listbox
For iItem = 0 To .ListCount - 1
lngCondition = .Column(0, iItem)
' Determine whether this AnimalID-HealthID is currently
' in the table
rs.FindFirst "[AnimalID] = " & Me.AnimalID & " AND " _
& "[HealthIssueID] = " & lngCondition
If rs.NoMatch Then ' this item has not been added
If .Selected(iItem) Then
' add it
rs.AddNew
rs!AnimalID = Me.AnimalID
rs!HealthIssueID = lngCondition
rs.Update
End If ' if it wasn't selected, ignore it
Else
If Not .Selected(iItem) Then
' delete this record if it's been deselected
rs.Delete
End If ' if it was selected, leave it alone
End If
Next iItem
End With
rs.Close
Set rs = Nothing
Set db = Nothing
Me.subAnimalCondition.Requery

PROC_EXIT:
Exit Sub

PROC_ERR:
MsgBox "Error " & Err.Number & " in cmdProcess_Click:" _
& vbCrLf & Err.Description
Resume PROC_EXIT

End Sub



John W. Vinson [MVP]
 
Thank you Allen...

So I basically use the subform to create the records on the form, did I
understand that correctly? I actually don't care about what it looks like so
much - I am most concerned about just getting it right and making the
reports easy and making adding additional categorires easy to add.

Thanks for the help :)

CY
 
Yes: use a subform instead of a list box for adding/editing/deleting the
related records.
 
Back
Top