check if record exists? 2

[bluedollar]

In VBA how do you check to see if a record exists? For example (pseudo code):

SQL = "select quote_num from progress where (progress.quote_num = [forms]![sf01]![txt_quote_num].value)"

runsql(SQL)

If SQL = null then
do something
else
do something else
end if

Any help would be greatly apprecaited

Thanks

Dan

 

[GHolden]

Hi,

You can't use RunSQL or Execute with SELECT statements.

You can use something like this...

If DCount("quote_num", "progress", "quote_num = " & [forms]![sf01]![txt_quote_num].value) > 0 Then
MsgBox ("Found&quot
Else
MsgBox ("Not Found&quot
End If

However depending on why you want to check and what you want to do next, there may be better ways.


There are two ways to write error-free programs; only the third one works.

 

[hwkranger]

but you can set a recordset to sql, if you're used to your SQL returning a recordset - then this is what you should do.

Have a recordset set to your SQL, then if the recordset has a count then you know it exists.



Hope this helps.

Randall Vollen
National City Bank Corp.

Just because you have an answer - doesn't mean it's the best answer.

 

[hwkranger]

btw: context for your rst would be as follows.

DAO:

dim db as dao.database
dim rst as dao.recordset

set db = currentdb()
set rst = db.openrecordset("quote_num", "progress", "quote_num = " & [forms]![sf01]![txt_quote_num].value)
if rst.recordcount > 0 then
'do something
end if

*** end of DAO ***

ADO:
Dim db As New ADODB.Connection
Dim rst As ADODB.Recordset

Set db = CurrentProject.Connection
Set rst = New ADODB.Recordset
rst.Open ("quote_num", "progress", "quote_num = " & [forms]![sf01]![txt_quote_num].value), db, adOpenDynamic, adLockOptimistic

if rst.recordcount then
'do something
end if
*** end of ado***

Randall Vollen
National City Bank Corp.

Just because you have an answer - doesn't mean it's the best answer.

 

[Quehay]

Nice pointer Randall!

Some nitpicky stuff:

DAO is faster with Jet data (per Microsoft). But if you use ADO use the lightest recordset possible that still supports are recordcount (or select COUNT and read that field). Forward Only is the fastest Rst (but no recordcount), and OpenStatic is next fastest and supports .Recordcount. Using adLockReadOnly is less costly than adLockOptimistic.

Why not make the code more maintainable and legible by putting the form value into a variable:

[tt]
Sub Example

Dim intValue as Integer
Dim strSQL as String
Dim Rst as DAO.Recordset
Dim intRows as Integer

intValue = [forms]![sf01]![txt_quote_num].value
strSQL = "select count(*) from progress where progress.quote_num = "

If intValue Then

strSQL = strSQL & intValue
Set Rst = CurrentDB.OpenRecordset
intRows = Rst.Fields(0)
Rst.Close

If intRows Then
'statement
End If

End If

Exit:
set Rst = Nothing
Exit Sub


End Sub[/tt]


Jeffrey R. Roberts
Insight Data Consulting
Access, SQL Server, & Oracle Development

http://www.insightdataconsulting.com

 

[bluedollar]

Thanks all, got it working perfectly.