F
FrankSmith
Hello there,
Please be gentle as I am new to all this!
I have a little experience with Excel VBA, but this is my first time with
Access 2003.
I have this code written within a form to perform a series of tasks just
before closing the form
When I run it, it works for the table (Inventory Transactions) but does not
copy the data into a new record in the second table (Order Details)
I hope that this time Outlook does not stop me sending you the entire
database.
The following code is in event procedure attached to the form “Order Details
Inventâ€, which must be run by clicking on [Add Items] from the form “Add An
Order and Details1â€, which itself must be called out by the first open form
(“Managing Sales “) when you click on “Add new sales Orderâ€
Please help.
Private Sub Form_Close()
If Me.TxtOrderID.Value = 0 Then
Exit Sub
End If
Dim txtProductCode As String
Dim txtProductName As String
Dim txtProductUnit As String
Dim txtSerialNumber As String
Dim dblQuantity As Double
Dim dblUnitCost As Double
Dim dblUnitPrice As Double
Dim dblDiscount As Double
Dim dblVat As Double
Dim dblprice As Double
Dim dblvatAmount As Double
Dim dblpriceincl As Double
Dim dblcost As Double
'
'
Set rs = CurrentDb.OpenRecordset("Inventory transactions")
With rs
Do Until .EOF
'.Edit
If OrderID = dblOrderID Then
txtProductCode = [Inventory transactions].[ProductCode]
txtProductName = [Inventory transactions].[ProductName]
txtProductUnit = [Inventory transactions].[ProductUnit]
txtSerialNumber = [Inventory transactions].[SerialNumber]
dblQuantity = [Inventory transactions].[UnitsSold]
dblUnitCost = [Inventory transactions].[UnitCost]
dblUnitPrice = [Inventory transactions].[UnitPrice]
dblDiscount = [Inventory transactions].[discountonSale]
dblVat = [Inventory transactions].[Vat]
dblprice = [Inventory transactions].[Price]
dblvatAmount = [Inventory transactions].[VatAmount]
dblpriceincl = [Inventory transactions].[PriceIncl]
dblcost = [Inventory transactions].[Cost]
Set db = CurrentDb
Set rst = db.OpenRecordset("Order Details")
With rst
..AddNew
!OrderID = TxtOrderID
'!CustCompanyName = Me.TxtCustomerID
!ProductCode = txtProductCode
!ProductName = txtProductName
!ProductUnit = txtProductUnit
!SerialNumber = txtSerialNumber
!Quantity = dblQuantity
!UnitCost = dblUnitCost
!UnitPrice = dblUnitPrice
!Discount = dblDiscount
!Vat = dblVat
!Price = dblprice
!VatAmount = dblvatAmount
!PriceIncl = dblpriceincl
!Cost = dblcost
'.Update
End With
rst.Close
End If
'.Update
.MoveNext
Loop
.Close
End With
Set rs = Nothing
'
'
'Set db = CurrentDb
'Set rst = db.OpenRecordset("Inventory transactions")
'Set rs = Me.Recordset.Clone
' rs.FindFirst "[OrderID] = " & (Nz(Me.TxtOrderID))
' If Not rs.EOF Then Me.Bookmark = rs.Bookmark
'Do While Not rst.EOF
'Loop
End Sub
To a novice such as myself it is causing headaches but to experts like you
it is probably very trivial.
Please Help.
Thanks in advance,
Please be gentle as I am new to all this!
I have a little experience with Excel VBA, but this is my first time with
Access 2003.
I have this code written within a form to perform a series of tasks just
before closing the form
When I run it, it works for the table (Inventory Transactions) but does not
copy the data into a new record in the second table (Order Details)
I hope that this time Outlook does not stop me sending you the entire
database.
The following code is in event procedure attached to the form “Order Details
Inventâ€, which must be run by clicking on [Add Items] from the form “Add An
Order and Details1â€, which itself must be called out by the first open form
(“Managing Sales “) when you click on “Add new sales Orderâ€
Please help.
Private Sub Form_Close()
If Me.TxtOrderID.Value = 0 Then
Exit Sub
End If
Dim txtProductCode As String
Dim txtProductName As String
Dim txtProductUnit As String
Dim txtSerialNumber As String
Dim dblQuantity As Double
Dim dblUnitCost As Double
Dim dblUnitPrice As Double
Dim dblDiscount As Double
Dim dblVat As Double
Dim dblprice As Double
Dim dblvatAmount As Double
Dim dblpriceincl As Double
Dim dblcost As Double
'
'
Set rs = CurrentDb.OpenRecordset("Inventory transactions")
With rs
Do Until .EOF
'.Edit
If OrderID = dblOrderID Then
txtProductCode = [Inventory transactions].[ProductCode]
txtProductName = [Inventory transactions].[ProductName]
txtProductUnit = [Inventory transactions].[ProductUnit]
txtSerialNumber = [Inventory transactions].[SerialNumber]
dblQuantity = [Inventory transactions].[UnitsSold]
dblUnitCost = [Inventory transactions].[UnitCost]
dblUnitPrice = [Inventory transactions].[UnitPrice]
dblDiscount = [Inventory transactions].[discountonSale]
dblVat = [Inventory transactions].[Vat]
dblprice = [Inventory transactions].[Price]
dblvatAmount = [Inventory transactions].[VatAmount]
dblpriceincl = [Inventory transactions].[PriceIncl]
dblcost = [Inventory transactions].[Cost]
Set db = CurrentDb
Set rst = db.OpenRecordset("Order Details")
With rst
..AddNew
!OrderID = TxtOrderID
'!CustCompanyName = Me.TxtCustomerID
!ProductCode = txtProductCode
!ProductName = txtProductName
!ProductUnit = txtProductUnit
!SerialNumber = txtSerialNumber
!Quantity = dblQuantity
!UnitCost = dblUnitCost
!UnitPrice = dblUnitPrice
!Discount = dblDiscount
!Vat = dblVat
!Price = dblprice
!VatAmount = dblvatAmount
!PriceIncl = dblpriceincl
!Cost = dblcost
'.Update
End With
rst.Close
End If
'.Update
.MoveNext
Loop
.Close
End With
Set rs = Nothing
'
'
'Set db = CurrentDb
'Set rst = db.OpenRecordset("Inventory transactions")
'Set rs = Me.Recordset.Clone
' rs.FindFirst "[OrderID] = " & (Nz(Me.TxtOrderID))
' If Not rs.EOF Then Me.Bookmark = rs.Bookmark
'Do While Not rst.EOF
'Loop
End Sub
To a novice such as myself it is causing headaches but to experts like you
it is probably very trivial.
Please Help.
Thanks in advance,