Calling Code from a Module

[Perfac]

1. Lately I appreciate a lot of help on calling code from Modules.
2. My self training is not sufficient for me to be as skilled as some of you, but I can refer to the ones I was taught.


3. I have one more "call" from Module, that I need to learn how.
4. On the attachment open form f6FieldNames. The code that I have on the form works.
5. In the top right corner, the first of ten button named btn01DiaryScript's code is at the bottom of the VBA page.
6. Please help me with the code on a Module, and then to call it from this button, doing the same.
7. When you opened f6FieldNames, you should have seen the word "SubjectF" in the field called "Field Name".
8. If you click on button one it opens form "f1RecordingsDiary".
9. On the form f1Recordingsdiary you should see the word "SubjectF" is brought forward through Openargs.


It all works exactly as I want it now. But I want to change it that when I click on button one, the code must be called from a module.

[Edgar]

Your Form's code requires a Form object (Me) to work. All you need to do is reference it from the module. The rest stays the same.

In your form, modify your code like this:

Private Sub btn01DiaryScript_Click()
openRecDryForm Me
End Sub

In the module, add that sub like this:

Sub openRecDryForm(frm As Form)
  If frm.NewRecord Then
    Exit Sub
  Else
    DoCmd.OpenForm "F1RecordingsDiary", , , , , , frm.txtIDcu
  End If
  frm.Dirty = False
End Sub

As is, it should work. However, thinking in "modules", we are hardcoding the form that this sub will open and we are also hardcoding the openargs. A better subroutine would take those parameters, like this:

Sub customFormOpener(originForm As Form, targetFormName as string, formOpenArgs as string)
  If originForm.NewRecord Then
    Exit Sub
  Else
    DoCmd.OpenForm targetFormName, , , , , , formOpenArgs
  End If
  originForm.Dirty = False
End Sub

And then call like this:

Private Sub btn01DiaryScript_Click()
customFormOpener Me, "F1RecordingsDiary", Me.txtIDcu
End Sub

With this approach, you should be able to reuse this code more easily. And we could go even further.

[Perfac]

Thanks, it works. I copy and paste these teachings to a separate little DB. One of the ways I learn. Button 2,3,9 works the same, but I am sure I can do that now.

[Perfac]

I first thought not to put this in a Module as well. Now I think there are good reasons too. I tried but haven't succeeded yet, it is a bit different.
On the same form in the attachment, f6FieldNames there is code with a small change. It must be like this.
If Me!FieldNameNoA > 0 Then
GoTo Skip
Else
If DCount("FienamIDa", "t6FieldNames") = 0 Then
Me!RecordClassNoA = DMax("RecordClassNoA", "q1RecordsClass") + 1
Me!FienamIDa = "REC" & DMax("RecordClassNoA", "q1RecordsClass") + 1
Me!FieldNameNoA = DMax("FieldNameNoA", "t6FieldNames") + 1
End If
End If
Me!CapturedByB = DLookup("UserNameB", "q5LoggedInUser")
Skip:

The code works on the forms, and it comes the same on more than 100 forms.
The references to other queries or tables however sometimes change.

[Gicu]

This line doesn't make sense, there is no criteria for the dCount so it will always return the number of records in the t6FieldNames table:

If DCount("FienamIDa", "t6FieldNames") = 0 Then

It should probably be:

If DCount("*", "t6FieldNames","FieldNameA ='" & Me.txtFieldNameA & "'") = 0 Then

The code seems quite specialized, but basically the idea is to replace any specific form references (controls, source fields, etc.) with argument of the public sub.
Here is an updated file with that change, please check if that is what you want.

Cheers,

[Perfac]

1. Yes, it is only the code in blue below that I need help here with.
If Me!FieldNameNoA > 0 Then
GoTo Skip
Else
If DCount("FienamIDa", "t6FieldNames") = 0 Then
Me!RecordClassNoA = DMax("RecordClassNoA", "q1RecordsClass") + 1
Me!FienamIDa = "REC" & DMax("RecordClassNoA", "q1RecordsClass") + 1
Me!FieldNameNoA = 1
Else
Me!RecordClassNoA = DMax("RecordClassNoA", "q1RecordsClass") + 1
Me!FienamIDa = "REC" & DMax("RecordClassNoA", "q1RecordsClass") + 1
Me!FieldNameNoA = DMax("FieldNameNoA", "t6FieldNames") + 1
End If
End If
Me!CapturedByB = DLookup("UserNameB", "q5LoggedInUser")
Skip:

2. This code is meant to do manual numbering in all the tables.
3. The first line must result that when a user edits a record, it will not renumber the record.
4. The line you refer to If DCount("FienamIDa", "t6FieldNames") = 0 Then, has the correct result now. I experienced challenges when this code must start numbering from 1, so I came to that.
5. On more than 100 forms the code is the same, but the fields referred to can be three different references. Hope I am clear.
6. I am not connecting immediately, but I will try.
7 I added q5LoggedInUser.
8. I just logged on and saw your reply, I am looking into it now.

[Perfac]

A similar challenge was solved by placing a textbox (not visible) on the forms. On all forms that text boxes got the same name. As I say, I am still trying to connect wit what you sent me.

[Perfac]

I may be wrong, but I suggest placing 5 text fields (names in red below) on each form.
If your code can the refer to those 5 text fields, will that work?
These text fields will return the correct data.

Call sbYourName(Me, "FienamIDa", "FieldNameNoA", "t6FIeldNames", "FieldNameA", "q1RecordsClass", "RecordClassNoA", "txtFieldNameA")
FienamIDa = ThisTableID
FieldNameNoA = ThisTableNumber
t6FieldNames = TableName
RecordClassNoA = JointTableNumber
q1RecordsClass = JointQueryName


txtFieldNameA: Not part of the blue code in #6.
FieldNameA: Not part of the blue code in #6.

[Gicu]

It sure can, you might not even need to add these textboxes to the forms, you could create a new lookup table that would hold the form name and the corresponding five strings for the fields, tables, queries and controls. Having that populated it would then just be a matter of using a dLookup to get the corresponding settings for each form:

Call sbYourName(Me, dLookup("ThisTableID","t_FormSettings","FormName='" & Me.Name & "'"),  dLookup("ThisTableNumber","t_FormSettings","FormName='" & Me.Name & "'"),...........

Cheers,

[Perfac]

Thank you.
It is 01h00 here. Will test that tomorrow.

[Gicu]

[Perfac]

Today my brain is not fresh. I tried to set up one table or query where fields are populated with the correct information of the 5 fields in red.
My thoughts brought me to a very complicated way and a massive amount of work.

1. To set up 5 text fields on every form is also much work but now I assume that as much better.
2. Confirm with me if the Module1 you suggest stays the same and show me how the "call" should then look like.
3. Assume that I have 5 text fields on the forms named as the names in red in #8.

Thank you very much.

[Perfac]

Exclude this line as well.
Me!CapturedByB = DLookup("UserNameB", "q5LoggedInUser")

[Gicu]

Please give it a try and let me know if OK.
Cheers,

[Perfac]

Is there an expression to populate the text field with the name of the source? My thought is to add a field in the source withe name, but then all forms must have it.