Access 2007 Form Displays #Error in all fields. Runtime error: -2147352567 (80020009)

AAlpha1
AAlpha1 used Ask the Experts™
on
I was working on form. All along, I tested the effect of new code as I added it.
The last thing I did was to enter a line of code that adds  field1 and 2 ..., etc
The form has three global variables whose value change based on which sub produced the value.

The form then displayed #Name? in ALL fields. Runtime error -2147352567 (80020009)
The control source table is not affected at all. It displays correct data.
The form would not refresh.

Any ideas? Thanks
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®

Commented:
post your code

Author

Commented:
Code below
Option Compare Database
Dim xInsCost As Variant, xLCcost As Variant, xLossCost As Variant
 
' -------------------------------------------------------------------
' Initialize on Form Load
' -------------------------------------------------------------------
Private Sub Form_Load()
 
    Call ResetCostFilter
    Me.Requery
    
End Sub
 
' -------------------------------------------------------------------
' Go to the Next record button
' -------------------------------------------------------------------
Private Sub CostNextRec_Click()
    
    Dim vDelID As Variant
    
    DoCmd.GoToRecord acDataForm, "fCosts", acNext
'    DoCmd.SelectObject acForm, "fDeliveries"            ' This works when the from is open
 
    vDelID = Forms!fCosts!DelID                ' Define delivery ID
    
    Dim FieldValue As Variant, IsFieldNull As String
    FieldValue = vDelID                      ' Define Field Value
    Call NullChk(FieldValue, IsFieldNull)   ' Check if Field is Null
 
    If IsFieldNull = "True" Then
            GoTo DelID_Err                   ' Stop further action
        Else
            Exit Sub
    End If
       
DelID_Err:
    MsgBox ("Undefined delivery ID." & Chr$(10) _
            & "No action can be taken on this record.")
    DoCmd.GoToRecord acDataForm, "fCosts", acFirst      ' Go to First record
 
End Sub
 
' -------------------------------------------------------------------
' Find Cost Data
' -------------------------------------------------------------------
Private Sub FindCosts_Click()
 
    Me.Requery
    
    DoCmd.GoToRecord acDataForm, "fCosts", acFirst
    If IsNull(IntConID) = False Then
        Exit Sub
    End If
    
    MsgBox ("Warning: Cost data not found")
    
'    DoCmd.OpenQuery "qCosts", acViewNormal
'    DoCmd.Close acQuery, "qCosts", acSaveNo
 
End Sub
 
' -------------------------------------------------------------------
' Insurance Cost Buttons
' -------------------------------------------------------------------
Private Sub FixInsCost_Click()
    Dim vInsCostFix
    vInsCostFix = Forms!fCosts!InsCostEst
    xInsCost = Forms!fCosts!InsCostEst
    MsgBox (" Ins Cost = " & xInsCost)
    
End Sub
 
Private Sub ClearInsCost_Click()
 
    Me.InsCostFix = ""
    xInsCost = Me.InsCostEst
    MsgBox (" Ins Cost = " & xInsCost)
    
End Sub
 
' -------------------------------------------------------------------
' LC Cost Buttons
' -------------------------------------------------------------------
Private Sub FixLCcost_Click()
 
    Me.LCcostFix = Me.LCcostEst
    xLCcost = Me.LCcostFix
    
End Sub
 
Private Sub ClearLCcost_Click()
 
    Me.LCcostFix = ""
    xLCcost = Me.LCcostEst
    
End Sub
 
' -------------------------------------------------------------------
' Loss Cost Buttons
' -------------------------------------------------------------------
Private Sub FixLossCost_Click()
 
    Me.LossCostFix = Me.LossCostEst
    xLossCost = Me.LossCostFix
    
End Sub
 
Private Sub ClearLossCost_Click()
 
    Me.LossCostFix = ""
    xLossCost = Me.LossCostEst
    
End Sub
 
' -------------------------------------------------------------------
' Reset filter criteria Button
' -------------------------------------------------------------------
Private Sub ResetCosts_Click()
 
    Call ResetCostFilter
    
End Sub
 
' -------------------------------------------------------------------
' Reset filter criteria
' -------------------------------------------------------------------
Private Sub ResetCostFilter()
    
    Forms!fCosts!fIntCPid = ""
    Forms!fCosts!fExtCPid = ""
    Forms!fCosts!fIntConID = ""
 
End Sub
 
Private Sub SaveDelCost_Click()
    Dim DelValue
    
    DelValue = 0.001 * (Forms!fCosts!QuantPerDel * Forms!fCosts!QuantPerDelCF * Forms!fCosts!uPubPrice)
    Me.InsCostEst = Me.InsCostPct * DelValue
    Me.InspectCost = Me.InspectSharePct * Me.InspectCostTot
    Me.LCcostEst = Me.LCcostPct * DelValue
    Me.LossCostEst = Me.LossCostPct * DelValue
    
' Calculate Total Costs
' -------------------------------------------------------------------
'    Me.TotalCosts = Me.VesselCosts + Me.DemCosts + xInsCost + Me.InspectCost + xLCcost + xLossCost + Me.Taxes
    MsgBox (" Total Costs = " & Me.TotalCosts)
 
    Me.Refresh
 
    DoCmd.RunCommand acCmdSaveRecord
    
End Sub

Open in new window

Commented:
If IsFieldNull = "True" Then  <<<==== Remove  "" if you used boolean data type
            GoTo DelID_Err                   ' Stop further action
        Else
Success in ‘20 With a Profitable Pricing Strategy

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Author

Commented:
I removed the whole Sub. Line 110 stopped the execution. Thank you.
Option Compare Database
Dim xInsCost As Variant, xLCcost As Variant, xLossCost As Variant
 
' -------------------------------------------------------------------
' Initialize on Form Load
' -------------------------------------------------------------------
Private Sub Form_Load()
 
    Call ResetCostFilter
    Me.Requery
    
End Sub
 
 
' -------------------------------------------------------------------
' Find Cost Data
' -------------------------------------------------------------------
Private Sub FindCosts_Click()
 
    Me.Requery
    
    DoCmd.GoToRecord acDataForm, "fCosts", acFirst
    If IsNull(IntConID) = False Then
        Exit Sub
    End If
    
    MsgBox ("Warning: Cost data not found")
    
'    DoCmd.OpenQuery "qCosts", acViewNormal
'    DoCmd.Close acQuery, "qCosts", acSaveNo
 
End Sub
 
' -------------------------------------------------------------------
' Insurance Cost Buttons
' -------------------------------------------------------------------
Private Sub FixInsCost_Click()
    Dim vInsCostFix
    vInsCostFix = Forms!fCosts!InsCostEst
    xInsCost = Forms!fCosts!InsCostEst
    MsgBox (" Ins Cost = " & xInsCost)
    
End Sub
 
Private Sub ClearInsCost_Click()
 
    Me.InsCostFix = ""
    xInsCost = Me.InsCostEst
    MsgBox (" Ins Cost = " & xInsCost)
    
End Sub
 
' -------------------------------------------------------------------
' LC Cost Buttons
' -------------------------------------------------------------------
Private Sub FixLCcost_Click()
 
    Me.LCcostFix = Me.LCcostEst
    xLCcost = Me.LCcostFix
    
End Sub
 
Private Sub ClearLCcost_Click()
 
    Me.LCcostFix = ""
    xLCcost = Me.LCcostEst
    
End Sub
 
' -------------------------------------------------------------------
' Loss Cost Buttons
' -------------------------------------------------------------------
Private Sub FixLossCost_Click()
 
    Me.LossCostFix = Me.LossCostEst
    xLossCost = Me.LossCostFix
    
End Sub
 
Private Sub ClearLossCost_Click()
 
    Me.LossCostFix = ""
    xLossCost = Me.LossCostEst
    
End Sub
 
' -------------------------------------------------------------------
' Reset filter criteria Button
' -------------------------------------------------------------------
Private Sub ResetCosts_Click()
 
    Call ResetCostFilter
    
End Sub
 
' -------------------------------------------------------------------
' Reset filter criteria
' -------------------------------------------------------------------
Private Sub ResetCostFilter()
    
    Forms!fCosts!fIntCPid = ""
    Forms!fCosts!fExtCPid = ""
    Forms!fCosts!fIntConID = ""
 
End Sub
 
Private Sub SaveDelCost_Click()
    Dim DelValue
    
    DelValue = 0.001 * (Forms!fCosts!QuantPerDel * Forms!fCosts!QuantPerDelCF * Forms!fCosts!uPubPrice)
    Me.InsCostEst = Me.InsCostPct * DelValue
    Me.InspectCost = Me.InspectSharePct * Me.InspectCostTot
    Me.LCcostEst = Me.LCcostPct * DelValue
    Me.LossCostEst = Me.LossCostPct * DelValue
    
' Calculate Total Costs
' -------------------------------------------------------------------
'    Me.TotalCosts = Me.VesselCosts + Me.DemCosts + xInsCost + Me.InspectCost + xLCcost + xLossCost + Me.Taxes
    MsgBox (" Total Costs = " & Me.TotalCosts)
 
    Me.Refresh
 
    DoCmd.RunCommand acCmdSaveRecord
    
End Sub

Open in new window

Commented:
Check the availability of that values. also try this, if you are facing to error post the error description and the error line

DelValue = 0.001 * (NZ(Forms!fCosts!QuantPerDel) * NZ(Forms!fCosts!QuantPerDelCF) * Nz(Forms!fCosts!uPubPrice))

Author

Commented:
Just before deleting the form, I checked the record source. That space was BLANK.
No wonder it could not find anything.

I'm in the process of adding the code back in.

I'll keep you posted.

Thanks
Commented:
The main cause of the problem was that the Record Source was blank (property sheet selection set to Form, it shows the table/query used as Record source)
I found it unusual that the Me. property did display the fields of the table! All the fields displayed the correct name defined in the table.

Thank you for the tips. I will certainly keep the Nz Method in mind.
Albert

Commented:
how you solve it?

Author

Commented:
The hint was that the table was unaffected, even as I saved the record, and, no other records were displayed; i.e. the form was no longer bound to the table.
Please see the attached document.
Property-Sheet.doc

Commented:
ok

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial