VB in Access 2013

Collapse
X
 
  • Time
  • Show
Clear All
new posts
  • MC42015
    New Member
    • Sep 2018
    • 22

    VB in Access 2013

    I am trying to use the where condition to assign the caption to the form I am opening through DoCmd.OpenForm triggered by command button OnClick.
    This form is a template I assign 4 different record sources (you'll see in part of the code), the caption in property sheet is blank.
    I would like it to show me different captions depending on the button I pushed to open
    Sample code of two of the buttons I've written so far:
    Code:
    Private Sub cmdCO_Click()
       DoCmd.OpenForm "qdffrmINVMF3713", , , Form.Caption = "Cost Open"
       Forms!qdffrmINVMF3713.RecordSource = "qryISSUE1costopen"
    End Sub
    
    Private Sub cmdPP_Click()
        DoCmd.OpenForm "qdffrmINVMF3713", , , Form.Caption = "Partial Payments"
       Forms!qdffrmINVMF3713.RecordSource = "qryISSUE2ppfrm"
    
    End Sub
    (I also tried Me.Caption but that didn't work)

    Form is opening with proper record source, but I can't get the where to trigger
    Imagine I am missing syntax?

    Thank you!
  • Nauticalgent
    New Member
    • Oct 2015
    • 109

    #2
    On the form's OnOpen event, try:
    Code:
    Select Case Me.RecordSource
         Case "qryISSUE1costopen"
              Me.Caption = "Cost Open"
         Case "qryISSUE2ppfrm"
              Me.Caption = "Partial Payments"
         Case Else
    End Select

    Comment

    • NeoPa
      Recognized Expert Moderator MVP
      • Oct 2006
      • 32662

      #3
      You seem to imagine that you can simply write an expression after a few commas (,) and the procedure will necessarily process that for you intelligently. It really doesn't work that way at all.

      DoCmd.OpenForm( ) has a set of parameters, many of which are optional. The seventh parameter is called OpenArgs and can be used to pass a string across to the Form. Let's forget about positional parameters though (See How to Use Named Arguments). They're just a problem waiting to happen for procedures like this one.
      Use something like :
      Code:
      Call DoCmd.OpenForm(FormName:="qdffrmINVMF3713" _
                        , OpenArgs:="qryISSUE1costopen;Cost Open")
      That way, in your Form_Open() event procedure you can include :
      Code:
      Private Sub Form_Open(Cancel As Integer)
          Dim astrOA() As String
      
          With Me
              astrOA = Split(Nz(.OpenArgs, ""), ";")
              If UBound(astrOA) < 1 Then
                  Cancel = True
                  'Error message if required.
                  Exit Sub
              End If
              .RecordSource = astrOA(0)
              .Caption = astrOA(1)
          End With
      End Sub

      Comment

      Working...