DCount Syntax w/ Autonumber

[Adam McReynolds]

I have a button event that fires A SQL INSERT if the DCount is 0 and fires SQL UPDATE if DCount is 2. Something is wrong with the syntax because my INSERT will fire initially but upon a 2nd attempt it will not fire the UPDATE but rather the INSERT again even though there is RecordID that matches and should DCount 2. Here is my code for the button:

'''''''''''''''''''''''''''''''''''''''''''''''''''''
'''''''''''''''''''''''''''''''''''''''''''''''''''''
'INSERTS TO TABLES- If no record exsits

If DCount("PaybackRecordID", "TBL_ACTION", PaybackRecordID = " & Me.RecordID.Value & ") = 0 Then

    'CUSTOMER OWED INSERT
    'If OK then SQL INSERT Into Action Table
    DoCmd.SetWarnings False
    DoCmd.RunSQL "INSERT INTO TBL_ACTION ([ActionEntity], [Action], [Qty], [StockType], [Notes], [TimeStamp], [PaybackRecordID]) Values ('" & Me.CustomerOwed & "', ""IN"", " & Me.PaybackLevel & ", '" & Me.StockType & "', '" & Me.Notes & "', Now(), " & Me.RecordID & ");"
    DoCmd.SetWarnings True
    Me.Refresh
    
    'BORROWER CUSTOMER INSERT
    'If OK then SQL INSERT Into Action Table
    DoCmd.SetWarnings False
    DoCmd.RunSQL "INSERT INTO TBL_ACTION ([ActionEntity], [Action], [Qty], [StockType], [Notes], [TimeStamp],[PaybackRecordID]) Values ('" & Me.Borrower & "', ""OUT"", " & Me.PaybackLevel & ", '" & Me.StockType & "', '" & Me.Notes & "', Now(), " & Me.RecordID & " );"
    DoCmd.SetWarnings True
    Me.Refresh
    
Else

    '''''''''''''''''''''''''''''''''''''''''''''''''''''
    '''''''''''''''''''''''''''''''''''''''''''''''''''''
    'UPDATES TO TABLES- If record exsits
    
    If DCount("PaybackRecordID", "TBL_ACTION", PaybackRecordID = " & Me.RecordID.Value & ") = 2 Then
        
        'CUSTOMER OWED UPDATE
        'If OK then SQL UPDATE Into Action Table
        DoCmd.SetWarnings False
        SQLupdate = "update TBL_ACTION set ([ActionEntity], [Action], [Qty], [StockType], [Notes], [TimeStamp]) = ('" & Me.CustomerOwed & "', ""IN"", " & Me.PaybackLevel & ", '" & Me.StockType & "', '" & Me.Notes & "', Now()) where PaybackRecordID = " & Me.RecordID & " AND ActionEntity = '" & Me.CustomerOwed & "';"
        DoCmd.RunSQL SQLupdate
DoCmd.SetWarnings True
        Me.Refresh
        
        'BORROWER CUSTOMER UPDATE
        'If OK then SQL UPDATE Into Action Table
        DoCmd.SetWarnings False
        SQLupdate = "update TBL_ACTION set ([ActionEntity], [Action], [Qty], [StockType], [Notes], [TimeStamp]) = ('" & Me.Borrower & "', ""OUT"", " & Me.PaybackLevel & ", '" & Me.StockType & "', '" & Me.Notes & "', Now()) where PaybackRecordID = " & Me.RecordID & " AND ActionEntity = '" & Me.Borrower & "';"
        DoCmd.RunSQL SQLupdate        
DoCmd.SetWarnings True
        Me.Refresh
        
    
    End If
    
End If

End Sub

Any help would be appreciated.
P.S. The button is called by an AfterUpdate event in a datasheet form and RecordID is an autonumber and PaybackRecordID is a number.

 

[pbaldy]

Try

DCount("PaybackRecordID", "TBL_ACTION", "PaybackRecordID = " & Me.RecordID.Value)

 

[Adam McReynolds]

Try

DCount("PaybackRecordID", "TBL_ACTION", "PaybackRecordID = " & Me.RecordID.Value)

That did it. But now that my UPDATE is firing I am getting a syntax error. Do you see it?

ActionEntity = Text
Action = Text
Qty = Number
StockType = Text
TimeStamp = Date/Time
Notes = Text

 

[pbaldy]

Sure, the proper syntax is:

UPDATE TableName
SET NumericField = 123, TextField= 'abc', DateField = #8/28/2013#
WHERE ...

 

[Adam McReynolds]

Sure, the proper syntax is:

UPDATE TableName
SET NumericField = 123, TextField= 'abc', DateField = #8/28/2013#
WHERE ...

Sorry not sure what you are saying. How does this relate to this or the rest of it?:

WHERE PaybackRecordID = " & Me.RecordID & " AND ActionEntity = '" & Me.Borrower & "';"

I have for number fields " & Me.NumberField & "
And for text fields '" & Me.TextField & "'
And for straight Text ""Text"" (This works at least on another SQL INSERT)

Thanks again for the help.

 

[pbaldy]

See the difference?

SET NumericField = 123, TextField= 'abc', DateField = #8/28/2013#
SET NumericField, TextField, DateField = 123, 'abc', #8/28/2013#

You're trying to use the INSERT syntax, which is different.

 

[Adam McReynolds]

See the difference?

SET NumericField = 123, TextField= 'abc', DateField = #8/28/2013#
SET NumericField, TextField, DateField = 123, 'abc', #8/28/2013#

You're trying to use the INSERT syntax, which is different.

Ahha! You are always a ginormous help. Thanks again. Here is what it converted to for anyone that may stumble on this thread:

DoCmd.RunSQL "UPDATE TBL_ACTION SET [ActionEntity] = '" & Me.CustomerOwed & "' , [Action] = ""IN"", [Qty] = " & Me.PaybackLevel & ",  [StockType] = '" & Me.StockType & "'  , [Notes] = '" & Me.Notes & "', [TimeStamp] = Now()WHERE PaybackRecordID = " & Me.RecordID & " AND [ActionEntity] = '" & Me.CustomerOwed & "'"

 

[pbaldy]

Happy to help!