You will need a small subform on the main form to make it simple to enter multiple categories per person. you could use a multi-select listbox but this involves VBA and is much more difficult to work with. The mini-subform can be formatted to blend into the main form so no one even realizes it is a subform.
I don't have a very good "eye" for GUI design. But when I saw Pat's quote (above) in another thread, I thought of a couple of places I could use this to solve an unusual problem and make my user's experience better. So I tried it out.
I have been unable to make a small subform "blend in". Specifically, I can't get rid of the "3D" effect on my small subform.
Is there a property that you need to set to remove the "3D" border from a subform? I haven't found it yet.
It is on the subform container control on the main form. Go to it, in the main form, click on the subform container and go to the Special Effect property.
But seriously, I had assumed that it was basically impossible because to look like a listbox, we had to have no grid (assuming we're using a datasheet view), and when we select a value, the entire row is highlighted, not just the word.
Is that doable?
Regarding the effects, I'd go for sunken- all listboxes, (to my eyes, at least) are slightly sunken anyway.
This was good thing because this was quite perfect solution to me not wanting to requery my multi-select listbox everytime we added a new record. The subform looks and acts just like a listbox.
Just one thing, though...
Everytime it's requeried becasuse of parent form moving to new record, there's a brief instant where "Name#" appears then everything works normally.
Yeah, get faster computers so it doesn't take so long to refresh (just kidding). Actually we do this with one of our apps - we set the subform container's visibile property to no and then back to yes whenever we change or requery. But, it all depends on which bothers you more - seeing #Name briefly or having the subform disappear and reappear.
One more quick question. If I enable vertical scrollbar, there's a gray area where it would be if the list is too short. Did anyone find a solution for that (short of overlaying a image of scrollbar, which IMHO is too much hassle & fugly)
not a solution to your problem, banana, but just a comment that A2007 looks MUCH nicer in this regard. it still leaves space for a scroll bar, but the background is white, not that garish dark brown/gray that has been the default since windows 3.11/Access 2...
dissapointingly, my beatiful forms looks pretty fugly when viewed in less than A2007 b/c they use default colours... *humph*
How many people here knew they could set recordsets directly to listbox?
I didn't but it certainly was possible. Works nicely without all monkeying of making a subform look & act like a listbox, too...
The code is significantly shorter and easier to manage than the old mutli-select codes commonly found, I'd think...
Note: lstSelection queries a lookup table, while lstSelected is the junction table for many-many relationship, Key1 being the primary key of the form's recordsource and key2 being the key from the lookup table.
Code:
Private sqdf As DAO.QueryDef
Private srst As DAO.Recordset
Private Sub Form_Current()
Set sqdf = CurrentDb.QueryDefs("querywithaparameter")
With sqdf
.Parameters("Key1") = Me.Key1
Set srst = .OpenRecordset(dbOpenDynaset)
End With
Set Me.lstSelected.Recordset = srst
End Sub
Private Sub Form_Unload(Cancel As Integer)
Set srst = Nothing
Set sqdf = Nothing
End Sub
Private Sub lstSelected_DblClick(Cancel As Integer)
Dim var As Variant
For Each var In Me.lstSelected.ItemsSelected
With srst
.FindFirst ("Key1=" & Me.Key1 & " AND Key2=" & Me.lstSelected.ItemData(var))
.Delete
End With
Next var
Set Me.lstSelected.Recordset = srst
End Sub
Private Sub lstSelection_DblClick(Cancel As Integer)
Dim var As Variant
For Each var In Me.lstSelection.ItemsSelected
With srst
.AddNew
.Fields("Key1") = Me.Key1
.Fields("Key2") = Me.lstSelection.ItemData(var)
.Update
End With
Next var
Set Me.lstSelected.Recordset = srst
End Sub
Note no requery is ever issued, except for moving between the form's record which changes the parameter of the query. A good thing™ if I say so myself.
Access 2002 introduced recordset binding for the native list controls.
An excellent addition it was too.
(My website is still down at the moment - so I can't point you to any different examples per se).
Another example is for those who like navigating a form via a combo (like the default wizard generated Bookmark stuff). With Access you load the data twice - once for the form - once for the combo. Alternatively - assign one recordset to both. Efficiency abounds.
(I dislike forms like that for the most part though anyway ;-)
Equally you can work with a list control's recordset object as already assigned by default Access binding (as per a recent question at Utter Access - see attached. An example of efficient filtering without issueing query requests to the database again for each filter).
