asked on

ODBC error message on a form Button in Access

Hi Experts,
I'm having an ODBC failed error message when to run an update in a form. Below is my code in the Update Button:
FromBox = 0
Tobox = 0
If IsNumeric(BoxHold1) Then
FromBox = CLng(BoxHold1)
End If

If IsNumeric(BoxHold2) Then
Tobox = CLng(BoxHold2)
End If

DoCmd.SetWarnings False

If FromBox = 0 Or Tobox = 0 Then
x = DLookup("countofBoxNo", "qryPalletUpdatednotnumCount")
y = MsgBox("You are about to update " & x & " records. Do you wish to continue?", vbYesNo)
If y = vbYes Then
DoCmd.OpenQuery "qryPalletUpdatednotnum"
MsgBox "Records Updated"
Else
MsgBox "Nothing has been changed"
End If
Else
x = DLookup("countofBoxNo", "qryPalletUpdatedCount")
y = MsgBox("You are about to update " & x & " records. Do you wish to continue?", vbYesNo)
If y = vbYes Then
DoCmd.OpenQuery "qryPalletUpdated"
MsgBox "Records Updated"
Else
MsgBox "Nothing has been changed"
End If
End If
DoCmd.SetWarnings True

when I tried to click Update Button, there is a message:
ODCB-Call Failed. [Microsoft][ODBC SQL Server Driver][SQL Server] Syntax error Converting the nvarchar Value '12496 lester' to a column of data type int.(#245)

I even changed to
If IsNumeric(BoxHold1) Then
FromBox = CInt(BoxHold1)
End If

If IsNumeric(BoxHold2) Then
Tobox = CInt(BoxHold2)
End If
still got the same error message.

BoxNo, Pall & Location are all Text in the table (nvarchar in SQL)

Thank you

Jim Dettman (EE MVE)

Go to the linked table manager first and refresh the link.

If any changes were made in the table, they are not reflected in Access until you do or drop the link and re-create it.

Jim.

Jim Dettman (EE MVE)

and if that doesn't do it, post the SQL of the query.

Jim.

urjudo

ASKER

We have the SQL as back end and Access as Front End. I created queries in the Front End

ASKER CERTIFIED SOLUTION

Jim Dettman (EE MVE)

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

urjudo

ASKER

qryPalletUpdatednotnum:
UPDATE MainTable SET MainTable.PalletNo = [forms]![frmPalletUpdated]![PalletBox], MainTable.Location = [Forms]![frmPalletUpdated]![LocationBox]
WHERE (((MainTable.BoxNo) Between [Forms]![frmPalletUpdated]![BoxHold1] And [Forms]![frmPalletUpdated]![BoxHold2]));

above query is for if Box# is not numeric

qryPalletUpdated:
Part1): SelectNumericRecord: SELECT MainTable.BoxNo, MainTable.PalletNo, MainTable.Location, IIf(IsNumeric([BoxNo]),"Yes","No") AS SelectBoxNo
FROM MainTable
WHERE (((IIf(IsNumeric([BoxNo]),"Yes","No"))="Yes"));

Part1 query: I pull all numeric box first then use on part2
Part2): UPDATE SelectNumericRecords SET SelectNumericRecords.PalletNo = [forms]![frmPalletUpdated]![PalletBox], SelectNumericRecords.Location = [Forms]![frmPalletUpdated]![LocationBox]
WHERE (((CLng([boxno])) Between [Forms]![frmPalletUpdated]![FromBox] And [Forms]![frmPalletUpdated]![ToBox]));

urjudo

ASKER

I did re-link the table, still same message

urjudo

ASKER

I knew it's something wrong on this part of the code:
If IsNumeric(BoxHold1) Then
FromBox = CLng(BoxHold1)
End If

If IsNumeric(BoxHold2) Then
Tobox = CLng(BoxHold2)
End If

If I command them out, it works but the system will not check if it's numeric or not

urjudo

ASKER

Thanks Jim. I fixed it. I removed all CLng in the queries, it works