CODEWITHSUNDEEP
sqlms-accessms-access-2007ms-access-2010
ihorko
ihorko

Reputation: 6945

Replace entered value of textbox in MS Access

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:

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?

Upvotes: 0

Views: 1884

Answers (2)

Johnny Bones
Johnny Bones

Reputation: 8402

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

Upvotes: 2

4dmonster
4dmonster

Reputation: 3031

Try to use OnChange event 

if nz(Me.CrazyTextBox.Text , "") = "*" then
    Me.CrazyTextBox.Text = -1
end if

And think about setting Format string for this crazy text box to something like 

# ##0.00;\*;0;"user entered nothing"

Upvotes: -1

Related Questions