Karen Schaefer
asked on
Invalid reference to Property on Form/Report
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_Assi gn]![SUB1] .Form![Sub 2].Form![S paresXdcrN o].RowSour ce = strSQL & " " & strWhere & " " & strSQL1"
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_Assi
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
errmsg.jpg
ASKER
Jeff,
What are you talking about Pilot's column????
What are you talking about Pilot's column????
ASKER
sorry posted wrong picture.
ASKER
Here is the correct one
ErrorMsg.png
ErrorMsg.png
Try:
Forms![frmXdcr_MeasNo_Assi gn]![SUB1] .Form![Sub 2].Form![S paresXdcrN o].Records ource = strSQL & " " & strWhere & " " & strSQL1"
JeffCoachman
Forms![frmXdcr_MeasNo_Assi
JeffCoachman
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
"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
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
ASKER
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?
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
(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
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