Solved

Invalid reference to Property on Form/Report

Posted on 2009-04-06
9
273 Views
Last Modified: 2013-11-28
I have a form containing Mainform w/Subform w/subform2.  On Subform2, has a combo which rowsource changes upon selection of a Option value.  I also need to set the rowsource of the combo on Load  (default value = ALL) of the Mainform from the swtichboard.  For some reason the code errors on load of the Mainform - I am setting the defalutvalue of the Option (frmeViews_click) = 1 ("ALL")  - to update the Rowsource for the combo on Subform2, however, continue thru the error and you make another selection on the frmeViews, including the "ALL", the code runs successfully.  

What can prevent the code from running on the load of the Mainform, but work when the form is active?  See the setting of the Sub2, field SparesXdcrNo.rowsource - this is where the code breaks on load of the form.

  Forms![frmXdcr_MeasNo_Assign]![SUB1].Form![Sub2].Form![SparesXdcrNo].RowSource = strSQL & " " & strWhere & " " & strSQL1"
Private Sub frmeXdcrSort_Click()
 

   On Error GoTo frmeXdcrSort_Click_Error
 

 strSQL = "SELECT FirstofEquipment_ID as Xdcr_No, Model as MFR_NO, LTrim([Nomenclature_Modifier]) AS EQPT_NAME, EQPT_LOC_NO, SERVICE_ORGN_CODE as SvcOrgn, SERVICE_DUE_DATE_CMT AS ServDueDate, LAST_SERVICE_DATE" & _

        " FROM TL_FTEM "

 gAPNo = DLookup("FTIR_APno", "TA_FTIR")

 Select Case frmeXdcrSort

        Case 1 'All

            Me.txtLkUp.Visible = False

            strWhere = "WHERE (EQPT_LOC_NO Like ""*" & GetgAPNo() & "*"")"

        Case 2 'FTX"

            Me.txtLkUp.Visible = False

            strWhere = "WHERE (FirstofEquipment_ID) Like 'FTX*' AND" & _

                        " (EQPT_LOC_NO Like ""*" & GetgAPNo() & "*"")"

        Case 3 'FTC

            Me.txtLkUp.Visible = False

            strWhere = "WHERE (FirstofEquipment_ID) Like 'FTC*' AND" & _

                        " (EQPT_LOC_NO Like ""*" & GetgAPNo() & "*"")"

        Case 4 'Service Due Date

            Me.txtLkUp.Visible = False

            strWhere = " WHERE (EQPT_LOC_NO Like ""*" & GetgAPNo() & "*"") AND" & _

                        " (SERVICE_DUE_DATE_CMT) Between DateAdd('yyyy',-1,Date()) And DateAdd('yyyy',1,Date())"

        Case 5

            Me.txtLkUp.Visible = True

            GoTo ResumeNext:

        Case 6 ' MfgNo wildcard search - limits the combo to specified MfgNo.

            strWhere = " WHERE (EQPT_LOC_NO Like ""*" & GetgAPNo() & "*"") AND" & _

                        " ((Model) Like ""*" & GetgMfgNo() & "*"")"

            Me.frmeXdcrSort.Value = 5

        Case 7 'Lab Only

            strWhere = " WHERE (EQPT_LOC_NO Like 'Lab*')"

End Select

    strSQL1 = " Group By FirstofEquipment_ID, Model, Nomenclature_Modifier, EQPT_LOC_NO, SERVICE_ORGN_CODE," & " SERVICE_DUE_DATE_CMT, LAST_SERVICE_DATE" & _

              " ORDER BY Model, FirstofEquipment_ID, EQPT_LOC_NO"

    cmdExpand_Click

    Forms![frmXdcr_MeasNo_Assign]![SUB1].Form![Xdcr_No].RowSource = strSQL & " " & strWhere & " " & strSQL1

    Forms![frmXdcr_MeasNo_Assign]![SUB1].Form![Sub2].Form![SparesXdcrNo].RowSource = strSQL & " " & strWhere & " " & strSQL1

ResumeNext:

    If Me.SUB1.Form.SubdatasheetExpanded = True Then

        cmdCollapse_Click

    End If

    Me.Refresh

   On Error GoTo 0

   Exit Sub
 

frmeXdcrSort_Click_Error:

    MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure frmeXdcrSort_Click of VBA Document Form_frmXdcr_MeasNo_Assign"

End Sub

Open in new window

errmsg.jpg
0
Comment
Question by:Karen Schaefer
  • 5
  • 4
9 Comments
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 24079364
First, I could find no reference to "Pilot's Column" in the code you posted.
Can you state what line the error occured on?

This leads me to belive that this might be an expression in a Table or query.

Check and run all the queries associated with this code, to see where the error might be.

JeffCoachman
0
 

Author Comment

by:Karen Schaefer
ID: 24079382
Jeff,

What are you talking about Pilot's column????
0
 

Author Comment

by:Karen Schaefer
ID: 24079385
sorry posted wrong picture.
0
 

Author Comment

by:Karen Schaefer
ID: 24079396
Here is the correct one
ErrorMsg.png
0
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 24081247
Try:

Forms![frmXdcr_MeasNo_Assign]![SUB1].Form![Sub2].Form![SparesXdcrNo].Recordsource = strSQL & " " & strWhere & " " & strSQL1"

JeffCoachman
0
 

Accepted Solution

by:
Karen Schaefer earned 0 total points
ID: 24089414
thanks for the suggestion however, the datasource for the combo should be RowSource not recordSource.

I decided to by pass the error msg- since the code is working in spite of the error msg.

frmeXdcrSort_Click_Error:
If Err.Number = 2455 Then
    Exit Sub
Else: MsgBox "Error " & Err.Number & " (" & Err.Description & _
    ") in procedure frmeXdcrSort_Click of VBA Document Form_frmXdcr_MeasNo_Assign"
End If
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 24090252
"thanks for the suggestion however, the datasource for the combo should be RowSource not recordSource."

Sorry it is hard to tell what your objects are based on the names.

So your solution is to simply ignore the error rather than to find its cause and fix it?

Bypassing the error is only a good idea if you know what the error is and why it occurs.
This means that you may have to add this "Bypass" code to every sub where the error occurs.
This means that you may be bypassing the error in a situation that may wreak havoc on your database, and you might not know, or when you do find out, it may be difficult to fix.

But I leave these issue to you.

JeffCoachman
0
 

Author Comment

by:Karen Schaefer
ID: 24090802
I am still trying to figure out what causing this problem - however, for now this is the solution -  do you have nay other ideas what can cause this issue?
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 24093104
Well without delving deep into the inner workings of your database, this will be difficult to even guess at.
(For me at least)

Perhaps it has something to do with the fact that a lot of the times, the Subforms events fire before the main forms events.

It's just that I have been bitten may times by attempts to bypass errors.

JeffCoachman
0

Featured Post

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

863 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

23 Experts available now in Live!

Get 1:1 Help Now