Best setup for cascading combo boxes (1 Viewer)

[Leyton01]

I am seeking some advice on how best to set up some cascading combo boxes (or if this is exactly what I need to start with). (I have read the link to Cascading combo boxes examples but my needs go beyond those examples).

The scenario -

I have a form used for user entry which I want to guide users to keep standardised naming as best as possible.

I have a facility type (Hospital, Nursing home, Community, GP and other) of which I have lists for the first 3 categories. The last 2 categories I want users to be able to select from a previously created entry OR free text a new entry.

My current setup is that I have all the Facility Information in the one table -
tblFacilities with the fields [Facility Type] and [Facility Name] plus some other linked information.

I then have 2 combo boxes cmbCompletionLocation which has the 5 facility types and a cmbCompletionFacility which has the rowsource set to a query:

SELECT tblFacilities.ID, tblFacilities.[Facility Name], tblFacilities.[Facility Type] FROM tblFacilities WHERE (((tblFacilities.[Facility Type])=[Forms]![frmlDocumentLog]![cmbCompletionLocation])) ORDER BY tblFacilities.[Facility Name];

The cmbCompletionLocation has an AfterUpdate event to requery the cmbCompletionFacility.

This seems to work well as in when they select "Hospital" it filters out the second combo box to only list the hospitals, which is the designed behaviour.

But when it comes to having the ability for users to enter free text if they have the last 2 options (GP and other) the combo box is locked as it is "limited to list" due to the way it is set up with bound columns.

Ultimately I would like that if a user enters in a new Facility Name in the cmbCompletionFacility it both saves that to the current record (in the table tblDocumentLog) and creates that new record in the tblFacilities table so it can be used in the future.

Am I going about this the wrong way? Is what I am hoping to achieve too complex? Is my database design set up the best way to do this? I originally had the different facility types in their own tables but this created a level of complexity I was not comfortable with.

 

[Pat Hartman]

Personally, I don't allow users to enter values into combo lists on the fly. Way too many mistakes get made because they enter typos rather than really new items.

If you want to allow them to enter new values, you have to set the Limit to List property to no. You can try doing this in the AfterUpdate event of the controlling combo when they select a value where you want to allow this.

Since I also abhor unnecessary hard-codeing, if I were going to do this (which I wouldn't), I would add a column to the lookup list that specifies whether entries are allowed on the fly or not.

I wouldn't allow on the fly entries. It is highly unlikely that you have a situation where they are so frequent that they would disrupt the flow of data entry if the user had to stop and go to the list add form.

 

[Cronk]

Use the NotInList event of the combo.

Look up the syntax and examples of how you can prompt the user as to entering a new look up item or not, and adding the new entry to the table.

Come back if you need more help.

 

[Leyton01]

I agree that I hate to allow users to manually add things but unfortunately I only have lists that I can limit response to in the 3 categories - I cannot create or keep up to date the lists for the others.

Sorry I should have said that I tried setting the Limit to List to "No" but it was giving an error:
'Microsoft Access can't set the LimitToList property to No right now. The first visible column, which is determined by the ColumnWidths property, isn't equal to the bound column' which I tried to solve but realised that it wasn't easy and even then it would set it to yes for everything (unless I dynamically set it in vba)

It looks like the Not in List event is what I need - I'll fiddle around with that.

 

[Pat Hartman]

I told you how to set the property via code. Please reread my comment #2

As you can see by the message, you MUST make the first column the bound column to make limitToList work correctly. You can hide the first column, by setting its column width to 0. Of course, if you messed this up when you created the combo, you may have other code that needs to be modified.

When you reference the combo -

Me.MyCombo

You are referrring to the BOUND column. So if you then need to refer to the text column specifically you need to use -

Me.MyCombo.Column(?)

The question mark would be replaced by the actual column number. Remember the RowSource of a combo is a zero-based array so the first colum is .Column(0) and the second column is .Column(1), and the third column is .Column(2), etc.

 

[Leyton01]

Thank you all for the help - I was reluctant to set a property via code that I could not set via the GUI (in case it created an issue that the initial restriction aimed to avoid)

I managed to achieve this with the NotInList event. Pat's solution may also work but as I tested the NotInList first and it worked I did not go further.

Thanks all