use a list with checkboxes to select categories

  • Thread starter Thread starter Jim Bartlett
  • Start date Start date
J

Jim Bartlett

I am writing a db for jewelry vendors. I want to
categorize the vendors by the type of items they sell.
Ex:
Bobs Jewelry - Watches
Franks Jewelry - Gold, Chains, Wedding Bands
Marys Jewelry - Diamonds, Rubies, Sapphires

I have the vendor table and a Vendor Type table. The
Vendor type table has KEY, YES/NO, Type fields with the
data Watches,Gold,Chains,Wedding
Bands,Diamonds,Rubies,Sapphires etc.

1. I want to select the items by checkbox
2. save them to the vendor record
3. when I open the vendor record, I want all types to
show with the saved types checked and the others unchecked
 
-----Original Message-----
I am writing a db for jewelry vendors. I want to
categorize the vendors by the type of items they sell.
Ex:
Bobs Jewelry - Watches
Franks Jewelry - Gold, Chains, Wedding Bands
Marys Jewelry - Diamonds, Rubies, Sapphires

I have the vendor table and a Vendor Type table. The
Vendor type table has KEY, YES/NO, Type fields with the
data Watches,Gold,Chains,Wedding
Bands,Diamonds,Rubies,Sapphires etc.

1. I want to select the items by checkbox
2. save them to the vendor record
3. when I open the vendor record, I want all types to
show with the saved types checked and the others unchecked
4. I want to be able to run reports based on vendor
Click to expand...
types. i.e. a list of all diamond vendors.
 
Jim Bartlett said:
I am writing a db for jewelry vendors. I want to
categorize the vendors by the type of items they sell.
Ex:
Bobs Jewelry - Watches
Franks Jewelry - Gold, Chains, Wedding Bands
Marys Jewelry - Diamonds, Rubies, Sapphires

I have the vendor table and a Vendor Type table. The
Vendor type table has KEY, YES/NO, Type fields with the
data Watches,Gold,Chains,Wedding
Bands,Diamonds,Rubies,Sapphires etc.

1. I want to select the items by checkbox
2. save them to the vendor record
3. when I open the vendor record, I want all types to
show with the saved types checked and the others unchecked
Click to expand...

I think you really need three tables:

Vendors
VendorID (primary key)
VendorName
(other fields ...)

MerchandiseTypes
MerchandiseID (primary key)
MerchandiseDescription
(other fields ...)

VendorsMerchandise
VendorID (compound primary key)
MerchandiseID (compound primary key)

A record in VendorsMerchandise represents the fact that vendor
(VendorID) deals in merchandise (MerchandiseID).

Given such tables, you can certainly represent the data correctly and
efficiently. All that's left is the user interface design. Normally,
one would present the VendorsMerchandise table as a subform on the
Vendors form, showing only the records on the subform that are related
to the current Vendor record on the main form. However, that won't let
you see all merchandise at once, nor select/deselect merchandise by
clicking with the mouse. There's no built-in, code-free Access
mechanism to do that, AFAIK, so it's necessary to write some code.

Tell me, are you absolutely wedded to the idea of using a check box to
select items? I know a way to do that, but the implementation looks a
bit clunky to my eye. How would you feel about using a multiselect list
box instead? The list box would show all the items, and the ones that
are actually selected for the current vendor would be highlighted. In
my experience, that looks a bit better than the calculated check boxes
that are otherwise required.
 
Thanks for your help. I had pretty much decided that I would need an "In
between" table but haven't figued out how to make it work the way I want it
to. I am not necessarily married to the checkbox idea, highlighting would be
OK and would be even better if the selected items were at the top of the list
when the vendor record was reopened.

Thanks again.
Jim
 
JimBartlett said:
Thanks for your help. I had pretty much decided that I would need an
"In between" table but haven't figued out how to make it work the way
I want it to. I am not necessarily married to the checkbox idea,
highlighting would be OK and would be even better if the selected
items were at the top of the list when the vendor record was reopened.

Thanks again.
Jim
Click to expand...

How about this, then?

Assuming you have set up tables the way I described, build a form named
"frmVendors" with the Vendors table as its recordsource. On that form,
put a list box with these properties:

Name: lstVendors
Column Count: 2
ColumnHeads: No
Columns Widths: 0"; 2" (or whatever seems approopriate)
Control Source: (blank)
Multi Select: Simple
Row Source Type: Table/Query
Row Source:
SELECT
MerchandiseTypes.MerchandiseID,
MerchandiseTypes.MerchandiseDescription,
IsNull([T].[VendorID]) AS SoldByCurrentVendor
FROM
MerchandiseTypes
LEFT JOIN
[SELECT *
FROM VendorsMerchandise VM
WHERE VM.VendorID = Forms!frmVendors!VendorID]. AS T
ON MerchandiseTypes.MerchandiseID = T.MerchandiseID
ORDER BY
IsNull([T].[VendorID]) DESC,
MerchandiseTypes.MerchandiseDescription;

Create the following code module for the form:

'----- start of code for form module -----
Option Compare Database
Option Explicit

Private Sub ClearMerchandiseSelections()

Dim intI As Integer

With Me.lstMerchandise
For intI = (.ItemsSelected.Count - 1) To 0 Step -1
.Selected(.ItemsSelected(intI)) = False
Next intI
End With

End Sub


Private Sub Form_Current()

Dim rs As DAO.Recordset
Dim intI As Integer

Me.lstMerchandise.Requery

' Clear all currently selected merchndise.
ClearMerchandiseSelections

If Not Me.NewRecord Then

Set rs = CurrentDb.OpenRecordset( _
"SELECT MerchandiseID FROM VendorsMerchandise " & _
"WHERE VendorID=" & Me.VendorID)

' Select the merchandise currently on record for this VendorID.
With Me.lstMerchandise
Do Until rs.EOF
For intI = 0 To (.ListCount - 1)
If .ItemData(intI) = CStr(rs!MerchandiseID) Then
.Selected(intI) = True
Exit For
End If
Next intI
rs.MoveNext
Loop
rs.Close
Set rs = Nothing
End With

End If


End Sub


Private Sub lstMerchandise_AfterUpdate()

On Error GoTo Err_lstMerchandise_AfterUpdate

Dim db As DAO.Database
Dim ws As DAO.Workspace
Dim strSQL As String
Dim blnInTransaction As Boolean
Dim varItem As Variant

' Make sure the current member record has been saved.
If Me.Dirty Then Me.Dirty = False

Set ws = Workspaces(0)
Set db = ws.Databases(0)

ws.BeginTrans
blnInTransaction = True

' Delete all merchandise now on record.
strSQL = "DELETE FROM VendorsMerchandise " & _
"WHERE VendorID = " & Me.VendorID

db.Execute strSQL, dbFailOnError

' Add each hobby selected in the list box.
With Me.lstMerchandise
For Each varItem In .ItemsSelected
strSQL = _
"INSERT INTO VendorsMerchandise (VendorID,
MerchandiseID) VALUES (" & _
Me.VendorID & ", " & .ItemData(varItem) & ")"
db.Execute strSQL, dbFailOnError
Next varItem
End With

ws.CommitTrans
blnInTransaction = False

Exit_lstMerchandise_AfterUpdate:
Set db = Nothing
Set ws = Nothing
Exit Sub

Err_lstMerchandise_AfterUpdate:
MsgBox "Error " & Err.Number & ": " & Err.Description,
vbExclamation, _
"Unable to Update"
If blnInTransaction Then
ws.Rollback
blnInTransaction = False
End If
Resume Exit_lstMerchandise_AfterUpdate

End Sub
'----- end of code for form module -----

That will give you a multi-select list box that reflects the mechandise
sold by a particular vendor by selecting those items, and updates it
when you de-select an item. When a vendor's record is first opened, the
selected items are always at the top of the list. I'm not sure that
last is a good idea, from a user-interface point of view, but you asked
for it.
 
Back
Top