423,682 Members | 1,352 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 423,682 IT Pros & Developers. It's quick & easy.

VB in Access 2013

P: 2
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:
Expand|Select|Wrap|Line Numbers
  1. Private Sub cmdCO_Click()
  2.    DoCmd.OpenForm "qdffrmINVMF3713", , , Form.Caption = "Cost Open"
  3.    Forms!qdffrmINVMF3713.RecordSource = "qryISSUE1costopen"
  4. End Sub
  5.  
  6. Private Sub cmdPP_Click()
  7.     DoCmd.OpenForm "qdffrmINVMF3713", , , Form.Caption = "Partial Payments"
  8.    Forms!qdffrmINVMF3713.RecordSource = "qryISSUE2ppfrm"
  9.  
  10. 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!
Sep 13 '18 #1
Share this Question
Share on Google+
2 Replies


Nauticalgent
P: 73
On the form's OnOpen event, try:
Expand|Select|Wrap|Line Numbers
  1. Select Case Me.RecordSource
  2.      Case "qryISSUE1costopen"
  3.           Me.Caption = "Cost Open"
  4.      Case "qryISSUE2ppfrm"
  5.           Me.Caption = "Partial Payments"
  6.      Case Else
  7. End Select
  8.  
Sep 14 '18 #2

NeoPa
Expert Mod 15k+
P: 31,084
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 :
Expand|Select|Wrap|Line Numbers
  1. Call DoCmd.OpenForm(FormName:="qdffrmINVMF3713" _
  2.                   , OpenArgs:="qryISSUE1costopen;Cost Open")
That way, in your Form_Open() event procedure you can include :
Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_Open(Cancel As Integer)
  2.     Dim astrOA() As String
  3.  
  4.     With Me
  5.         astrOA = Split(Nz(.OpenArgs, ""), ";")
  6.         If UBound(astrOA) < 1 Then
  7.             Cancel = True
  8.             'Error message if required.
  9.             Exit Sub
  10.         End If
  11.         .RecordSource = astrOA(0)
  12.         .Caption = astrOA(1)
  13.     End With
  14. End Sub
Sep 14 '18 #3

Post your reply

Sign in to post your reply or Sign up for a free account.