Vba code loop help (1 Viewer)

[Ashfaque]

Hi,

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

ProdCode-----ProductName-------Stock
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.

ProdCode----ProductName--------Qty------ComboCode
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

ProdCode-----ProductName-------Stock
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 once the item is finished from the T_ComboProd.

Run-time error ‘3021’
No current record


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)

rst1.MoveLast
rst1.MoveFirst

Do While Not rst1.RecordCount


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
TempQty = 0
NEWPURQTY = 0
rst2.Update
rst2.Close

X = X - 1
If X >= rst1.RecordCount Then
Exit Do
Else

rst1.MoveNext
End If
Next X
Loop

End If
rst1.Close


Else

HERE IT IS PERFOMING OTHER INSTRUCTION IF ProdCode is not 1.
.....
.......
.........

I believe the loop is not in proper way.

One more thing, I will have many combo boxes in future and I will enter all their respective items in T_ComboProd tbl with their ComboCode. So how can I handle if there are entries other than combo box 1?

Can somebody help?

If need I can upload my db.

Thanks in advance.

With kind regards,
Ashfaque

 

[ajetrumpet]

you need to clean this up to get an answer

 

[Ashfaque]

you need to clean this up to get an answer

This is what I want to know where is the problem and how can I do this

What I was suspecting is the loop problem.

Thanks,
Ashfaque

 

[vbaInet]

This is what I want to know where is the problem and how can I do this

What I was suspecting is the loop problem.

Thanks,
Ashfaque

lol Ashfaque, ajetrumpet means you need to cut down on content.

 

[Ashfaque]

Sorry, I am not that much advance to understand the meaning.

Does it mean that I have to check with error.number keyword along with if-then-else statement and terminate if the event occurs?

Regards,
Ashfaque

 

[Steve R.]

Run-time error ‘3021’
No current record
-------------------------------------------------------------------------------
Set rst1 = CurrentDb.OpenRecordset("Select * from T_ComboProd Where ComboCode = " & ProdCode)

rst1.MoveLast
rst1.MoveFirst

Since you received "No Current Record" as an error message, you may have a situation were no records were retrieved. You may want to check the syntax of your SQL query. Even if the syntax is correct there may be situations where nothing is returned, such as an item inventory being =0. I would suggest using DCOUNT to verify that you have retrieved at least one record. Look-up the correct syntax for DCOUNT in Access help.

if DCOUNT >1 then
           rst1.MoveLast
           rst1.MoveFirst
   Else
            Message Box to Inform User that an error occured
            Bypass all remaining code that depends on the true situation.
   end if

 

[dcb]

Fill in the answers here: (Marked with '' ??)

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

rst1.MoveLast
rst1.MoveFirst

Do While Not rst1.RecordCount
'' ?? Why not [ Do Until rst1.EOF]


For X = 1 To rst1.RecordCount - 1
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
TempQty = 0
NEWPURQTY = 0
rst2.Update
rst2.Close

X = X - 1 '' ?? Why are you doing this?????
If X >= rst1.RecordCount Then
'' ?? What are you trying to do/test here as X will always = 1?
Exit Do
Else

rst1.MoveNext
End If
Next X
Loop

End If
rst1.Close

 

[Ashfaque]

Thanks all guys,

Special thanks to dcb. I corrected code as below and works fine.

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

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

Next X
Loop

End If
rst1.Close

With kind regards,
Ashfaque

 

[Ashfaque]

Hi again,

It is generating below error now with below code;

Run-time error '91'
Object variable 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....Here it is stuck up and produce error

Else

other code here folows
.......
.........
End sub

 

[dcb]

Put it before your "end if"