Error By Manually Changing Dates in Textbox (OnChange)

[ohmydatabase]

Hey,

So I'm working on a form that aims to automatically display records based on the values of two textboxes. The textboxes are defined as dates, so the little calendar icon will pop up when you click into it. Some users prefer to type their date rather than use the calendar.

Here's where I go wrong. I have an Event Procedure OnChange that defines and SQL Query and defines the "date range" of the SQL Query using the values in the textbox. When the user deletes the current value in textbox, this causes errors when the code automatically tries to run this SQL Query. For example: the default value of the textbox is Date(). The user wishes to change the day so they naturally click into the day portion of the date and hit backspace to clear the day. When they clear the day, the textbox reads "01//2017" (or something similar). When the SQL Query attempts to open a recordset using that date range I get a syntax error because #01//2017# is not a valid date.

Is there a better way to do this and am I approaching this problem in the right way? I've looked into validation rules/text, but I don't have a strong grip on it. So I figured I'd post here to see if anyone had some insight.

Thank you.

[pbaldy]

Use the after update event rather than the change event, which fires with every keystroke.

[ohmydatabase]

Thank you! I'll try this out.
Does the on update event trigger upon the user pressing Enter or anything like that? I guess my real question is what is an "update"?

[aytee111]

"Update" would happen after something has been typed in AND after it has been given to Access, i.e. moving out of that field by clicking or pressing enter/tab. Purely typing in a textbox is a visual thing, you see it but Access does not. Once you get out of that control (textbox or ...) then the information is given to Access - that is when the Update event is fired. (NOTE: it is not the underlying record on the table that has been updated, only the temporary space that Access uses for each individual field on the form. It is the form's AfterUpdate that happens after the table has been updated.)