F5JD wrote:
[color=blue]
> I must clarify a point. I have one form and only one subform. It could
> have more. I say that for simplification. The subform (datasheet) has
> only one control. It could have more. This control is a combo box. If
> the club displayed has 3 members, we have 3 lines with name plus the
> line with the asterisk.
>
> What I want to see : when you click on the combobox to add a member or
> replace, I want the drop down list show only names of persons who are
> not members of that club.
>
> Suppose we have a total of 7 persons. John, Laureen, Luc, Marc, Peter,
> Sonia, Vanda.
>
> 4 clubs : Chess, Foot-Ball, Ski, Parachuting
>
> Luc, Marc and Vanda are members of the foot-ball club.
>
> When the form displays foot-ball, the subform displays (vertically) Luc,
> Marc, Vanda, *.
>
> If I click one of the combobox, I want to the dropdown list show only
> John, Laureen, Peter, Sonia. Now, clicking one of these must update
> ClubsMembers, the table joining Clubs and perons.
>
> Maybe it cannot be done with Access. I have many books here, never found
> an example, even close to that.
>
> Sorry for this long post. English is not my first language.
>
> I'll watch your answer. My email is
f5jd@tele2.fr
>
> Regards
>
> *** Sent via Developersdex
http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it![/color]
I WOULD NOT do your way. Why? Because your combo box rowsource would cause
problems.
Let's say your subform has a combo box. The combo box displays names that
ARE NOT IN THE CLUB. Now how would your names that are IN the club be
displayed in existing record?
Next, you are making this more difficult on yourself then necessary. There
are many ways to approach this problem. Here is one concept.
In your main form have 2 combos and 1 subform.
The first combo would select a club. The second combo (actualy a listbox
would be better) would contain a list of all people NOT IN the club. The
subform would contain a list of records for that club.
When a club is selected, it would requery the subform and combo (or listbox)
for persons not in that club. The subform WOULD NOT contain a combo. It
would display the person's name instead.
This is the recordsource for your subform
SELECT Person.PersonID, Person.PersonName
FROM Person
WHERE Person.PersonID Not In (Select PersonID From ClubsPersons Where
ClubID = [Forms]![MainForm]![ComboClubs])
ORDER BY Person.PersonName;
This is the rowsource for your combo (or listbox)
SELECT Person.PersonID, Person.PersonName
FROM Person
WHERE Person.PersonID Not In (Select PersonID From ClubsPersons Where
ClubID = [Forms]![MainForm]![ComboClubs])
ORDER BY Person.PersonName;
If I had a listbox, I would probably have an event on the OnDblClick to add
it. (I us DAO) Ex:
Sub ListBox_DblClick
If msgbox("Do you want to add this person to the list?",vbYesNo +
vbQuestion,"Confirm Add") = vbYes then
Dim rst as recordset
set rst = currentdb.openrecordset("ClubsPersons",dbopendynas et)
rst.AddNew
rst!ClubID = Me.ComboClubs
rst!PersonID = Me.ListBoxNotInClub
rst.Update
rst.close
set rst = Nothing
Forms!MainForm!SubForm.Form.Requery 'refresh your list
Me.ListBox.requery
endif
End Sub
You want to requery the combo/listbox when you have added to remove the name
from the list. I'm not sure if you can requery while it has the focus. You
may have to set focus to the club combo, do the requery, then set focus
back.
The way you have it set up right now, it sounds like the names are displayed
in a combo for those not in the club so those that are in the club wouldn't
display the name.
Good luck.
