Problems with Cascading Combo Box

[Steff]

I have created a database with a cascading combo box in three forms. However, it actually works, but still gives the following error from time to time (translated from Germ. to English):

You entered the expression After Update as the event property setting. This expression caused an error: Module not found. The expression may not return the name of a macro, user-defined function name, or [event procedure]. An error may have occurred while evaluating a function of a result or a marker.



I used the following code to update the cascading boxes:
Private Sub Level_1_AfterUpdate()
Me.Level_2.Requery
End Sub
Private Sub Level_2_AfterUpdate()
Me.Level_3.Requery
End Sub
Private Sub Level_3_AfterUpdate()
Me.Level_4.Requery
End Sub
...and the same for all the other levels (four levels in total).


Does anyone know, why I get this issue and how I can solve it? Does it have to do with the updating? I'm quite new to access and usually only work with R or Matlab...




The file is 500kb to large for an upload, so I'll use this dropbox link, if this is ok? I tried to delete some tables and forms but the .accdb file it didn't get smaller :/
https://www.dropbox.com/scl/fi/pvz1e...d8fr272fs&dl=0

[June7]

Should be able to attach up to 2mb zip file. Use Windows compression and I will be able to extract.

Be sure to run Compact & Repair first.

Many will not download from external location.

[Steff]

Thank you so much! Should be attached now as .zip and I did compress and repair beforehand!

[Micron]

I tried compact/repair and several other things before importing all your objects into another db, figuring the db was corrupt but no difference. So I copied the rowsource of Level 1 from Formular Hämatologische Tumore then deleted the combo. Then added a new combo, pasted in the row source, reselected the bound field and problem gone. All I can conclude is that you had one corrupt combo which you copied everywhere, either individually or by way of copying forms.

Spaces and special characters in object names (save for underscore) is not advised.

[June7]

Why not use better field names than Feld1, Feld2, Feld3, Feld4?

So you want to save each combobox selection instead of just the record ID of the lookup table. That is duplication of data.

Since form RecordSource is an unfiltered SQL and not joining tables, could just reference table.

To echo Micron, strongly advise not to use spaces nor punctuation/special characters in naming convention. I always name controls different from fields or objects, like cbxLevel1. Have dependent comboboxes reference combobox name, not field. Since comboboxes are on same form, should not need fully qualified reference (this is an advantage if form is used as subform). ORDER BY is not needed when using DISTINCT - it's redundant. Should be able to simplify the SQL statements.

SELECT DISTINCT Feld1 FROM [Häm ICCC-3 und ICD-O];
SELECT DISTINCT Feld2 FROM [Häm ICCC-3 und ICD-O] WHERE Feld1=[cbxLevel1];
SELECT DISTINCT Feld3 FROM [Häm ICCC-3 und ICD-O] WHERE Feld2=[cbxLevel2];
SELECT DISTINCT Feld4 FROM [Häm ICCC-3 und ICD-O] WHERE Feld3=[cbxLevel3];

However, your db is not liking my changes. At one point I deleted and recreated the first combobox and did not get the error but now it is back. And it can't find the comboboxes with new name nor the old one when I changed back.

Same assessment as Micron - something is corrupted. Could be just a combobox or the form - rebuild, starting with comboboxes and if needed, the forms.

[Steff]

Thanks a lot! This is actually already a full recreated version, since I had the issue before :/ However, I guess I start again and will not use spaces and see if it then works. The error is weird though: If I open another form next to the one that suddenly has an issue and then use the combo boxes of the other form, the error actually disappears and the combo boxes all work again (how?).

Also tried deleting the first level combo, but yes, error reappeared. I also intentionally didn't copy any forms or boxes in this db (which I did the first time I made this db and had the same error, but the first time the error didn't go away when opening another form...). I will try to rebuild the form tomorrow and see what happens (will then build the whole db again, if necessary).

Thanks a lot again!!

[June7]

No spaces or punctuation/special characters - that means no hyphens, slashes, octothorps, parens, colons, etc.

[Micron]

... and use a naming convention to make things easier to follow for everyone, including yourself.
Naming conventions
- http://access.mvps.org/access/general/gen0012.htm
- https://www.access-programmers.co.uk...d.php?t=225837

What not to use in names
- http://allenbrowne.com/AppIssueBadWord.html

[Steff]

Thanks so much!! Will do!!