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.