Data Macros w/Access 2010?

[pmen]

I have two forms (frm_total_PC's) and (frm_retired_PC's) and would like to delete any of the computers in the (frm_Total_PC's) but have it restored or placed in the (frm_Retired_PC's). In the end they would be deleted but saved in the retired form. If you can give an example of how to do this, It would be so cool. Thanks in advance.

[meratigoerr]

You want to move data from one form to another.. .. are there tables associated with these forms? 1. Create an append query to create new records in the the retired table, based off your selected records in the total table. 2. create a delete query to delete records from total table that match in retired table.

$0.02 -- Don't use spaces in your object names. It unnecessarily complicates things.

[pmen]

Thank you, I will try this and let you know when it works. For the record I do have two tables for each of the forms. In case that helps. I also have a delete button on the retired form, which I hope the append query will allow the PC to be moved to the retired table. Thanks again.

[pmen]

After Review and testing the append query it does work, however, I only need to retire computers once in a while and need to some how attach a query or macro to the delete button on my (frmtotalPCInventory) and somehow forward the deleted PC to the retired table(tblRetiredcomputers) and form (frmRetiredcomputers) at a moments notice. Any suggestions?

[meratigoerr]

Set up your append query to reference the Primary key on your current form. Call the query from vba or macro in the onclick event of your button (vba: DoCmd.OpenQuery "YourAppendQuery"). Then call the second query. (Which doesn't have to have specific criteria since it can used without it.
Are you having a specific problem with the usage of the buttons, or setting up queiries themselves?

[pmen]

Both, at this point. I'll keep plugging away. Thanks for sticking with me on this project. Obviously, I'm new to this. But I won't give up. I will keep you abreast of my progress.

[June7]

Why 'move' records? Why not just change the value in a field to show status?

[pmen]

Why 'move' records? Why not just change the value in a field to show status?

eventually the PC's that are in the table(which are a few) will need to be discarded for Parts or given away. Nevertheless, we need just an inventory of those being retired. I don't mind keeping the original Total on a seperate form, gives us a little bit of history to fall back on.
Any help is appreciated.

[June7]

Status attributes could be 'Parts', 'Donated', 'Sold', 'InUse', 'Unassigned'. Have another field for DateRetd. All handled in one table. Or to comply with 'normalization' principle of no empty fields, have a 'retired' table with fields for ComputerID, DateRetd, DispositionType ('Parts', 'Donated', 'Sold'). Join the two tables in query, join type 'Include all records from Inventory ...', have critera of Is Null under the disposition field. Only active computers will show.

'Moving' records and/or replicating data is not taking full advantage of relational database structure nor the power of query filtering/sorting.

Are you using VBA or macros?

Post code and SQL statements for analysis if still having issues.

[pmen]

I keep getting this error on the debug (run time error 3075 - Syntax error in Date in query expression (DomainAsset#)

here is my code:
Option Compare Database
Option Explicit
'Remember to set a Reference to Microsoft DAO object Library
Private Sub btnDelete_Click()

If MsgBox("Deleting This Record will Affect The Whole Database" & vbCrLf & " Do You Really Want To Delete It?", vbInformation + vbYesNo, "DELETE RECORD!") = vbYes Then
DoCmd.SetWarnings False
DoCmd.RunSQL "DELETE * FROM tblTotalPCInventory WHERE (DomainAsset#=" & ")"
Me.Requery ' this will requery the form.
DoCmd.SetWarnings True
Else
Exit Sub
End If
End Sub

My fields are:
Domain Asset # Location Lab PC Name S/N OS Processor Drive Size RAM Zip/Floppy Disc Model PO# Notes Laptop/netbbok/Desktop
BBC 001 TBRC - C2988 3 BBC39037810 39037810-0001 etc ....

Help:

[June7]

The SQL is incomplete as it does not have criteria. What is the value DomainAsset is = to?

DoCmd.RunSQL "DELETE * FROM tblTotalPCInventory WHERE DomainAsset#=" & somevaluehere

somevaluehere can be a reference to field or control on form.

# is a special character and this might cause an issue. Enclose in [] - [DomainAsset#].

Advise not to use spaces, special characters, punctuation (underscore is exception) in any names, nor reserved words as names.

[pmen]

Domain asset # has a alphanumeric value ( BBC 001) which each Computer is tagged. The next computer is - BBC 002 etc...

[June7]

Then will also need text delimiters. I use apostrophe in sql statements:

DoCmd.RunSQL "DELETE * FROM tblTotalPCInventory WHERE [DomainAsset#]='" & somevaluehere & "'"

[pmen]

Then will also need text delimiters. I use apostrophe in sql statements:

DoCmd.RunSQL "DELETE * FROM tblTotalPCInventory WHERE [DomainAsset#]='" & somevaluehere & "'"

I put this as my statement:
DoCmd.RunSQL "DELETE * FROM tblTotalPCInventory WHERE [DomainAsset#]='" & "*[!((a-z) or (0-9))]*" & "'"

and it gets me to a dialogue box that says "Enter Parameter Value" then it wants me to insert something as the "DomainAsset#".
Even though I put the "BBC 001", which I don't want to do, it does nothing.
BTW Merry Christmas and thank you so much for your help.

[meratigoerr]

I put this as my statement:
DoCmd.RunSQL "DELETE * FROM tblTotalPCInventory WHERE [DomainAsset#]='" & "*[!((a-z) or (0-9))]*" & "'"

and it gets me to a dialogue box that says "Enter Parameter Value" then it wants me to insert something as the "DomainAsset#".
Even though I put the "BBC 001", which I don't want to do, it does nothing.
BTW Merry Christmas and thank you so much for your help.

First, anytime you use the asterisk in a where clause there should be a like somewhere in there. Where [DomainAsset] = '" & Like "*YourValues*'"

Second, when ever you see quotations followed by & then more quotations, then either the & is not required or the quotations are incorrect. In this case I think the missing Like statement would break this up.

Third, if it's asking for a parameter value, it can't find your field [DomainAsset#] or doesn't recognize it. I agree with June. You should not use special characters in your field names, which is one possibility for your error. Make sure you've spelt it correctly and that you've also spelt your table name correctly. Also make sure that this field is available to your form.

Fourth, Should this query every become successful it will most likely erase your entire table. If you're looking to delete only one record from a table, the one that is the current bookmark on your form. Your SQL statement should reference the primary key of that form (and table) to delete only the one record. The where clause of your SQL will usually look more like: Where [DomainAsset] = '" & Me.primaryKey & "'"

Fifth, this is a personal opinion, but I do know that it's shared by a lot of people. I try and use the execute method for SQL queries instead of RunSQL. With the execute method you still have the ability to debug. You also don't have to set warnings using the execute method. The limitation of not being able to use the me. reference is easily gotten around by saving your SQL as a string before calling it.

Sixth, again I'm going to agree with June's earlier statement. Changing the status of a record is much more desirable than deleting it. Too many errors could occur to accidently delete the wrong record, or delete the record before you've successfully moved it to the other table. Deleting records should only be done when absolutely necessary. It's easier to change a record from active to inactive ( or InUse to Parts or Sold ) then It is to move the record to a new table. Since both tables would have identical structure, this is redundant, creates unnecessary work and code, and opens the door to a ton of errors. ( usually tables with identical structure and identical data stored is a clue to potential design flaw in the database.)