Form VBA w/A2013

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

 

[Uncle Gizmo]

I think it's possible to mark it as solved, although I'm not sure how.

I'm not sure on the moderators policy on marking it solved even though I am a moderator!

 

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

 

[Uncle Gizmo]

I agree with vb. I showed a different way of doing it, because I couldn't get the correct way to work.

Knowing you can call a public function within another form is a good tool to add to your box of knowledge.

I use the method when I pass variables via custom properties of a form. Strangely the forms "on load" and "on open" Events happened before the custom properties are set which is very annoying.

The other time I have found it useful to call a public function is when using a generic calendar form to add dates to a textbox, you lose the ability to move to the next control automatically after the date is added via the agency of the after update event (the event doesn't fire) however you can imitate the after update event by calling your own function.

The purpose so that the user is automatically moved to the next control in the sequence of controls for data entry. For example opening a combo box and dropping it down, not possible from a normal popup form and not possible from the date entering facility offered by Microsoft Access.

 

[vbaInet]

I use the method when I pass variables via custom properties of a form. Strangely the forms "on load" and "on open" Events happened before the custom properties are set which is very annoying.

What do you mean by custom properties Uncle G?

 

[Dick7Access]

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.

I am starting to get it. does both forms have to be open (loaded)?

 

[Dick7Access]

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.

I was starting to get it but then I noticed you mention [label in menu form] The label is in the main data form. The string I put in the fx [.fX ("Supporting Churches")] is what needs to show up in the lbl on the main form. Please lay it on me again with that thought in mind

 

[vbaInet]

Yes you were getting it, that was supposed to say "Main form".

If you want to drive a car, you have to get in the car to drive it right? Then once you're in the car, you can change gears, change the direction of the steering wheel etc. Now the form is your car, in order to change the property of one the controls in the car, you need to first access that form. That's why you do this Forms(strFormName) to access the form, followed by a dot, then to access the controls, i.e. textbox; label etc, you write Controls("Label Name") afterwards.

You set the caption after opening the form.

 

[Dick7Access]

Yes you were getting it, that was supposed to say "Main form".

If you want to drive a car, you have to get in the car to drive it right? Then once you're in the car, you can change gears, change the direction of the steering wheel etc. Now the form is your car, in order to change the property of one the controls in the car, you need to first access that form. That's why you do this Forms(strFormName) to access the form, followed by a dot, then to access the controls, i.e. textbox; label etc, you write Controls("Label Name") afterwards.

You set the caption after opening the form.

Thanks, think I got it. Oh! in case I crash, where's the brake pedal

 

[vbaInet]

Just reach for the brakes, you're in the car already

 

[Dick7Access]

VB

What am I missing? Opens but doesn't change label.

  '------------------------------------------------------------
  ' TRYING SHORT VBA
  ' cmdSupportingCh_Click via Dick's Main menu
  'opens church form filtered with just supporting churches
  '------------------------------------------------------------
  Private Sub cmdSupportingCh_Click()                                  
  Dim strFrmName As String
  strFrmName = "frmChurchesAllDick"                                
     DoCmd.OpenForm strFrmName
     DoCmd.ApplyFilter "qrySupportingCh"
    Forms(strFrmName).Controls("lblSupportingCH").Caption = "Supporting Churches"                              '  DoCmd.Close acForm, "frmMainMenuDick", acformyes
  End sub

 

[vbaInet]

Check that the Visible property of the label is set to True.

 

[Dick7Access]

Check that the Visible property of the label is set to True.

Funny you should say that, as I am working on a copy. It did have the visible property as no. I found that right away. I thought sure that is what is was going to be, but it wasn't.

 

[vbaInet]

So after setting it back to Yes you're back up and running?

 

[Dick7Access]

So after setting it back to Yes you're back up and running?

No, I reset to yes before you suggested it. It opens but doesn't change the label. It show the generic name that I have in there.

 

[vbaInet]

Something isn't right. Can you upload a db with just the two forms attached Dick7?