Using BeforeUpdate on an unbound text box

  • Thread starter Thread starter Guest
  • Start date Start date
Would you be able to provide more information or direction on the two methods
that you describe below?

Thanks,
KB

Klatuu said:
The OldValue property does not work for unbound controls. There are a few
ways you can accomplish this. One would be to use the Tag property of the
control, another would be to create your own property and use that. Also,
the Undo method does not apply to unbound controls.

KellyB said:
I am building a form for the user to create a new record. Because the data
that is entered is for several different tables, none of the fields are
bound. Some of the fields are loaded with default values when the form is
opened (e.g. the IDnumber field is loaded with the next available ID number).
Once the data is entered and the user clicks the 'confirm' button, then I've
got code to parse the data to the various tables.

Now, I want to warn (and prevent) the user from creating a duplicate entry
in one of the tables.

I copied the following code from the Access Help documentation:

Private Sub ProductName_BeforeUpdate(Cancel As Integer)
If(Not IsNull(DLookup("[ProductName]", _
"Products", "[ProductName] ='" _
& Me!ProductName & "'"))) Then
MsgBox "Product has already been entered in the database."
Cancel = True
Me!ProductName.Undo
End If
End Sub

I used this exact same code (just changed the field and table names). This
works to catch the duplicate entry, but:

The Undo statement does not appear to work. Is this because the txt box is
unbound? If this is the case, how could I modify the code to revert back to
the old value?

Thanks for your help,
KB
Click to expand...
Click to expand...
Click to expand...
 
I did as instructed, and I get an error for the line:
Me!ProductName = Me.ProductName.Tag

The error message is
"Run-time error '2115'
The macro or function set to the BeforeUpdate or ValidationRule property for
this field is preventing Microsoft Office Access from saving the data in the
field."

Any thoughts?
KB

Klatuu said:
The easiest would be to use the Tag property.
When you load data from the tables into your form, put a line in that will
put the value into the control's tag property:
Me.ProductName.Tag = YourRecordset![PRODUCT_NAME]

Then change the code below:

Private Sub ProductName_BeforeUpdate(Cancel As Integer)
If(Not IsNull(DLookup("[ProductName]", _
"Products", "[ProductName] ='" _
& Me!ProductName & "'"))) Then
MsgBox "Product has already been entered in the database."
Cancel = True
Me!ProductName = Me.ProductName.Tag
End If

KellyB said:
Would you be able to provide more information or direction on the two methods
that you describe below?

Thanks,
KB

Klatuu said:
The OldValue property does not work for unbound controls. There are a few
ways you can accomplish this. One would be to use the Tag property of the
control, another would be to create your own property and use that. Also,
the Undo method does not apply to unbound controls.

:

I am building a form for the user to create a new record. Because the data
that is entered is for several different tables, none of the fields are
bound. Some of the fields are loaded with default values when the form is
opened (e.g. the IDnumber field is loaded with the next available ID number).
Once the data is entered and the user clicks the 'confirm' button, then I've
got code to parse the data to the various tables.

Now, I want to warn (and prevent) the user from creating a duplicate entry
in one of the tables.

I copied the following code from the Access Help documentation:

Private Sub ProductName_BeforeUpdate(Cancel As Integer)
If(Not IsNull(DLookup("[ProductName]", _
"Products", "[ProductName] ='" _
& Me!ProductName & "'"))) Then
MsgBox "Product has already been entered in the database."
Cancel = True
Me!ProductName.Undo
End If
End Sub

I used this exact same code (just changed the field and table names). This
works to catch the duplicate entry, but:

The Undo statement does not appear to work. Is this because the txt box is
unbound? If this is the case, how could I modify the code to revert back to
the old value?

Thanks for your help,
KB
Click to expand...
Click to expand...
Click to expand...
 
I've used the brute force method:
SendKeys "{ESC}"

Seems to work,
KB


Klatuu said:
The easiest would be to use the Tag property.
When you load data from the tables into your form, put a line in that will
put the value into the control's tag property:
Me.ProductName.Tag = YourRecordset![PRODUCT_NAME]

Then change the code below:

Private Sub ProductName_BeforeUpdate(Cancel As Integer)
If(Not IsNull(DLookup("[ProductName]", _
"Products", "[ProductName] ='" _
& Me!ProductName & "'"))) Then
MsgBox "Product has already been entered in the database."
Cancel = True
Me!ProductName = Me.ProductName.Tag
End If

KellyB said:
Would you be able to provide more information or direction on the two methods
that you describe below?

Thanks,
KB

Klatuu said:
The OldValue property does not work for unbound controls. There are a few
ways you can accomplish this. One would be to use the Tag property of the
control, another would be to create your own property and use that. Also,
the Undo method does not apply to unbound controls.

:

I am building a form for the user to create a new record. Because the data
that is entered is for several different tables, none of the fields are
bound. Some of the fields are loaded with default values when the form is
opened (e.g. the IDnumber field is loaded with the next available ID number).
Once the data is entered and the user clicks the 'confirm' button, then I've
got code to parse the data to the various tables.

Now, I want to warn (and prevent) the user from creating a duplicate entry
in one of the tables.

I copied the following code from the Access Help documentation:

Private Sub ProductName_BeforeUpdate(Cancel As Integer)
If(Not IsNull(DLookup("[ProductName]", _
"Products", "[ProductName] ='" _
& Me!ProductName & "'"))) Then
MsgBox "Product has already been entered in the database."
Cancel = True
Me!ProductName.Undo
End If
End Sub

I used this exact same code (just changed the field and table names). This
works to catch the duplicate entry, but:

The Undo statement does not appear to work. Is this because the txt box is
unbound? If this is the case, how could I modify the code to revert back to
the old value?

Thanks for your help,
KB
Click to expand...
Click to expand...
Click to expand...
 
Is this officially an Access bug? There is nothing in the help files (at
least, not in mine) to say that .Undo only works for Bound Controls.

I can confirm the OP's finding that calling .Undo on a changed unbound
textox does not raise an error but it does not change the contents either.

It appears that DoCmd.RunCommand acCmdUndo won't do either -- raises a "Not
available at this time" error.

Hmmmm


Tim F
 
Back
Top