Populating a combobox

  • Thread starter Thread starter David Wren
  • Start date Start date
D

David Wren

I am writing a database for a caravan owners club that runs caravan rallies.

The database at present has three tables - Members, Rallies and a Junction
table that links members to rallies. I have a form where the rally is
selected which has a subform, linked to the RallyID field, where members
names are selected via a combobox. This all works fine but my problem is
that I want to populate the combobox with only the member's names that are
not already added so there can be no duplication. I am tying the combobox
to a query but I can't fathom a way of constructing the query to give the
correct results.

If anyone has a solution I would be very pleased to hear.

David
 
Dave

I hope that this clarifies things.

The form selects the rally and the subform has, at present, a single
combobox to select the attending members names.

Let's say that you have three members Andy, Bertie & Charlie in the members
table and that you have just added a new rally via the form and want to add
Andy as an attending member. The combobox would give the option of
selecting from all three names, but when at a later date you want to add
someone else the combobox only gives the option of selecting Bertie &
Charlie as of course Andy is already booked onto the rally.

Hope this makes it clear and thanks for you interest.

David
 
Hi David -
I'm assuming that your combo-box pulls the data from a
table. If so, you will need to add the "new attendees" to
that source table. Once completed, all persons should now
show up in your combo-box.

Hope this helps.

~April
 
David,
Use the following SQL as the rowsource of the combobox:
SELECT Members.Membername FROM Members WHERE
Members.MemberID Not In (Select Junctiontable.FK_MemberID
FROM Junctiontable where Junctiontable.FK_RallyID=RallyID

The whole of the Select statement needs to be on one line
before you paste it into the rowsource property.
You will need to replace the table names (Members and
Junctiontable above) with your table names and also the
field names (Membername, FK_MemberID and FK_RallyID above)
with your field names. Also, you need to pass the RAllyID
to the query, so replace RallyID above with whateever
holds this in the form.

If you need further help, email me.

Dave
 
Thanks for that Dave. I replaced the RallyID with a reference to my form
textbox and added a closing bracket and it works a treat also I have learnt
a valuable lesson in SQL. As always with me and Access I have now arrived
at the next hurdle - but I'm working on it.

Thanks again

David
 
Back
Top