setting different record source to a form based on a field value of a linked form (1 Viewer)

[lamha]

I have 3 forms: frmFIN, frmCM, and frmCL. They are all the same except that they are based on different queries (qryFIN, qryCM, and qryCL). Therefore, I want to reuse frmFIN and set the correct query as its Record Source based on the Field value of another form where I have a command button that opens the 3 forms above. Anyone has any idea on how to do this. Here's the code under that command button:
Private Sub cmdOpenForm_Click()
'Dim strNewRecord As String
'Dim stDocName As String
Dim LinkCriteriaFinal As String
'stDocName = "FrmFIN"
LinkCriteriaFinal = "[ITEM_SKEY]=" & Me!frmITEM_STATE![ITEM_SKEY]
LinkCriteriaFinal = LinkCriteriaFinal & "And" & "[Item_State]=" & Me!frmITEM_STATE![Item_State]
LinkCriteriaFinal = LinkCriteriaFinal & "and" & "[READING_TYPE_CODE]=" & "'" & Me!frmTRACE_LIST![READING_TYPE_CODE] & "'"

If Me!frmTRACE_LIST!WORK_CENTER Like "TL*" Then
'strNewRecord = "SELECT * FROM qryFin "
'stDocName.RecordSource = strNewRecord
DoCmd.OpenForm "FrmFIN", , , LinkCriteriaFinal, acFormReadOnly
ElseIf Me!frmTRACE_LIST!WORK_CENTER Like "CM*" Then
If Me!frmTRACE_LIST!READING_TYPE_CODE Like "AVG*" Then
MsgBox "This is a Flat Trace. Please push that button to generate trace."
cmdFlat.Enabled = True

Else

DoCmd.OpenForm "FrmCM", , , LinkCriteriaFinal, acFormReadOnly
End If
Else
Me!frmTRACE_LIST!WORK_CENTER = "CASH"
DoCmd.OpenForm "frmCL", , , LinkCriteriaFinal, acFormReadOnly
End If

 

[TexasTJ]

O.K. - if I understand what you want to do instead of having three identical forms you want to use just one form and change the recordsource depending upon the conditions in the form generating the opening command.

You could use the OpenArgs portion of the OpenForm command to pass the recordsource to the form (frmFin).

DoCmd.OpenForm "FrmFIN", , , LinkCriteriaFinal, acFormReadOnly,,,"qryCM"

In the form Load event for frmFin have:

Me.RecordSource = Me.OpenArgs

If you wanted to dress things up further you could change the frmFin's colors/captions, etc., dependent upon what the recordsource of the form is at runtime.

Good luck!

 

[lamha]

Thanks for your help. I did as you told, but I kept getting the error msg says "Compile error: variable not defined", and it highlighted the first field name I have in my code.
I checked the Record Source under form load. It already set to Me.recordsource = "qryCM", but somehow the data is not picked up.

 

[TexasTJ]

"Compile error: variable not defined"

You've checked to ensure there isn't a typo in the field name and/or the field name is slightly different in each query?

 

[lamha]

THANK YOU VERY MUCH. It worked. I added brackets around the field name and it worked great EXCEPT 1 PROBLEM. It doesn't pass the critera to the form. The form displays the first record of the query instead. Do you know how to fix this?

 

[TexasTJ]

Have you turned the filter on in your form? In the load procedure for the form try adding code something like:

Me.FilterOn = True

 

[lamha]

I found out that I could set the criteria in the query. Everything works fine now. This reusing form thing is so neat. Thanks a bunch