subform RecordSource

[rjoshi2]

On my form_load I want set the RecordSource of a subform1 based on the CAN number (in the main form). After that I want to set the RecordSource of subform2 based on the CC Code from subform1. I would like utilize this same code after a CAN update. Any help would be appreciated.

Thank You,
rjoshi

my code:

Private Sub Form_Load()
Dim getdata As String
Dim candata As String
Dim costdata As String

MsgBox ("Hello: " & CurrentUser & "!&quot
'data for current form
getdata = "Select * from FY03COMREG where " & _
"FY03COMREG.[User ID] = '" & CurrentUser & "'"
Me.RecordSource = getdata
Me.Requery

'data for subform frmSubCanTable
'compare the value of CAN (current form/FY03COMREG table)
'with value of CAN in the master CAN table

candata = "SELECT * FROM FY03COMREG, [FY03 CAN Master Table]" & _
"WHERE (((FY03COMREG.CAN)=[FY03 CAN Master Table].[CAN]));"
Me.RecordSource = candata
Me.Requery

'data for subfrom frmSubCostCenter
'compare the value of cc code (frmSubCanTable/master CAN table)
'with value of cc code in the cost center table

costdata = "SELECT * FROM [Cost Center], [FY03 CAN Master Table]" & _
"WHERE [FY03 CAN Master Table].[CC Code]=[Cost Center].[CC Code];"
Me.RecordSource = costdata
Me.Requery

End Sub

 

[BSman]

For at least the first subform, you don't have to use a fancy query as your source. You can use the entire table as the source for the subform. Just be sure that on your main form you set the linking to the subform object using the CAN column (which must be contained in both the source table for the main form and the source table for the subform).

You might be able to set up a sub-subform within your subform and link by the CC code in the source table for the subform to the CC form for the sub-subform. (A subform within a subform may only work with newer versions of Access.)

With these links the subform(s) are automatically changed to matching records when you change the record on the main form (and the resulting value in CAN).

 

[rjoshi2]

Question how due you set up the link that you talked about?

 

[BSman]

On your main form select the subform/subreport control that contains your subform. You will see two properties called Link Child Fields and Link Master Fields. Go to Link Child Fields, then click on the ... to the right of it that shows when you have selected the property.

Access will then display a dialog box where you pick the field(s) that must match for the record source for the two forms. Select the one (or more) fields that must match, then close the dialog box. Now your link is set up.