asked on

SQL Update Problem

Hi,

I have this function:

Public Function myUpdateSingleField(ByVal strField As String, ByVal strDomain As String, _
ByVal strNewField As String, Optional strCriteria As Variant)

Dim strSql As String
Dim cn As New ADODB.Connection
Set cn = New ADODB.Connection
Set cn = CurrentProject.Connection
cn.CursorLocation = adUseClient

'cn.Open

If Not IsMissing(strCriteria) Then
strSql = "UPDATE " & strDomain & " SET " & strField & " = " & strNewField & " WHERE " & strCriteria
Else
strSql = "UPDATE " & strDomain & " SET " & strField & " = " & strNewField
End If
MsgBox (strSql)
cn.Execute (strSql)
cn.Close
Set cn = Nothing

End Function

If I call with this code it works fine:

Call myUpdateSingleField("receipt_number", "tbl_kitting", "2222", "kitting_id = " & Forms!fdlgOutStanding.txtKitting_id.Value & " ")

However if I try to call it and change the strNewField values it fails.

strNewField = Forms!fdlgOutStanding.txtreceipt.Value

Call myUpdateSingleField("receipt_number", "tbl_kitting", Forms!fdlgOutStanding.txtreceipt.Value, "kitting_id = " & Forms!fdlgOutStanding.txtKitting_id.Value & " ")

Error: No value given for one or more required parameters.

strNewField = “test string”

Call myUpdateSingleField("receipt_number", "tbl_kitting", "test string", "kitting_id = " & Forms!fdlgOutStanding.txtKitting_id.Value & " ")

Error: Syntax error (missing operator) in query expression 'test string'.

Any ideas why its failing?

cas1

Change it to:

Andy

Public Function myUpdateSingleField(ByVal strField As String, ByVal strDomain As String, _
    ByVal strNewField As String, Optional strCriteria As Variant)

    Dim strSql As String
    Dim cn As New ADODB.Connection
    Set cn = New ADODB.Connection
    Set cn = CurrentProject.Connection
    cn.CursorLocation = adUseClient
   
    'cn.Open
   
    If Not IsMissing(strCriteria) Then
        strSql = "UPDATE " & strDomain & " SET [" & strField & "] = " & strNewField & " WHERE " & strCriteria
    Else
        strSql = "UPDATE " & strDomain & " SET [" & strField & "] = " & strNewField
    End If
    MsgBox (strSql)
    cn.Execute (strSql)
    cn.Close
    Set cn = Nothing

End Function

Open in new window

ASKER CERTIFIED SOLUTION

cas1

membership

Create a free account to see this answer

Signing up is free and takes 30 seconds. No credit card required.

See answer

andyw27

ASKER

Perfect, thanks very much.