G
Guest
I have an [Inventory Transactions] Table with a field named "QOH"
Right now the following code will update my Products table in the field QOH
and it reads that field from my assembly creations form which holds a
subform named "tmpCreateKit"
I need QOH to be read from my table "Inventory Transactions"
and QOH to be updated to that table as well.
Inventory Transactions is where I receive and order my products.
I can get it to read from Inventory Transactions but when I try to post an
assembly it says it can't because of duplicate values.
The following code is what I have or you can Download the Database at
http://www.pittmangamecalls.com/Product_Assembly.mdb
Any help will be greatly appreciated
Alvin
Option Compare Database
----------------------------------------------------------------------------
Private Sub cmdCheck_Click()
Dim rs As DAO.Recordset
Dim db As DAO.Database
Dim blnNotEnough As Boolean
Set db = CurrentDb
Set rs = db.OpenRecordset("Select * from tmpCreateKit")
blnNotEnough = False
While Not rs.EOF
rs.Edit
rs!unitstobeused = rs!UnitsNeeded * Me.txtQTM
rs![Qty Remaining] = rs![QOH] - (rs!UnitsNeeded * Me.txtQTM)
If rs![QOH] - (rs!UnitsNeeded * Me.txtQTM) < 0 Then
blnNotEnough = True
End If
rs.Update
rs.MoveNext
Wend
Me.tmpCreateKit.Requery
If blnNotEnough Then
cmdPost.Enabled = False
Else
cmdPost.Enabled = True
End If
End Sub
Private Sub cmdPost_Click()
Dim rstemp As DAO.Recordset
Dim rsTransaction As DAO.Recordset
Dim rsProduct As DAO.Recordset
Dim rsAssembly As DAO.Recordset
Dim db As DAO.Database
Dim strTransDetail As String
Set db = CurrentDb
Set rstemp = db.OpenRecordset("select * from tmpCreateKit")
Set rsTransaction = db.OpenRecordset("select * from [Inventory
Transactions]")
Set rsProduct = db.OpenRecordset("select * from Products")
Set rsAssembly = db.OpenRecordset("select * from Assembly")
strTransDetail = "Assembly Requisition for: " & Me.ProductName
While Not rstemp.EOF
rsTransaction.AddNew
rsTransaction!TransactionDate = Now()
rsTransaction!ProductID = rstemp![Product ID]
rsTransaction!TransactionDescription = strTransDetail
rsTransaction!UnitsUsed = rstemp!unitstobeused
rsTransaction.Update
rsProduct.FindFirst "[ProductID] = " & rstemp![Product ID]
rsProduct.Edit
rsProduct!QOH = rstemp![Qty Remaining]
rsProduct.Update
rstemp.MoveNext
Wend
rstemp.MoveFirst
rsAssembly.FindFirst "[ProductID] = " & rstemp!KitID
rsAssembly.Edit
rsAssembly!QOH = rsAssembly!QOH + Me.txtQTM
rsAssembly.Update
Me.cmdCheck.SetFocus
Me.cmdPost.Enabled = False
Me.txtQTM = 0
Form_Current
End Sub
Private Sub Command10_Click()
On Error GoTo Err_Command10_Click
DoCmd.GoToRecord , , acPrevious
Exit_Command10_Click:
Exit Sub
Err_Command10_Click:
MsgBox Err.Description
Resume Exit_Command10_Click
End Sub
Private Sub Command11_Click()
On Error GoTo Err_Command11_Click
DoCmd.GoToRecord , , acNext
Exit_Command11_Click:
Exit Sub
Err_Command11_Click:
MsgBox Err.Description
Resume Exit_Command11_Click
End Sub
Private Sub Command12_Click()
On Error GoTo Err_Command12_Click
DoCmd.GoToRecord , , acNewRec
Exit_Command12_Click:
Exit Sub
Err_Command12_Click:
MsgBox Err.Description
Resume Exit_Command12_Click
End Sub
Private Sub Command13_Click()
On Error GoTo Err_Command13_Click
DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
DoCmd.DoMenuItem acFormBar, acEditMenu, 6, , acMenuVer70
Exit_Command13_Click:
Exit Sub
Err_Command13_Click:
MsgBox Err.Description
Resume Exit_Command13_Click
End Sub
Private Sub Command14_Click()
On Error GoTo Err_Command14_Click
DoCmd.Close
Exit_Command14_Click:
Exit Sub
Err_Command14_Click:
MsgBox Err.Description
Resume Exit_Command14_Click
End Sub
Private Sub Combo16_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object
Set rs = Me.Recordset.Clone
rs.FindFirst "[ProductID] = " & Str(Nz(Me![Combo16], 0))
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub
Sub Form_Current()
Dim rs As DAO.Recordset
Dim tmprs As DAO.Recordset
Dim db As DAO.Database
Dim strSQL As String
strSQL = "SELECT Kits.*, Products.QOH FROM Kits INNER JOIN Products ON
Kits.[Product ID] = Products.ProductID WHERE (((Kits.KitID)=" & Me.ProductID
& "));"
Set db = CurrentDb
Set rs = db.OpenRecordset(strSQL)
Set tmprs = db.OpenRecordset("select * from tmpCreateKit")
While Not tmprs.EOF
tmprs.Delete
tmprs.MoveNext
Wend
While Not rs.EOF
tmprs.AddNew
tmprs!ParentID = rs!ParentID
tmprs!KitID = rs!KitID
tmprs!ProductName = rs!ProductName
tmprs![Product ID] = rs![Product ID]
tmprs!UnitsNeeded = rs!UnitsNeeded
tmprs!unitstobeused = 0
tmprs!QOH = rs!QOH
tmprs![Qty Remaining] = rs!QOH
tmprs.Update
rs.MoveNext
Wend
Me.tmpCreateKit.Requery
End Sub
----------------------------------------------------------------
Right now the following code will update my Products table in the field QOH
and it reads that field from my assembly creations form which holds a
subform named "tmpCreateKit"
I need QOH to be read from my table "Inventory Transactions"
and QOH to be updated to that table as well.
Inventory Transactions is where I receive and order my products.
I can get it to read from Inventory Transactions but when I try to post an
assembly it says it can't because of duplicate values.
The following code is what I have or you can Download the Database at
http://www.pittmangamecalls.com/Product_Assembly.mdb
Any help will be greatly appreciated
Alvin
Option Compare Database
----------------------------------------------------------------------------
Private Sub cmdCheck_Click()
Dim rs As DAO.Recordset
Dim db As DAO.Database
Dim blnNotEnough As Boolean
Set db = CurrentDb
Set rs = db.OpenRecordset("Select * from tmpCreateKit")
blnNotEnough = False
While Not rs.EOF
rs.Edit
rs!unitstobeused = rs!UnitsNeeded * Me.txtQTM
rs![Qty Remaining] = rs![QOH] - (rs!UnitsNeeded * Me.txtQTM)
If rs![QOH] - (rs!UnitsNeeded * Me.txtQTM) < 0 Then
blnNotEnough = True
End If
rs.Update
rs.MoveNext
Wend
Me.tmpCreateKit.Requery
If blnNotEnough Then
cmdPost.Enabled = False
Else
cmdPost.Enabled = True
End If
End Sub
Private Sub cmdPost_Click()
Dim rstemp As DAO.Recordset
Dim rsTransaction As DAO.Recordset
Dim rsProduct As DAO.Recordset
Dim rsAssembly As DAO.Recordset
Dim db As DAO.Database
Dim strTransDetail As String
Set db = CurrentDb
Set rstemp = db.OpenRecordset("select * from tmpCreateKit")
Set rsTransaction = db.OpenRecordset("select * from [Inventory
Transactions]")
Set rsProduct = db.OpenRecordset("select * from Products")
Set rsAssembly = db.OpenRecordset("select * from Assembly")
strTransDetail = "Assembly Requisition for: " & Me.ProductName
While Not rstemp.EOF
rsTransaction.AddNew
rsTransaction!TransactionDate = Now()
rsTransaction!ProductID = rstemp![Product ID]
rsTransaction!TransactionDescription = strTransDetail
rsTransaction!UnitsUsed = rstemp!unitstobeused
rsTransaction.Update
rsProduct.FindFirst "[ProductID] = " & rstemp![Product ID]
rsProduct.Edit
rsProduct!QOH = rstemp![Qty Remaining]
rsProduct.Update
rstemp.MoveNext
Wend
rstemp.MoveFirst
rsAssembly.FindFirst "[ProductID] = " & rstemp!KitID
rsAssembly.Edit
rsAssembly!QOH = rsAssembly!QOH + Me.txtQTM
rsAssembly.Update
Me.cmdCheck.SetFocus
Me.cmdPost.Enabled = False
Me.txtQTM = 0
Form_Current
End Sub
Private Sub Command10_Click()
On Error GoTo Err_Command10_Click
DoCmd.GoToRecord , , acPrevious
Exit_Command10_Click:
Exit Sub
Err_Command10_Click:
MsgBox Err.Description
Resume Exit_Command10_Click
End Sub
Private Sub Command11_Click()
On Error GoTo Err_Command11_Click
DoCmd.GoToRecord , , acNext
Exit_Command11_Click:
Exit Sub
Err_Command11_Click:
MsgBox Err.Description
Resume Exit_Command11_Click
End Sub
Private Sub Command12_Click()
On Error GoTo Err_Command12_Click
DoCmd.GoToRecord , , acNewRec
Exit_Command12_Click:
Exit Sub
Err_Command12_Click:
MsgBox Err.Description
Resume Exit_Command12_Click
End Sub
Private Sub Command13_Click()
On Error GoTo Err_Command13_Click
DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
DoCmd.DoMenuItem acFormBar, acEditMenu, 6, , acMenuVer70
Exit_Command13_Click:
Exit Sub
Err_Command13_Click:
MsgBox Err.Description
Resume Exit_Command13_Click
End Sub
Private Sub Command14_Click()
On Error GoTo Err_Command14_Click
DoCmd.Close
Exit_Command14_Click:
Exit Sub
Err_Command14_Click:
MsgBox Err.Description
Resume Exit_Command14_Click
End Sub
Private Sub Combo16_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object
Set rs = Me.Recordset.Clone
rs.FindFirst "[ProductID] = " & Str(Nz(Me![Combo16], 0))
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub
Sub Form_Current()
Dim rs As DAO.Recordset
Dim tmprs As DAO.Recordset
Dim db As DAO.Database
Dim strSQL As String
strSQL = "SELECT Kits.*, Products.QOH FROM Kits INNER JOIN Products ON
Kits.[Product ID] = Products.ProductID WHERE (((Kits.KitID)=" & Me.ProductID
& "));"
Set db = CurrentDb
Set rs = db.OpenRecordset(strSQL)
Set tmprs = db.OpenRecordset("select * from tmpCreateKit")
While Not tmprs.EOF
tmprs.Delete
tmprs.MoveNext
Wend
While Not rs.EOF
tmprs.AddNew
tmprs!ParentID = rs!ParentID
tmprs!KitID = rs!KitID
tmprs!ProductName = rs!ProductName
tmprs![Product ID] = rs![Product ID]
tmprs!UnitsNeeded = rs!UnitsNeeded
tmprs!unitstobeused = 0
tmprs!QOH = rs!QOH
tmprs![Qty Remaining] = rs!QOH
tmprs.Update
rs.MoveNext
Wend
Me.tmpCreateKit.Requery
End Sub
----------------------------------------------------------------
Thank you all anyway. I been working on