Code Loop Help Again

[Ashfaque]

Hi again,

I tried to put my problem in my previous thread but it says 'Your massage can not be uploaded bcz you token has expired' Hence I am posting this.

I dont know what happened but suddenly produced above error at my second entry only and stuck up at Red highlted code line.

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


Private Sub Confirm_Click()

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 Or Me.ProdCode = 500 Then
ElseIf Me.ProdCode = 1000 Or Me.ProdCode = 1500 Then

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

rst1.MoveLast
rst1.MoveFirst

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.Edit
rst2!Stock = TempQty + NEWPURQTY
rst2.Update
TempQty = 0
NEWPURQTY = 0
rst2.Close
rst1.MoveNext
'End If
Next X
Loop
End If
rst1.Close

Else

Other code here....

Regards,
Ashfaque

 

[vbaInet]

Same problem again Mr Ashfaque? lol. Zip and attach your db. I'll see if I can have a quick look. Explain what I should do to replicate the error.

 

[dfenton]

Can you edit your question putting the CODE tags around your code, so the indentation is clear? There appear to me to be a lot of redundancies in your code, but it's not clear without it being formatted appropriately. To format as code, put the word CODE in all caps inside square brackets ([]), and close it with /CODE inside square brackets.

 

[Ashfaque]

Thanks Guys,

Here is my db I attached to review.

Please note that, there is ZERO stock for those products in T_ProductMaster tbl that are contents of combo pack. I have listed them in T_ComboProd tbl along with their respective code.

There are more than one combo boxes where diff products are coming with hence the code should check the combo code with If-Then statement.

Please run the form F_PurchaseHeader and select invoice # 3 from combo box place on the form. I have entered there 2 items. Click on first check mark that will take you to the code.

With kind regards,
Ashfaque

 

[vbaInet]

Can you edit your question putting the CODE tags around your code, so the indentation is clear? There appear to me to be a lot of redundancies in your code, but it's not clear without it being formatted appropriately. To format as code, put the word CODE in all caps inside square brackets ([]), and close it with /CODE inside square brackets.

Just iterating on dfenton's remarks.

 

[namliam]

The code as posted...

[B]Private Sub[/B] Confirm_Click()

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 Or Me.ProdCode = 500 Then
ElseIf Me.ProdCode = 1000 Or Me.ProdCode = 1500 Then

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

 rst1.MoveLast
 rst1.MoveFirst

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.Edit
    rst2!Stock = TempQty + NEWPURQTY
        rst2.Update
        TempQty = 0
        NEWPURQTY = 0
        rst2.Close
        rst1.MoveNext
        'End If
Next X
Loop
End If
[B][COLOR="Red"]rst1.Close[/COLOR][/B]

Else

[/QUOTE]

Problems here are:
Dim TempCode
Dim TempQty, NEWPURQTY
Where is the AS... part??? You NEED the as part!

Dim X: X = 0
Bad BAD habit!
Dim X
X = 0
Is better/more clear but again NO AS... part.... you need it...

TempCode = ProdCode
You dont seem to be using the TempCode much and probably ProdCode should be Me.ProdCode looking at the rest of your code?

This part
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
" good practice " puts the declaration of variables at the very top of subs/functions....
The same good practice indents code... for each if/loop/for etc.

Takng all that... your " new and improved code would be something like...

[B]Private Sub[/B] Confirm_Click()
    Dim db As DAO.Database
    Dim rst As DAO.Recordset
    Dim rst1 As DAO.Recordset
    Dim rst2 As DAO.Recordset
    Dim TempCode As Integer
    Dim TempQty As Integer, NEWPURQTY As Integer
    Dim X As Double

    If Confirm.Value = -1 Then
    
        X = 0
        TempCode = ProdCode
        
        If Me.ProdCode = 1 Or Me.ProdCode = 500 Then
        ElseIf Me.ProdCode = 1000 Or Me.ProdCode = 1500 Then
            Set rst1 = CurrentDb.OpenRecordset("Select * from T_ComboProd Where ComboCode = " & TempCode)
        
            rst1.MoveLast
            rst1.MoveFirst
        
            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.Edit
                    rst2!Stock = TempQty + NEWPURQTY
                    rst2.Update
                    TempQty = 0
                    NEWPURQTY = 0
                    rst2.Close
                    rst1.MoveNext
                'End If
            Next X
            Loop
        End If
        [B][COLOR="Red"]rst1.Close[/COLOR][/B]
        
    Else

Looking at this I can clearly see your closing the rst1 outside the If/Elseif structure, while it is beeing opened/created inside it....

Change that and you fix your problem.

 

[Ashfaque]

Thanks Mailman for the tips.

But my friend modification still doesn't update stock data in T_ProductMaster. Even I placed rst1!close before End if. It directly jumps to second else and updates a signle record in T_ProductMaster tbl.

Thanks,
Ashfaque

 

[vbaInet]

Could you give a brief explanation of your code and especially why you have declared 3 recordsets?

What do you want your code to do?

 

[Ashfaque]

Thanks VbaInet,

First 2 recordsets I used to read records from 2 diff tbls where I need loop thru which I need to update multiple records in T_ProductMaster tbl. The third one I used after my last ELSE statement where I am opening same T_ProductMaster tbl to update single record if the ProdCode is not the ones that I put into the above criteria.
If you look into my previously attached db, you will find this. You may say that I can use close recordset (rst.close) and again can reopen. Yes you are correct but I just used for my understanding only.

Thanks in advance for the help you will extend
Regards,
Ashfaque

 

[namliam]

No where on your form coding do you fill your ProdCode, which is a hidden field thus cannot be entered by the user either.

Thus your code shown here will always go into the last else as your prodcode will never = anything

 

[vbaInet]

It's still a bit confusing to me If you are checking two tables asynchronously, for each record of the table 1, you would need to loop through all the records in table 2. One, the number of records in table 1 could be more or less than that of table 2. Two, the ordering of the fields could be different. Do you think you can achieve this using SQL instead? Any chance of giving an example using data.

 

[Ashfaque]

OK I will explain in detail to you.

1. The stock qty in tbl T_ProductMaster is being updated whenever there is purchase qty entered into tbl T_PurchaseFooter. This works like this. After entry of PurQty in T_PurchaseFooter, code will look for the same prodcode in T_ProductMaster and update its stock qty.

This logic is perfect for the single records to update.

But we do not purchase single items always. There is one box called Combo Box which contains more than 25 products in it.

Let us say there are 200 different products in my T_ProductMaster tbl ok?

When I purchase a combo box, I will get 25 products in less cost than to purchase single items.

So once the combo is purchased, the stock of the T_ProductMaster should be updated only for those products that contains in the combo box. Isn’t it?

There are different type of combo boxes available with supplier. Some of them having 25 product, some of them 15 product or some of them 32. But all those products are already available in our master tbl which is T_ProductMaster.

So to know which combo contains how much qty of product, I have placed there tbl called T_ComboProd where I placed their qty of respective products.

After clicking on confirm check mark, it should search the ComboCode and add the qty each products from T_ComboProd tbl and update the stock in T_ProductMaster.

If the purchased item is not a combo box item, them simply it should update the qty for the respective item entered into the T_PurchaseFooter,

I hope I made it clear now.

Thanks,
Ashfaque