Page 1 of 2 12 LastLast
Results 1 to 15 of 30
  1. #1
    Star Lounger
    Join Date
    Jun 2005
    Location
    Delaware
    Posts
    79
    Thanks
    0
    Thanked 0 Times in 0 Posts

    VBA Code Errors (Access)

    Hi I posted a request earlier about creating Audit Logs in Access. I followed the instructions provided from the AllenBrowne Website. I created the temporary audit table and audit table. On my form I have an "Add Record" command button so that the user can insert a new row and begin typing data. My dilemma is that whenever I click on the Add Record button I receive the following error message: "Main Menu...The expression On Click you entered as the event property setting produced the following error: Procedure Declaration does not match description of event or procedure having the same name. * The expression may not result in the name of a macro, the name of a user-defined function, or [Event Procedure]. *There may have been an error evaluariing the function, event, or macro.

    My other dilemma is that I will try to change an existing record, then I hit the save button on the toolbar and I think everything is fine. But then I try to exit out of the form and I receive the same error message as above but it states Before Update instead of OnClick.

    I am attaching the VBA Code for the module and for my form. The form VBA begins on page 7. Any assistance would be greatly appreciated. Please keep in mind that I am very limited in VBA and this is actually my first time attempting to work in it directly.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: VBA Code Errors (Access)

    You have modified Allen Browne's code without discernible reason, and thereby introduced several errors. I would suggest copying his code exactly.

  3. #3
    Star Lounger
    Join Date
    Jun 2005
    Location
    Delaware
    Posts
    79
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: VBA Code Errors (Access)

    I did copy and paste it in. The changes I made were (or so I thought) to the table names to reflect the tables in my database.

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: VBA Code Errors (Access)

    The code is generic, you shouldn't put specific names in that code. You should do that in the calls to Allan Browne's code in your form module.

  5. #5
    Star Lounger
    Join Date
    Jun 2005
    Location
    Delaware
    Posts
    79
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: VBA Code Errors (Access)

    Ok, I copied and pasted the module code exactly like it is listed on Allen's website. I still get the same error messages.

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: VBA Code Errors (Access)

    Could you post a stripped down copy of your database? See <post#=401925>post 401925</post#> for instructions.

  7. #7
    Star Lounger
    Join Date
    Jun 2005
    Location
    Delaware
    Posts
    79
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: VBA Code Errors (Access)

    Here it is.

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: VBA Code Errors (Access)

    Private Sub Form_BeforeUpdate()

    must be

    Private Sub Form_BeforeUpdate(Cancel As Integer)

    Call AuditDelEnd(audTmpADP_GL_Oracle_GL2, audADP_GL_Oracle_GL2, Status)

    The table name arguments must be quoted strings, and the names must be those used in the database window:

    Call AuditDelEnd("audTmpADP GL Oracle GL2", "audADP GL Oracle GL2", Status)

    You keep on referring to a non-existent field audID (that is in the audit table, not in the record source of the form). The AutoNumber field is ID, and when referring to its name, you must use quotes: "ID".

    You haven't copied the LogError function from Allen Browne's website.

    You haven't set a reference to the Microsoft DAO 3.6 Object Library.

    You haven't created the tLogError table needed by the LogError function.

    The code fails due to bad naming conventions, so I replaced spaces in the names with underscores.

  9. #9
    Star Lounger
    Join Date
    Jun 2005
    Location
    Delaware
    Posts
    79
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: VBA Code Errors (Access)

    Thank you very much. Like I said I am NOT used to working directly with VBA code. I really appreciate it. Now, my next problem is that I have to do an audit log for 3 other forms. Would I need to do the same thing for each of them? Meaning creating a audit table, audit temp table, and the log error table? You also mentioned that I didn't reference the Object Library (I have NO idea what that is, hoping I can copy and paste) doe this need to be created for the other three forms also?

    Thanks for your patience and this website is great!!

  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: VBA Code Errors (Access)

    1) The DAO reference needs to be set only once, in Tools | References... in the Visual Basic Editor. The reference is stored with the database, so you don't need to repeat it.

    2) You will have to create an audit table and an audit temp table with the appropriate structure for each table you want to audit. You do NOT need to duplicate the log error table, it is shared.

  11. #11
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: VBA Code Errors (Access)

    Firstly put an Option Explicit after line 1 of the forms code. Then choose debug/compile.

    You have not defined the Form_BeforeUpdate correcly, it should include "Cancel as Integer" between the brackets.

    When you compile after fixing the Form_BeforeUpdate line you will find"audTmpADP_GL_Orecle_GL2 has not been defined.

  12. #12
    Star Lounger
    Join Date
    Jun 2005
    Location
    Delaware
    Posts
    79
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: VBA Code Errors (Access)

    Ok, like I said before I am a little on the "dummy" side when it comes to working directly with VBA. I imported the changes you made to my original database and they continue to work smoothly. Thanks again.

    Now, for the remaining forms that need to be audited, I did basically the same thing. I copied the structure only of my underlying table for the form and renamed them the audit table and temp audit tables. I have added the Form Events as documented in Allen's documentation.

    I tried to test it by clicking on the "Add Record" command button to enter a test record; I get the same errors as I did yesterday. This is the error message: "The expression on CLick you entered as the event property setting produced the following error: Expected: end of statement.

    *The expression may not result in the name of a macro, the name of a user-defined function or [Event Procedcure].
    * There may have been an error evaluating the function, event, or macro."

    I viewed how you entered the form events on the one you help me with yesterday and entered it the same way. I did notice that you did not include the Dim bWasNewRecord As Boolean statement for this but included Form Name - 1 and Form Name - 2. I tried to mimic what you produced yesterday, but I am coming up with more errors. So, obvioulsy I am doing something incorrectly.

    Thanks for your help.

  13. #13
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: VBA Code Errors (Access)

    You'll have to study the code in the form that works, and compare it to the code in the other forms. It's hard to be more specific than that without seeing what you have done.

  14. #14
    Star Lounger
    Join Date
    Jun 2005
    Location
    Delaware
    Posts
    79
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: VBA Code Errors (Access)

    Ok, here is the database stripped down. I am trying to replicate the audit funciton in the CU Branch Mapping Form.

  15. #15
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: VBA Code Errors (Access)

    If you want to include comments in a code module, you must prefix the text with an apostrophe, otherwise VBA will try to interpret it as code. So for example the first line in the module for CU Branch Mapping:

    Form CU Branch Mapping - 1

    must be

    ' Form CU Branch Mapping - 1

    or you must remove that line entirely. There is one other such line in the code.

    Another reason the code fails is that your table name contains spaces. I told you yesterday that this is a bad naming convention. It is much better NOT to have spaces in table names.
    Please replace the spaces in your table names with underscores (or whatever you prefer), and adjust the code correspondingly.

    I told you yesterday that you had to set a reference to the Microsoft DAO 3.6 Object Library. This has not been done in the database you attached.

Page 1 of 2 12 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •