Best setup for cascading combo boxes

[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.

 

[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.

 

[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