Page 1 of 2 12 Last
  • Jump to page:
    #1
  1. No Profile Picture
    Registered User
    ASP Explorer (0 - 99 posts)

    Join Date
    Dec 2012
    Posts
    13
    Rep Power
    0

    Red face Data Macros w/Access 2010?


    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.
  2. #2
  3. Contributing User
    ASP Adventurer (500 - 999 posts)

    Join Date
    Jun 2011
    Location
    South Jersey
    Posts
    544
    Rep Power
    59
    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.
  4. #3
  5. No Profile Picture
    Registered User
    ASP Explorer (0 - 99 posts)

    Join Date
    Dec 2012
    Posts
    13
    Rep Power
    0
    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.
  6. #4
  7. No Profile Picture
    Registered User
    ASP Explorer (0 - 99 posts)

    Join Date
    Dec 2012
    Posts
    13
    Rep Power
    0
    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?
  8. #5
  9. Contributing User
    ASP Adventurer (500 - 999 posts)

    Join Date
    Jun 2011
    Location
    South Jersey
    Posts
    544
    Rep Power
    59
    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?
  10. #6
  11. No Profile Picture
    Registered User
    ASP Explorer (0 - 99 posts)

    Join Date
    Dec 2012
    Posts
    13
    Rep Power
    0
    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.
  12. #7
  13. Moderator
    ASP Super Genius (4500 - 4999 posts)

    Join Date
    Apr 2009
    Location
    The Great Land
    Posts
    4,621
    Rep Power
    650
    Why 'move' records? Why not just change the value in a field to show status?
    I like to know if my suggestion helped you. One way to let me know is by clicking star at bottom left corner of post.
    Debug!Debug!Debug!http://www.cpearson.com/excel/debug.htm
  14. #8
  15. No Profile Picture
    Registered User
    ASP Explorer (0 - 99 posts)

    Join Date
    Dec 2012
    Posts
    13
    Rep Power
    0

    reports and deleted inventory


    Originally Posted by June7
    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.
  16. #9
  17. Moderator
    ASP Super Genius (4500 - 4999 posts)

    Join Date
    Apr 2009
    Location
    The Great Land
    Posts
    4,621
    Rep Power
    650
    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.
    Last edited by June7; December 14th, 2012 at 06:16 PM.
  18. #10
  19. No Profile Picture
    Registered User
    ASP Explorer (0 - 99 posts)

    Join Date
    Dec 2012
    Posts
    13
    Rep Power
    0
    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:
  20. #11
  21. Moderator
    ASP Super Genius (4500 - 4999 posts)

    Join Date
    Apr 2009
    Location
    The Great Land
    Posts
    4,621
    Rep Power
    650
    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.
  22. #12
  23. No Profile Picture
    Registered User
    ASP Explorer (0 - 99 posts)

    Join Date
    Dec 2012
    Posts
    13
    Rep Power
    0

    Value


    Domain asset # has a alphanumeric value ( BBC 001) which each Computer is tagged. The next computer is - BBC 002 etc...
  24. #13
  25. Moderator
    ASP Super Genius (4500 - 4999 posts)

    Join Date
    Apr 2009
    Location
    The Great Land
    Posts
    4,621
    Rep Power
    650
    Then will also need text delimiters. I use apostrophe in sql statements:

    DoCmd.RunSQL "DELETE * FROM tblTotalPCInventory WHERE [DomainAsset#]='" & somevaluehere & "'"
  26. #14
  27. No Profile Picture
    Registered User
    ASP Explorer (0 - 99 posts)

    Join Date
    Dec 2012
    Posts
    13
    Rep Power
    0

    Value


    Originally Posted by June7
    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.
  28. #15
  29. Contributing User
    ASP Adventurer (500 - 999 posts)

    Join Date
    Jun 2011
    Location
    South Jersey
    Posts
    544
    Rep Power
    59
    Originally Posted by pmen
    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.)
Page 1 of 2 12 Last
  • Jump to page:

Similar Threads

  1. Access macros
    By dragon2309 in forum Microsoft Access Help
    Replies: 2
    Last Post: March 1st, 2006, 05:59 PM
  2. Help with Access Macros
    By maxdaemon in forum Microsoft Access Help
    Replies: 3
    Last Post: August 19th, 2004, 09:36 AM
  3. Word Macros from Access
    By sando in forum Microsoft Access Help
    Replies: 0
    Last Post: July 25th, 2004, 10:29 PM
  4. Access Macros
    By Steve Schofield in forum ASP Development
    Replies: 0
    Last Post: June 5th, 2000, 04:08 PM

IMN logo majestic logo threadwatch logo seochat tools logo