Replace entered value of textbox in MS Access

Question

In an MS Access DB table, I have decimal field; let's say "Price". The user should be able to enter any of these options in a text box on the Form:

• If the user enters a decimal value -> it's the product price, so save it in DB
• If the user enters nothing -> this means the value should be saved as NULL
• If the user enters "*" text -> this means they can't get the price

The problem is with "*", since the field is decimal I can't store an asterisk because it's text, therefore I need to store -1 value instead. I tried to handle that in the Before_Update event of the text box, but it doesn't work.

Who can advise how to implement that?

Answer 1

First of all, I ALWAYS suggest people not use bound forms. Trying to undo a record in a bound form is always dicey. Unbind the form. That is, have no table as its recordsource. Once all the data is input by the user, have a "Submit" button that will run VBA to write the record to your data. If you do this, you can put anything you want in that Price box, and when you're writing the data to the table you can change it to whatever you want by using an If/Then/Else statement to write the value to that field.

Something sorta like this:

Dim DB as database
Dim rec as Recordset

Set DB = CurrentDB
Set rec = DB.OpenRecordset ("Select * from MyTable")

rec.AddNew
rec("MyField") = Me.SomeField
rec("MyField2") = Me.SomeOtherField
if Me.MyPriceField = "*" then
rec("PriceField") = -1
Else
rec("PriceField") = Me.MyPriceField
End If
rec.Update