accessing result set from SQL query in Visual Basic

[hwilliams]

I have a typical Access form where users enter data and press a button to save the data as a new record in an existing table. The table has two primary keys, jobId and clientId. Prior to adding the new record, I want to verify that the jobId and clientId combination don't already exist in my table. That way I can display my own error messages, rather than getting the Access integrity contraint messages. What is the best approach to doing this? I am a VBA rookie.

My thought was to do something like this:

Private Sub validateKeys()

Dim strSQL As String
Dim intJobCount As String

strSQL = "SELECT COUNT(JobID) AS [JobCount] FROM tblJob WHERE ClientID = "ClientID & " AND JobID = '" & JobID & "';"

intJobCount = DoCmd.RunSQL strSQL
If intJobCount > 0 Then
'Output error message
Else
'Add record
End Sub

You'll notice my code falls apart at the point where I try and set the result of the SQL statement to intJobCount. How can I access this value? Is there a better way of doing this?

Thanks so much!

 

[KenReay]

Hi

I assume you mean that the promary key is a compound key of
ClientId, JobId?

I will also assume you are using DAO, if you are using ADO, post back and I will give revised example.

Private Sub validateKeys()

Dim Db as Database
Dim Rs as DAO.Recordset
Dim strSQL As String

strSQL = "SELECT COUNT(JobID) AS [JobCount] FROM tblJob WHERE ClientID = "ClientID & " AND JobID = '" & JobID & "';"

Set Db = CurrentDb()
Set Rs = Db.OpenRecordset(strSQL)
If Rs.RecordCount > 0 Then
'Output error message
Else
'Add record
End If
Rs.Close
Set RS = Nothing
set db = nothing
End Sub Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[BillPower]

Hi hwilliams, try this:

Dim lngJobCount As Long
lngJobCount = DCount("JobID", "[tblJob]", "[ClientID] = " & Me!ClientID & " AND [JobID] = " & Me!JobID)

If lngJobCount > 0 Then
'Output error message
Else
'Add record
End Sub