Counting of Records in a Before opening a Data Form

[I H Naqvi]

I have One Control form and One Data Form. I get certain inputs on control
from and Want to Open Data Form based on Them.
Before Openning, I want to count the records of that query with a run time
WHERE Clause to see that query will return any record
or not. If query return no record the msgbox should display telling "no
matching records are
found" otherwise data form will display quered records. I am a student of
Oracle and dont know much about MS Acccess. I have used following
code but it returns an error of Type Mismatch ( Error 13 ). I also tried to
execute .OpenRecordSet
Command from Setting a QueryDef But it also results same error. Pl correct
this code or tell me Do we have any command
evivalent of QueryHit of Oracle.

The Code is

Dim dbsSecurity As Database
Dim rstCriteria As Recordset
Dim CountR As Long
Dim stDocName As String
Dim stLinkCriteria As String


Set dbsSecurity = CurrentDb
Set rstCriteria = dbsSecurity.OpenRecordset("Criteria")
'Where Criteria is a valid Query Name
CountR = rstCriteria.RecordCount

if CountR=0 then

MsgBox ("Selected Criteria Returns No Record")
Exit Sub

Else

stDocName = "Build Criteria1"
DoCmd.OpenForm stDocName, , , stLinkCriteria

End if

rstCriteria.Close
dbsSecurity.Close

 

[SteveS]

First make sure that the query returns records. Open the criteria form and
select the criteria. Go to the Main database window and run the query
"Criteria".

Paste the following code in your your sub for the button:

'---------------------

Dim dbsSecurity As DAO.Database
Dim rstCriteria As DAO.Recordset

Dim CountR As Long
Dim stDocName As String
Dim stLinkCriteria As String


Set dbsSecurity = CurrentDb
Set rstCriteria = dbsSecurity.OpenRecordset("Criteria")
'Where Criteria is a valid Query Name

'CountR will be:
' zero if no records
' one if records returned
CountR = rstCriteria.RecordCount

' close objects
rstCriteria.Close
dbsSecurity.Close

'destroy objects
Set rstCriteria = Nothing
Set dbsSecurity = Nothing


If CountR = 0 Then
MsgBox "Selected Criteria Returns No Record"
Else
stDocName = "Build Criteria1"
DoCmd.OpenForm stDocName

'DoCmd.OpenForm stDocName, , , stLinkCriteria
End If

'---------------------


Make sure you have a reference to (for Access 2000)


Microsoft DAO 3.6 Object Library


HTH

 

[I H Naqvi]

The Thing is Not Yet done as it is again generating Error " Too Few
Parameters: Required 2". I have Again floated a rephased Question. Pl do
reply.

 

[SteveS]

When running the query "Criteria" or the form "Master
Information", the form "Criteria Building Form" MUST be open.

Where did you paste the modified code I provided?

What is the code behind the "OK" button on the "Criteria Building Form"?

Would you post the SQL of the query "Criteria"?