Access VBA sub with form as parameter doesn't alter the form
- by Ski
I have a Microsoft Access 2003 file with various tables of data. Each table also has a duplicate of it, with the name '[original table name]_working'. Depending on the user's choices in the switchboard, the form the user choose to view must switch its recordsource table to the working table. I refactored the relevant code to do such a thing into the following function today:
Public Sub SetFormToWorking(ByRef frm As Form)
With frm
.RecordSource = rst![Argument] & "_working"
.Requery
Dim itm As Variant
For Each itm In .Controls
If TypeOf itm Is subForm Then
With Item
Dim childFields As Variant, masterFields As Variant
childFields = .LinkChildFields
masterFields = .LinkMasterFields
.Form.RecordSource = .Form.RecordSource & "_working"
.LinkChildFields = childFields
.LinkMasterFields = masterFields
.Form.Requery
End With
End If
Next
End With
End Sub
The lines of code that call the function look like this:
SetFormToWorking Forms(rst![Argument])
and
SetFormToWorking Forms(cmbTblList)
For some reason, the above function doesn't change the recordsource for the form. I added the 'ByRef' keyword to the parameter just to be certain that it was passing by reference, but no dice. Hopefully someone here can tell me what I've done wrong?