Combobox on continuous form only showing first record (1 Viewer)

[George-Bowyer]

Hello,

I have a db that stores information about clubs and people who are members and officers of those clubs.

People can apply to go on courses and in order to add someone to the relevant course records I have form where the user can pick from the entire list of contacts nationwide in a subform (in continuous forms format) that shows names and addresses. That's all fine.

However, because there can be several John Smiths and the address does not make it obvious, I am trying to put a combo-box in the form which shows which positions they hold in which clubs. (People can hold more then one position in more than one club or no positions in any club).

So when they search for John Smith and find two living in Northampton, they can click the dropdown and say "Ah, yes, I want the one that's Chairman of Northampton North, not the one who's Secretary of Northampton South".

I have created a combobox based on a query that looks for positions held by [fldContatctID] in the form but when I use the form in a subform in continuous forms format, it shows every record holding the positions of the first record on the list. What I want it to do, obviously, is show the positions of the person on that line only.

I suspect I am making some very basic mistake - but I can't work out where it is.

Many thanks,

George

 

[theDBguy]

Sounds like you made the combobox unbound. If you go to the fifth record and change the combo, does it also change it for all the others?

Sent from phone...

 

[June7]

Could you provide your db for analysis? Follow instructions at bottom of my post.

 

[Mike Krailo]

So when they search for John Smith and find two living in Northampton, they can click the dropdown and say "Ah, yes, I want the one that's Chairman of Northampton North, not the one who's Secretary of Northampton South".

Could you possibly display the position in the controltip text? Just set the value in the on current event. Hovering the mouse over last name field shows the position.

Maybe you can show the query you are using. Usually a hidden control on the main form is used to update it with the current ID of the selected record. Something like CurrID would work. Just update it in the current event and reference that field in your query.

 

[George-Bowyer]

Sounds like you made the combobox unbound. If you go to the fifth record and change the combo, does it also change it for all the others?

Sent from phone...

Yes, the combobox is unbound.

It's been a while since I did any db design work and I'm very rusty. I wouldn't be at all surprised if I shouldn't be trying to use a combobox at all.

I don't want the combobox (or whatever) to be able change anything - it needs to be read-only. Changing positions happens on a different form

This part of the set-up is not that complex (or, at least, I don't think so, in theory...)

The tblContacts table has a fldContactID and a load of name, address and other contact fields. (Yes, I know, it's not fully normalised).

In the tblOrganisationPositions table each record has a fldContactID from tblContacts, a fldClubID from tblClubs and a fldPositionID from tblPositions (plus a few other fields about dates, notes, etc).

The subform I am talking about is based on a simple query on tblContacts, with a line for each contact. When a line is selected in the subform, the relevant fldContactID goes to a txtContactID control in the form header for use in code when a button is clicked after the relevant record is selected.

I want to put a dropdown box in the line for each contact showing what records (club and position) show up when you search tblOrganisationPositions for that fldContactID.

I assumed that a combobox was the correct vehicle, but I may well be wrong.

 

[June7]

Sounds like you are talking about a cascading/conditional combobox. This does require a bit of VBA. And they don't work very nicely on continuous or datasheet form. Review https://stackoverflow.com/questions...ous-form-the-second-combo-doesnt-show-its-val

Could have one combobox to show contacts and positions. It just means if a contact has multiple positions, they would be listed more than once.

If this combobox is intended to be used to apply filter, what code have you associated with it?

If you just want to display the selected contact's positions, an unbound listbox is good for that. Also needs a little VBA to work.

 

[Mike Krailo]

If you just need to differentiate names by position, then i would try and simplify it using position codes and display that extra info on a dynamic label on the main form. A sample database would make it easier to demo. At least give us the tables involved. I don't like the combo box because you just need to tell which Smith has which position.

 

[George-Bowyer]

Hi, thanks for your answers.

It's really difficult to include an example database, because this one has more than 50 tables with numerous relationships and by the time I have removed all the confidential information, it won't make any sense at all.

This is the form, I'm talking about.

It's a simple continuous form based on a simple query on the contacts table sorted into alphabetical order.

The text box in the form header draws the fldContactID from the selected line in the table.

The combobox not bound to any field in the main query. It is supposed to show the results of a search on tblOrganisationPositions for Club and Position info for all positions held by the fldContacID of the person on that line.

The combobox has a club (red) and a position (green). It is not intended to do anything or to be edited. I just want to be able to click the dropdown arrow and see which positions each person holds.

The problem is that the clubs and positions shown are all for the first name in the source query, instead of different positions for each person. In the photo above, I have clicked the dropdown line for A. Smith, but I am seeing the positions held by Aaron A. Aardvark.

 

[Gasman]

Do not remove the confidential data. Convert it so it is obfuscated

How to arrange order of records - Page 2

Hi, I've got a tricky to explain problem, so I'll try to explain with an example. Basically, I have a payments table, with various payment types (fee, porterage, travel, early start etc). What I want to do eventually, is to create separate remittance advice reports for each supplier...

www.accessforums.net

 

[Mike Krailo]

It appears you have a many people to many positions relationship going on there. So you should have a junction table connecting the two. Is that correct? If so, this should be relatively simple to look at only smiths that are in a particular position group to begin with using a main Position form that only shows people in the selected position. If you don't have that table structure, then that's what I recommend you do.

 

[Pat Hartman]

If the combo boxes are not bound, all rows will show the identical value. You probably need to change the row source of the combo to include a Where clause that selects the value for the active record. You will need to requery the combos in the click event of the person row.