Page 4 of 5 FirstFirst 12345 LastLast
Results 46 to 60 of 65
  1. #46
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,861
    @edgar
    End With is in post #35 ?
    Please use # icon on toolbar when posting code snippets.
    Cross Posting: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?sear...bug+access+vba

  2. #47
    Edgar is online now Competent Performer
    Windows 8 Access 2016
    Join Date
    Dec 2022
    Posts
    271
    Quote Originally Posted by Welshgasman View Post
    @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.

  3. #48
    Perfac's Avatar
    Perfac is offline Expert
    Windows 10 Access 2016
    Join Date
    May 2016
    Location
    Centurion Pretoria
    Posts
    618
    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.

  4. #49
    Edgar is online now Competent Performer
    Windows 8 Access 2016
    Join Date
    Dec 2022
    Posts
    271
    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.

  5. #50
    Perfac's Avatar
    Perfac is offline Expert
    Windows 10 Access 2016
    Join Date
    May 2016
    Location
    Centurion Pretoria
    Posts
    618
    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.

  6. #51
    Edgar is online now Competent Performer
    Windows 8 Access 2016
    Join Date
    Dec 2022
    Posts
    271
    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.

  7. #52
    Perfac's Avatar
    Perfac is offline Expert
    Windows 10 Access 2016
    Join Date
    May 2016
    Location
    Centurion Pretoria
    Posts
    618
    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.

  8. #53
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    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.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  9. #54
    Perfac's Avatar
    Perfac is offline Expert
    Windows 10 Access 2016
    Join Date
    May 2016
    Location
    Centurion Pretoria
    Posts
    618
    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.

  10. #55
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,398
    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

  11. #56
    Perfac's Avatar
    Perfac is offline Expert
    Windows 10 Access 2016
    Join Date
    May 2016
    Location
    Centurion Pretoria
    Posts
    618
    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.
    Attached Files Attached Files

  12. #57
    Perfac's Avatar
    Perfac is offline Expert
    Windows 10 Access 2016
    Join Date
    May 2016
    Location
    Centurion Pretoria
    Posts
    618
    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.

  13. #58
    Perfac's Avatar
    Perfac is offline Expert
    Windows 10 Access 2016
    Join Date
    May 2016
    Location
    Centurion Pretoria
    Posts
    618
    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

  14. #59
    Edgar is online now Competent Performer
    Windows 8 Access 2016
    Join Date
    Dec 2022
    Posts
    271
    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:
    Code:
    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:
    Code:
    Sub OpenTutorial(tutName As String)
        FollowHyperlink "D:\Attachments\InformationPages\" & tutName & ".docx"
    End Sub
    And from your Form:
    Code:
    Private Sub btn08TutorialScript_Click()
        OpenTutorial Me.WordDoc2.Value
    End Sub

  15. #60
    Perfac's Avatar
    Perfac is offline Expert
    Windows 10 Access 2016
    Join Date
    May 2016
    Location
    Centurion Pretoria
    Posts
    618
    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
    Click image for larger version. 

Name:	230606a.png 
Views:	6 
Size:	24.3 KB 
ID:	50325

Page 4 of 5 FirstFirst 12345 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. General module calling public function in an open form
    By GraeagleBill in forum Programming
    Replies: 3
    Last Post: 09-30-2022, 02:08 PM
  2. form onload event when calling standard module sub generates err
    By Synergy.ron@gmail.com in forum Access
    Replies: 6
    Last Post: 04-16-2021, 03:29 PM
  3. Calling a module into a form
    By CraigR in forum Modules
    Replies: 3
    Last Post: 12-12-2018, 08:04 PM
  4. Replies: 5
    Last Post: 11-25-2017, 03:45 AM
  5. Calling A Module Function To Open A Form
    By orcinus in forum Modules
    Replies: 3
    Last Post: 09-29-2010, 04:43 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums