Give user ability to copy last record?

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

Guest

I've looked at this issue a thousand different ways and just cannot come up
with the solution. I've always gotten great advice here so I'm hoping you
guys will give this a go.

I have three standard tables tblCust, tblOrder, tblOrdDetail. When my user
enters the main form, he would like a pop up box asking him whether he wants
to copy the customer's last order to a current order. If so, the fields
should populate with the last order (order and order details) and he should
be able to make changes. If not, he would like a blank screen to enter a new
order.

I'm sure there's an easy solution that I'm missing but after researching for
a week and trying every kind of query and coding I can think of, I'm out of
ideas. Any brilliant suggestions?

Thank you for your time and consideration
Kim
 
Kim, you can do it BUT:
The routine will actually insert the new record and details. If the user
changes his mind, he will have to DELETE the order.
Below is the code that works for tables in my order entry/invoicing
database. I don't actually use this code, I came up with it as an exercise
and possible solution for your problem.
The idea is to insert a new record in the Orders table, then copy all the
detail records for the old order back into the detail table, but with the FK
of the new OrderID.
Add a hidden textbox txtOldOrderID.
Code assumes the old order is displayed in the form and the below command
button on that form is clicked.

Good luck,
UpRider

Private Sub cmdCopyOrder_Click()
Dim strSQL As String
Dim rstran As DAO.Recordset
Dim rsobj As DAO.Recordset
txtOldOrderID = [OrderID]
'copy the order back into the Orders table
strSQL = "INSERT INTO tblOrders (LastChanged, DateCreated, CustomerID,
ShipperID, CustPOnum, InvoiceNo, OrderDate, RequiredDate, ShippedDate,
Freight, OrderProdTot, OrderNotes, CategoryID, EmployeeID, ShipperTracking,
OrderIncomplete, TranType ) " _
& "SELECT tblOrders.LastChanged, tblOrders.DateCreated,
tblOrders.CustomerID, tblOrders.ShipperID, tblOrders.CustPOnum,
tblOrders.InvoiceNo, tblOrders.OrderDate, tblOrders.RequiredDate,
tblOrders.ShippedDate, tblOrders.Freight, tblOrders.OrderProdTot,
tblOrders.OrderNotes, tblOrders.CategoryID, tblOrders.EmployeeID,
tblOrders.ShipperTracking, tblOrders.OrderIncomplete, tblOrders.TranType " _
& "From tblOrders " _
& "WHERE (((tblOrders.OrderID)= " &
[Forms]![frmOrdersMain]![txtOldOrderID] & "));"
'Do it
CurrentDb.Execute strSQL, dbFailOnError
Me.Requery

'get a recordset with all the detail from the original order
strSQL = "select * from tblorderdetails " _
& "where tblOrderDetails.OrderID = " & Forms!frmOrdersMain!txtOldOrderID

'now copy the old details using the new OrderID
Set rsobj = CurrentDb.OpenRecordset("tblOrderDetails") 'target table
Set rstran = CurrentDb.OpenRecordset(strSQL) 'transaction
table
With rstran
.MoveFirst
While Not rstran.EOF
rsobj.AddNew
rsobj!OrderID = [OrderID]
rsobj!ProductID = !ProductID
rsobj!CategoryID = !CategoryID
rsobj!UnitPrice = !UnitPrice
rsobj!Quantity = !Quantity
rsobj!Discount = !Discount
rsobj.Update
.MoveNext
Wend

End With
rstran.Close
rsobj.Close
Set rstran = Nothing
Set rsobj = Nothing
'overwrite the obviously superceded fields...
Me.Requery
Me.ShippedDate = Date + 2
Me.CustPOnum = vbNull
Me.OrderDate = Date
cmdOK.Enabled = True
End Sub
 
Kim, an update to the previous post. The code has not changed, but note
this:
The code below will work with one caveat. The recordsource for the form
must be sorted on the OrderID DESCending.
The problem is, if the OrderID is an autonumber, how do you determine the
OrderID of the record just inserted by the APPEND/INSERT query so that you
can look it up and display it on the form? I don't know. The workaround is
to have the recordsource sorted on OrderID Descending.
Then after a Me.Requery, the form will display the first record in the
recordset, which would be the highest OrderID, presumably the one just
created. In some rare situations this might not work.
If you are creating your own OrderID, this is no problem, you can add code
to display the new record with findfirst criteria,
just after the first AND second Me.Requerys below.
You can add code to dynamically change the recordsource and then put it back
to what it was if you wish. That change must be between the DbExecute for
the INSERT query and the Me.Requery.

Hope this update will avoid grief.

UpRider

UpRider said:
Kim, you can do it BUT:
The routine will actually insert the new record and details. If the user
changes his mind, he will have to DELETE the order.
Below is the code that works for tables in my order entry/invoicing
database. I don't actually use this code, I came up with it as an
exercise and possible solution for your problem.
The idea is to insert a new record in the Orders table, then copy all the
detail records for the old order back into the detail table, but with the
FK of the new OrderID.
Add a hidden textbox txtOldOrderID.
Code assumes the old order is displayed in the form and the below command
button on that form is clicked.

Good luck,
UpRider

Private Sub cmdCopyOrder_Click()
Dim strSQL As String
Dim rstran As DAO.Recordset
Dim rsobj As DAO.Recordset
txtOldOrderID = [OrderID]
'copy the order back into the Orders table
strSQL = "INSERT INTO tblOrders (LastChanged, DateCreated, CustomerID,
ShipperID, CustPOnum, InvoiceNo, OrderDate, RequiredDate, ShippedDate,
Freight, OrderProdTot, OrderNotes, CategoryID, EmployeeID,
ShipperTracking, OrderIncomplete, TranType ) " _
& "SELECT tblOrders.LastChanged, tblOrders.DateCreated,
tblOrders.CustomerID, tblOrders.ShipperID, tblOrders.CustPOnum,
tblOrders.InvoiceNo, tblOrders.OrderDate, tblOrders.RequiredDate,
tblOrders.ShippedDate, tblOrders.Freight, tblOrders.OrderProdTot,
tblOrders.OrderNotes, tblOrders.CategoryID, tblOrders.EmployeeID,
tblOrders.ShipperTracking, tblOrders.OrderIncomplete, tblOrders.TranType "
_
& "From tblOrders " _
& "WHERE (((tblOrders.OrderID)= " &
[Forms]![frmOrdersMain]![txtOldOrderID] & "));"
'Do it
CurrentDb.Execute strSQL, dbFailOnError
Me.Requery

'get a recordset with all the detail from the original order
strSQL = "select * from tblorderdetails " _
& "where tblOrderDetails.OrderID = " &
Forms!frmOrdersMain!txtOldOrderID

'now copy the old details using the new OrderID
Set rsobj = CurrentDb.OpenRecordset("tblOrderDetails") 'target table
Set rstran = CurrentDb.OpenRecordset(strSQL) 'transaction
table
With rstran
.MoveFirst
While Not rstran.EOF
rsobj.AddNew
rsobj!OrderID = [OrderID]
rsobj!ProductID = !ProductID
rsobj!CategoryID = !CategoryID
rsobj!UnitPrice = !UnitPrice
rsobj!Quantity = !Quantity
rsobj!Discount = !Discount
rsobj.Update
.MoveNext
Wend

End With
rstran.Close
rsobj.Close
Set rstran = Nothing
Set rsobj = Nothing
'overwrite the obviously superceded fields...
Me.Requery
Me.ShippedDate = Date + 2
Me.CustPOnum = vbNull
Me.OrderDate = Date
cmdOK.Enabled = True
End Sub

Kim said:
I've looked at this issue a thousand different ways and just cannot come
up
with the solution. I've always gotten great advice here so I'm hoping you
guys will give this a go.

I have three standard tables tblCust, tblOrder, tblOrdDetail. When my
user
enters the main form, he would like a pop up box asking him whether he
wants
to copy the customer's last order to a current order. If so, the fields
should populate with the last order (order and order details) and he
should
be able to make changes. If not, he would like a blank screen to enter a
new
order.

I'm sure there's an easy solution that I'm missing but after researching
for
a week and trying every kind of query and coding I can think of, I'm out
of
ideas. Any brilliant suggestions?

Thank you for your time and consideration
Kim
Click to expand...
Click to expand...
 
Thank you very much, I'll try this straight away.

UpRider said:
Kim, an update to the previous post. The code has not changed, but note
this:
The code below will work with one caveat. The recordsource for the form
must be sorted on the OrderID DESCending.
The problem is, if the OrderID is an autonumber, how do you determine the
OrderID of the record just inserted by the APPEND/INSERT query so that you
can look it up and display it on the form? I don't know. The workaround is
to have the recordsource sorted on OrderID Descending.
Then after a Me.Requery, the form will display the first record in the
recordset, which would be the highest OrderID, presumably the one just
created. In some rare situations this might not work.
If you are creating your own OrderID, this is no problem, you can add code
to display the new record with findfirst criteria,
just after the first AND second Me.Requerys below.
You can add code to dynamically change the recordsource and then put it back
to what it was if you wish. That change must be between the DbExecute for
the INSERT query and the Me.Requery.

Hope this update will avoid grief.

UpRider

UpRider said:
Kim, you can do it BUT:
The routine will actually insert the new record and details. If the user
changes his mind, he will have to DELETE the order.
Below is the code that works for tables in my order entry/invoicing
database. I don't actually use this code, I came up with it as an
exercise and possible solution for your problem.
The idea is to insert a new record in the Orders table, then copy all the
detail records for the old order back into the detail table, but with the
FK of the new OrderID.
Add a hidden textbox txtOldOrderID.
Code assumes the old order is displayed in the form and the below command
button on that form is clicked.

Good luck,
UpRider

Private Sub cmdCopyOrder_Click()
Dim strSQL As String
Dim rstran As DAO.Recordset
Dim rsobj As DAO.Recordset
txtOldOrderID = [OrderID]
'copy the order back into the Orders table
strSQL = "INSERT INTO tblOrders (LastChanged, DateCreated, CustomerID,
ShipperID, CustPOnum, InvoiceNo, OrderDate, RequiredDate, ShippedDate,
Freight, OrderProdTot, OrderNotes, CategoryID, EmployeeID,
ShipperTracking, OrderIncomplete, TranType ) " _
& "SELECT tblOrders.LastChanged, tblOrders.DateCreated,
tblOrders.CustomerID, tblOrders.ShipperID, tblOrders.CustPOnum,
tblOrders.InvoiceNo, tblOrders.OrderDate, tblOrders.RequiredDate,
tblOrders.ShippedDate, tblOrders.Freight, tblOrders.OrderProdTot,
tblOrders.OrderNotes, tblOrders.CategoryID, tblOrders.EmployeeID,
tblOrders.ShipperTracking, tblOrders.OrderIncomplete, tblOrders.TranType "
_
& "From tblOrders " _
& "WHERE (((tblOrders.OrderID)= " &
[Forms]![frmOrdersMain]![txtOldOrderID] & "));"
'Do it
CurrentDb.Execute strSQL, dbFailOnError
Me.Requery

'get a recordset with all the detail from the original order
strSQL = "select * from tblorderdetails " _
& "where tblOrderDetails.OrderID = " &
Forms!frmOrdersMain!txtOldOrderID

'now copy the old details using the new OrderID
Set rsobj = CurrentDb.OpenRecordset("tblOrderDetails") 'target table
Set rstran = CurrentDb.OpenRecordset(strSQL) 'transaction
table
With rstran
.MoveFirst
While Not rstran.EOF
rsobj.AddNew
rsobj!OrderID = [OrderID]
rsobj!ProductID = !ProductID
rsobj!CategoryID = !CategoryID
rsobj!UnitPrice = !UnitPrice
rsobj!Quantity = !Quantity
rsobj!Discount = !Discount
rsobj.Update
.MoveNext
Wend

End With
rstran.Close
rsobj.Close
Set rstran = Nothing
Set rsobj = Nothing
'overwrite the obviously superceded fields...
Me.Requery
Me.ShippedDate = Date + 2
Me.CustPOnum = vbNull
Me.OrderDate = Date
cmdOK.Enabled = True
End Sub

Kim said:
I've looked at this issue a thousand different ways and just cannot come
up
with the solution. I've always gotten great advice here so I'm hoping you
guys will give this a go.

I have three standard tables tblCust, tblOrder, tblOrdDetail. When my
user
enters the main form, he would like a pop up box asking him whether he
wants
to copy the customer's last order to a current order. If so, the fields
should populate with the last order (order and order details) and he
should
be able to make changes. If not, he would like a blank screen to enter a
new
order.

I'm sure there's an easy solution that I'm missing but after researching
for
a week and trying every kind of query and coding I can think of, I'm out
of
ideas. Any brilliant suggestions?

Thank you for your time and consideration
Kim
Click to expand...
Click to expand...
Click to expand...
 
Back
Top