Calling Module from a form.

[Perfac]

1.It is around 7 years since I started learning Access, although maintaining life took most of the time of course.
2.I downloaded and watched a serious amount of video clips, and many good people taught me stuff on the forums.
3.I would like to learn about setting up a module and then call it from a form, or query.


4.I am sure the clips I got covers it, but they are lengthy, and I do not always connect immediately since there are a lot, I have not learnt yet.
5.If someone can refer me to easy training on the subject I will appreciate.
6.I hope the example I add here is a little easy, and it may go a long way teaching me something.
7. My app has more than 100 main forms. When on the last record, the NEXT button creates a new record when clicked, it shouldn't, so I solved that by adding the code below for that not to happen. This code is the same on every form, the next button always named btn13NextRecord . There are a few items like this where one module can be set up, and then with short code be called to the forms.

Private Sub btn13NextRecord_Click()
With Recordset
If .AbsolutePosition = .RecordCount - 1 Then
'you are on the last record
MsgBox "Sorry, this is the last Record.", vbInformation
Else
'you are on some other record
DoCmd.GoToRecord , , acNext
End If
End With
End Sub

This post is asking someone to show me exactly how the code in module should be, and how does the VBA look that call it to the form, even though I should be able to do the form part myself.

[Edgar]

There may be a lot of ways to accomplish this. Take a closer look at your code, it needs a Recordset. If you use that same code in a module, it will throw an error complaining that Recordset is not defined, because modules have no Recordsets, but Forms do.

What can we do to let our code work with a Recordset? In a module, we create a subroutine that can take a Recordset as a parameter. It can look like this:

Sub MyNextRecord(rst As Recordset)
    With rst
        If .AbsolutePosition = .RecordCount - 1 Then
            'you are on the last record
            MsgBox "Sorry, this is the last Record.", vbInformation
        Else
            'you are on some other record
            DoCmd.GoToRecord , , acNext
        End If
    End With
End Sub

Notice it looks almost exactly like yours, but it takes a Recordset as a parameter. So, how do we use this? We bind it to the click event of a button, like it used to be, but now we'll use the module's code instead like this:

Private Sub btn13NextRecord_Click()
    MyNextRecord Me.Recordset
End Sub

Since we are calling that subroutine from the Form, and the Form has a Recordset, we simply use that Recordset there. This is not the only way to get the Recordset, but it is very simple and required the least amount of code modification.

This code can be used in all of your main forms.

[Edgar]

You can even take it a step further if you have a back button and use this modification, which would effectively browse back or forwards, and even jump a set amount of records. This subroutine will take the recordset as parameter and a steps parameter to know where you want to go and how. So now we can provide 2 arguments from the Form code that make it all more dynamic and it will help reduce the amount of code in your Form.

Sub RecordNavigator(rst As Recordset, steps As Long)
    With rst
        Dim newPosition As Long
        newPosition = .AbsolutePosition + steps

        If newPosition < 0 Or newPosition >= .RecordCount Then
            MsgBox "Invalid record position."
        Else
            .Move newPosition - .AbsolutePosition
        End If
    End With
End Sub

And you can call it like this:
From Next record button
RecordNavigator Me.Recordset, 1

From Previous record button
RecordNavigator Me.Recordset, -1

From, say, a textbox where you let the user choose how many records to skip
RecordNavigator Me.Recordset, 3

And so on...

[Edgar]

It is difficult to talk about the whole extent of modules and classes in a form post, but I suppose someone will have a link to a good source of information on how to keep on learning. For now, let me know if this is clear or not.

[June7]

I have this procedure:

Public Sub ViewData(strDirection)
'called by forms used to view and navigate records

If Not IsNull(Form_SampleInfo.tbxViewDataFormNAME) Then
    DoCmd.Close acForm, Form_SampleInfo.tbxViewDataFormNAME, acSaveNo
End If

With Form_SampleInfo

.RecordsetClone.Bookmark = .Bookmark
Select Case strDirection
    Case "Quit"
        DoCmd.Close acForm, "SampleInfo", acSaveNo
    Case "Next"
        .RecordsetClone.MoveNext
        If Not .RecordsetClone.EOF Then
            DoCmd.GoToRecord acForm, "SampleInfo", acNext
        Else
            .RecordsetClone.MoveLast
            MsgBox "Last record."
            .btnNext.Enabled = False
        End If
        .btnPrevious.Enabled = True
    Case "Previous"
        .RecordsetClone.MovePrevious
        If Not .RecordsetClone.BOF Then
            DoCmd.GoToRecord acForm, "SampleInfo", acPrevious
        Else
            .RecordsetClone.MoveFirst
            MsgBox "First record."
            .btnPrevious.Enabled = False
        End If
        .btnNext.Enabled = True
End Select

End With

End Sub

One example of calling:

Private Sub btnNext_Click()
Me.btnViewLabData.SetFocus
Call ViewData("Next")
End Sub

It's been so long since I wrote this, I am actually a little unclear about exactly how it works so am analyzing now. I think it is designed to only work with stand-alone form, not subform.

[Perfac]

Thank you, Edgar. I learnt something. There are a couple of actions more. I will probably be able to do them now.

[Perfac]

Thank you June7, I will look well at what you sent.

[Perfac]

Edgar, the other buttons work correct on the macro it is connected to when placed.

[Perfac]

It may be easy for the more experienced, but it would have saved me many hours if I knew this a couple of years ago. Thank you.
There are about 20 buttons doing the same on every form.
There is one button named btn08TutorialScript with the VBA below. The button opens a WORD document which is stored outside my app. I want to do the same as what we discussed previously, but the WORD document name is f6FieldNames in this case. On every form of course there is a different name for the WORD document. If there is a way to link that area to text field, or some field, then I can also set up a separate Module here.

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

[June7]

Sure, can concatenate a field to literal string to build document file path. If field is in form's RecordSource, just reference it.

[CJ_London]

There are about 20 buttons doing the same on every form.

depending on where these buttons are, one of the things I do is to have a subform to contain the buttons such as you show in post #8.

The code behind the buttons then simply refers to the parent form objects/recordsets. Just to paraphrase your code in post #1 it would look like

Private Sub btn13NextRecord_Click()
    With Parent.Recordset
        If .AbsolutePosition = .RecordCount - 1 Then
            'you are on the last record
             MsgBox "Sorry, this is the last Record.", vbInformation
        Else
'            you are on some other record
             DoCmd.GoToRecord , , acNext
         End If
    End With
End Sub

You use the same subform on all your forms, so design is consistent and any mods only need to be made once

[Perfac]

June7.
Let us say all the different forms have a field with same fieldname "FormName" in the record source. The different names of the forms will also be the names of the WORD documents. Sorry, I do not know how to do what you are saying.

[Perfac]

Hi CJ. Thanks, for me learning of your idea.
1. I could have 10 items of VBA code that are on every of the more than 100 forms that are the same. Excluding, First, Previous and last buttons.
2. Those 10 "paragraphs" of code could all be placed on one module, if that is what I choose. Then have 10, 3 line "events" that can be copied at once to each form.
3. Almost every form in my app has a sub form, but it is just a list for viewing, there are only a few that have a sub query with parent and child records. Invoices, etc.
4. I wonder if adding another sub form, even if not visible, will not make the form more "heavy".

[CJ_London]

Not sure why you think the subform would not be visible - it has the buttons to execute the code

[Perfac]

Sorry. You said that clearly, and I understand now not an hour ago. I will consider it; it sounds good. From starters 7 years ago to the current 10 buttons were placed in the form header, and 11 in the footer. The furniture in my head will have to be rearranged as I see it now.