Results 1 to 2 of 2

Error Object required: Please help

  1. #1
    lccrews is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Aug 2017
    Location
    Los Angeles
    Posts
    178

    Error Object required: Please help


    I am currently trying to learn more about VBA, and to practice, I am converting VBA that refers to queries to straight VBA without referencing queries. Unfortunately, I'm off to a rough start. I read that you could use multiple DAO recordsets in one sub/function. Is that true? I'm trying to convert this code:
    Code:
    Private Sub Weight_AfterUpdate()
    
    If Me.Dirty Then Me.Dirty = False
    
    If DLookup("SumOfWeight", "qry_BP10_BlendingCheck_Pt1") > DLookup("Quantity", "qry_BP10_BlendingCheck_Pt2") Then
    Output = MsgBox("Mix exceeds quantity from formula.", vbCritical, "Error") = vbAbort
    Me.Weight.Value = "0"
    
    
    End If
    
    
    End Sub
    To this:
    Code:
    Private Sub Weight_AfterUpdate()
    Dim SumOfWeight As Double
    Dim Quantity As Double
    Dim strSQL1 As String
    Dim strSQL2 As String
    Dim rst1 As DAO.Recordset
    Dim rst2 As DAO.Recordset
    
    
    strSQL1 = "SELECT tbl_MBR_MiscSteps.RawMaterial, Sum(tbl_MBR_MiscSteps.Weight) AS SumOfWeight " _
            & "FROM tbl_MBR_MiscSteps " _
            & "WHERE (((tbl_MBR_MiscSteps.Step) Like 'Bl01' Or (tbl_MBR_MiscSteps.Step) Like 'Bl02' Or (tbl_MBR_MiscSteps.Step) Like 'Bl03' " _
            & "Or (tbl_MBR_MiscSteps.Step) Like 'Bl04' Or (tbl_MBR_MiscSteps.Step) Like 'Bl05' Or (tbl_MBR_MiscSteps.Step) Like 'Bl06' " _
            & "Or (tbl_MBR_MiscSteps.Step) Like 'Bl07' Or (tbl_MBR_MiscSteps.Step) Like 'Bl08' Or (tbl_MBR_MiscSteps.Step) Like 'Bl09' " _
            & "Or (tbl_MBR_MiscSteps.Step) Like 'Bl10')) " _
            & "GROUP BY tbl_MBR_MiscSteps.CO, tbl_MBR_MiscSteps.RawMaterial " _
            & "HAVING (((tbl_MBR_MiscSteps.CO)='" & [Forms]![frm_BP10_Tablet_MBR_Process]![CO] & "') " _
            & "AND ((tbl_MBR_MiscSteps.RawMaterial)='" & [Forms]![frm_BP10_Tablet_ViewMBR]![qry_BP10_Tablet_Blending subform].[Form]![RawMaterial] & "'));"
    
    
    Set rst1 = CurrentDb.OpenRecordset(strSQL1)
    
    
    SumOfWeight = rst!SumOfWeight
    
    
    strSQL2 = "SELECT tbl_Formulas.Quantity " _
            & "FROM tbl_Formulas " _
            & "WHERE (((tbl_Formulas.RawMaterial)='" & [forms]![frm_BP10_Tablet_ViewMBR]![qry_BP10_Tablet_Blending subform].[Form]![RawMaterial]) & "' " _
            & "AND ((tbl_Formulas.Item)='" & [forms]![frm_BP10_Tablet_MBR_Process]![ITEM] & "') AND ((tbl_Formulas.BP)='" & [forms]![frm_BP10_Tablet_MBR_Process]![BP] & "') " _
            & "AND ((tbl_Formulas.BillType)='" & [forms]![frm_BP10_Tablet_MBR_Process]![BILLTYPE] & "') AND ((tbl_Formulas.Old)=No));"
            
    Set rst2 = CurrentDb.OpenRecordset(strSQL2)
    
    
    Quantity = rst!Quantity
    
    
    If SumOfWeight > Quantity Then Output = MsgBox("Mix exceeds quantity from formula.", vbCritical, "Error") = vbAbort
    
    
    Me.Weight.Value = "0"
    
    
    rst1.Close
    Set rst1 = Nothing
    
    
    rst2.Close
    Set rst2 = Nothing
    
    
    End Sub
    I'm getting object required error and it is pointing to this line:
    Code:
    SumOfWeight = rst!SumOfWeight
    Can anyone tell me what is wrong with my code? Or is it not possible to set multiple rst's.
    Last edited by lccrews; 06-14-2018 at 12:45 PM.

  2. #2
    lccrews is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Aug 2017
    Location
    Los Angeles
    Posts
    178
    I solved my own problem. I should have put "SumOfWeight = rst1!SumOfWeight" and the same for the second part. Simple mistake.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Object required error in If statement
    By monardasprite in forum Programming
    Replies: 4
    Last Post: 12-03-2015, 01:02 PM
  2. Runtime 424 error... Object required.
    By sanderson in forum Programming
    Replies: 8
    Last Post: 08-09-2015, 07:10 PM
  3. Object Required Error
    By sgp667 in forum Programming
    Replies: 1
    Last Post: 11-06-2012, 02:15 AM
  4. Error: Object Required
    By compooper in forum Programming
    Replies: 6
    Last Post: 06-22-2011, 06:52 AM
  5. Object Required Error.
    By Robeen in forum Forms
    Replies: 1
    Last Post: 03-28-2011, 09:30 AM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Tech Forums: Microsoft Office Forums