Converting in VBA Access

Question

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

Can you simplify the example - what type are certificat_cipti, categorii_permis etc? — John Sibly
– John Sibly, Commented May 3, 2013 at 8:41

HansUp · Accepted Answer · 2013-05-03 18:53:36Z

1

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.

answered May 3, 2013 at 18:53

HansUp

97.3k11 gold badges80 silver badges139 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Barranka · Accepted Answer · 2013-05-03 18:21:16Z

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)

Collectives™ on Stack Overflow

Converting in VBA Access

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related