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
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