INSERT or Update Issue (1 Viewer)

[Bigmo2u]

I am trying to do this in VBA, but have been unsuccessful. I don't i may even be doing it wrong. I have search all over the site for an exampl of what I am tryin gto do.

When a user hits btnSave VBA decides whether to run an insert or update sql.

Right now the code stops at the DoCmd.RunSQL sSQL with the error:

"A RunSQL action requires an argument consisting of an SQL statement."

here is the code on the form:

Dim sSQL As String

sSQL = "SELECT * FROM tblDNclaims WHERE ClaimNo = " & Me.ClaimNo
DoCmd.RunSQL sSQL

Select Case sSQL
    Case Is = Null
        Call InsertDN(Me.ClaimNo, Me.DN_Date, Me.DNclerk, Me.CatCode, Me.DN_Reason)
    Case Is <> Null
        Call UpdateDN(Me.ClaimNo, Me.DN_Date, Me.DNclerk, Me.CatCode, Me.DN_Reason)
End Select

Here is the functions (I like to have it in module so i can call these statements from anywhere)

Public Function UpdateDN(uClaim As Long, uDate As Date, uClerk As String, uCatCode As String, uReason As String)

Dim uSQL As String

uSQL = "UPDATE tblDNclaims SET DN_Date = # uDate #, DNclerk = '" & uClerk & "', CatCode = '" & uCatCode & "', DN_Reason = '" & uReason & "' _ &"
WHERE "ClaimNo = " & uClaim

DoCmd.RunSQL uSQL

End Function

Public Function InsertDN(iClaim As Long, iDate As Date, iClerk As String, iCatCode As String, iReason As String)

Dim iSQL As String

iSQL = "INSERT INTO tblDNclaims (ClaimNo, DN_Date, DNclerk, CatCode, DN_Reason, TimeStamp) _ &"
VALUES "(iClaim, # iDate #, '" & iClerk & "', '" & iCatCode & "', '" & iReason & "')"

DoCmd.RunSQL iSQL

End Function

I hope someone can help me.

 

[boblarson]

You can't use RunSQL to run a select query. What you need is to bring back a value:

Select Case DCount("ClaimNo","tblDNclaims","[ClaimNo]=" & Me.ClaimNo)
    Case 0
        Call InsertDN(Me.ClaimNo, Me.DN_Date, Me.DNclerk, Me.CatCode, Me.DN_Reason)
    Case Else
        Call UpdateDN(Me.ClaimNo, Me.DN_Date, Me.DNclerk, Me.CatCode, Me.DN_Reason)
End Select

 

[Bigmo2u]

boblarson - Thank you so much for the help.

 

[boblarson]

Glad to be able to help

 

[Bigmo2u]

I have a violation error for type coversion failure. The only field I see could be an issue is:

Reason

in the table it is a memo field.

here are the variables from the table:

ClaimNo = long int
DN_Date = Date
DNclerk = text
Catcode = text
DN_Reason = memo

there are 2 other fields but the populate automatically when a new record is entered.

DateStamped = date
TimeStamped = Now

 

[boblarson]

You'll have to encapsulate the items that need delimiters:

Select Case DCount("ClaimNo","tblDNclaims","[ClaimNo]=" & Me.ClaimNo)
    Case 0
        Call InsertDN(Me.ClaimNo, "#" & Me.DN_Date & "#", "'" & Me.DNclerk & "'", "'" & Me.CatCode & "'", "'" & Me.DN_Reason & "'")
    Case Else
        Call UpdateDN(Me.ClaimNo, "#" & Me.DN_Date & "#", "'" & Me.DNclerk & "'", "'" & Me.CatCode & "'", "'" & Me.DN_Reason & "'")
End Select

 

[Bigmo2u]

boblarson, thanks, I have major issue in my eyes. the Catcode on the form is 2 colums and I need it to display the second column. on the for that is what it displaying, but not pulling it that way.

I really appreciate your time on this.
Craig

 

[Bigmo2u]

Fixed the problem, but still have a type mismatch.....

 

[boblarson]

What datatype(s) is the cat code and have you delimited properly for the query?