Recordsource

[jrmellis]

I have set the recordsource of a form based on selections from combo boxes. How do I check if records were returned?

 

[pdx_man]

You can check to see if the results of a DLookup return a NULL value. ie

WhereStr = "Criteria1Field = " & cmb1 & " AND " ...
If Not IsNull(DLookup("Fieldname","TableName",WhereStr)) Then
Open Form with RecordSource based on Combo (cmb) boxes

End If

If the table is being selected from a combo box, then substitute the value of the combo box reference for the TableName.

 

[jrmellis]

I am not sure I understand. One of the SQL statement for the Recordsource is below. I tried using the DLookup with rstRecSource as the domain but it gave me an error.

Any more advice.

Thanks

strSQL = "SELECT Sites.Name, Parish.[Parish Name], [Picture Gallery].SiteCode, " & _
"[Picture Gallery].ImageID, [Picture Gallery].Category, " & _
"[Picture Gallery].Description, [Picture Gallery].Type, " & _
"[Picture Gallery].ImagePath FROM (Sites INNER JOIN [Picture Gallery] " & _
"ON Sites.Code = [Picture Gallery].SiteCode) INNER JOIN Parish ON " & _
"Sites.[Parish Id] = Parish.[Parish Id] ORDER BY Parish.[Parish Name]"

rstRecSource = strSQL
Me.RecordSource = rstRecSource

 

[pdx_man]

Try adding this before setting the RecordSource:

dim rst as Recordset
dim dbs as Database

Set dbs = CurrentDb

set rst = dbs.OpenRecordset(strSQL)
if rst.RecordCount <> 0 then
Me.RecordSource = StrSql
'Open the form
end if
rst.close
dbs.close