Full Version: Bypass The Code Except 1 If Open From An Open Command Button
UtterAccess Discussion Forums > Microsoft® Access > Access Q and A
Frank Situmorang
May 22 2012, 06:14 PM
Hello,

This is my code on open event of the report. How can I make it that if open from one specific button, it will bypass this code except 1 statement.

CODE
Private Sub Report_Open(Cancel As Integer)
On Error Resume Next

Call FillReportLabels(Me.Name)
strMsg = DLookup("MessageString", "[Lookup Message String_Qry]", "[FormName] = '" & Me.Name & "' AND [StringNumber] = 1")
  Select Case MsgBox(strMsg, vbQuestion Or vbYesNoCancel)
    Case vbCancel
      Cancel = True
      Exit Sub
    Case vbYes
      Me.Detail.Visible = True
      

    Case vbNo
    strMsg = DLookup("MessageString", "[Lookup Message String_Qry]", "[FormName] = '" & Me.Name & "' AND [StringNumber] = 2")
        Select Case MsgBox(strMsg, vbQuestion Or vbYesNo)
         Case vbYes
        
         'Me.Detail.Visible = False
         'Me.RdivNameL_label.Visible = False
         'Me.RdivNameL_ctrl.Visible = False
         Me.RdivAddrs_label.Visible = False
         Me.RdivAddrs_ctrl.Visible = False
         Me.RdivAddrs2_ctrl.Visible = False
         Me.Rdivcity_label.Visible = False
         Me.Rdivcity_ctrl.Visible = False
         Me.RdivRegn_label.Visible = False
         Me.RdivRegn_ctrl.Visible = False
         Me.RdivPost_label.Visible = False
         Me.RdivPost_ctrl.Visible = False
         Me.RdivCountry_label.Visible = False
         Me.RdivCountry_ctrl.Visible = False
         Me.RdivPh_label.Visible = False
         Me.RdivPh_ctrl.Visible = False
         Me.RdivFax_label.Visible = False
         Me.RdivFax_ctrl.Visible = False
         Me.RdivEaddress_label.Visible = False
         Me.RdivEaddress_ctrl.Visible = False
         'Me.RUnionName_L_label.Visible = False
         'Me.RUnionName_L.Visible = False
         Me.RUAddress_label.Visible = False
         Me.RUaddress1.Visible = False
         Me.RUaddress2.Visible = False
         Me.RUcity_alabel.Visible = False
         Me.RUcity.Visible = False
         Me.RUpostalCode_label.Visible = False
         Me.RUpostalCode.Visible = False
         Me.RUCountry_label.Visible = False
         Me.RUCountry.Visible = False
         Me.RUPhone_label.Visible = False
         Me.RUPhone.Visible = False
         Me.RUFax_label.Visible = False
         Me.RUFax.Visible = False
         Me.RUemailAddress_label.Visible = False
         Me.RUemailAddress.Visible = False
         'Me.RRegionName_L_label.Visible = False
         'Me.RRegionName_L.Visible = False
         Me.RRAddress1_label.Visible = False
         Me.RRAddress1.Visible = False
         Me.RRAddress2.Visible = False
         Me.RRcity_label.Visible = False
         Me.RRcity.Visible = False
         Me.RRwgion_label.Visible = False
         Me.RRregion.Visible = False
         Me.RRpostalCode_label.Visible = False
         Me.RRpostalCode.Visible = False
         Me.RRCountry_label.Visible = False
         Me.RRCountry.Visible = False
         Me.RRPhone_label.Visible = False
         Me.RRPhone.Visible = False
         Me.RRFax_label.Visible = False
         Me.RRFax.Visible = False
         Me.RRemailAddress_label.Visible = False
         Me.RRemailAddress.Visible = False
         'Me.RDistrictName_local_label.Visible = False
         'Me.RDistrikName_L.Visible = False
         Me.RDisaddress_label.Visible = False
         Me.RDisAddress1.Visible = False
         Me.RdisAddress2.Visible = False
         Me.RDisCity_label.Visible = False
         Me.RDisCity.Visible = False
         Me.RDisRegion_Label.Visible = False
         Me.RDisRegion.Visible = False
         Me.RDispostalCode_label.Visible = False
         Me.RdispostalCode.Visible = False
         Me.RDisCountry_label.Visible = False
         Me.RDisCountry.Visible = False
         Me.RDisPhone_label.Visible = False
         Me.RDisPhone.Visible = False
         Me.RDisFax_label.Visible = False
         Me.RdisFax.Visible = False
         Me.RdisPastor_label.Visible = False
         Me.RdisPastor.Visible = False
         Me.RdisOthercontact_label.Visible = False
         Me.RdisOcontact.Visible = False
         Me.RDisemailAddress_label.Visible = False
         Me.RdisemailAddress.Visible = False
         Me.RCaddress_label.Visible = False
         Me.RCaddress_ctrl.Visible = False
         Me.RCaddress2_cntrl.Visible = False
         Me.RCcity_label.Visible = False
         Me.RCcity_ctrl.Visible = False
         Me.RCregion_label.Visible = False
         Me.RCregion_cntrl.Visible = False
         Me.RCpostalCode_label.Visible = False
         Me.RCpostalCode_ctrl.Visible = False
         Me.RCCountry_label.Visible = False
         Me.RCCountry_cntrl.Visible = False
         Me.RCPhone_label.Visible = False
         Me.RCPhone_ctrl.Visible = False
         Me.RCFax_label.Visible = False
         Me.RCFax_ctrl.Visible = False
         Me.RcPastor_label.Visible = False
         Me.RcPastor_cntrl.Visible = False
         Me.RCOthercontact_label.Visible = False
         Me.RCOthercontact_cntrl.Visible = False
         Me.RCemailAddress_label.Visible = False
         Me.RCemailAddress_cntrl.Visible = False
        Case vbNo
            
            
        Me.Detail.Visible = False
        Me.RdivAddrs_label.Visible = False
         Me.RdivAddrs_ctrl.Visible = False
         Me.RdivAddrs2_ctrl.Visible = False
         Me.Rdivcity_label.Visible = False
         Me.Rdivcity_ctrl.Visible = False
         Me.RdivRegn_label.Visible = False
         Me.RdivRegn_ctrl.Visible = False
         Me.RdivPost_label.Visible = False
         Me.RdivPost_ctrl.Visible = False
         Me.RdivCountry_label.Visible = False
         Me.RdivCountry_ctrl.Visible = False
         Me.RdivPh_label.Visible = False
         Me.RdivPh_ctrl.Visible = False
         Me.RdivFax_label.Visible = False
         Me.RdivFax_ctrl.Visible = False
         Me.RdivEaddress_label.Visible = False
         Me.RdivEaddress_ctrl.Visible = False
         'Me.RUnionName_L_label.Visible = False
         'Me.RUnionName_L.Visible = False
         Me.RUAddress_label.Visible = False
         Me.RUaddress1.Visible = False
         Me.RUaddress2.Visible = False
         Me.RUcity_alabel.Visible = False
         Me.RUcity.Visible = False
         Me.RUpostalCode_label.Visible = False
         Me.RUpostalCode.Visible = False
         Me.RUCountry_label.Visible = False
         Me.RUCountry.Visible = False
         Me.RUPhone_label.Visible = False
         Me.RUPhone.Visible = False
         Me.RUFax_label.Visible = False
         Me.RUFax.Visible = False
         Me.RUemailAddress_label.Visible = False
         Me.RUemailAddress.Visible = False
         'Me.RRegionName_L_label.Visible = False
         'Me.RRegionName_L.Visible = False
         Me.RRAddress1_label.Visible = False
         Me.RRAddress1.Visible = False
         Me.RRAddress2.Visible = False
         Me.RRcity_label.Visible = False
         Me.RRcity.Visible = False
         Me.RRwgion_label.Visible = False
         Me.RRregion.Visible = False
         Me.RRpostalCode_label.Visible = False
         Me.RRpostalCode.Visible = False
         Me.RRCountry_label.Visible = False
         Me.RRCountry.Visible = False
         Me.RRPhone_label.Visible = False
         Me.RRPhone.Visible = False
         Me.RRFax_label.Visible = False
         Me.RRFax.Visible = False
         Me.RRemailAddress_label.Visible = False
         Me.RRemailAddress.Visible = False
         'Me.RDistrictName_local_label.Visible = False
         'Me.RDistrikName_L.Visible = False
         Me.RDisaddress_label.Visible = False
         Me.RDisAddress1.Visible = False
         Me.RdisAddress2.Visible = False
         Me.RDisCity_label.Visible = False
         Me.RDisCity.Visible = False
         Me.RDisRegion_Label.Visible = False
         Me.RDisRegion.Visible = False
         Me.RDispostalCode_label.Visible = False
         Me.RdispostalCode.Visible = False
         Me.RDisCountry_label.Visible = False
         Me.RDisCountry.Visible = False
         Me.RDisPhone_label.Visible = False
         Me.RDisPhone.Visible = False
         Me.RDisFax_label.Visible = False
         Me.RdisFax.Visible = False
         Me.RdisPastor_label.Visible = False
         Me.RdisPastor.Visible = False
         Me.RdisOthercontact_label.Visible = False
         Me.RdisOcontact.Visible = False
         Me.RDisemailAddress_label.Visible = False
         Me.RdisemailAddress.Visible = False
        End Select
    Case Else
      Cancel = True
      MsgBox "Unrecognized option!", vbExclamation  ' Should never hit this case
  End Select
End Sub


The oen that needs to still go is this, because it is the multi language:
QUOTE
Call FillReportLabels(Me.Name)


We want it to bypass if we click it from this button:

CODE


Private Sub Open_ReportSDACh_Click()
On Error GoTo Err_Open_ReportSDACh_Click
    
    Dim stDocName As String
    
    stDocName = "Rpt_SDADirectory"
    
    DoCmd.OpenReport stDocName, acPreview
    
Exit_Open_ReportSDACh_Click:
    Exit Sub

Err_Open_ReportSDACh_Click:
    MsgBox Err.Description
    Resume Exit_Open_ReportSDACh_Click
    

End Sub


I appreciate for any helps

Frank

MikeLyons
May 22 2012, 06:52 PM
I would use a global variable in a module and set that just before opening the report in your button's Click event. Inspect that variable for the value indicating that you should run the additional code using an If..Then block.

Remember to set the global variable back to the default afterward in order to prevent the code from running when you don't want it.

Mike
theDBguy
May 22 2012, 07:40 PM
Hi Frank,

Since you are using the Open event, you should be able to take advantage of the OpenArgs parameter when you open your report.

Just my 2 cents... 2cents.gif
Frank Situmorang
May 22 2012, 08:52 PM
DbGuy,


How would we say it in Open Argment DBGuy, do you have the code samples?.

Thanks if you could give me the sample.

Frank
theDBguy
May 22 2012, 10:52 PM
Hi Frank,

You could try something like this:

DoCmd.OpenReport stDocName, acPreview, , , , , "Bypass"

Then, in the Open event of the report:

If Me.OpenArgs & "" = "Bypass" Then
'skip the code
Else
Call FillReportLabels(Me.Name)
End If

Just my 2 cents... 2cents.gif
Frank Situmorang
May 23 2012, 12:25 AM
Ok DbGuy, I will try it, and let you know if it is succesfull.

Frank
Frank Situmorang
May 23 2012, 12:34 AM
DbGuy,


What is the code to skip the code

Thanks

Frank
Frank Situmorang
May 23 2012, 12:41 AM
DbGuy,

When I click the button to open the report there is an error message Box saying " Compiler error wrong number of Arguments or invalid property assignment" and there is OK and Help button at the bottom of the box.

Thanks for your help again.


Frank
BruceM
May 23 2012, 07:44 AM
QUOTE
What is the code to skip the code

You could leave it as DBGuy wrote it, with the comment to skip the code. If there is code after the If statement, if OpenArgs is "Bypass", the code will skip to the end of the If. If you don't want to run any more code in that procedure, replace the comment with Exit Sub:

CODE
If Me.OpenArgs & "" = "Bypass" Then
    Exit Sub
  Else
    etc.
End If

It looks as if you are setting the Visible property to False for all of the controls whether the response is Yes or No. Seems like a lot of duplicated effort. Also, be aware that if you hide a control or section, Access may not know that it is supposed to be visible for the next record. If the report is a single record, or if the same format applies to all of the records, this will not be a problem, but if more than one record you will probably need to set the Visible property for each one.

If all of the controls you want to hide are in a section of the report, you can just hide that section. Otherwise, for a large number of controls, you may do better to loop through them. You could set the Tag property of the controls to "Z" (without the quotes) or whatever you like. Then in the Format event for the report section (you may be able to use the Open event, depending on the details of what you need to do):

CODE
Dim ctl As Control

Select Case MsgBox "Go ahead?", vbYesNo
  Case vbYes
    For Each ctl In Me.Controls
      ctl.Visible = (ctl.Tag <> "Z")
    Next ctl
End Select


When you compile the code (Debug >> Compile in the VBA editor), what line is highlighted? If nothing, what line is highlighted when you receive the error message?
Marsupilami72
May 23 2012, 07:52 AM
A comma too much...this should work:

DoCmd.OpenReport stDocName, acPreview, , , , "Bypass"


BTW: It would be much more elegant, instead of setting identical properties für dozens of controls manually, to use the tag-property to identify the controls and run through the controls enumeration in a loop...
BruceM
May 23 2012, 08:20 AM
Good catch with the extra comma. I have a hard time remembering how many commas to use, so instead I usually do something like this:

CODE
DoCmd.OpenReport stDocName, _
  View:=acPreview, _
  OpenArgs:="Bypass"


I agree about looping through the controls. I posted code that suggests how to go about that.
Frank Situmorang
May 23 2012, 10:12 AM
Marsupilami72,

Thanks very much, it works like a charm, you are awesome. Thanks to all of you who enrich my knowledge.

Frank

theDBguy
May 23 2012, 10:41 AM
Hi Frank,

QUOTE (Frank Situmorang @ May 23 2012, 08:12 AM) *
Marsupilami72,

Thanks very much, it works like a charm, you are awesome. Thanks to all of you who enrich my knowledge.

Frank

Sorry for the delay and the extra comma. Glad to hear you got it sorted out. Good luck with your project.

Thanks for the assist, guys! thumbup.gif
Frank Situmorang
May 23 2012, 06:36 PM
DbGuy and Marsupilami72 and All,

Sorry to ask again, I found it later that when I click I applied Filter, the result is to bypass the code, like the open report filter command. This is my Code in the Applied Filter:

CODE


Private Sub cmdApplyFilterSDACh_Click()
Dim strfilter As String
    Dim strMsg As String
    Dim strOrgSel As String
    Dim ctl As Control
    Dim i As Integer
    
    ' Check that the report is open
    If SysCmd(acSysCmdGetObjectState, acReport, "Rpt_SDADirectory") <> acObjStateOpen Then
        ' Note: This is message string #1.
        strMsg = DLookup("MessageString", "[Lookup Message String_Qry]", "FormName = '" & Me.Name & "' AND StringNumber = 1")
        MsgBox strMsg
        Exit Sub
    End If
      
    'Loop  to find selected items in list box
    Set ctl = Me.Church_orgselected
    For i = 0 To ctl.ListCount - 1
        If ctl.Selected(i) = True Then strOrgSel = strOrgSel & Chr(34) & ctl.ItemData(i) & Chr(34) & ", "
    Next i
    If strOrgSel <> "" Then
        strfilter = Choose(Me.FraOrgdipilih.Value, "DivisionName_E", "[Union Name_L]", "[Region Name_L]", "DistrctName_L", "ChurchName_L")
        strOrgSel = " IN (" & Left(strOrgSel, Len(strOrgSel) - 2) & ")"
    End If

    strfilter = strfilter & strOrgSel
  
    'Apply filter to report
    With Reports![Rpt_SDADirectory]
        .Filter = strfilter
        .FilterOn = True
    End With


End Sub


I want it to work for this code in the on open report:

CODE


Private Sub Report_Open(Cancel As Integer)
On Error Resume Next
Call FillReportLabels(Me.Name)
If Me.OpenArgs & "" = "Bypass" Then
' Skip the code
Exit Sub

Else

strMsg = DLookup("MessageString", "[Lookup Message String_Qry]", "[FormName] = '" & Me.Name & "' AND [StringNumber] = 1")
  Select Case MsgBox(strMsg, vbQuestion Or vbYesNoCancel)
    Case vbCancel
      Cancel = True
      Exit Sub
    Case vbYes
      Me.Detail.Visible = True
      

    Case vbNo
    strMsg = DLookup("MessageString", "[Lookup Message String_Qry]", "[FormName] = '" & Me.Name & "' AND [StringNumber] = 2")
        Select Case MsgBox(strMsg, vbQuestion Or vbYesNo)
         Case vbYes
        
         'Me.Detail.Visible = False
         'Me.RdivNameL_label.Visible = False
         'Me.RdivNameL_ctrl.Visible = False
         Me.RdivAddrs_label.Visible = False
         Me.RdivAddrs_ctrl.Visible = False
         Me.RdivAddrs2_ctrl.Visible = False
         Me.Rdivcity_label.Visible = False
         Me.Rdivcity_ctrl.Visible = False
         Me.RdivRegn_label.Visible = False
         Me.RdivRegn_ctrl.Visible = False
         Me.RdivPost_label.Visible = False
         Me.RdivPost_ctrl.Visible = False
         Me.RdivCountry_label.Visible = False
         Me.RdivCountry_ctrl.Visible = False
         Me.RdivPh_label.Visible = False
         Me.RdivPh_ctrl.Visible = False
         Me.RdivFax_label.Visible = False
         Me.RdivFax_ctrl.Visible = False
         Me.RdivEaddress_label.Visible = False
         Me.RdivEaddress_ctrl.Visible = False
         'Me.RUnionName_L_label.Visible = False
         'Me.RUnionName_L.Visible = False
         Me.RUAddress_label.Visible = False
         Me.RUaddress1.Visible = False
         Me.RUaddress2.Visible = False
         Me.RUcity_alabel.Visible = False
         Me.RUcity.Visible = False
         Me.RUpostalCode_label.Visible = False
         Me.RUpostalCode.Visible = False
         Me.RUCountry_label.Visible = False
         Me.RUCountry.Visible = False
         Me.RUPhone_label.Visible = False
         Me.RUPhone.Visible = False
         Me.RUFax_label.Visible = False
         Me.RUFax.Visible = False
         Me.RUemailAddress_label.Visible = False
         Me.RUemailAddress.Visible = False
         'Me.RRegionName_L_label.Visible = False
         'Me.RRegionName_L.Visible = False
         Me.RRAddress1_label.Visible = False
         Me.RRAddress1.Visible = False
         Me.RRAddress2.Visible = False
         Me.RRcity_label.Visible = False
         Me.RRcity.Visible = False
         Me.RRwgion_label.Visible = False
         Me.RRregion.Visible = False
         Me.RRpostalCode_label.Visible = False
         Me.RRpostalCode.Visible = False
         Me.RRCountry_label.Visible = False
         Me.RRCountry.Visible = False
         Me.RRPhone_label.Visible = False
         Me.RRPhone.Visible = False
         Me.RRFax_label.Visible = False
         Me.RRFax.Visible = False
         Me.RRemailAddress_label.Visible = False
         Me.RRemailAddress.Visible = False
         'Me.RDistrictName_local_label.Visible = False
         'Me.RDistrikName_L.Visible = False
         Me.RDisaddress_label.Visible = False
         Me.RDisAddress1.Visible = False
         Me.RdisAddress2.Visible = False
         Me.RDisCity_label.Visible = False
         Me.RDisCity.Visible = False
         Me.RDisRegion_Label.Visible = False
         Me.RDisRegion.Visible = False
         Me.RDispostalCode_label.Visible = False
         Me.RdispostalCode.Visible = False
         Me.RDisCountry_label.Visible = False
         Me.RDisCountry.Visible = False
         Me.RDisPhone_label.Visible = False
         Me.RDisPhone.Visible = False
         Me.RDisFax_label.Visible = False
         Me.RdisFax.Visible = False
         Me.RdisPastor_label.Visible = False
         Me.RdisPastor.Visible = False
         Me.RdisOthercontact_label.Visible = False
         Me.RdisOcontact.Visible = False
         Me.RDisemailAddress_label.Visible = False
         Me.RdisemailAddress.Visible = False
         Me.RCaddress_label.Visible = False
         Me.RCaddress_ctrl.Visible = False
         Me.RCaddress2_cntrl.Visible = False
         Me.RCcity_label.Visible = False
         Me.RCcity_ctrl.Visible = False
         Me.RCregion_label.Visible = False
         Me.RCregion_cntrl.Visible = False
         Me.RCpostalCode_label.Visible = False
         Me.RCpostalCode_ctrl.Visible = False
         Me.RCCountry_label.Visible = False
         Me.RCCountry_cntrl.Visible = False
         Me.RCPhone_label.Visible = False
         Me.RCPhone_ctrl.Visible = False
         Me.RCFax_label.Visible = False
         Me.RCFax_ctrl.Visible = False
         Me.RcPastor_label.Visible = False
         Me.RcPastor_cntrl.Visible = False
         Me.RCOthercontact_label.Visible = False
         Me.RCOthercontact_cntrl.Visible = False
         Me.RCemailAddress_label.Visible = False
         Me.RCemailAddress_cntrl.Visible = False
        Case vbNo
            
            
        Me.Detail.Visible = False
        Me.RdivAddrs_label.Visible = False
         Me.RdivAddrs_ctrl.Visible = False
         Me.RdivAddrs2_ctrl.Visible = False
         Me.Rdivcity_label.Visible = False
         Me.Rdivcity_ctrl.Visible = False
         Me.RdivRegn_label.Visible = False
         Me.RdivRegn_ctrl.Visible = False
         Me.RdivPost_label.Visible = False
         Me.RdivPost_ctrl.Visible = False
         Me.RdivCountry_label.Visible = False
         Me.RdivCountry_ctrl.Visible = False
         Me.RdivPh_label.Visible = False
         Me.RdivPh_ctrl.Visible = False
         Me.RdivFax_label.Visible = False
         Me.RdivFax_ctrl.Visible = False
         Me.RdivEaddress_label.Visible = False
         Me.RdivEaddress_ctrl.Visible = False
         'Me.RUnionName_L_label.Visible = False
         'Me.RUnionName_L.Visible = False
         Me.RUAddress_label.Visible = False
         Me.RUaddress1.Visible = False
         Me.RUaddress2.Visible = False
         Me.RUcity_alabel.Visible = False
         Me.RUcity.Visible = False
         Me.RUpostalCode_label.Visible = False
         Me.RUpostalCode.Visible = False
         Me.RUCountry_label.Visible = False
         Me.RUCountry.Visible = False
         Me.RUPhone_label.Visible = False
         Me.RUPhone.Visible = False
         Me.RUFax_label.Visible = False
         Me.RUFax.Visible = False
         Me.RUemailAddress_label.Visible = False
         Me.RUemailAddress.Visible = False
         'Me.RRegionName_L_label.Visible = False
         'Me.RRegionName_L.Visible = False
         Me.RRAddress1_label.Visible = False
         Me.RRAddress1.Visible = False
         Me.RRAddress2.Visible = False
         Me.RRcity_label.Visible = False
         Me.RRcity.Visible = False
         Me.RRwgion_label.Visible = False
         Me.RRregion.Visible = False
         Me.RRpostalCode_label.Visible = False
         Me.RRpostalCode.Visible = False
         Me.RRCountry_label.Visible = False
         Me.RRCountry.Visible = False
         Me.RRPhone_label.Visible = False
         Me.RRPhone.Visible = False
         Me.RRFax_label.Visible = False
         Me.RRFax.Visible = False
         Me.RRemailAddress_label.Visible = False
         Me.RRemailAddress.Visible = False
         'Me.RDistrictName_local_label.Visible = False
         'Me.RDistrikName_L.Visible = False
         Me.RDisaddress_label.Visible = False
         Me.RDisAddress1.Visible = False
         Me.RdisAddress2.Visible = False
         Me.RDisCity_label.Visible = False
         Me.RDisCity.Visible = False
         Me.RDisRegion_Label.Visible = False
         Me.RDisRegion.Visible = False
         Me.RDispostalCode_label.Visible = False
         Me.RdispostalCode.Visible = False
         Me.RDisCountry_label.Visible = False
         Me.RDisCountry.Visible = False
         Me.RDisPhone_label.Visible = False
         Me.RDisPhone.Visible = False
         Me.RDisFax_label.Visible = False
         Me.RdisFax.Visible = False
         Me.RdisPastor_label.Visible = False
         Me.RdisPastor.Visible = False
         Me.RdisOthercontact_label.Visible = False
         Me.RdisOcontact.Visible = False
         Me.RDisemailAddress_label.Visible = False
         Me.RdisemailAddress.Visible = False
        End Select
    Case Else
      Cancel = True
      MsgBox "Unrecognized option!", vbExclamation  ' Should never hit this case
  End Select
  End If
End Sub


How can we make it to read againstarting from here:

strMsg = DLookup("MessageString", "[Lookup Message String_Qry]", "[FormName] = '" & Me.Name & "' AND [StringNumber] = 1")
Select Case MsgBox(strMsg, vbQuestion Or vbYesNoCancel)
Case vbCancel
Cancel = True
Exit Sub
Case vbYes
Me.Detail.Visible = True



Frank Situmorang
May 23 2012, 09:36 PM
Marsupilami72,

I am interested in your suggestion. Do you have the sampel of Taging property looping?.

Frank
Marsupilami72
May 24 2012, 05:40 AM
BruceM has posted a sample code already:

QUOTE (BruceM @ May 23 2012, 02:44 PM) *
CODE
Dim ctl As Control

Select Case MsgBox "Go ahead?", vbYesNo
  Case vbYes
    For Each ctl In Me.Controls
      ctl.Visible = (ctl.Tag <> "Z")
    Next ctl
End Select


In this example, every control on the form, that has set the Tag-property to "Z" will be made invisible, all others visible.

Personally, i would change it a little bit:

CODE
Dim ctl As Control

If MsgBox ("Go ahead?", vbYesNo)=vbYes then

    For Each ctl In Me.Controls
      If ctl.Tag<>"Z" then ctl.Visible = false
    Next ctl

End If


That way, all the other controls remain unaltered.
BruceM
May 24 2012, 06:58 AM
QUOTE
How can we make it to read again starting from here:

I am unclear what you mean. Read again? Do you mean you want the code to start again from that point? If so, when do you want that to occur?

The following two lines of code will accomplish the opposite thing:

ctl.Visible = (ctl.Tag <> "Z")
If ctl.Tag<>"Z" then ctl.Visible = False

If the Tag is set to "Z", the first statement will evaluate to:

ctl.Visible = False

If the Tag is something other than "Z", it will be:

ctl.Visible = True

The second statement will take no action if the Tag is set to "Z", and will hide the control if it is something other than "Z". I was thinking of setting the Tag property to "Z" for controls that are to be hidden; Marsupilami's suggestion was based on the Tag being set to "Z" for controls that are to be visible. When looping through the code I prefer to set the Visible property for every control. In the Open event it doesn't matter, but for events that run for every record, it could happen that the control was hidden for the previous record, and it needs to be visible for the current record.

I notice that some controls are hidden for both vbYes and vbNo. If it needs to be that way (hidden for both vbYes and vbNo), you could set the Tag property of those controls to "X"; for controls hidden for vbYes only, set the Tag to "Y"; and for controls hidden for vbNo only, set the Tag to "Z". Then the test for vbYes could be:

ctl.Visible = Not (ctl.Tag = "X" Or ctl.Tag = "Y")

The test for vbNo could be:

ctl.Visible = Not (ctl.Tag = "X" Or ctl.Tag = "Z")

Or you could use the If...Then method. The thing to keep in mind with two possibilities is that you must use = (equals); <> will not work. If the Tag is something other than "X", it could be "Y"; if it is something other than "Y", it could be "X". The following will always be True:

(ctl.Tag <> "X" Or ctl.Tag <> "Z")



Frank Situmorang
May 24 2012, 08:59 AM
Thanks Bruce for your explanation. Let me explain in more details:

In my filter form there are 3 command buttons:

1. Open Report button
2. Applied Filter button
3. Remove Filter Bjutton

For the Open Report Button, I do not want to ask for option of visible part of the details or not, since the purpose is just to open the report first for the Applied Filter Button to work (see this code below)
CODE
If SysCmd(acSysCmdGetObjectState, acReport, "Rpt_SDADirectory") <> acObjStateOpen Then
        ' Note: This is message string #1.
        strMsg = DLookup("MessageString", "[Lookup Message String_Qry]", "FormName = '" & Me.Name & "' AND StringNumber = 1")
        MsgBox strMsg
        Exit Sub
.

Ok OpenArgument is the way not to give the option of showing details or not.
But my problem now is that when I press the Button of Applied Filter, there is no more question to aks us whether to show or not the details or some of the details ( not all details).

I appreciate your help again. I want it when I press the Applied Filter Button it still gives us a choice wheterh to show some details or not. Probably I am still wrong in the Code. Or could we have more than one Open argument ?.

Frank
BruceM
May 24 2012, 10:34 AM
QUOTE
the purpose is just to open the report first

You need to filter a report before it is opened. Opening a report is the same as printing it. If you want to revise the output, you need to print (open) the report again.

I like your technique for message box messages, but since I can't see the message I don't know what you are asking. I will just say that I would prefer to set up the report parameters before opening the report. If a filter is to be applied, that should be passed as the filter argument of OpenReport. Values used for formatting such as hiding controls could be passed in OpenArgs. If I knew what you are asking with the message boxes (the ones in the report's Open event) I may be able to offer a suggestion.
Frank Situmorang
May 24 2012, 06:27 PM
Bruce,


Ok since it is in multilanguage, it is not show in the code, but the message is in Msg table, message no. 1 that says : "Do you want to see the Details?"

And then in the 2nd vbYes/no question says " You want to show the name of the Church??"


Thanks for your giving me suggestion.

Frank
Frank Situmorang
May 25 2012, 01:24 AM
Bruce,

Can we pass the parameter from button1 as "Bypass", and the Applied Button as Bypass2 then the "If OpenArgs" in Openevent of the report can work?

Frank
BruceM
May 25 2012, 08:03 AM
For one of the buttons, something like:

DoCmd.OpenReport stDocName, _
View:=acPreview, _
OpenArgs:="Bypass"

For the other button, OpenArgs is "Bypass2".

Then you could do something like this in the report's Open event:

CODE
Dim ctl     As Control
Dim strArgs As String

strArgs = Nz(Me.OpenArgs,"")

Select Case strArgs
  Case "Bypass"
    For Each ctl In Me.Controls
      ctl.Visible = Not(ctl.Tag = "X" Or ctl.Tag = "Y")
    Next ctl
  Case "Bypass2"
    For Each ctl In Me.Controls
      ctl.Visible = Not(ctl.Tag = "X" Or ctl.Tag = "Z")
    Next ctl
  Case Else
    ' Whatever is needed
End Select


"X" is the tag for controls that are hidden in both situations. "Y" and "Z" are the tags for controls that are hidden in one situation or the other, but not both. You can use anything you like for OpenArgs or the tags.

Frank Situmorang
May 26 2012, 07:58 PM
Bruce,

It seems it is diffictult for me to figure out. First because of assinging visible and and visible control to Z & Y, while my method is not like that.

But let me try to study.

Thnaks very much,


Frank
BruceM
May 29 2012, 06:36 AM
For future reference, when posting in UA it is helpful if you show just a few examples. In this case, the listing of all controls makes it a little difficult to figure out what is supposed to happen. I had understood that some controls are to be hidden if OpenArgs is Bypass, other controls are to be hidden if OpenArgs is Bypass2, and other controls are hidden for both OpenArgs values. If so, a few controls would be enough to show that.

I realize there are other tests that need to occur. I tried to show an example of looping through the controls, but it will not replace other tests. That is, in your original code there is a Yes/No/Cancel message box. If the answer is Cancel, don't open the report. If it is Yes, show the Detail section. If it is No, ask another question, then loop through the controls. Looping through the controls is supposed to replace the long list of controls. I had assumed the rest of the code would stay the same.
This is a "lo-fi" version of our main content. To view the full version with more information, formatting and images, please click here.