Most of the time this works perfectly .. but on occasion a user enters a "BOAT_KIND" (stored as a variable "strBoat") with a ' in it ( exmaple: TRIMARAN 105' NIGEL ).
Having that extra ' in it is breaking the update statement.
To get rid of any quote mark in a string using Access VBA, use Replace.
Copy-paste the below line into your Immediate window and it will return abcdef
? Replace("abc'def", "'", "")
So just take the above and motify Miriam's SQL above where you have strBoat, and it'll work.
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
There are a large Range of Names that the clients are using.. when i alter the code per Miriam's suggestion i get an error on this boat name now "C+C 115".
error No: -2147217900
Invalid column name 'C+C 115'.
Unlimited question asking, solutions, articles and more.
mbizup
Give this a try:
Dim qd As New DAO.QueryDef On Error GoTo EH CurrentDb.QueryDefs.Delete ("qtemp") Set qd = CurrentDb.CreateQueryDef("qtemp", "UPDATE dbo.TblTapeWIP SET BOAT_KIND = @BoatKind WHERE ITEM_NUM = @ItemNum") With qd .Parameters("@BoatKind") = strBoat .Parameters("@ID") = strItemNum End With qd.Execute Set qd = Nothing Exit SubEH: If Err.Number = 3265 Then Resume Next MsgBox "ERROR " & Err.Number & ": " & Err.Description
Dim qd As New DAO.QueryDef Set qd = CurrentDb.CreateQueryDef(, "UPDATE dbo.TblTapeWIP SET BOAT_KIND = @BoatKind WHERE ITEM_NUM = @ItemNum") With qd .Parameters("@BoatKind") = strBoat .Parameters("@ItemNum") = strItemNum End With qd.Execute Set qd = Nothing
so i just replace it with a space.. maybe not the best way but this issue has already taken enough of my time.. cost to benefit you know..
Thanks for the solutions.
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
Open in new window