Error on VBA Code

captainlove

captainlove
Local time
Today, 14:32
Joined
Apr 18, 2008
Messages
39
I am getting an error on my select statement with this code and the if statement.

Can anyone help

Private Sub txtRevisedCost_LostFocus()
sql = "select * FROM projectupctable WHeRE Prj_no = '" & Me.prj_No & "' AND upc = " & Me.UPC"
rs.Open SQL, CurrentProject.Connection, adOpenDynamic, adLockOptimistic
if rs.RecordCount = 0'then
Else
rs.Fields("RevisedCost") = Me.txtRevised
rs.Update
End If
rs.Close
rs = Nothing

End Sub
 
I am getting an error on my select statement with this code and the if statement.

Can anyone help

Private Sub txtRevisedCost_LostFocus()
sql = "select * FROM projectupctable WHeRE Prj_no = '" & Me.prj_No & "' AND upc = " <--- the error probably occurs here. This is marking the end of the statement with the quotes. & Me.UPC"
rs.Open SQL, CurrentProject.Connection, adOpenDynamic, adLockOptimistic
if rs.RecordCount = 0'then <--- this is bad syntax. :)
Else
You need an rs.edit right here.
rs.Fields("RevisedCost") = Me.txtRevised
rs.Update
End If
rs.Close
rs = Nothing

End Sub
............
 
so what should it look like Adam
 
Code:
Private Sub txtRevisedCost_LostFocus()

    sql = "select * FROM projectupctable WHERE Prj_no =" & _
        Me.prj_No & " AND upc =" & Me.UPC
    rs.Open sql, CurrentProject.Connection, adOpenDynamic, adLockOptimistic

        If rs.RecordCount = 0 Then
        Else
            rs.edit
            rs.Fields("RevisedCost") = Me.txtRevised
            rs.Update
        End If
    rs.Close
    rs = Nothing

End Sub
 
Code:
Private Sub txtRevisedCost_LostFocus()

    sql = "select * FROM projectupctable WHERE [COLOR="Red"][B][SIZE="4"]Prj_no =" & _
        Me.prj_No & " AND upc =" & Me.UPC[/SIZE][/B] <--- this is OK, 
           but only if the UPC and Prj_no are any data types other than text.[/COLOR]
    rs.Open sql, CurrentProject.Connection, adOpenDynamic, adLockOptimistic

        If rs.RecordCount = 0 Then
        Else
            rs.edit
            rs.Fields("RevisedCost") = Me.txtRevised
            rs.Update
        End If
    rs.Close
    rs = Nothing

End Sub
...........
 
...........

That is of course very true. There are a couple approaches to solving that problem.
Code:
Private Sub txtRevisedCost_LostFocus()
'    one way
     sql = "select * FROM projectupctable WHERE Prj_no ='" & _
         Me.prj_No & "' AND upc ='" & Me.UPC & "'"

'    another way
     sql = "select * FROM projectupctable WHERE Prj_no =" & Chr(34) & _
         Me.prj_No & Chr(34) & " AND upc =" & Chr(34) & Me.UPC & Chr(34)

'    finally another would be to create a custom function that returns
'    your string in quotes
     sql = "select * FROM projectupctable WHERE Prj_no =" & _
     quote(Me.prj_No) & " AND upc =" & quote(Me.UPC ) 

Pubilc Function quote(byval v2BQuoted As Variant) As String

    quote = Chr(34) & v2BQuoted & Chr(34)

End Function

Or something like that

End Sub
 
Also, you can't get a recordcount from an ADO recordset using rst.RecordCount UNLESS you open it adOpenStatic or adOpenKeyset.
 

Users who are viewing this thread

Back
Top Bottom