Solved

Run-Time error '2424'

Posted on 2009-04-01
32
627 Views
Last Modified: 2012-05-06
I went out on disability and was not able to work on this problem. I am back and am still having trouble. Below is the complete code for the function.

Public Function basLogTrans(Frm As Form) As Boolean
 

    Dim MyCtrl As Control

    Dim MyMsg As String

    Dim Hist As String
 

   

    

    For Each MyCtrl In Frm.Controls

        If ((MyCtrl.ControlType = acTextBox) Or (MyCtrl.ControlType = acComboBox)) Then

        

        

             If (MyCtrl.Value <> MyCtrl.OLDVALUE) _

               Or (IsNull(MyCtrl) And Not IsNull(MyCtrl.OLDVALUE)) _

               Or (Not IsNull(MyCtrl) And IsNull(MyCtrl.OLDVALUE)) Then

                    If (MyCtrl.ControlType = dbMemo) Then

                            Hist = "tblHistMemo"

                        Else

                            Hist = "tblHist"

                    End If

                    Call basAddHist(MyCtrl, Frm.txtREC_ID.Value, Frm.Caption)

            End If

        

       End If

        

    Next MyCtrl
 

    basLogTrans = True
 

End Function

Public Function basActiveCtrl(Ctl As Control) As Boolean

    Select Case Ctl.ControlType

        Case Is = acTextBox

            basActiveCtrl = True

        Case Is = acComboBox

            basActiveCtrl = True

    End Select

End Function

Public Function basAddHist(MyCtrl As Control, REC_ID As Integer, Cap As String)
 

Dim db As DAO.Database

Dim rs As DAO.Recordset
 

Set db = CurrentDb()

Set rs = db.OpenRecordset("TBL_TRANS_HIST", dbOpenDynaset)
 

rs.AddNew

    rs!TRANSACTION = Cap

    rs!REC_ID = REC_ID

    rs!FLDNAME = MyCtrl.ControlSource

    rs!OLDVALUE = MyCtrl.OLDVALUE

    rs!NEWVALUE = MyCtrl.Value

    rs!TRANS_DATE = Date

    rs!TRANS_TIME = Time()

    rs!TRANS_BY = Environ("username")
 
 

rs.Update

rs.Close
 

Set db = Nothing

Set rs = Nothing
 

End Function

Open in new window

0
Comment
Question by:Sariff
  • 13
  • 12
  • 7
32 Comments
 
LVL 28

Expert Comment

by:TextReport
ID: 24041999
Can you please advise the error description and also what line gets you the error.
Cheers, Andrew
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 24042041
can you put

option explicit

on the top of your code, then do a debug/compile. any issues must be addressed

Now run it, do you still get an issue?

If so then put a breakpoint on this line

For Each MyCtrl In Frm.Controls

(click on that line, hit F9, you should see a red dot on the left hand side)
now run it, then step thru by hitting F8

Error is application-defined or object error so something wrong with one of the objects

0
 
LVL 65

Expert Comment

by:rockiroads
ID: 24042123
Im a bit unsure about this

If (MyCtrl.ControlType = dbMemo) Then

I didnt think dbMemo was a valid choice here. Also what are you doing with Hist cos your setting it but I cant see any use of it
0
 

Author Comment

by:Sariff
ID: 24042146
Run-time error '2424'

The expression you entered has a field, control, or property name that the database cant find.

Below is the code line where the arrow is...
               Or (Not IsNull(MyCtrl) And IsNull(MyCtrl.OLDVALUE)) Then

Open in new window

0
 
LVL 28

Expert Comment

by:TextReport
ID: 24042208
Is it probably the OLDVALUE property, it is only available to bound controls and only certain cotrols.
Go to the immediate window while the code is in break / error mode and try ?myctrl.name and ?MyCtrl.OLDVALUE
Chers, Andrew
0
 

Author Comment

by:Sariff
ID: 24042212
Hey rocki

followed your drections...it stopped on
        If ((MyCtrl.ControlType = acTextBox) Or (MyCtrl.ControlType = acComboBox)) Then
I got 109 for acTextBox and 111 for acComboBox, then I F8 and it skips to the end...
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 24042241
Also, might be worth checking the value

instead of IsNull(Myctrl) do IsNull(MyCtrl.value)

You can soon find out the issue by doing this, then stepping thru should hopefully help identify further (remember to leave variables defined as variant)

    Dim sValue
    Dim sOldVaue
 
   
    For Each MyCtrl In Frm.Controls
       
        If ((MyCtrl.ControlType = acTextBox) Or (MyCtrl.ControlType = acComboBox)) Then
       
            sValue = MyCtrl.Value
            sOldValue = MyCtrl.OldValue
           
             If (sValue <> sOldValue) _
               Or (IsNull(sValue) And Not IsNull(sOldValue)) _
               Or (Not IsNull(sValue) And IsNull(sOldValue)) Then

0
 
LVL 65

Expert Comment

by:rockiroads
ID: 24042259
Sariff, the form in question that you passed in, how many controls in there? are you expecting any textboxes to be found?
0
 

Author Comment

by:Sariff
ID: 24042265
here are my variables

    Dim MyCtrl As Control
    Dim MyMsg As String
    Dim Hist As String
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 24042276
the f9/f8 instructions is a way of debugging code, it will come in handy for you next time you got a issue like this
0
 

Author Comment

by:Sariff
ID: 24042281
16 controls...yes Textboxes should be found
0
 

Author Comment

by:Sariff
ID: 24042300
Rocki - I got rid of (3) unbound controls THANKS TEXTREPORT. Now I get a "6 Overflow" error
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 24042317
How are you calling this function?
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 24042327
I got a typo in my code

Dim sOldVaue


should be

Dim sOldValue
0
 

Author Comment

by:Sariff
ID: 24042621
Event: Before Update
Call basLogTrans(Me.Form)
0
 
LVL 28

Expert Comment

by:TextReport
ID: 24042631
"Rocki - I got rid of (3) unbound controls THANKS TEXTREPORT. Now I get a "6 Overflow" error" don't mention it :-) I assume this is progress.
rocki, I will take a step away from this else it is going to get silly, drop me an email if you need me to pick something up.
Cheers, Andrew
0
Get up to 2TB FREE CLOUD per backup license!

An exclusive Black Friday offer just for Expert Exchange audience! Buy any of our top-rated backup solutions & get up to 2TB free cloud per system! Perform local & cloud backup in the same step, and restore instantly—anytime, anywhere. Grab this deal now before it disappears!

 
LVL 65

Expert Comment

by:rockiroads
ID: 24042657
No probs Andrew
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 24042662
I can understand what with England playing tonite. Im currently watching it as Im typing!
0
 

Author Comment

by:Sariff
ID: 24042677
Thanks Andew
0
 
LVL 65

Accepted Solution

by:
rockiroads earned 300 total points
ID: 24042864
ok, you are calling it correctly.

tweaked your basLogTrans method so it checks the controlsource. this means it works on bounded fields only. Also by assigning to variables, we can use NZ to make the checking simpler


Public Function basLogTrans(Frm As Form) As Boolean
 
    Dim MyCtrl As Control
    Dim MyMsg As String
    Dim Hist As String
    Dim sValue As String
    Dim sOldValue As String
   
   
    MsgBox "Name of Form to Process is " & Frm.Name & " and number of controls is " & Frm.Controls.Count
   
    For Each MyCtrl In Frm.Controls
   
        If MyCtrl.ControlType = acTextBox Or MyCtrl.ControlType = acComboBox Then
   
            'Handle bounded controls
            If MyCtrl.ControlSource <> "" Then
           
                sValue = Nz(MyCtrl.Value, "")
                sOldValue = Nz(MyCtrl.OldValue, "")
   
                MsgBox "Found " & MyCtrl.Name & "-" & sValue & "-" & sOldValue
               
                If sValue <> sOldValue Then
                    MsgBox "Value Differs"
                    If (MyCtrl.ControlType = dbMemo) Then
                        Hist = "tblHistMemo"
                    Else
                        Hist = "tblHist"
                    End If
                    Call basAddHist(MyCtrl, Frm.txtREC_ID.Value, Frm.Caption)
                End If
            End If
        End If
   
    Next MyCtrl
   
    basLogTrans = True
 
End Function
0
 
LVL 28

Expert Comment

by:TextReport
ID: 24042892
Rocki, it might be worth testing the control source for the first character being an = as well.
Cheers, Andrew
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 24043211
yes, good point and only 1-0 up, playing well at least
0
 

Author Comment

by:Sariff
ID: 24048622
Rocki

The function checked eveything. I still got the over flow error..
On this line:                     Call basAddHist(MyCtrl, Frm.txtREC_ID.Value, Frm.Caption)

When I hover over MyCtrl is see a value as well as Frm.Caption. I see nothing for Frm.txtREC_ID
0
 
LVL 28

Expert Comment

by:TextReport
ID: 24048651
Back from the footie, great result.
Try the parameter REC_ID should be LONG not INTEGER

Cheers, Andrew

Public Function basAddHist(MyCtrl As Control, REC_ID As Long, Cap As String)
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 24048659
in the table, what is frm.txtREC_ID defined as?

Overflow on a number, possibly exceeding int

change definition here from Integer to Long

from
Public Function basAddHist(MyCtrl As Control, REC_ID As Integer, Cap As String)

to
Public Function basAddHist(MyCtrl As Control, REC_ID As Long, Cap As String)
0
 

Author Comment

by:Sariff
ID: 24048957
Thanks guys. It works perfectly. One last thing...how would you exclude certain Text boxes?
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 24049180
Are these textboxs bounded? if so then one simple way is to use the TAG property.
If u go in design view and look at the properties of that textbox, there is one called TAG. this is freetext.
Add something here say IGN
then in code you can check the tag property
eg

If MyCtrl.ControlSource <> "" and MyCtrl.Tag <> "IGN" Then


0
 
LVL 28

Assisted Solution

by:TextReport
TextReport earned 200 total points
ID: 24049227
TAG is a good option, you can also use the NAME property and prefix or suffix the name with something.

If MyCtrl.ControlSource <> "" and Right(MyCtrl.Name,2) <> "X" Then

The trick is to be consistant and pick a rule you wont be breaking.

Cheers, Andrew
0
 

Author Comment

by:Sariff
ID: 24050237
Got it working! Thanks!
0
 

Author Comment

by:Sariff
ID: 24051285
Is there a way I give both of you the same number of points?
0
 
LVL 28

Expert Comment

by:TextReport
ID: 24051327
When you close the question you can accept a posting as the answer and others as assisted, when you do this you assign what points you wish to award for each posting.

Saying that though this is an assumption as to how it works as i haven't closed a question myself for a while.

Cheers, Andrew
0
 

Author Closing Comment

by:Sariff
ID: 31565447
Thanks!
0

Featured Post

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

Most if not all databases provide tools to filter data; even simple mail-merge programs might offer basic filtering capabilities. This is so important that, although Access has many built-in features to help the user in this task, developers often n…
I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

757 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now