How to set the value of an unbound textbox with a default value to an empty string with Access VBA

1
votes

I have a database with information about visitors to our department. Here is my setup: A user selects a visitor name from a listbox. Access assigns the values from that visitor's record to temporary variables and opens an unbound form where those variables are the default values in the text field. When a user changes a value in a textbox, an After_Update event uses SQL to update the corresponding field in that visitor's record and assigns the new value to the temporary variable.

After_Update example:

Dim strRUN As String
strRUN = updateVisitorOnDirty(Me.txtVisitorTravelMethod, "VisitorTravelMethod", 1)
HideWarnings (strRUN)
TempVars!strVisitorTravelMethod = Nz(Me.txtVisitorTravelMethod.Value, "")

"updateVisitorOnDirty" is a function that returns SQL to update a field (specified in second argument) in the table with visitor information with the first argument. (The number at the end is says it's a string.) "HideWarnings" turns warnings off, executes the SQL, and then turns warnings back on.

My problem: When a user clears a textbox, the After_Update event makes the textbox revert to it's previous value.

I thought at first that maybe the unbound textbox was reverting to its default value when it was cleared, but I put a breakpoint in the sub that fires during After_Update:

If I change a value in mytextbox from a to b, debug.print shows the value of mytextbox as b. If I change a value in mytextbox from a to (blank, or empty string), debug.print shows the value of mytextbox to still equal a.

My workaround has been to include a little "x" near the textbox that the user can click to 1)clear the textbox, 2)update the temporary variable, and 3)update the table. It works fine, but it seems like there should be a better way. Help?

Please let me know if you need any other information or if I should otherwise re-word this. I come here all the time for answers, but this is the first time I've actually submitted my own question. Thank you!

(I edited this to make the code show up correctly...didn't leave a line before.)

2
ms-accessvbams-access-2010

2 Answers

1
votes

I figured out that the unbound textboxes were reverting to their default value when a user tried to make them blank. To stop them from doing this, I put this in the AfterUpdate of the textboxes:

Call ResetDefault(Me.textbox1.Text, Me.textbox1)

And defined the function:

Public Sub ResetDefault(ByVal strChange As String, ByRef txtCurrent As TextBox)
If Nz(strChange, "") = "" Then txtCurrent.DefaultValue = ""
End Sub

That is, if a user cleared a textbox, the default value of that textbox was set to "", allowing the textbox to actually go blank.

0
votes

I think the best approach is to scrap the after_update event on your text boxes and instead either put a save button on your unbound form so that all updates are written in one pass, or capture the forms closing event and update your table then.

This is the advantage of using unbound forms, you do not have to comit data until you are absolutely ready to, but with using an after_update event you are almost mimic-ing the behaviour of a bound form.