missing something obvious in my code snippet.Can someone help?

[Big1934]

I am just testing if a record exists in and sql server
CertificateNr. All I am trying to do is get the value of a column of a table in sql server from Access vba to test its value in my if. I just do know know how to do this.
1.how to run the below select and put the value in the variable strCertificate number

2. how to run the strCheck(the variable with T-sql select string in it)? use a 'DoCmd.runsql strCheck'
or a execute or what.

3. is using Exit Funtion good practice to not let the code complete after tossing and error to the use that they cannot complete the action , Notify IT support?

Thanks! Adam

'Check to see that there is not an Endorsement Transaction Record into table: "EndorsementTransactions"
strCheck = "SELECT CertificateNr " & _
"FROM dbo.EndorsementTransactions " & _
"WHERE strCertificateNr = " & CertificateNr.Value & " AS strCertificateNr"
DoCmd.RunSQL strCheck

If strCertificateNr <> "" Then
MsgBox "Cannot Endorse! Please notify IT / Development" & vbCrLf & vbCrLf & _
strCertificateNr & vbCrLf & _
vbOKOnly & vbExclamation, cstrApplicationName
Exit Function
End If

 

[addy]

I would probably use:

strCertificateNr = 'Reference where you get the value from here

If DCount("CertificateNr", "EndorsementTransactions", "[CertificateNr] = " & strCertificateNr) = 0 Then

'Do some stuff here

Else

Msgbox "add some stuff here"

End If

 

[randy700]

Unless I'm mistaken, the RunSQL method only works with ACTION queries. Try using the Execute method.


Randy

 

[Big1934]

THanks, yes i went to the execute(sqlstr)
what do you mean by action queries ? like update,insert?
thanks

 

[formerTexan]

An action query will alter data. An action query uses an execute method. A SELECT query returns a set of records without any data alteration. It does not use the execute method.

If you are attempting to run a select query and open a recordset, then

Dim db As DAO.Database
Dim rs As DAO.Recordset
Set db = CurrentDb()
Set rs = db.OpenRecordset(strCheck)

Cheers, Bill