Calling Module from a form.

[Welshgasman]

@edgar
End With is in post #35 ?

[Edgar]

@edgar
End With is in post #35 ?

True that. Anyway, this last piece of code also takes care of the typos, I hope! having no access to the source, I can't really know it until Hans posts back.

[Perfac]

Great, it works now. Since the last message, I was busy with something else in the app. Needed less than a minute to make the latest changes.

[Edgar]

Nice, if you have doubts about the code, let me know. I insist about object inspection being a good technique to get to know how Access works under the hood, lots of undocumented stuff can be automated using the properties visualized in the object tree.

Thanks for the rep points, Hans.

[Perfac]

1. Some days I think of Access as a miracle. (In Afrikaans, miracle is "wonderwerk"). Other days I wonder if Access works.
2. A new error arose. "Too many fields defined." No table has more than 50 fields, and no query has more than 80 fields.
3. I searched and already learnt a bit about that. It is very frustrating that it all of a sudden happen with a couple of my queries.
4. I have not changed anything I know of. I rebuild the query and when I get to half the number of fields the query had for years it gives the message.

5. As I wrote previously in this post. There are 21 common buttons which will help a lot if they all can be referenced from a Module. I will still have an issue with maybe two others. I made notes and will soon get training on the subject of calling from modules, but maybe I will bother you, or place a new post on that. As soon as the test sample of my DB is ready I will post it.

[Edgar]

I've never encountered that error, but according to this
https://learn.microsoft.com/en-us/office/troubleshoot/access/too-many-fields-defined-error
You probably modified a table too many times and now it has reached the limit internally. You can create a copy of the problematic table and hook relationships to that new table in order to delete the one giving problems. That will start the field count again.

As another exercise to work from modules, make a new database, create a form, open it, add a module and then write code in it to manipulate the form's properties.

[Perfac]

I found that reference before you sent it but thank you. It is a tragedy. I think Mr. Bill Gates must work overtime and fix that. I wonder in a query if the count of 255 include all fields of linked objects, or only the ones being pulled down.

[June7]

I never encountered that error either. Compact & Repair should fix.

Limit is 255 fields pulled into query. Total fields for all tables pulled from can be higher. I have a db demonstrating that. Has a query hitting that limit.

[Perfac]

What the reference states, is that even when fields are deleted it stays in the count of 255. The two queries that brought up the issue is large and have many tables linking. All developers delete fields, me as well. After 7 years it is the first time I encounter it, but probably possible that those two went over 255. One query is for Employees and the other for agents. Both of the queries use 8 out of 12 same tables, like t1Address, t1BankParticulars, t1Titles, t1UserPermissionLevels etc. Around 12 tables are linked. I think the 12 tables have much less than 255 fields in total, let's say 200. Not more than 80 fields are pulled into query. But I do not know how many fields I deleted through the years. It doesn't feel like I deleted 20 fields. Whatever the issue, I am busy with what is suggested to fix it. It is good to know that now.

I always Compact & Repair frequently. That did not fix.

[CJ_London]

What the reference states, is that even when fields are deleted it stays in the count of 255.

the link also says

Access also creates a new field (increasing the internal column count by 1) for every field whose properties you modify.

and to fix

• Create a new copy of the table. To do so, follow these steps:
  
  
  1. Make note of any relationships with the table.
  2. Select the table.
  3. On the File menu, click Save As.
  4. In the Save Table 'table name' To box, type a new name, and then click OK.
  5. Select the same table that you selected in step 2, and then press DELETE.
  6. Rename the table that you saved in step 3 to the original table name.
  7. Re-establish any relationships with the new table.

You haven't said you have done that part as well as compact/repair

And the error is to do with the table, not the query

[Perfac]

Edgar
1.In the long post at post #21 you helped me with the code. See form f6FieldNames.
2.Apologies if I misunderstand. Ignore anything else in the attached example.
3.My word document is saved externally as f00Defaultform.docx in the directory as you see it.
4.As you may see. On the form there is a text box named "WordDoc2". The field name in the query is WordDoc1.
5.btn08TutorialScript is on the form top right corner. The button with the i. 8th from left. 3rd from right.

[Perfac]

Thanks CJ. When I searched the Internet I found that reference with the words you suggest. I learn more when you say it is to do with the table and not the query. Yes, I am doing the part of the 7 point advice, even though I feel now less that my count could have gone to 255 on any one table. Because of other improvements, I actually decided to create a brand new table for the 15 tables applicable. I assume that the LINKING of 15 tables had to be part of the error here, since all the other smaller queries do not result the same error.

[Perfac]

Edgar.
Years ago someone helped me with this code, preceding the opening of external word documents. It works well on my forms, but this post is all about calling it from a Module.

Function Openword(conPath As String)
Dim appword As Word.Application
Dim doc As Word.Document


On Error Resume Next
Error.Clear
Set appword = GetObject(, "word.application")
If Err.Number <> 0 Then
Set appword = New Word.Application
appword.Visible = True
End If
Set doc = appword.Documents.Open(conPath, , True)
appword.Activate


Set doc = Nothing
Set appword = Nothing
End Function

Then
Private Sub btn08TutorialScript_Click()
Dim mydoc As String
mydoc = "D:\Attachments\InformationPages\f00Defaultform.do cx"
Call Openword(mydoc)
End Sub

[Edgar]

I opened the form, then I clicked the i button. It throws an error telling me Openword is not a sub. Then you post the code for that function in post #58. The code in post #58 is for opening word documents and automating them, you do that when you want to perform operations on the document, like formatting, adding text, etc. If you already have a docx file that you just want to open, just do this from your Form:

Private Sub btn08TutorialScript_Click()
    Followhyperlink "D:\Attachments\InformationPages\" & Me.WordDoc2.Value & ".docx"
End Sub

Or if you want to do this from a module for whatever reason, then, since you already have a OpenTutorial sub, just do this from your Module:

Sub OpenTutorial(tutName As String)
    FollowHyperlink "D:\Attachments\InformationPages\" & tutName & ".docx"
End Sub

And from your Form:

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

[Perfac]

I am sure you understand what I want for my app. Because of your help, I have done a couple myself so far.
1. There are more than ten buttons or other "paragraphs" of code, that are the same on more than 100 forms.
2. Up to now, if I learnt of an improvement, I have to visit all those forms and fix one by one.
3. If the code is on a module, it can be changed at once of course. The more than 10 phrases can just be copied when setting up a new form, right?
4. It also shortens some VBA pages, although my VBA pages are not too long. The code in #58 can be left out.
5. The spelling of the word document is exactly consistent with what I have in the field.
6. The word document should be read only. Users must not be able to change them.

I copied your suggestion to the form. It errors as in the image when I click on i.
Private Sub btn08TutorialScript_Click()
Followhyperlink "D:\Attachments\InformationPages" & Me.WordDoc2.Value & ".docx"
End Sub