Results 1 to 2 of 2
  1. #1
    Join Date
    Jan 2005
    Nanded, India

    Unanswered: Run-time error '91' - VBA Code Help

    Hi guys,
    I have below tables in my db.

    1. T_ProductMaster (Fields are ComboCode, ProdCode, ProductName, Stock etc)
    2. T_ComboProd (Fields are ComboCode, ProdCode, ProductName & Qty)
    3. T_PurchaseFooter (Fields are ProdCode, ProductName, PurQty etc.)

    What I am trying to do is as below:

    Stock in T_ProductMaster tbl is updating perfectly when I am purchasing single-single material qty feeding thru T_PurchaseFooter (tbl/form) while making purchase entry.

    The main problem is when I purchase the combo box which contents around 25 items and are already in T_ProductMaster table, the entry in T_PurchaseFooter is only single because Combo Box has product code 1. So user will enter Purqty as 1.

    What I need is once the combo box is entered (prodcode 1) and cofirm thru a check mark, all the 25 items should be update stock in T_ProductMaster.

    To do this I have created separate table which is table # 2. T_ComboProd tbl for all the items coming in combo box along with their qty.

    Let us say I have 3 items in my T_ProductMaster

    14-----------Aloe Bath Gelee----- 3
    15-----------Aloe Vera Gel--------0
    39-----------Arctic Sea-----------5

    Let us say tbl T_ComboProd has 2 items for Combo Box.

    15----------Aloe Vera Gel-------- 8 ---------- 1
    39----------Arctic Sea----------17----------- 1

    When I enter single for each product in T_PurchaseFooter, it is updating stock perfectly, But If I make entry of Combo box (single entry) it should check all the items of Combo box from tbl T_ComboProd and get their respective qty from same tbl and update to T_ProductMaster tbl. This is being done after confirmation thur a check mark in the same record set line.

    So the stock in T_ProductMaster tbl (for those combo box items from T_ComboProd) should like below

    15-----------Aloe Vera Gel--------8
    39-----------Arctic Sea-----------22

    I have written below vba code to do this and it is updating all the respective items from T_ComboProd to T_ProductMaster tbl BUT it produces below error.

    Run-time error '91'
    Object variable or With block variable not set

    If Confirm.Value = -1 Then

    Dim db As DAO.Database
    Dim rst As DAO.Recordset
    Dim rst1 As DAO.Recordset
    Dim rst2 As DAO.Recordset
    Dim TempCode
    Dim TempQty, NEWPURQTY
    Dim X: X = 0
    TempCode = ProdCode

    If Me.ProdCode = 1 Then

    Set rst1 = CurrentDb.OpenRecordset("Select * from T_ComboProd Where ComboCode = " & ProdCode)


    Do Until rst1.EOF

    For X = 1 To rst1.RecordCount
    Set rst2 = CurrentDb.OpenRecordset("Select * from T_ProductMaster where XCode = " & rst1!ComboCode & _
    " And ProdCode = " & rst1!ProdCode & "")

    NEWPURQTY = rst1!Qty
    TempQty = rst2!Stock
    rst2!Stock = TempQty + NEWPURQTY
    TempQty = 0

    Next X

    End If

    my other code lines...

    Please extend your help.

    With kind regards,
    With kind regards,

  2. #2
    Join Date
    Oct 2004
    Oxfordshire, UK
    DAO object variable references can get dropped if you don't create an explicit reference to the parent object.

    Set db = CurrentDb()
    Set rst = db.OpenRecordset([SQL Expression])

    Sometimes you even have to do this:
    Set wspDefault = Application.DefaultWorkspaceClone
    Set db = wspDefault.OpenDatabase(CurrentDb.Name)
    Set rst = db.OpenRecordset([SQL Expression])

    Also, if there was an error when you tried to create a DAO object variable, trying to close it will raise another error...

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts