Late Binding of subform RecordSource

[Mike_L_W]

Hello,
I am test converting Access 2003 databases to Access 2007.
On this particular DB I have widely used code to late bind the sub forms
record source similar to the example below. Access 2007 evaluates the code
without any error but also fails to display the qry results in any sub forms
without Link Master and Child fields set. Any ideas on how to modify this
method to work with 2007 or must I find an alternative?

Thanks for any help
Best Regards
Mike

Private Sub MainTab_Change()
On Error GoTo Err_MainTab_Change

'The following code leaves the subform RecordSource unbound,
'until the user clicks on the tab. This on-demand technique will allow the
main form to load 'without trying to populate all of the subforms.

Select Case MainTab.Pages.Item(MainTab.Value).Name

THIS WILL DISPLAY BECAUSE THERE ARE MASTER & CHILD LINKS
Case "DueActions"
If Len(FsubVLActionsRequired.Form.RecordSource) = 0 Then
FsubVLActionsRequired.Form.RecordSource = "QryVLDueActions"
End If

Case "BookWork"
If Len(FsubBookingData.Form.RecordSource) = 0 Then
FsubBookingData.Form.RecordSource = "QryBookingData"
FsubVLNextServiceNotes.Form.RecordSource = "QryNextServiceNotes"
End If

Case "Maintenance"
If Len(fsubVLVehiclesMaint.Form.RecordSource) = 0 Then
fsubVLVehiclesMaint.Form.RecordSource = "QryMaintenanceData"
End If

THIS WILL NOT WORK AS THERE ARE NO MASTER & CHILD FIELDS SET
AS THE DATA REQ IS NOT RELATED TO THE CURRENT RECORD
Case "OrdersOutstanding"
If Len(FsubOutstandingORDERS.Form.RecordSource) = 0 Then
FsubOutstandingORDERS.Form.RecordSource = "QryOutstandingOrders"
End If

End Select

Exit_MainTab_Change:
Exit Sub

Err_MainTab_Change:
MsgBox Err.Description
Resume Exit_MainTab_Change

End Sub

 

[Douglas J. Steele]

I'm not really sure I understand the question. Can't you also set the values
of the LinkChildFields and LinkMasterFields when you're setting the
RecordSource?

 

[Mike_L_W]

I'm not really sure I understand the question. Can't you also set the values
of the LinkChildFields and LinkMasterFields when you're setting the
RecordSource?

 

[Mike_L_W]

Douglas,
Thanks for the reply, I just posted to you but nothing appeared in the
post!! I'll try again....

In the case I am asking about the subform is on a Tab control and is to show
a list of data NOT related to the current record and so no LinkMaster / Child
fields are required. This works fine in 2003, but in 2007 NO data is returned
even though a valid qry record source has been applied, giving the impression
that no data exists, when in fact it does. If I set the record source in
design mode without Link fields then 2007 will display the data, however this
defeats the objective of the late binding of the record source until and if
required by user input.
My question is why this now happens and if there is another way to late bind
the source in v2007.

Regards Mike

 

[Douglas J. Steele]

Sorry, I haven't worked with Access 2007 enough to know what's different in
that regard.

 

[Klatuu]

Mike,
I haven't really worked with 2007 either, but just a thought. Have you
tried a requery of the subform after applying the Record Source?
I know in 2003 it is not necessary.

There is another technique you might experiment with. I have one form used
for some import validation where the user is working in one subform with the
rows from the import that have errors. In the other subform, I use five
different forms as the source object, depending on which the user selects.
The forms already have the record source identified, but they are not loaded
until the user clicks a command button which sets the source object of the
subform control. In your case, it would be when they select a tab. Here is
an example of one of the command buttons to show what I mean.

Private Sub cmdPerformAcctUnit_Click()
On Error GoTo Err_cmdPerformAcctUnit_Click
Dim rst As Recordset

Me.fsubTableEdit.SourceObject = "frmsubAcctUnit"
DoCmd.Maximize
Me.fsubTableEdit.Width = 2940
'Bold selection - unbold the rest
Me.cmdChartOfAccounts.FontBold = False
Me.cmdPerformAcctUnit.FontBold = True
Me.cmdTaskTable.FontBold = False
Me.cmdAttributes.FontBold = False
Me.cmdEmployee.FontBold = False

Set rst = Me!fsubTableEdit.Form.RecordsetClone
rst.FindFirst "[PerformAcctUnit] = '" &
Me!frmSubTaskValidation!PerformAcctUnit & "'"
If Not rst.NoMatch Then
Me!fsubTableEdit.Form.Bookmark = rst.Bookmark
End If
Set rst = Nothing

Exit_cmdPerformAcctUnit_Click:
Exit Sub

 

[Mike_L_W]

Many thanks for your thoughts, I will try a re query on the sub form and if
that fails experiment with your suggestion.

Best regards
Mike