Object doesn't support this property or method, Error 438-CodePudding

I am trying to do some math through VBA, and came across this error which I haven't seen before. Some googling tells me that it has to do with Intelli-Sense options that aren't available for the object being applied to it. But as far as I can tell, it shouldn't be messing up anything here because I didn't use Intelli-Sense in the line the debugger is highlighting. I will post the full code below, any insight is very much appreciated!!


    'Declare Variable
    Dim L As Double
    Dim Length As Double
    Dim OrderFTG As Double
    Dim UoM As String
    Dim W As Double
    Dim frm As Access.Form
    Set frm = Forms!Frm_JobTicket
    
    'Set L equal to Length from Tbl_JobTicketMould
    L = DLookup("Length", "Tbl_JobTicketMould", "Access_ID =" & Forms!Frm_JobTicket!Part_Number)
    
    'Convert Length to Feet
    Length = (L \ 12)
    
    'Find Unit of Measure for this part
    UoM = DLookup("Unit_of_Measure", "Tbl_JobTicketUoM", "Access_ID =" & Forms!Frm_JobTicket!Part_Number)
    
    'Mupltiply Length times Quantity to get Order Footage
    OrderFTG = Int((Length * Me.Txt_Pcs_JobTicket))
    
    'If UoM is PCS then insert that number. Otherwise set equal to Quantity Ordered divided by Length of piece(in FT)
    If UoM = "PCS" Then Me.Txt_Pcs_JobTicket = Me.Quantity_Ordered Else: Me.Txt_Pcs_JobTicket = Abs(Int(Me.Quantity_Ordered \ Length))
    
    'Define limits of the loop. Then runs through all Wrap SQ FTG fields and inputs calculation
    For W = 1 To 3
    
       'If UoM is PCS then calculate Order Footage to find Wrap Sqaure Footage. Otherwise take slit size in FT and multiply by Order Quantity and Scrap Rate
    If UoM = "PCS" Then
    frm("Txt_Wrap" & W & "SQFTG_JobTicket") = (((frm("Wrap_Slit" & W) \ 12) * OrderFTG) * (frm(RIP_Scrap_Rate   1)))
    Else: frm("Txt_Wrap" & W & "SQFTG_JobTicket") = (((frm("Wrap_Slit" & W) \ 12) * frm(Quantity_Ordered)) * (frm(RIP_Scrap_Rate   1)))
    End If
    Next W

I am also having trouble with this line OrderFTG = Abs(Int((Length * Me.Txt_Pcs_JobTicket))) It keeps coming out to half of the required amount, the data tips for the Abs part of the evaluation will come out to 12,000 and OrderFTG then equals 6,000. I am not sure how that is happening as there is no division whatsoever even happening in that expression.

Thank you!

CodePudding user response：

This simpler example demonstrates the problem I think you're dealing with.

I added a text box, txtScrap_Rate, to my form, opened it in Form View, and entered the number 27 into the text box.

So then I can retrieve that value with the frm(<control name>) approach you're using ... as long as I enclose the control name in quotes.

set frm = Forms!Form18b
? frm("txtScrap_Rate")
 27 
? frm("txtScrap_Rate")   1 ' add one to Scrap Rate
 28

Without the quotes, Access interprets txtScrap_Rate to be an undeclared variable instead of a control name.

? frm(txtScrap_Rate)
Null
' result is the same as giving it any undeclared variable name, such as this one:
? frm(Bogus)
Null

And, without the quotes, if I try to also do the plus one inside the parentheses ...

? frm(txtScrap_Rate   1)

... I get the same 438 error as you:

Object doesn't support this property or method

I think you have this issue with RIP_Scrap_Rate in two places. Also I suspect you need to quote the control name in frm(Quantity_Ordered)

You would be wise to include Option Explicit in your module's Declarations section. Access will then alert you about anything it thinks is an undeclared variable but you think should be something else.