2424 or 2450 error

[reggied]

I have the below code in a subform. The code works when I open the subform directly by itself. But it gives me a 2424 or 2450 error when I open the parent form. I've tried a bunch of different ways of referencing the control (cboFinishID) but I keep getting the error message that Access either can't find the form or can't find the control. Why does this work when the subform is opened directly by itself but not when it is opened as part of the parent form?

Private Sub Detail_Paint()


Dim FIN As Long
If Len(Me.cboFinishID & "") < 1 Then
    FIN = 0
    Else
    FIN = Forms!sfrmLineItemsDE.Form!cboFinishID
End If

If DLookup("HexCode", "qryColors", "FinishID = " & FIN) <> 0 Then
    Me.cboFinishID.BackColor = DLookup("HextoRGB", "qryColors", "FinishID = " & Forms!sfrmLineItemsDE.cboFinishID)
    Me.cboFinishID.ForeColor = RGB(255, 255, 255)
    'MsgBox "RGB is " & Red & ", " & Green & ", " & Blue
  
    Else
    Me.cboFinishID.BackColor = RGB(255, 255, 255)
    Me.cboFinishID.ForeColor = RGB(0, 0, 0)
    
End If
End Sub

 

[Gasman]

Because the path to any object is completely different.

Review http://access.mvps.org/access/forms/frm0031.htm

 

[reggied]

Because the path to any object is completely different.

So I tried the below revisions based on the link but I still get the 2450 error. And it only happens when I move to certain records on the parent form (including the blank/add new record)

But I haven't figured out any meaningful differences between the parent form records that have the subform working and the ones that don't have it working.
I also don't see anything in the below code that is linked or meaningful to the parent form.

Private Sub Detail_Paint()


Dim FIN As Long
If Len(Me.cboFinishID & "") < 1 Then
    FIN = 9 'FinishID with HexCode of 0
    Else
    FIN = Me.cboFinishID
End If

If DLookup("HexCode", "qryColors", "FinishID = " & FIN) <> 0 Then
    Me.cboFinishID.BackColor = DLookup("HextoRGB", "qryColors", "FinishID = " & FIN)
    Me.cboFinishID.ForeColor = RGB(255, 255, 255)
    
  
    Else
    Me.cboFinishID.BackColor = RGB(255, 255, 255)
    Me.cboFinishID.ForeColor = RGB(0, 0, 0)
    
End If
End Sub

 

[Gasman]

Well if you have to tell us what that error is.
We (at least me) do not know them all by heart. Why newbies think we do, is beyond me.

Learn to walk your code with breakpoints and F8 to see what the code does, not what you think it does.

Failing that compact, possibly zip if too large) and upload the DB, with explicit instructions on how to recreate the issue.
All we need is enough to see the issue.

 

[The_Doc_Man]

Errors 2424 and 2450 are "cannot find object" and "cannot find subform"

One thing to consider: When you open a form which hosts a sub-form, the sub-form is opened, loaded, and made current FIRST, after which the parent form gets its turn. So it is possible that on opening, this reference cannot work at the time you execute it from the sub-form:

FIN = Forms!sfrmLineItemsDE.Form!cboFinishID

because cboFinishID (presumably, a control in the parent form) hasn't been loaded yet. If it hasn't been loaded, it doesn't exist in the object list for the form at that moment. Remember, Access VBA works by names based on named objects it can see. It isn't compiled code. It is emulated.

You have an IF statement block that prevents execution of the line I called out under certain circumstances. Would a "direct open" of the sub-form provide the conditions that stop that IF-block from executing?

Check this linked article and scroll down to see "Working with Sub-forms" to read about how events work in the form/sub-form case.

Order of events for database objects - Microsoft Support

support.microsoft.com

 

[reggied]

Errors 2424 and 2450 are "cannot find object" and "cannot find subform"

One thing to consider: When you open a form which hosts a sub-form, the sub-form is opened, loaded, and made current FIRST, after which the parent form gets its turn. So it is possible that on opening, this reference cannot work at the time you execute it from the sub-form:

FIN = Forms!sfrmLineItemsDE.Form!cboFinishID

because cboFinishID (presumably, a control in the parent form) hasn't been loaded yet. If it hasn't been loaded, it doesn't exist in the object list for the form at that moment. Remember, Access VBA works by names based on named objects it can see. It isn't compiled code. It is emulated.

You have an IF statement block that prevents execution of the line I called out under certain circumstances. Would a "direct open" of the sub-form provide the conditions that stop that IF-block from executing?

Check this linked article and scroll down to see "Working with Sub-forms" to read about how events work in the form/sub-form case.

Got it. I had revised that line to:

FIN = Me.cboFinishID

But it still doesn't work. What's odd to me is that the subform errors out to 2424 only when specific parent records are pulled up and there is no reference to any parent form controls or data in this subform code. cboFinishID is a control in the subform (sfrmLineItemsDE).

When I open the subform directly (by itself) everything works fine!

 

[reggied]

Well if you have to tell us what that error is.
We (at least me) do not know them all by heart. Why newbies think we do, is beyond me.

Learn to walk your code with breakpoints and F8 to see what the code does, not what you think it does.

Failing that compact, possibly zip if too large) and upload the DB, with explicit instructions on how to recreate the issue.
All we need is enough to see the issue.

I get the 2424 error:

What's odd is that the error only appears when viewing specific parent records. The code works when I open the subform directly and for most of the parent records. I will try making a db that has the relevant forms and tables copied so I can easily upload.

 

[Gasman]

Do not forget the explicit instructions as well.

 

[MajP]

If you have a parent form and a subform that is set to allow additions = false then when you move to parent that returns no child records t is as if the controls on the subform do not exist.
So me!cboPN cannot be found .

 

[MajP]

Actually It is a 2427 error, "You Entered an Expression that has no value", but it still may be the same cause of your problem.

 

[MarkK]

Why use the Detail_Paint() event handler? This method runs at unpredictable times, and not in response to changes in your data. For instance, just rolling your mouse over a command button fires Detail_Paint() four times. I don't claim this is the cause of your error, but using Detail_Paint() will run your code way, way, way more frequently than required.

 

[reggied]

If you have a parent form and a subform that is set to allow additions = false then when you move to parent that returns no child records t is as if the controls on the subform do not exist.
So me!cboPN cannot be found .

Interesting thought!

Both the parent form and subform are set to allow additions. One of the error instances happens when I move to the blank/new record on the parent form. That's why I put in the Len(Me.cboFinishID & "") < 1 test to see if it is a null value. But the same error occurs on some of the existing records as well and I am thus far stumped.

 

[reggied]

Why use the Detail_Paint() event handler? This method runs at unpredictable times, and not in response to changes in your data. For instance, just rolling your mouse over a command button fires Detail_Paint() four times. I don't claim this is the cause of your error, but using Detail_Paint() will run your code way, way, way more frequently than required.

good question. I currently use conditional formatting to color the cboFinishID control. Originally, I thought there would only be a dozen or so colors. I was just advised that the list would grow to 90 colors! I believe with conditional formatting you can only have about 50 sets or rules (this sound right?)

I am not crazy about the Detail Paint() event firing that many times especially when you are scrolling a continuous form of 30+ records. But I was told that the resulting screen flicker is less noticeable with the faster pcs.

Is there another way to color the control boxes?

 

[Gasman]

Interesting thought!

Both the parent form and subform are set to allow additions. One of the error instances happens when I move to the blank/new record on the parent form. That's why I put in the Len(Me.cboFinishID & "") < 1 test to see if it is a null value. But the same error occurs on some of the existing records as well and I am thus far stumped.

Test for New record then.

 

[reggied]

Do not forget the explicit instructions as well.

Attached is the db.

frmSalesOrderEntryCabs is the parent form and is a list of Sales Orders.

sfrmSOShipInfo is a subform that was only added to allow the user a familiar way of editing ship info.

sfrmLineItemsDE is a subform and contains the code causing the error. It lists line items for each Sales order. This subform does not have the full conditional formatting of various colors for each cboFinishID. I am attempting to replace that with code in the Detail_Paint event.

User should be able to flip through each of the sales orders and see various colors in the sfrmLineItemsDE without any errors popping off.

Thanks in advance for any assistance!

 

[Gasman]

Not a great start, just trying to get into design mode of the topmost form. Forms missing.
No Option Explicit, so a bunch of variables not declared.

I am out. Just too many variables not defined.
Records, TotalRecords etc.
How it even work correctly is beyond me. I have never created a DB without Option Explicit.

 

[MajP]

I you want to use conditional formatting instead of the paint event then here is how you do that dynamically.

Solved - Programatically change color on continous form conditional formatting

Hi, I'm trying to change the color of a textbox on a continous form. I need to color to change based on the RGB values in the text box. Is it possbile to do?

www.access-programmers.co.uk

The syntax for conditional formatting can be really picky see this

VBA Conditional Formatting error because I have more than 3 formatting requirements.

I agree. I’ve spent so long trying different things but to no avail. It will be interesting to see what DBguy has done. Sorry guys, I still can only respond using my phone. However, I have also been playing in the background and got the same results as @MajP. My demo from the other thread I...

www.access-programmers.co.uk

I agree with @MarkK, the On paint can give some bizarre results with the continuous repainting. If any of the painted controls is e

 

[MajP]

As far as I can tell this is some kind of timing error. That control is not rendered when the call is made. This fixes the problem, but not a great solution. Basically ignore the error. Let me see if I can do it using dynamic conditional formatting instead.

    Private Sub Detail_Paint()
    
    On Error GoTo Detail_Paint_Error
    
    Dim FIN As Long
    Debug.Print Me.Recordset.RecordCount
    If Not Me.Recordset.EOF Then
        If Not Me.NewRecord Then
            If Len(Me.cboFinishID.Value & "") < 1 Then
                FIN = 9 'FinishID with HexCode of 0
                Else
                FIN = Me.cboFinishID
            End If
            
            If DLookup("HexCode", "qryColors", "FinishID = " & FIN) <> 0 Then
                Me.cboFinishID.BackColor = DLookup("HextoRGB", "qryColors", "FinishID = " & FIN)
                Me.cboFinishID.ForeColor = RGB(255, 255, 255)
                
              
                Else
                Me.cboFinishID.BackColor = RGB(255, 255, 255)
                Me.cboFinishID.ForeColor = RGB(0, 0, 0)
                
            End If
        End If
    End If
    
    On Error GoTo 0
    Exit Sub

Detail_Paint_Error:
   If Err.Number = 2424 Then
     Debug.Print Me.SalesOrderID
    Else
     MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure Detail_Paint, line " & Erl & "."
    End If
End Sub

 

[MajP]

Yep. This works no flicker. need to remove the errors in the color query.

Private Sub Form_Load()
   removeFormatConditions
   AddFormatConditions
End Sub

Public Sub AddFormatConditions()
  Dim rs As DAO.Recordset
  Dim color As Long
  Dim Finish As Long
  Dim con As FormatCondition
  Set rs = CurrentDb.OpenRecordset("qryColors")
  Do While Not rs.EOF
    color = rs!hextorgb
    Finish = CLng(rs!FinishID)
    Set con = Me.cboFinishID.FormatConditions.Add(acFieldValue, acEqual, Finish)
    With con
      'forcolor if you wanted
      .BackColor = color
    End With
    rs.MoveNext
  Loop
End Sub
Public Sub RemoveFormatConditions()
 Dim con As FormatCondition
 For Each con In Me.cboFinishID.FormatConditions
    con.Delete
 Next con
End Sub

 

[reggied]

I you want to use conditional formatting instead of the paint event then here is how you do that dynamically.



The syntax for conditional formatting can be really picky see this


I agree with @MarkK, the On paint can give some bizarre results with the continuous repainting. If any of the painted controls is e

Nice! But with conditional formatting I would be limited to 50 colors, right? I am told that the color list would grow to 90. We started with 9 and we are now up to 15 a couple months later. . .

I am also not crazy about how the On paint event constantly fires. Maybe I can stay with conditional formatting and use a single color for multiple color codes if the shades are close enough to each other.