CODEWITHSUNDEEP
ms-accessvba
Sergio
Sergio

Reputation: 109

Converting in VBA Access

I have a question with my converting in VBA. I made a button in a form and when I press it, it should make an update in my database table, but I get an ERROR:

arithmetic overflow error converting numeric to data type numeric

How can solve this problem? Here is my code:

Private Sub Inserare_Click()
  Dim strSQL As String
  Dim rst As New ADODB.Recordset

  strSQL = "UPDATE dbo.Soferi_test SET number= '" &  
           Me![subform_soferi].Form!certificat_cipti & _
           "',drivers= '" & Me![subform_soferi].Form!categorii_permis & _
           "' WHERE IDNO = " & Forms!Forma_redactare_soferi!IDNO

  Call AttServ(strSQL, rst)
  MsgBox ("Datele au fost introduse cu success")
End Sub

Upvotes: 1

Views: 175

Answers (2)

HansUp
HansUp

Reputation: 97101

number is a reserved word. Bracket that name in your UPDATE statement so the db engine knows it's supposed to be a field name.

strSQL = "UPDATE dbo.Soferi_test SET [number]= '" &

If that change doesn't resolve the problem, inspect the completed SQL statement. Insert this line after you store the string to strSQL.

Debug.Print strSQL

You can view that output in the Immediate window (go there with Ctrl+g). You can then copy that statement text into SQL View of a new Access query for testing. Add the statement to your question if you can't get it to work properly and tell us whether you get the same or different error message.

It may also help to show us the code from your AttServ procedure which uses strSQL.

Upvotes: 1

Barranka
Barranka

Reputation: 21047

I think the problem is in the field type of the table you are trying to update.

Example: If you try to set the value of a small integer field to a long integer, value, Access will throw an error.

Check the type of your fields and set them to the appropriate type.

Other think that can be wrong is the value of the text boxes. Just to be sure, try to convert the values.

Example: Cdbl(Me![subform_soferi].Form!certificat_cipti) will convert the string in your text field to a double value.

Also, I recommend you use the value attribute of your text boxes: 

Me![subform_soferi].Form!certificat_cipti.Value

or

CDbl(Me![subform_soferi].Form!certificat_cipti.Value)

Notice that if you use the "conversion" functions (CDbl, CInt, CDate, etc.) an error will be thrown if the conversion fails (for example, trying to convert 12abc to an integer will fail)

Upvotes: 0

Related Questions