Cascading combobox error and related ???'s to compact & repair

[DevTycoon]

I have cboCombo1 that cascades to cboCombo2 wich then cascades approved values to cboCascade3.

The database is used by at most 3 people at one time and is located on a shared drive. I have recently got complaints that the cboCombo3 triggers an error AFTER a selection is made. (This means that the me.requery from cboCombo1 and cboCombo2 are not triggering this error.) The user complaint did not provide the error code. Sorry for not posting that. All i know is after the user acknowledges the error by hitting end or debug the record value selected is still committed to the table. Should I just make error handling code for this control that forces resume on error; that just seem sloppy.

After running a compact / repair operation the error is not duplicable but then will surface after a few more hours of database usage.


Is my if statement making any sense? I need to make sure the form saves so a calculated subform will update. That is the only reason why i included the if me.dirty in the update code. Is there a better way to update the subform containing calculations instead of making other form controls constantly save if they are dirty?


Code error:

Private sub cboCombo3 _afterupdate()

If me.dirty then docmd.save  ' calculated sub form needs this code so it will update
docmd.requery "[cboCombo1]"  ' i read that requerying the Parent combo boxes is neccesary for the cascade to work
docmd.requery "[cboCombo2]"
end if
end sub

Why does compacting and repair only solve problem for short duration of time?

Would this be avoided by splitting the database?

Why would only one user experience this error if more than one person is accessing the file at once?

I sincerely appreciate your feedback,

DevTK

 

[vbaInet]

I'll mention a couple of things:

1. It will definitely be sloppy programming, as you rightly said, if you don't deal with the problem.
2. We need to know exactly what the error message is.
3. For cascading combo boxes, you only need to requery the children, not the parents. Perhaps this could be the culprit!
4. DoCmd.Save doesn't save a record it saves design changes. You want:

If Me.Dirty Then Me.Dirty = False

5. Why do you even need to force save?
6. Your users should not even be able to click Debug in the first place. If your db is properly split, your forms, reports and code should be locked down.

I think I've covered everything.

 

[DevTycoon]

I'll mention a couple of things:

1. It will definitely be sloppy programming, as you rightly said, if you don't deal with the problem.
2. We need to know exactly what the error message is.
3. For cascading combo boxes, you only need to requery the children, not the parents. Perhaps this could be the culprit!
4. DoCmd.Save doesn't save a record it saves design changes. You want:

If Me.Dirty Then Me.Dirty = False

5. Why do you even need to force save?
6. Your users should not even be able to click Debug in the first place. If your db is properly split, your forms, reports and code should be locked down.

I think I've covered everything.

I will get the error message and test the code changes you recommended. Does it make sense that compact and repair would solve the issue...just for a small time frame?

 

[vbaInet]

Does it make sense that compact and repair would solve the issue...just for a small time frame?

No! It's a design flaw as already pointed out.