VBA, Decimal Data Type and MySql

[Acropolis]

Hi All,


I am having some problems getting VBA to create an entry into a MySQL backend.


The value I want to enter is from a text box on the form, say with the value of 0.5, and going into a MySQL DB, with the data type of Decimal - Length 5,2


I keep getting runtime error 3759 - Data truncated error message coming up.


I have tried everything I can think of to get it over, and spent a couple of hours yesterday afternoon googling for help but nothing found worked.


I have tried CDec, CDbl, Single, Double everything I can think of.


How can something that should be so simple prove to be so hard


Any help greatly appreciated.

 

[pr2-eugin]

Have you tried using,

FormatNumber(0.5, 2)

 

[Acropolis]

I had tried something similar before and no joy, just tried now and still the same thing.

 

[pr2-eugin]

Is it still the same error? Or something else?

 

[Acropolis]

Same error

 

[pr2-eugin]

Are you sure the error is happening here? Can you show the Query you are using?

 

[Acropolis]

    Set db = CurrentDb
    Set rsOsirisAdd = db.OpenRecordset(Name:="meter_new", Options:=dbSeeChanges)
    UOM = FormatNumber(Me.txtUOM.Value, 2)
    Debug.Print UOM
    With rsOsirisAdd
        .AddNew
        ![SerialNumber] = Me.txtSerial
        ![MeterTypeID] = 1
        ![OboxID] = 1
        ![OboxPort] = "P1"
        ![UOM] = UOM
        ![MeterDiameter] = MeterSize
        ![BillMeterID] = 0
        ![UtilityID] = Me.cboUtility
        ![Description] = "SOMEWHERE"
        ![Created] = Now
        ![Modified] = Now
        ![MeterLocation] = Null
        .Update
        .Bookmark = .LastModified
        MeterID = rsOsirisAdd("ID")
        .Close
    End With
    Set rsOsirisAdd = Nothing

that is with Dim UOM as Double


If I try it as Integer, then I don't get an error message on this part and it changes the value to 0.


When the error pops up, clicking Debug takes me to the line ![UOM] = UOM which is where I am getting the issue, as it is this that is the decimal.