duplicate recorsets

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi there,
This is my first post; I hope someone could help me.
I am new on the VBA arena and have some difficulties in trying the following
thing. I am currently using Access 2003.

I found clients tend to order the same thing from time to time. Taking the
Northwind database as sample I need to add a bottom to "Orders form" to say
"Duplicate Invoice" beside "Print Invoice". Basically, I would like to add a
new invoice that copies the current Order record (apart from OrderID) and ALL
the "Order details". I manage to copy the main order record however I do not
know how to copy the records from "Order details" at the same time.

Any suggestions about the code I should use?

Thanks in advance.
 
Hi,
once you copied order record - you need to get an order id on new order and
then run append query to copy Order details. just make a new append query,
based on Order details, and make sure that you inserting new order ID for
new records

--
Best regards,
___________
Alex Dybenko (MVP)
http://alexdyb.blogspot.com
http://www.PointLtd.com
 
Hi,
say NewOrderID var holds an ID of just created order record, and OldOrderID
of original

strSQL="Insert into OrderDetails (OrderID, ProductID, Qty, Price) Select " &
NewOrderID & " , ProductID, Qty, Price from OrderDetails Where OrderID=" &
OldOrderID

currentdb.execute strSQL, dbfailonerror

--
Best regards,
___________
Alex Dybenko (MVP)
http://alexdyb.blogspot.com
http://www.PointLtd.com
 
Following your code I add a new botton on the form and insert the code
below, however I have the following error on the last statement:
"Number of query values and destination fields are not the same. (Error 3346)"

I am sure the solution is very simple...


Dim db As Database
Dim rec As Recordset
Dim strSQL As String
Dim NewOrderID As Integer
Dim OldOrderID As Integer

Set db = CurrentDb()
Set rec = db.OpenRecordset("Orders")

OldOrderID = txtOrderID.Value
strSQL = "INSERT INTO Orders (CustomerID, EmployeeID, OrderDate,
RequiredDate, ShippedDate, ShipVia, Freight, ShipName, ShipAddress, ShipCity,
ShipRegion, ShipPostalCode, ShipCountry ) SELECT Orders.CustomerID,
Orders.EmployeeID, Orders.OrderDate, Orders.RequiredDate, Orders.ShippedDate,
Orders.ShipVia, Orders.Freight, Orders.ShipName, Orders.ShipAddress,
Orders.ShipCity, Orders.ShipRegion, Orders.ShipPostalCode, Orders.ShipCountry
FROM Orders WHERE (((OrderID)=" & OldOrderID & "));"
db.Execute strSQL, dbFailOnError

With rec
.MoveFirst
.MoveLast
End With

NewOrderID = rec("OrderID")
strSQL = "INSERT INTO [Order Details] (ProductID, UnitPrice, Quantity,
Discount) SELECT " & NewOrderID & ",ProductID,UnitPrice,Quantity,Discount
FROM [Order Details] WHERE (((OrderID)=" & OldOrderID & "));"
db.Execute strSQL, dbFailOnError
 
Hi,
here a correct line:
strSQL = "INSERT INTO [Order Details] (OrderID, ProductID, UnitPrice,
Quantity,
Discount) SELECT " & NewOrderID & ",ProductID,UnitPrice,Quantity,Discount
FROM [Order Details] WHERE (((OrderID)=" & OldOrderID & "));"

you missed OrderID


--
Best regards,
___________
Alex Dybenko (MVP)
http://alexdyb.blogspot.com
http://www.PointLtd.com


Francisco said:
Following your code I add a new botton on the form and insert the code
below, however I have the following error on the last statement:
"Number of query values and destination fields are not the same. (Error
3346)"

I am sure the solution is very simple...


Dim db As Database
Dim rec As Recordset
Dim strSQL As String
Dim NewOrderID As Integer
Dim OldOrderID As Integer

Set db = CurrentDb()
Set rec = db.OpenRecordset("Orders")

OldOrderID = txtOrderID.Value
strSQL = "INSERT INTO Orders (CustomerID, EmployeeID, OrderDate,
RequiredDate, ShippedDate, ShipVia, Freight, ShipName, ShipAddress,
ShipCity,
ShipRegion, ShipPostalCode, ShipCountry ) SELECT Orders.CustomerID,
Orders.EmployeeID, Orders.OrderDate, Orders.RequiredDate,
Orders.ShippedDate,
Orders.ShipVia, Orders.Freight, Orders.ShipName, Orders.ShipAddress,
Orders.ShipCity, Orders.ShipRegion, Orders.ShipPostalCode,
Orders.ShipCountry
FROM Orders WHERE (((OrderID)=" & OldOrderID & "));"
db.Execute strSQL, dbFailOnError

With rec
.MoveFirst
.MoveLast
End With

NewOrderID = rec("OrderID")
strSQL = "INSERT INTO [Order Details] (ProductID, UnitPrice, Quantity,
Discount) SELECT " & NewOrderID & ",ProductID,UnitPrice,Quantity,Discount
FROM [Order Details] WHERE (((OrderID)=" & OldOrderID & "));"
db.Execute strSQL, dbFailOnError




Alex Dybenko said:
Hi,
say NewOrderID var holds an ID of just created order record, and
OldOrderID
of original

strSQL="Insert into OrderDetails (OrderID, ProductID, Qty, Price) Select
" &
NewOrderID & " , ProductID, Qty, Price from OrderDetails Where OrderID="
&
OldOrderID

currentdb.execute strSQL, dbfailonerror

--
Best regards,
___________
Alex Dybenko (MVP)
http://alexdyb.blogspot.com
http://www.PointLtd.com
Click to expand...
Click to expand...
 
Alex
Thanks a lot, it works perfectly.
F

Alex Dybenko said:
Hi,
here a correct line:
strSQL = "INSERT INTO [Order Details] (OrderID, ProductID, UnitPrice,
Quantity,
Discount) SELECT " & NewOrderID & ",ProductID,UnitPrice,Quantity,Discount
FROM [Order Details] WHERE (((OrderID)=" & OldOrderID & "));"

you missed OrderID


--
Best regards,
___________
Alex Dybenko (MVP)
http://alexdyb.blogspot.com
http://www.PointLtd.com


Francisco said:
Following your code I add a new botton on the form and insert the code
below, however I have the following error on the last statement:
"Number of query values and destination fields are not the same. (Error
3346)"

I am sure the solution is very simple...


Dim db As Database
Dim rec As Recordset
Dim strSQL As String
Dim NewOrderID As Integer
Dim OldOrderID As Integer

Set db = CurrentDb()
Set rec = db.OpenRecordset("Orders")

OldOrderID = txtOrderID.Value
strSQL = "INSERT INTO Orders (CustomerID, EmployeeID, OrderDate,
RequiredDate, ShippedDate, ShipVia, Freight, ShipName, ShipAddress,
ShipCity,
ShipRegion, ShipPostalCode, ShipCountry ) SELECT Orders.CustomerID,
Orders.EmployeeID, Orders.OrderDate, Orders.RequiredDate,
Orders.ShippedDate,
Orders.ShipVia, Orders.Freight, Orders.ShipName, Orders.ShipAddress,
Orders.ShipCity, Orders.ShipRegion, Orders.ShipPostalCode,
Orders.ShipCountry
FROM Orders WHERE (((OrderID)=" & OldOrderID & "));"
db.Execute strSQL, dbFailOnError

With rec
.MoveFirst
.MoveLast
End With

NewOrderID = rec("OrderID")
strSQL = "INSERT INTO [Order Details] (ProductID, UnitPrice, Quantity,
Discount) SELECT " & NewOrderID & ",ProductID,UnitPrice,Quantity,Discount
FROM [Order Details] WHERE (((OrderID)=" & OldOrderID & "));"
db.Execute strSQL, dbFailOnError




Alex Dybenko said:
Hi,
say NewOrderID var holds an ID of just created order record, and
OldOrderID
of original

strSQL="Insert into OrderDetails (OrderID, ProductID, Qty, Price) Select
" &
NewOrderID & " , ProductID, Qty, Price from OrderDetails Where OrderID="
&
OldOrderID

currentdb.execute strSQL, dbfailonerror

--
Best regards,
___________
Alex Dybenko (MVP)
http://alexdyb.blogspot.com
http://www.PointLtd.com


Alex
Thanks for the info.
Could you show in VBA how can I do it?
regards

:

Hi,
once you copied order record - you need to get an order id on new
order
and
then run append query to copy Order details. just make a new append
query,
based on Order details, and make sure that you inserting new order ID
for
new records

--
Best regards,
___________
Alex Dybenko (MVP)
http://alexdyb.blogspot.com
http://www.PointLtd.com


Hi there,
This is my first post; I hope someone could help me.
I am new on the VBA arena and have some difficulties in trying the
following
thing. I am currently using Access 2003.

I found clients tend to order the same thing from time to time.
Taking
the
Northwind database as sample I need to add a bottom to "Orders form"
to
say
"Duplicate Invoice" beside "Print Invoice". Basically, I would like
to
add
a
new invoice that copies the current Order record (apart from
OrderID)
and
ALL
the "Order details". I manage to copy the main order record however
I
do
not
know how to copy the records from "Order details" at the same time.

Any suggestions about the code I should use?

Thanks in advance.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top