Getting Record Count for Recordsource

[kathyc20]

Hi.

I have a main form with a button on it to load a new form which I want to run a query on (see code below).

I need to find out the record count on the result of this query prior to loading the new form and save it to a variable.

Any help would be appreciated.

Private Sub btnEmploymentInfoForm_Click()
Dim sSQL As String

sSQL = ""
sSQL = sSQL & "SELECT * FROM [Employment Info] WHERE "
sSQL = sSQL & "[Employment Info].[Employee Number] = "
sSQL = sSQL & "'" & sTempEmpNumber & "'"

OpenNewForm ("frmEmploymentInfo")
Form_frmEmploymentInfo.RecordSource = sSQL
End Sub

 

[PHV]

yourVariable = DCount("*", "[Employment Info]", "Employee Number]='" & sTempEmpNumber & "'")


Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886