Ashfaque
Search Beautiful Girls from your town for night
- Local time
- Tomorrow, 04:17
- Joined
- Sep 6, 2004
- Messages
- 897
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
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