update table from a form.

[PrittyMe143]

i'm viewing the data of the Purchased Items table from a certain form. i place a command button "Dispose" so that when i click it the current record will be deleted from the Purchased Items table and will placed in the Disposed Items table. Help me plezzzzzzzz!

 

[JustMaybe]

Had a very similar problem not long ago....
this is some old code i used...

this code is deleting a record from the table orders and copying it to the table 'invoiced'

Private Sub DUPLICATION_Click()
On Error GoTo Err_DUPLICATION_Click
Dim rsOrders As Recordset
Dim rsInvoiced As Recordset

Dim sOrderID As String

Set rsOrders = Application.CurrentDb.OpenRecordset("orders", dbOpenDynaset, dbPessimistic)
Set rsInvoiced = Application.CurrentDb.OpenRecordset("Invoiced")
'the table you want to copy the data from
rsOrders.MoveLast
rsOrders.MoveFirst
'
sOrderID = Me.OrderID
'
rsOrders.FindFirst ("orderID = " & sOrderID)
'
rsInvoiced.AddNew
'
rsInvoiced("orderID") = rsOrders("orderID")
'
rsInvoiced("CustomerID") = rsOrders("CustomerID")
'
rsInvoiced("EmployeeID") = rsOrders("EmployeeID")
'
rsInvoiced("OrderDate") = rsOrders("OrderDate")
'
rsInvoiced("ShipVia") = rsOrders("ShipVia")
'
rsInvoiced.Update
rsOrders.delete
'
rsOrders.CLOSE
rsInvoiced.CLOSE

I'm aware that using a recordset isn't always the quickest or logical way (it does work though!)

Someone gave me this code on these forums yesterday to copy fields...this could also be modified

Private Sub Purchase_Orders_Click()

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "PurchaseOrder"
'
DoCmd.OpenForm stDocName, , , stLinkCriteria
'
[Forms]![PurchaseOrder]![EndUser] = [Forms]![Orders]![EndUser]
[Forms]![PurchaseOrder]![Employee] = [Forms]![Orders]![EmployeeID]
[Forms]![PurchaseOrder]![OrderID] = [Forms]![Orders]![OrderID]

End Sub


if you need to copy details from a subform as wll let me know and ive got the complete code. Hope this helps, am new to VB myself..

Let me know if you need anymore help

Sarah

 

[PrittyMe143]

thanks sarah but it it didn't worked on mine. i tried revitalizing my original codes and followed your tips but it caused error/s.

what i supposedly need is to update my Disposed Items table based on the values of the controls in my form which displayed an instance of my Purchased Items table then delete that instance from the latter table.

somehow the algorithm would resemble this:

[disposed item].[field] = [current form].[control]

i tried doing that (well of course using the VB codes) but it didn't work and it said that i cannot directly assign a value for the Disposed Items table from the current mode blah, blah, blah...

 

[bradcccs]

Just a clarification:

Does the new "disposed item" record need to have the same Primary ID as the original "purchased item"?

Do you have cascade updates/deletes etc related to the item you are about to "dispose"?

 

[bradcccs]

My thoughts in the mean time:

Frm_Purchased = Form you are viewing purchased
PurchasedID = ID key of purchased item

Use dispose button to activate an append query (Qry_AppendPurchased)

Qry_AppendPurchased is based on the Purchased Table with a PurchasedID criteria of Forms!Frm_Purchased.PurchasedIDbox

Thus only the record you are viewing will be appended to the Disposed Table

Once appended, you could use the Delect current record action, or have a Delete query of a similar structure to the append query.

Obviously, depending on the structure of you db, this could have profound effects on cascading data. Practice with caution.

Brad.

 

[PrittyMe143]

so kind of u, brad. in fact, u gave me a chance to try to work on action queries.

i tried doing ur advise but nothing happened even if the cascade update option is checked as well as the enforce referential integrity.

 

[bradcccs]

I suggest you treat the process as two seperate operations initially, and combine once they are both functioning.

If you construct your append query correctly, then the record should be able to be transferred (added) to the second table, regardless of ref int. etc.

You will have to fullfil all the requirements of your destination table. ie: include all required fields / correct data types etc. or the append query will fail.

have fun.

Brad.

 

[PrittyMe143]

YES! thank u so much, brad. ur really a HEAVEN of a kind! (uh, what's that?!) if ur just here, i could have kissed you (just kidding). it was just i didn't really get everything that you are trying to point out and besides i didn't know much about action queries. but because of you,... i love them now.

thanks so much!!!