General discussion

Locked

Record Source features/Access 2000

By bratchev ·
Can a report/form have more than one record source? If not, how can I connect a single unbound control on a form/report to a table/query different from the form's/report's original record source? All I want to do is to get a text field on a report count the records in a table different from the record source table of the report.

This conversation is currently closed to new comments.

3 total posts (Page 1 of 1)  
| Thread display: Collapse - | Expand +

All Comments

Collapse -

Record Source features/Access 2000

by Peyison In reply to Record Source features/Ac ...

One way to do this would be to create a sub-report and have the subreport contain the textbox you wanted. Another way is to do it in VBA code.

Right click in the Detail area of the report. Select Build Event -> Code Builder. It will take you to the Detail_Format event. This event is fired when the detail area is being formatted. At this point, you can do a query and assign the results to an unbound text box in the detail section (in this example, named tbxEmpCount)

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
Dim db As Database
Dim rst As Recordset
Dim sSql As String

sSql = "Select Count(EmpIds) as CountEmps from tblEmployees"

Set db = CurrentDb
Set rst = db.OpenRecordset(sSql)

If Notrst.EOF or Not IsNull(rst.EOF) Then
tbxEmpCount = oRst!CountEmps
Else
tbxEmpCount = 0
Loop

rst.Close
Set rst = Nothing
Set db = Nothing
End Sub

-------------

Hope this helps.

Collapse -

Record Source features/Access 2000

by bratchev In reply to Record Source features/Ac ...

Thanks, it worked. I had written similar code including the SQL string, however, I was counting rows total (COUNT(*)) and not records in a particular field. Thanks again.

Collapse -

Record Source features/Access 2000

by bratchev In reply to Record Source features/Ac ...

This question was closed by the author

Back to Web Development Forum
3 total posts (Page 1 of 1)  

Related Discussions

Related Forums