Solved Run-time error 5 - Invalid procedure call or argument (1 Viewer)

lacampeona

Registered User.
Local time
Today, 06:48
Joined
Dec 28, 2015
Messages
235
Hello experts

I start to get some error.

Run-time error 5 - Invalid procedure call or argument

Does anybody know what could be the reason?
My code in the module? Windows updates?
I have to say that everythink was perfect and now 3 days ago error error errror... i try to export all the database in new database and i still get the error.
I am so sad......will i have to do all from the begining?

thanks in advance

when i click debug
i get this yellow marking

here is my module: line 52 :
' Skip Updates field
If ctlData.Name = "Updates" Then GoTo NextCtl
Code:
Option Compare Database

Public Function AuditData2()
' Comments : This function is used to provide an audit trail of all changes to a record
' : entered on a secured form. After adding a locked memo field called 'Updates'
' : on the source form, add a reference to the Before Update event to this function.
' Requirement: A bound memo field named 'Updates' must exist on the active form
' Parameters : None
' Returns : Appends Audit Trail data as a string to the memo field on the active form
' Created By : Ofni Systems Inc.
' Modified : 04/07/01
' --------------------------------------------------------
Dim frmActive As Form
Dim ctlData As Control
Dim strEntry As String
Dim User As String

Dim Kdo As String



Kdo = TempVars!UserName

User = CreateObject("wscript.network").ComputerName

Set frmActive = Screen.ActiveForm

'Set date and current user if form has been updated.
    frmActive!Updates = frmActive!Updates & Chr(13) & Chr(10) & _
    "----------------------------------------------------------------------------------------------" & Chr(13) & Chr(10) & _
    "Changes made on " & Date & " " & Time & " by " & Kdo & ";"


'Determine who, when and where
strEntry = " by " & Kdo & " on " & Now




' If new record, record it in audit trail and exit sub
If frmActive.NewRecord = True Then
frmActive!Updates = frmActive!Updates & "New Record Added" & strEntry
'Uncomment the next line if you do not want to record the details of the initial entry
'Exit Function
End If
'Check each data entry control for change and record
For Each ctlData In frmActive.Controls
' Only check data entry type controls
Select Case ctlData.ControlType
Case acTextBox, acComboBox, acCheckBox, acOptionButton
' Skip Updates field
If ctlData.Name = "Updates" Then GoTo NextCtl
'Skip unbound controls (3 is ControlSource)

Select Case IsNull(ctlData.Value)
'Check for deleted data
Case True
If Not IsNull(ctlData.OldValue) Then
frmActive!Updates = frmActive!Updates & Chr(13) & Chr(10) & " " & ctlData.Name & " Data Deleted: Old value was '" & ctlData.OldValue & "'" & strEntry
End If
'Check for new or changed data
Case False
If IsNull(ctlData.OldValue) And Not IsNull(ctlData.Value) Then
frmActive!Updates = frmActive!Updates & Chr(13) & Chr(10) & " " & ctlData.Name & " Data Added: " & ctlData.Value & strEntry
'If control had previous value, record previous value
ElseIf ctlData.Value <> ctlData.OldValue Then
frmActive!Updates = frmActive!Updates & Chr(13) & Chr(10) & " " & ctlData.Name & " changed from '" & ctlData.OldValue & "' to '" & ctlData.Value & "'" & strEntry
End If
End Select
End Select
NextCtl:
Next ctlData
End Function
 

theDBguy

I’m here to help
Staff member
Local time
Yesterday, 21:48
Joined
Oct 29, 2018
Messages
19,212
Do you know how to step through code?
 

lacampeona

Registered User.
Local time
Today, 06:48
Joined
Dec 28, 2015
Messages
235
Hello
maybe a little?
you mean that dark red point? or step into -some options you can choose from the top?
hmmmm
 

theDBguy

I’m here to help
Staff member
Local time
Yesterday, 21:48
Joined
Oct 29, 2018
Messages
19,212
Hello
maybe a little?
you mean that dark red point? or step into -some options you can choose from the top?
hmmmm
Yes, that's exactly what I meant. When you step through the code, you can examine each variable's or object's content, so you can make sure they are getting the correct data you're expecting. Otherwise, it may be causing the error you're seeing.
 

GPGeorge

Grover Park George
Local time
Yesterday, 21:48
Joined
Nov 25, 2004
Messages
788
What happened three or four days ago? Did you change anything, or did your IT department change anything?

I know that the most recent updates from Microsoft introduced at least one bug involving attachment fields, so it's not out of the question that something else along those lines is involved.

The highlighted error occurs here, i.e. this line is highlighted in yellow:

If ctlData.Name = "Updates" Then GoTo NextCtl

And the field in the table is also called "Updates" and that it is a memo field.

Given the fact that the memo field is somehow a problem now, you could try some things to see what happens.

1) Rename the control on the form to something other than the same name as the field to which it is bound, such as txtUpdates.
2) Use a different datatype field as a test to see if the issue is related to the memo field.
 

lacampeona

Registered User.
Local time
Today, 06:48
Joined
Dec 28, 2015
Messages
235
Hello
yes i make something different
I insert some function Idle time that will close the ms access database if user is more than 15 min away.
i try then then to comment that code out but then i started to get errores in module audit data which was perfect.
ok i will try to learn how to make step in to the code to see what is the problem?

I will try to delete that idle form and see what happen....:unsure:

thank you for your advices
 

Gasman

Enthusiastic Amateur
Local time
Today, 05:48
Joined
Sep 21, 2011
Messages
10,881
Might want to start indenting your code. :(
You took the trouble to place within code tags, so well done, but without indentation, no need.
I myself found that harder than need be to follow.
 

lacampeona

Registered User.
Local time
Today, 06:48
Joined
Dec 28, 2015
Messages
235
Lets say i have some wrong code..
why ms access didnt tell me that one year ago? why now? :cautious:

you mean that if i have some code writen 30 year ago...30 years is working good and then in 5 years will not working anymore??
that is not justice..:oops:
 

theDBguy

I’m here to help
Staff member
Local time
Yesterday, 21:48
Joined
Oct 29, 2018
Messages
19,212

lacampeona

Registered User.
Local time
Today, 06:48
Joined
Dec 28, 2015
Messages
235
Now I start to get some another errorr
is my database destroying.............. :cautious:
what could be wrong here?

1655488629745.png
 

lacampeona

Registered User.
Local time
Today, 06:48
Joined
Dec 28, 2015
Messages
235
Thanks dbguy
I will read the article to see how to do that
 

GPGeorge

Grover Park George
Local time
Yesterday, 21:48
Joined
Nov 25, 2004
Messages
788
Lets say i have some wrong code..
why ms access didnt tell me that one year ago? why now? :cautious:

you mean that if i have some code writen 30 year ago...30 years is working good and then in 5 years will not working anymore??
that is not justice..:oops:
I understand the frustration. Every time a new Windows or Office update is realized, we all hold our breath that it won't include another bug. It all too often does just that. On the other hand, if everything were locked down and static 30 years ago, we'd be missing out on more than one innovation, so it's not all that simple.

In this case, "something" changed, and the possibility that that is related to an Office or Windows Update is not high, but not zero either.

We have to move step by step through the trouble-shooting steps in a careful, objective approach as suggested by theDBGuy.

Did you recently do anything else? Upgrade the Access version on your computer, for example?

How about the data being processed? That highlighted line actually expects a non-Null value to be supplied as strNewNum and it doesn't allow for strings longer than 3 digits, i.e. 999 values. Are you now in excess of that number of records after processing things for a while?

One of the problems of old code is exactly that, it's old. It was based on "then" and perhaps "now" the situation is different. Perhaps some thought to how that could be involved will suggest other factors, as well.
 

lacampeona

Registered User.
Local time
Today, 06:48
Joined
Dec 28, 2015
Messages
235
Hello

GPGeorge

no I am at number 032 and code stops working.
yes i will have to learn this steps.
thanks for all advices
i will spend some days in studying all that and testing the database
my last update was 25 may...and i notice that 3 days ago ( i was testing the database all the time..almost every day...)

i have to resolve all that :unsure:

 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Yesterday, 23:48
Joined
Feb 28, 2001
Messages
23,216
I'm going to take a really wild guess here. If I'm wrong, the test I propose is not difficult and won't break anything that wasn't already broken. (I think.) FIRST AND FOREMOST, make a copy of the file that contains your forms. Before you do ANYTHING ELSE, make that copy in a safe place so you can get back to where you were by deleting the test version that you just created and replacing it with that copy. So that means you get back to exactly where you were if this doesn't work.

Open that copy of the project. Look at any page of code, such as the code you showed us from post #10. From that VBA page, use the menu path Tools >> References. That will bring up the References dialog box, essentially a list of optional libraries to enable various types of automation.

In the complex reference dialog box, SLOWLY scroll through the checked references. See if ANY of them show up as either "missing" or "broken." It IS possible that more than one module would be affected. Don't stop this examination until you have examined every reference that had a check in its checkbox. You don't have to look at unchecked references. It would be rare to have more than a dozen checked references. For my worst project at work, it was only about 14 references. You are unlikely to have anywhere near that amount.

IF any checked items are missing or broken, note their names and then uncheck them. Now close that references box. Save the code now that you have cleared out the error/missing references. Close the project. Re-open it.

Now re-open the Tools >> References path from a VBA page again. It doesn't matter which one. Step through the references to find the ones that were unchecked as described above to see if you actually have the modules that were previously missing or broken. IF there is a reference with the same name, check it. After the update, perhaps you would see the same name but containing a different number. It is OK to use but you generally want the one with the highest number but otherwise the same name.

Now prepare to force Access to forget all prior compilation using the DECOMPILE method.


Part of that process in the link discusses doing a Compact & Repair operation. Attempt to decompile, then compile, then do the C&R

If the compilation succeeds, you SHOULD be good to go.

IF you had no missing or unchecked items, skip ahead to the DECOMPILE/RECOMPILE stuff.

If this doesn't work either, it is possible you have tripped over something in a patch. I know that earlier in the week I got patched on two nights in a row. That usually means a patch went wrong and got corrected the next day. Despite doing a pretty good job most of the time, Microsoft is quite capable of screwing up a good thing. If it really IS due to a bad patch, I can only advise patience for your problem.
 

lacampeona

Registered User.
Local time
Today, 06:48
Joined
Dec 28, 2015
Messages
235
Helloo
good news. :sneaky:
yes the problem was with the reference library.
I tryed:
1. new empty database and then I import all the objects from my last database = not working
2. an old database then I delete all the objects from that old database and import all the objects from the new database= Working
3. My last database= I try to check and uncheck the references that are the same like in my old database = not working

I have only 6 references. is that good? or I am misssing something?

sooo my question is? How that happen? Did I checked and checked the references? I think I didnt touch nothing...hmmm
How do you know which references ( must be checked) you must have ?:unsure:
hmmmmmmm
I was worry that suddenly the codes are not working anymore.

Thank you DocMan


1655499846249.jpeg
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Yesterday, 23:48
Joined
Feb 28, 2001
Messages
23,216
How do you know which references ( must be checked) you must have ?

Some of it is obvious. For instance, if you are working on how to send mail with Outlook automation, you almost certainly need the Outlook libraries. If you are working on some kind of Excel operation other than simple import/export, you probably need the Excel library. Same ideas apply for Word or PowerPoint.

Let's say, for discussion, you wanted to use a FileSystemObject in your project. When you look up the Microsoft help on that FileSystemObject, you would find that you needed the Windows Scripting library to make it work.

The trickiest cases are if you wanted to use some third-party software, they would have to supply you with the library in the installation package and register that library during the install process. Then their documentation (usually a user's manual of some kind) would have to name the library you needed to automate with their product.

So to know what you need, know what you are intending to use.

In your case, you have listed a few "standard" references. You have the VBA library, Access 16.0 library, Office 16.0 database engine library - all of which are standard after an Access installation. I don't know about OLE being standard, but it isn't rare. You have two others - ADO 6.0 and Outlook 16.0 - and neither of those are unusual either.

I have only 6 references. is that good? or I am misssing something?

You know something is wrong when the code won't compile and calls out a line of code that has a function, subroutine, or object-related reference. If your code compiles, you don't have too few references. It might not be possible to so easily say whether you had too many references, though I guess it IS possible to have too many.
 

lacampeona

Registered User.
Local time
Today, 06:48
Joined
Dec 28, 2015
Messages
235
Thank you for your explanation.
Today I get again the error 5..........
I am now opening and checking some old database that I have to see what is diference
and then I am starting all again from the beginingo_O
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 12:48
Joined
May 7, 2009
Messages
16,881
do you have the Memo/Long text field (Controlname is Updates) on your form?
i made a little change.
you have comment on Unbound control but does not implement it so i added code for it:
Code:
Option Explicit

Public Function AuditData2()
' Comments : This function is used to provide an audit trail of all changes to a record
' : entered on a secured form. After adding a locked memo field called 'Updates'
' : on the source form, add a reference to the Before Update event to this function.
' Requirement: A bound memo field named 'Updates' must exist on the active form
' Parameters : None
' Returns : Appends Audit Trail data as a string to the memo field on the active form
' Created By : Ofni Systems Inc.
' Modified : 04/07/01
' --------------------------------------------------------
Dim frmActive As Form
Dim ctlData As Control
Dim strEntry As String
Dim User As String

Dim Kdo As String

'arnelgp
Dim ctlSrc As String

Kdo = TempVars!UserName

User = CreateObject("wscript.network").ComputerName

Set frmActive = Screen.ActiveForm

'Set date and current user if form has been updated.
    frmActive!UPDATES = frmActive!UPDATES & Chr(13) & Chr(10) & _
    "----------------------------------------------------------------------------------------------" & Chr(13) & Chr(10) & _
    "Changes made on " & Date & " " & Time & " by " & Kdo & ";"


'Determine who, when and where
strEntry = " by " & Kdo & " on " & Now




' If new record, record it in audit trail and exit sub
If frmActive.NewRecord = True Then
    frmActive!UPDATES = frmActive!UPDATES & "New Record Added" & strEntry
    'Uncomment the next line if you do not want to record the details of the initial entry
    'Exit Function
End If
'Check each data entry control for change and record
For Each ctlData In frmActive.Controls
    ' Only check data entry type controls
    Select Case ctlData.ControlType
        Case acTextBox, acComboBox, acCheckBox, acOptionButton
            ' Skip Updates field
            If ctlData.Name <> "Updates" Then
                'Skip unbound controls (3 is ControlSource)
                'arnelgp
                'but you did not try to test for Unbound control?
                ctlSrc = ctlData.ControlSource & "="
                If Left$(ctlSrc, 1) = "=" Then 'unbound
                    'ignore it
                Else
                    Select Case IsNull(ctlData.Value)
                        'Check for deleted data
                        Case True
                            If Not IsNull(ctlData.OldValue) Then
                                frmActive!UPDATES = frmActive!UPDATES & Chr(13) & Chr(10) & " " & ctlData.Name & " Data Deleted: Old value was '" & ctlData.OldValue & "'" & strEntry
                            End If
                            'Check for new or changed data
                        Case False
                            If IsNull(ctlData.OldValue) And Not IsNull(ctlData.Value) Then
                                frmActive!UPDATES = frmActive!UPDATES & Chr(13) & Chr(10) & " " & ctlData.Name & " Data Added: " & ctlData.Value & strEntry
                            'If control had previous value, record previous value
                            ElseIf ctlData.Value <> ctlData.OldValue Then
                                frmActive!UPDATES = frmActive!UPDATES & Chr(13) & Chr(10) & " " & ctlData.Name & " changed from '" & ctlData.OldValue & "' to '" & ctlData.Value & "'" & strEntry
                            End If
    
                    End Select
                End If
            End If
    End Select
Next ctlData
End Function
 

Users who are viewing this thread

Top Bottom