Deleting info from one table and placing it in another via a form

[mjnov85]

Ok so here is my problem.

I have a subform that I use to capture upto 6 different items that a user can order within 1 customer order (this is the main form). Now I would like to have the user be able to come back to the form and "cancel" any 1 item off of a certain order number. I would like to add a cancel button next to each line item such that when they press it, it deletes that item from the main tblorder and places it in a tblHistory. I need to keep the canceled orders for historical reasons, and to create customer satisfaction metrics later on. How can i do this? From what i've read, some things i've come accross are append query's, but i think that there can be a simple way to write this in an On Click event for a button, in VBA.

Also each line item has multiple combo boxes that contain data I want to "delete", each combo box goes to a column in the tblOrder. I would name the columns in the tblHistory the same as the columns in the tblOrder.

In addition I would like the Order Number (which is in the main form and is a primary key) to be extracted with the cancel button so that I can cross reference items with order numbers for report purposes later on. Any help will be greatly appretiated.

Tank you
Mustansir Jeevanjee

 

[Uncle Gizmo]

Personally I wouldn't delete the information, I would add an extra check box that when checked stops the information being displayed in the sub form.

Doing it this way removes the need to move it to a separate table.

 

[CyberLynx]

I agree with Uncle Gizmo. That's the route I would personally take. In my opinion it's a lot easier to take care of.

.

 

[mjnov85]

Unfortunately i have to physically remove that information from that order. It must be deleted and put on the tblHistory. This is because i do not want these parts to be ordered later on in the process. If i just didnt want them to be displayed it wouldnt be a big deal, but i must have it so it gets placed on a seperate table.

So any ideas on how i can accomplish this guys??

 

[Uncle Gizmo]

>>>So any ideas on how i can accomplish this guys??<<<

First of all let's clarify where you are at the moment, you have a main form which has one record "orders" on this main form you have the subform which is usually termed "line items" this sub form is linked to a separate table storing the line items related to the individual "orders" record.

There is an example of this in the northwind sample database supplied by Microsoft. The Microsoft database displays the line items as a "datasheet view" I assume that you want to display your line items as a continuous form view so that you can have a command button against each record?

If this is correct, then I seem to recall that there is a problem with using a command button on a form in continuous form view, in that it acts on all of the records, however I haven't done much work with continuous forms so I may be Wrong.

If these assumptions are correct, then my first task would be to establish that I could extract the information from each row in the continuous form and to this end I would create a command button which produced a message box displaying the information.

Once I had this working, I would then write a function to appened this information into the table tblHistory, and then another function to delete the record from the line items table.

 

[mjnov85]

Thanks for the information Uncle Gizmo. I was wondering if you know any resource on how to do what you are telling me to do, because i dont really know how to code that well in VBA and it would be nice to have a template to base my code off of. Once again thanks for all the help!!!

 

[CyberLynx]

You will need to do a number of things and for the most part, it will be required through code. Without any real information....this is just simple step stone method that may get you started.

In the OnClick event of your SubForm Command Button:

[COLOR="DarkGreen"]'Declare Variables....[/COLOR]
Dim Ret As Integer
Dim StrgSQL As String

[COLOR="DarkGreen"]'Ensure the User really wants to Delete the Order Item....[/COLOR]
Ret = MsgBox("Are You Sure You Want To Delete This Oder Item?", _
              vbQuestion + vbYesNo, "Delete Order Item" <> vbYes Then Exit Sub

[COLOR="DarkGreen"]'Insert the item we're going to Delete into the History Table (tblHistory).
'Create our SQL String...[/COLOR]
StrgSQL = "INSERT INTO tblHistory (OrderID,ItemID,CustomerID,ItemName,QuantityOrdered) " & _
          "VALUES (" & Me.OrderID & "," & Me.ItemID & "," & Me.CustomerID & ",'" & _
          Me.ItemName & "'," & Me.QuantityOrdered & ";"

[COLOR="DarkGreen"]'Run the SQL String[/COLOR]
CurrentDb.Execute StrgSQL, dbFailOnError

[COLOR="DarkGreen"]'Delete the selected Order item From the Orders Table (tblOrders)
'Create our SQL String....[/COLOR]
StrgSQL = "DELETE FROM tblOrders WHERE OrderID=" & Me.OrderID & _
           " AND ItemID=" & Me.ItemID & ";"

[COLOR="DarkGreen"]Run the SQL String....[/COLOR]
CurrentDB.Execute StrgSQL, dbFailOnError
[COLOR="DarkGreen"]
'Requery the Form.[/COLOR]
Me.Requery

As for your Combo Boxes....I don't know. Without seeing your Database I would just be guessing which is what all this is for the most part anyways. For all you want to do, you really need to provide a lot of details.

.