Need Help

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,
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top