Null Recordset

[gblack]

OK, forgive me if this has already been answered somewhere but I tried to find it by searching first.

What I am trying to do is to filter data in a form by user. What I want to do is pull up only the users information when said user opens said form. However, I am having issues with this, because when I try to filter, if there is no data already in the table for said user, I get an error.

How can I check the recordset to see if there is any data first? I mean once I run my strSQL, is there a way to check if the recordset exists or not? I mean I know there must be, but what is the syntax to do this?

Thanks,
Gary

 

[RuralGuy]

You could use a DCount() to check for any matching records or look at the RecordSet.RecordCount. A RecordCount = 0 indicates no records returned.

 

[Bat17]

http://www.access-programmers.co.uk/forums/showpost.php?p=453715&postcount=5

HTH

Peter

 

[mazza]

I tried a test with a simple module/query but keep getting the following error?
"the select statement includes a reserved word or an argument name that is misspelled or missing, or the punctuation is incorrect"

Where do I go wrong here?


Private Sub Command0_Click()
Dim rst As DAO.Recordset
Dim strSql As String
strSql = "SELECT TblOil.OilType, TblOil.OilDescr, " & _
"FROM TblOil;"
Set rst = CurrentDb.OpenRecordset(strSql)
If Not (rst.EOF And rst.BOF) Then

MsgBox "found some results"
Else
MsgBox "found no results"
End If
End Sub

 

[Mile-O]

You have a floating comma in the SQL statement. Take that out and then report back.

 

[mazza]

If I remove the comma I get a syntax error (missing operator) in query expression.

Get a bit frustrated with it now...it seems so simple

 

[RuralGuy]

strSql = "SELECT TblOil.OilType, TblOil.OilDescr, " & _
"FROM TblOil;"

Did you remove the Red comma?

 

[Mile-O]

Another note, I don't see why you need a String variable; it would make more sense to use a constant.

i.e.

Const strSQL As String = "SELECT TblOil.OilType, TblOil.OilDescr FROM TblOil;"

Since your SQL is static, however, your would be better to actually design this query and save it. You can then DCount it or, if you need to use ADO/DAO then count the records in the query.

Remember, a saved query doesn't cause database bloat the way creating a query every time you run this code does.

 

[SammyJ]

EOF

All he needs.