I have an unbound form that is used as a container to collect user input (names, group and dates) through combos related to three different tables and then to process a number of SQLs statements to add records to yet another table. This is to be distributed to non-expert users. In order to avoid processing before all the data has been entered correctly, I have locked controls and then progressively unlocked them as the user works through the form.
All works OK, except that....
I am expecting that the user will want to process several 'names' one after the other, so at the final stage I want to reset the form back to its initial state. However when the code re-runs the routine to lock the controls it returns error 2166, 'control cannot be locked whilst there are unsaved changes'.
What i think is happening (but correct me if you know different) is that because the form is unbound and the processing is a piece of SQL, the information is still being held in each of the controls and is not being be 'cleared' or 'saved' in the normal way by moving to a new record as would happen on bound form.
I have tried resetting the controls to Null, Nothing, Empty and "" immediately before locking without success - also 'docmd.runcomma nd accmdsave' and refresh.
Any ideas about how to reset this unbound form back to its initial state programmaticall y without the bother of closing it and re-opening it each time?
All works OK, except that....
I am expecting that the user will want to process several 'names' one after the other, so at the final stage I want to reset the form back to its initial state. However when the code re-runs the routine to lock the controls it returns error 2166, 'control cannot be locked whilst there are unsaved changes'.
What i think is happening (but correct me if you know different) is that because the form is unbound and the processing is a piece of SQL, the information is still being held in each of the controls and is not being be 'cleared' or 'saved' in the normal way by moving to a new record as would happen on bound form.
I have tried resetting the controls to Null, Nothing, Empty and "" immediately before locking without success - also 'docmd.runcomma nd accmdsave' and refresh.
Any ideas about how to reset this unbound form back to its initial state programmaticall y without the bother of closing it and re-opening it each time?