Calling Module from a form.

[moke123]

As Edgar said, there are many ways to accomplish things in access.

I use a navbar class to navigate form recordsets. This includes several options to either disable the next/previous buttons, cycle the records, or fire a messagebox. An optional record count label. An optional new record button. All buttons are optional except next and previous. Works with main forms and subforms.

3 lines of code in your form

'in declarations section

     Dim clsNB As clsFormNavBar      

'In form load event

    Set clsNB = New clsFormNavBar
    clsNB.InitCls Me, Me.cmdPrev, Me.cmdNext, Me.cmdFirst, Me.cmdLast, Me.cmdNew, Me.LblCount, Disable

Heres an example showing various configurations.

[Perfac]

CJ. I assume June is very busy. I am keen to learn what he advised me to do in #10. If you read what I wrote in #9, can you help? I need to be spoon fed on that, i.e. the exact steps.

[Welshgasman]

Very nice moke123.

I cannot get a messagebox up on classbar 3 though?
I can go to the last record and if I press next, nothing.
It does work on NavBar4 though?

Edit: In fact neither xPrev or XNext do anything for me? In debug, I put a breakpoint on the next sub in the class and I never get there?

[CJ_London]

I presume you are referring to this

mydoc = "D:\Attachments\InformationPages\f6FieldNames.docx"

if so June was suggesting


mydoc = "D:\Attachments\InformationPages\” & f6FieldNames & “.docx"

[Perfac]

Yes. Do you get what I am asking? Any way that will have the required result will do.

[Edgar]

If you want to make this work from a module:

Private Sub btn08TutorialScript_Click()
    Dim mydoc As String
    mydoc = "D:\Attachments\InformationPages\f6FieldNames.docx"
    Call Openword(mydoc)
End Sub

You could do something like this from a module, it's what June7 referred to:

Sub OpenTutorial(tutName As String)
    Dim mydoc As String
    mydoc = "D:\Attachments\InformationPages\" & tutName & ".docx"
    Call Openword(mydoc)
    'or get rid of mydoc and do this:
    'Call Openword("D:\Attachments\InformationPages\" & tutName & ".docx")
End Sub

Now, since you have a different tutorial name in every Form and it can be either in a textbox or a field, if you want to use the textbox:

Private Sub btn08TutorialScript_Click()
    OpenTutorial Me.Controls("textboxName").Value
End Sub

And if you want to use a Field:

Private Sub btn08TutorialScript_Click()
    OpenTutorial Me.Recordset.Fields("fieldName").Value
End Sub

I'm being explicit about the source here, but this should work:

Private Sub btn08TutorialScript_Click()
    OpenTutorial Me.SomeName.Value
End Sub

Just be aware that there is something funny happening in this last piece of code.
If there is a control named "SomeName", then it will pick its value with this code.
If there is a field named "SomeName", then it will pick its value with this code.
If both a control and a field were named "SomeName", then Access will use the textbox value. Be aware that there are priorities with this particular syntax, that's the funny thing.

[davegri]

Very nice moke123.

I cannot get a messagebox up on classbar 3 though?
I can go to the last record and if I press next, nothing.
It does work on NavBar4 though?

Edit: In fact neither xPrev or XNext do anything for me? In debug, I put a breakpoint on the next sub in the class and I never get there?

Nothing wrong with Class code itself, just an invocation error on the form. The red original was clsNB3.

Option Compare DatabaseOption Explicit


Dim clsNB As clsFormNavBar
Dim clsNB2 As clsFormNavBar
Dim clsNB3 As clsFormNavBar
Dim clsNB4 As clsFormNavBar




Private Sub Form_Load()


    Set clsNB = New clsFormNavBar
    clsNB.InitCls Me, Me.cmdPrev, Me.cmdNext, Me.cmdFirst, Me.cmdLast, Me.cmdNew, Me.LblCount, Disable


    Set clsNB2 = New clsFormNavBar
    clsNB2.InitCls Me, Me.btPrev, Me.btNext, Me.btFirst, Me.btLast, , Me.LblCount, Cycle


    Set clsNB3 = New clsFormNavBar
    clsNB3.InitCls Me, Me.xPrev, Me.xNext, Me.xFirst, Me.xLast, , Me.LblCount, MessageBx
    
    Set clsNB4 = New clsFormNavBar
    clsNB4.InitCls Me, Me.btoP, Me.btoN, , , , Me.LblCount, MessageBx




End Sub

[moke123]

Very nice moke123.

I cannot get a messagebox up on classbar 3 though?
I can go to the last record and if I press next, nothing.
It does work on NavBar4 though?

Edit: In fact neither xPrev or XNext do anything for me? In debug, I put a breakpoint on the next sub in the class and I never get there?

My Bad GasMan.

I added the last set of buttons just before I posted and did it by copy and paste.

I had

    Set clsNB4 = New clsFormNavBar
    clsNB3.InitCls Me, Me.btoP, Me.btoN, , , , Me.LblCount, MessageBx

I didn't change the second line clsNB3 should have been clsNB4

    Set clsNB4 = New clsFormNavBar
    clsNB4.InitCls Me, Me.btoP, Me.btoN, , , , Me.LblCount, MessageBx

I made the correction and replaced the file above in post #16

[Welshgasman]

Well spotted. I missed that completely.

Edit: Strangely, the first and last worked fine?

[Perfac]

Thank you very much Edgar. I will test and reply to you, it looks good. My protocol is to never name a control the same as a field. If the fieldname is EmployeeName, the control will be named txtEmployeeName. I have a separate table storing the almost 3000 fieldnames, not two with the same name. With what was discussed in this post, of course some buttons will have the same name on different forms. And now, when this works, I assume the field and control that stores the WORD document name, will have the same name, on all forms. No problem??

[Edgar]

when this works, I assume the field and control that stores the WORD document name, will have the same name, on all forms. No problem??

If I understood well, in every form, the field and control will be named differently but each form will have them with the same names, if that is so, then there should not be any problem, the code in the module can be called from any of the 100 forms. But even if the names were all distinct in all forms, you could simply take the field or control names as parameters for the subroutine so when you call the subroutine from a form, you provide the field or control as argument and it runs with no issue.

There is also the possibility that all 100 forms can be reduced to only one, considering a lot of stuff repeats. If that's the case, then you should consider a different design if you have the time for that.

[Perfac]

It seems you understand correct. I will start to aplly that now.

Been elsewhere for 7 hours, caught some sleep. From which country are you? Smile, I do not have immediate insight how my app can work with one form only. It is only the 21 buttons (10 in the header, 11 in the footer) that are the same, having the same function and as buttons being named the same. The detail area differs 100% on each form. I retired recently, since 60 arrived. I enjoy this now as a hobby, and I am happy to learn what I did in this post. If my app is on a network, and multiple users use it, I would like to understand how one form will do the job. Over 32 years I owned 3 medium sized businesses, 2 still exists and using my app. I think my app is dynamic, but far from perfect, and there are some exciting plans for it in the not-so-distant future.
Hans.

[Edgar]

What kind of network are you speaking of? are you planning to move this to a web environment? The suggestion of placing everything in one form derives from the 21 buttons you are currently using. Maybe having one form with those 21 buttons and a subform control in the middle switching forms would be better. Coming from my experiences with another framework called ReactJS, that possibility came to mind.

By the way, I am currently in Mexico, Hans. Not much Access work over here, but writing about it helps me relax. Being 35 I'm still far from retirement, but it's great to know you're enjoying your retired life and also improving your apps and gaining knowledge as a hobby. As you know, software development can be an endless journey, but as long as the things we make function well and bring us joy and pride, we can consider it a successful endeavor. Speaking of which, achieving success in one business is impressive enough, but three of that size, I will take any advice you can throw at me, honestly.

So I look forward to hearing more about your app and your future plans, I'm here to help and learn as well.

[Perfac]

Here is one of the VBA "paragraphs" I am trying to place on one module, and call it from the forms. You can see the error, what is the fix?
That VBA was always on the event Form_Open and it worked. I am calling it from that event; by UserPermission Me.Recordset.

[Welshgasman]

Why do you Dim AllowEdits as a string, then set it to False. Same with the others.