Re: use a list with checkboxes to select categories
From: Dirk Goldgar (dg_at_NOdataSPAMgnostics.com)
Date: 03/16/05
- Next message: JimmyD: "Re: are reports the primary end files not forms?"
- Previous message: andrew v via AccessMonster.com: "Re: Seating availability depending on two fields....please assist..."
- In reply to: JimBartlett: "Re: use a list with checkboxes to select categories"
- Messages sorted by: [ date ] [ thread ]
Date: Wed, 16 Mar 2005 12:29:54 -0500
"JimBartlett" <JimBartlett@discussions.microsoft.com> wrote in message
news:EF32D30A-D3CD-4AE0-A698-A32D19BD74C0@microsoft.com
> 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
>
> "Dirk Goldgar" wrote:
>
>> "Jim Bartlett" <Jim.Bartlett@sbcglobal.net> wrote in message
>> news:0e5701c51d1f$8c41a070$a501280a@phx.gbl
>>> 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
>>
>> 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.
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.
-- Dirk Goldgar, MS Access MVP www.datagnostics.com (please reply to the newsgroup)
- Next message: JimmyD: "Re: are reports the primary end files not forms?"
- Previous message: andrew v via AccessMonster.com: "Re: Seating availability depending on two fields....please assist..."
- In reply to: JimBartlett: "Re: use a list with checkboxes to select categories"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
