Error 2465 when trying to assign recordset value to control on subform in report

0
votes

I am trying to fill 5 sub forms(Fr_Rep_x)(x=1 to 5) in a report(Report1) with recordset values(5 different pages) by invoking a public function 5 times. The subforms and the public function are invoked in the report_activate event

I am getting the above error 2465: Application-defined or object-defined error on the below line of the public function:

Reports("Report1").fr_name.Controls(Lbl_ON).Caption = rst("Order_Number")

Report Activate Event

Private Sub Report_Activate()
    Dim i As Integer
    For i = 1 To 5
        DoCmd.OpenForm "Fr_Rep_" & i, , , , , acHidden
        Call MORep(i, Forms!Fr_Main!NavSubform.Form!Lbox_Comp.Column(0), "00" & i & "0", Forms!Fr_Main!NavSubform.Form!Lbox_Comp.Column(3))
    Next i
End Sub

Public Function

Public Sub MORep(Form_num As Integer, MO_ON As Long, MO_Op As String, Mat As String)
Dim fr_name As String
fr_name = "Fr_Rep_" & Form_num

var_order = MO_ON
var_op_n = MO_Op
var_mat = Mat
var_matdes = DLookup("Material_Description", "Tbl_MO_Rev_Control", "Material = '" & var_mat & "'")
var_matrev = DLookup("MO_Rev_No", "Tbl_MO_List_Archive", "Order_Number = " & var_order)

Dim rst As DAO.Recordset
Dim dbs As DAO.Database
Set dbs = CurrentDb()
Set rst = dbs.OpenRecordset("Tbl_MO_List_Archive", Type:=dbOpenDynaset)
rst.FindFirst "[Order_Number] = " & var_order '& "*'" & "AND MO_Rev_No ='" & var_mo_rev & ""
Dim str1
Reports("Report1").fr_name.Controls(Lbl_ON).Caption = rst("Order_Number")
Reports("Report1").fr_name.Form.Lbl_OQ.Caption = rst("Order_Qty")
Reports("Report1").fr_name.Form.Lbl_Batch.Caption = rst("Batch")
Reports("Report1").fr_name.Form.Lbl_MRP.Caption = rst("MRP_Controller")
Reports("Report1").fr_name.Form.Lbl_PC.Caption = rst("Profit_Center")
Reports("Report1").fr_name.Form.Lbl_PS.Caption = rst("Production_Scheduler")
Reports("Report1").fr_name.Form.Lbl_Mat.Caption = rst("Material")
Reports("Report1").fr_name.Form.Lbl_Pr_D.Caption = Format(rst("MO_Issue_Date"), "DD-MMM-YYYY")
Reports("Report1").fr_name.Form.Lbl_Pr_T.Caption = rst("MO_Issue_Time")

Set rst = Nothing

Forms(fr_name).Controls(Lbl_Mat_D).Caption = var_matdes
Forms(fr_name).Controls(Lbl_T_Pg).Caption = DLookup("Total_Pages", "Tbl_MO_Rev_Control", "MO_Rev_No = '" & var_matrev & "'")
Forms(fr_name).Controls(Lbl_SAP).Caption = "Form # " & DLookup("SAP_Form_no", "Tbl_MO_Rev_Control", "MO_Rev_No = '" & var_matrev & "'")

Set rst = dbs.OpenRecordset("Tbl_MO_Op_Archive", Type:=dbOpenDynaset)
rst.FindFirst "[Order_Number] = " & var_order '& "*'" & "AND MO_Rev_No ='" & var_mo_rev & ""

If var_op_n = "0010" Then
    Forms(fr_name).Controls(Lbl_Ver_Drw_T).Visible = True
    Forms(fr_name).Controls(Lbl_Ver_Drw_B).Visible = True
    Forms(fr_name).Controls(Lbl_Ver_Drw_Sign).Visible = True
    Forms(fr_name).Controls(Lbl_Ver_Drw_Dt).Visible = True
    Forms(fr_name).Controls(Lbl_Pg).Caption = rst("10_Page_No")
    Forms(fr_name).Controls(Lbl_Ver_Drw_Sign).Caption = rst("10_Verif_Drw_Rev_Stamp")
    Forms(fr_name).Controls(Lbl_Ver_Drw_Dt).Caption = rst("10_Verif_Drw_Rev_Date_Stamp")
        'hide stamp labels for op 0020 to 0050
    Forms(fr_name).Controls(Lbl_FPV).Visible = False
    Forms(fr_name).Controls(Lbl_Clk).Visible = False
    Forms(fr_name).Controls(Lbl_Sign_Date).Visible = False
    Forms(fr_name).Controls(Lbl_VS_Acc).Visible = False
    Forms(fr_name).Controls(Lbl_VS_Rej).Visible = False
    Forms(fr_name).Controls(Lbl_PC_NA).Visible = False
    Forms(fr_name).Controls(Img_VS_Stamp).Visible = False
    Forms(fr_name).Controls(Lbl_Stamp_Type).Visible = False
    Forms(fr_name).Controls(Lbl_PC_Stamp).Visible = False
    Forms(fr_name).Controls(Lbl_PC_Acc).Visible = False
    Forms(fr_name).Controls(Lbl_PC_Rej).Visible = False
    Forms(fr_name).Controls(Lbl_Rej).Visible = False
ElseIf var_op_n = "0020" Then
    Forms(fr_name).Controls(Lbl_FPV).Visible = True
    Forms(fr_name).Controls(Lbl_Clk).Visible = True
    Forms(fr_name).Controls(Lbl_Sign_Date).Visible = True
    Forms(fr_name).Controls(Lbl_VS_Acc).Visible = True
    Forms(fr_name).Controls(Lbl_VS_Rej).Visible = True
    Forms(fr_name).Controls(Lbl_PC_NA).Visible = True
    Forms(fr_name).Controls(Lbl_PC_Acc).Visible = True
    Forms(fr_name).Controls(Lbl_PC_Rej).Visible = True
    Forms(fr_name).Controls(Lbl_Rej).Visible = True

    Forms(fr_name).Controls(Lbl_Ver_Drw_T).Visible = False
    Forms(fr_name).Controls(Lbl_Ver_Drw_B).Visible = False
    Forms(fr_name).Controls(Lbl_Ver_Drw_Sign).Visible = False
    Forms(fr_name).Controls(Lbl_Ver_Drw_Dt).Visible = False
    Forms(fr_name).Controls(Lbl_Pg).Caption = rst("20_Page_No")
    Forms(fr_name).Controls(Lbl_FPV).Caption = rst("20_First_Pc_Verif_Stamp")
    Forms(fr_name).Controls(Lbl_Clk).Caption = rst("20_Clock_No_Stamp")
    Forms(fr_name).Controls(Lbl_Sign_Date).Caption = rst("20_Date_Stamp")
    Forms(fr_name).Controls(Lbl_VS_Acc).Caption = rst("20_Sample_Verif_Acc_Stamp")
    Forms(fr_name).Controls(Lbl_VS_Rej).Caption = rst("20_Sample_Verif_Rej_Stamp")
    Forms(fr_name).Controls(Lbl_PC_NA).Caption = rst("20_Verif_Stamp_Type")          'user stamp not stored in 0020 op, only NA comment
    Forms(fr_name).Controls(Lbl_PC_Acc).Caption = rst("20_Pc_Comp_Acc_Stamp")
    Forms(fr_name).Controls(Lbl_PC_Rej).Caption = rst("20_Pc_Comp_Rej_Stamp")
    Forms(fr_name).Controls(Lbl_Rej).Caption = rst("20_Reject_No_Stamp")
ElseIf var_op_n = "0030" Then
    Forms(fr_name).Controls(Img_VS_Stamp).Visible = False
    Forms(fr_name).Controls(Lbl_Stamp_Type).Visible = False
    Forms(fr_name).Controls(Lbl_PC_Stamp).Visible = False
    Forms(fr_name).Controls(Lbl_Pg).Caption = rst("30_Page_No")
    Forms(fr_name).Controls(Lbl_FPV).Caption = rst("30_First_Pc_Verif_Stamp")
    Forms(fr_name).Controls(Lbl_Clk).Caption = rst("30_Clock_No_Stamp")
    Forms(fr_name).Controls(Lbl_Sign_Date).Caption = rst("30_Date_Stamp")
    Forms(fr_name).Controls(Lbl_VS_Acc).Caption = rst("30_Sample_Verif_Acc_Stamp")
    Forms(fr_name).Controls(Lbl_VS_Rej).Caption = rst("30_Sample_Verif_Rej_Stamp")
    Forms(fr_name).Controls(Lbl_PC_NA).Caption = rst("30_Verif_Stamp_Type")          'user stamp not stored in 0030 op, only NA comment
    Forms(fr_name).Controls(Lbl_PC_Acc).Caption = rst("30_Pc_Comp_Acc_Stamp")
    Forms(fr_name).Controls(Lbl_PC_Rej).Caption = rst("30_Pc_Comp_Rej_Stamp")
    Forms(fr_name).Controls(Lbl_Rej).Caption = rst("30_Reject_No_Stamp")
ElseIf var_op_n = "0040" Then
    Forms(fr_name).Controls(Lbl_Pg).Caption = rst("40_Page_No")
    Forms(fr_name).Controls(Lbl_FPV).Caption = rst("40_First_Pc_Verif_Stamp")
    Forms(fr_name).Controls(Lbl_Clk).Caption = rst("40_Clock_No_Stamp")
    Forms(fr_name).Controls(Lbl_Sign_Date).Caption = rst("40_Date_Stamp")
    Forms(fr_name).Controls(Lbl_VS_Acc).Caption = rst("40_Sample_Verif_Acc_Stamp")
    Forms(fr_name).Controls(Lbl_VS_Rej).Caption = rst("40_Sample_Verif_Rej_Stamp")
    Forms(fr_name).Controls(Lbl_PC_Stamp).Caption = rst("40_Verification_Stamp")                     'user stamp IS stored in 0040 op
    Forms(fr_name).Controls(Lbl_Stamp_Type).Caption = rst("40_Verif_Stamp_Type")                     'user stamp IS stored in 0040 op
    Forms(fr_name).Controls(Lbl_PC_Acc).Caption = rst("40_Pc_Comp_Acc_Stamp")
    Forms(fr_name).Controls(Lbl_PC_Rej).Caption = rst("40_Pc_Comp_Rej_Stamp")
    Forms(fr_name).Controls(Lbl_Rej).Caption = rst("40_Reject_No_Stamp")
ElseIf var_op_n = "0050" Then
    Forms(fr_name).Controls(Img_VS_Stamp).Visible = False
    Forms(fr_name).Controls(Lbl_Stamp_Type).Visible = False
    Forms(fr_name).Controls(Lbl_PC_Stamp).Visible = False
    Forms(fr_name).Controls(Lbl_Pg).Caption = rst("50_Page_No")
    Forms(fr_name).Controls(Lbl_FPV).Caption = rst("50_First_Pc_Verif_Stamp")
    Forms(fr_name).Controls(Lbl_Clk).Caption = rst("50_Clock_No_Stamp")
    Forms(fr_name).Controls(Lbl_Sign_Date).Caption = rst("50_Date_Stamp")
    Forms(fr_name).Controls(Lbl_VS_Acc).Caption = rst("50_Sample_Verif_Acc_Stamp")
    Forms(fr_name).Controls(Lbl_VS_Rej).Caption = rst("50_Sample_Verif_Rej_Stamp")
    Forms(fr_name).Controls(Lbl_PC_NA).Caption = rst("50_Verif_Stamp_Type")          'user stamp not stored in 0030 op, only NA comment
    Forms(fr_name).Controls(Lbl_PC_Acc).Caption = rst("50_Pc_Comp_Acc_Stamp")
    Forms(fr_name).Controls(Lbl_PC_Rej).Caption = rst("50_Pc_Comp_Rej_Stamp")
    Forms(fr_name).Controls(Lbl_Rej).Caption = rst("50_Reject_No_Stamp")
End If

If Form_num = 5 Then
    Set rst = Nothing
    Set dbs = Nothing
End If

If var_op_n = "0020" Or var_op_n = "0050" Then
    Forms(fr_name).Controls(Lbl_FPV).Top = 5250
    Forms(fr_name).Controls(Lbl_Clk).Vertical = False
    Forms(fr_name).Controls(Lbl_Clk).Top = 5250
    Forms(fr_name).Controls(Lbl_Sign_Date).Top = 5250
    Forms(fr_name).Controls(Lbl_VS_Acc).Top = 5250
    Forms(fr_name).Controls(Lbl_VS_Rej).Top = 5250
    Forms(fr_name).Controls(Lbl_PC_NA).Top = 5250
    Forms(fr_name).Controls(Lbl_PC_Acc).Top = 5250
    Forms(fr_name).Controls(Lbl_PC_Rej).Top = 5250
    Forms(fr_name).Controls(Lbl_Rej).Top = 5250
Else
    Forms(fr_name).Controls(Lbl_FPV).Top = 7250
    Forms(fr_name).Controls(Lbl_Clk).Vertical = True
    Forms(fr_name).Controls(Lbl_Clk).Top = 7250
    Forms(fr_name).Controls(Lbl_Sign_Date).Top = 7250
    Forms(fr_name).Controls(Lbl_VS_Acc).Top = 7250
    Forms(fr_name).Controls(Lbl_VS_Rej).Top = 7250
    Forms(fr_name).Controls(Lbl_PC_NA).Top = 7250
    Forms(fr_name).Controls(Lbl_PC_Acc).Top = 7250
    Forms(fr_name).Controls(Lbl_PC_Rej).Top = 7250
    Forms(fr_name).Controls(Lbl_Rej).Top = 7250
End If

Call OpNum
Call MatNum
Call MatRev
'Forms(fr_name)(Lbox_Op_List).Requery
'Forms(fr_name).Controls(Lbox_Op).Requery
Forms(fr_name).Controls(Lbl_Op).Caption = DLookup("Operation_Description", "Tbl_Ops_Desc", "MO_Rev_No = '" & var_matrev & "' AND Oper_No ='" & var_op_n & "'")

End Sub

Report Structure and Code movement Getting stuck at step 6, All forms have same controls and control names

please advice, I guess the report subform control referencing is wrong?

1
ms-accessvbams-access-2010ms-access-2007
Please add Option Explicit to your modules and compile the code, probably you made a mistake in variable names. As I understand, instead of fr_name.Controls should be fr_name.Form.Controls. Whal is Lbl_ON? – Sergey S.
This look overly complicated and probably unnecessary. I'm mystified by the need to move controls around within a form. Just hided / unhide as required, you can sit them on top of each other if you really need multiple controls. Also You can use the tag property to loop around sets of controls and enable / change them. – Minty
@Minty I have edited the question to include the Report structure and code movement. I had to do this as all subforms are copies of each other with same control name and just the control values are being changed in each subform. – Tinker
@Sergey S. adding Option Explicit stops the code on Forms(fr_name).Controls(Lbl_Mat_D).Caption = var_matdes and required me to define Lbl_Mat_D as a variable when it actually is a control name; I have corrected the fr_name.Controls to 'fr_name.Form.Controls' the report is poping up but the recordset values are not getting assigned to any of the subforms – Tinker
@Sergey S. Lbl_ON is the name of a control on the form Label OrderNumber I have edited the question to include the Report structure and code movement. All subforms are copies of each other with same control name and just the control values are being changed in each subform. – Tinker

1 Answers

0
votes

As to your code, Lbl_ON has not been assigned a value here:

Reports("Report1").fr_name.Controls(Lbl_ON).Caption = rst("Order_Number")

To check, insert a debug line. Also, try specifying the Value property:

Debug.Print "Lbl_ON:", Lbl_ON
Reports("Report1").fr_name.Controls(Lbl_ON).Caption = rst("Order_Number").Value

If Lbl_ON can be Null, there is no control to access, thus:

If Not IsNull(Lbl_ON) then
    Reports("Report1").fr_name.Controls(Lbl_ON).Caption = rst("Order_Number").Value
End If

That will, of course, not adjust anything, so you will wish to correct that Lbl_ON has no value.