Orthodox Dave
Home Developer
- Local time
- Today, 02:50
- Joined
- Apr 13, 2017
- Messages
- 218
In my databases in the past I always used "Real" fields as Primary Keys, but recently I decided (reluctantly) to convert to the arguably "more conventional" method of using meaningless autonumbers as Primary Keys - (but only when the Real key field is longer than, say, 5 digits). There's no Orphan problem if you use referential integrity properly (cascades etc). But I'm not here to rant - another time!
But these autonumber primaries have made life difficult in this situation:
I have a continuous forms subform with a bound combo box for Documents and 3 unbound combo boxes for Company, Discipline and Document Type, which I use to filter the Documents combo (otherwise its row list would be far too long).
In Access, the Rowsource property of a combo box not only controls what you can select, but also what is displayed (grrr). Also, when filtering the rowsource of a combo box in a continuous forms subform, if the bound column is not also the visible column, the filter only works for the first sub-record in the series and the combos in the remaining sub-records go blank. I don't remember reading that on the tin!
Even if you include the subrecord's ID in the rowsource (...OR cboDocID = ‘” & Me.DocID & “’ etc) it still only works for the current subrecord. That is not the case if the bound column is visible - I tested this (all other factors being equal) and then all records DID show (their meaningless numbers).
The Limit to List property could not be turned off either (greyed out), because to apply Limit to List, the bound column must be the visible column (for reasons best known to Microsoft).
Because the bound column of the combo is this meaningless autonumber, I have to display a user-friendly column (not the bound column). So my way round this was to apply the filter only at the time of selecting the Document combo - on Got Focus. Then the event sub checks if any or all of the 3 unbound combos is populated and if so, modifies the rowsource accordingly. Then on Lost Focus, the rowsource is returned to the way it was.
It does work, but it’s so clunky – surely there must be a better way. My tyrannically tidy mind hates inelegant solutions! But am I missing something obvious here?
But these autonumber primaries have made life difficult in this situation:
I have a continuous forms subform with a bound combo box for Documents and 3 unbound combo boxes for Company, Discipline and Document Type, which I use to filter the Documents combo (otherwise its row list would be far too long).
In Access, the Rowsource property of a combo box not only controls what you can select, but also what is displayed (grrr). Also, when filtering the rowsource of a combo box in a continuous forms subform, if the bound column is not also the visible column, the filter only works for the first sub-record in the series and the combos in the remaining sub-records go blank. I don't remember reading that on the tin!
Even if you include the subrecord's ID in the rowsource (...OR cboDocID = ‘” & Me.DocID & “’ etc) it still only works for the current subrecord. That is not the case if the bound column is visible - I tested this (all other factors being equal) and then all records DID show (their meaningless numbers).
The Limit to List property could not be turned off either (greyed out), because to apply Limit to List, the bound column must be the visible column (for reasons best known to Microsoft).
Because the bound column of the combo is this meaningless autonumber, I have to display a user-friendly column (not the bound column). So my way round this was to apply the filter only at the time of selecting the Document combo - on Got Focus. Then the event sub checks if any or all of the 3 unbound combos is populated and if so, modifies the rowsource accordingly. Then on Lost Focus, the rowsource is returned to the way it was.
It does work, but it’s so clunky – surely there must be a better way. My tyrannically tidy mind hates inelegant solutions! But am I missing something obvious here?