Form VBA w/A2013

[Dick7Access]

I have a Menu form that has 5 cmdbuttons that opens the same main forms but with 5 different filters. I want a label or text box to change to the name of the filter. I am using VBA to open the form. I can’t code a form that’s not open yet. Is there any way to accomplished the above?

 

[vbaInet]

Or you could:
1. Set it after opening the form:

docmd.openform ...
Forms!FormName.LabelName.Caption = "filter"

Or
2. Pass it via OpenArgs

 

[Dick7Access]

I tried that VB,

With Forms(strFrmName)
    lblFilter.Caption = "filter"
    End With

but it didn't work...

Then I see your answer and realised why:-

With Forms(strFrmName)
    .lblFilter.Caption = "filter"
    End With

I left the damned period off... time for bed me thinks ....

My aunt missed a period when i was a kid and the whole family went crazy, and we didn't even have a computer. <G>

 

[vbaInet]

Those pesky dots!

 

[Dick7Access]

Yes... Periods can cause all sorts of problems. I prefer "Full Stop" myself, and really, that's a better description if you think about it.

I have a bigger problem right now. I am in MS and there are at least 5 buffets within 10 miles. Decisions, decisions, decisions.

 

[vbaInet]

So do I. I think Americans prefer period.

Dick7Access, what happened when you missed your period?

 

[Dick7Access]

So do I. I think Americans prefer period.

Dick7Access, what happened when you missed your period?

very funny <G>

 

[Dick7Access]

Or you could:
1. Set it after opening the form:

docmd.openform ...
Forms!FormName.LabelName.Caption = "filter"

Or
2. Pass it via OpenArgs

Forms!FormName.LabelName.Caption = "filter"

how would the form know what filter cmd I had selected?

Or
2. Pass it via OpenArgs

[/QUOTE]
I will have to look up my notes on "OpnArgus" It has been years since I used it.

 

[vbaInet]

I thought that you're opening another form, is that not the case?

If you want to apply a filter on the same form where the command buttons are located, you don't need to close and reopen the form.

OpenArgs is one of the parameters in OpenForm.

 

[Dick7Access]

I thought that you're opening another form, is that not the case?

If you want to apply a filter on the same form where the command buttons are located, you don't need to close and reopen the form.

OpenArgs is one of the parameters in OpenForm.

Right, but my menu form has 5 cmd, and nothing else, no data. Each cmd opens the same main form which has the data, but filters it according to the cmd. I have an older version of this db where I did put all the cmd's on the main form and was able to change the lbl without any problem. I am changing it around because I didn't like all the clutter on the main form, plus practicing VBA. I haven't been able to try anything since you guys posted, as I am having to break down the db by states to send it via email to a person in AZ.

 

[vbaInet]

Ok, you have a menu form and a main form... that was my original thinking. So yes, for you, it's easier to re-open the form.

In order to grab the filter it all depends on how the filter is being passed?

 

[Dick7Access]

UG
This is what is on cmd button on the menu form that opens main data form called “frmChurchesAllDick” filtered by only supporting churches

  Private Sub cmdSupportingCh_Click()                                   
  Dim strFrmName As String
  strFrmName = "frmChurchesAllDick"
      DoCmd.OpenForm strFrmName    
      With Forms(strFrmName)
      .fX ("Support")
      End With
  End Sub

This is what is on the vba of form “frmChurchesAllDick”

  Public Function fX(strPassed As String)
      Me.lblFilter.Caption = strPassed
  End Function

This is Where it hangs up

  With Forms(strFrmName)
      .fX ("Support")
      End With

What do I have wrong?

 

[Dick7Access]

What happens if you comment out the line:-

'fX("Support")

crashes here

Private Sub Form_Load()
Public Function fX(strPassed As String)

Me.lblSupportingCH.Caption = strPassed

End Function

 

[Dick7Access]

Is lblFilter a label? It could be a textbox, which don't have a caption property.

It's a Label

 

[Dick7Access]

Ah, you have put the public function inside a private function, therefore it is not visible to the calling procedure. Make it a separate procedure within your forms module and I suspect it will work.

Now I thought I had put it as a public back when I looked at it, it was private and did not look right. I will give it another try. Thanks

Ps. I have some people who say why do you spend so much time figuring out how to code. My answer is in one respect the same reason you spend so much time figuring out a cross-word puzzle. When I get finish I have something that will help me and help other. When you get finish you have a piece of paper with words on it.

 

[Dick7Access]

we have lift off!
Thanks

Ps. Am I suppose to mark this solved somepalce?

 

[Dick7Access]

UG
Here is the finish product. 4 more cmd’s to go. In case you are wondering why I have stuff named Dick, I have to have the same code opening a form called “Jenny” my wife doesn’t like my colors or arrangement. When on rare occasions that I give the whole db away I change the names to something generic.
For a laugh take a look at the message box in case it crashes for my wife.

  '------------------------------------------------------------
  ' cmdSupportingCh_Click via Dick's Main menu
  'opens church form filtered with just supporting churches
  '------------------------------------------------------------
  Private Sub cmdSupportingCh_Click()
    On Error GoTo errorHandlerSuppCh_SPC     ' SPC stands for supporting churches
  Dim strFrmName As String
  strFrmName = "frmChurchesAllDick"
      DoCmd.OpenForm strFrmName
      DoCmd.ApplyFilter "qrySupportingCh"
      With Forms(strFrmName)
      .fX ("Supporting Churches")
      End With                                 
     DoCmd.Close acForm, "frmMainMenuDick", acformyes
      Exit Sub
  errorHandlerSuppCh_SPC:
     MsgBox "An error has accrued.  You will be taken back to 'Welcome' screen.   If it happens
 again try closing the program and re-opening it.  If that doesn't work call Dick", vbDefaultButton2
     DoCmd.OpenForm "frmSplash"
  End Sub

 

[vbaInet]

It is quite a bit of code just to change the caption property isn't it. But like suggested earlier, you could have just done it in one line of code:

Forms(strFrmName).Controls("[COLOR="Blue"]LabelName[/COLOR]").Caption = "[COLOR="blue"]Supporting Churches[/COLOR]"

 

[Dick7Access]

It is quite a bit of code just to change the caption property isn't it. But like suggested earlier, you could have just done it in one line of code:

Forms(strFrmName).Controls("[COLOR=Blue]LabelName[/COLOR]").Caption = "[COLOR=blue]Supporting Churches[/COLOR]"

You lost me again VB, how is main form going to pick that up :supporting churches" from menu form.
If there is a way, I would like to know as half of this effort is to learn more VBA. When I made this db 20 some years ago, I had no VBA in it. I did every thing with macros. It is a huge job but I am learning more as I convert it.. BTW before I come to AWF I try goggling it, but the problem there is knowing what to Google.

 

[vbaInet]

It's good that you're learning VBA.

Forms is a collection of loaded forms in your db. A loaded form is one that is open in any view, i.e. design, single form, continuous form etc. When you enter the name of a form in Forms() you are referring to that particular form; regardless of which form the code is being run. The same thing goes for Controls(); it's a collection of controls in the form.

Now in your case, strFormName is a variable that holds the name of the menu form that you're opening, so in order to reference the label in the menu form we first refer to the form (i.e. Forms(strFormName)), followed by a dot, and then the name of the label (i.e. Controls("Name of label")). If you put all that together you get the code above.

I hope it makes sense.