Notices

Great community. Great ideas.

Welcome to ASP/Free, a community dedicated to helping beginners and professionals alike in improving their knowledge of Microsoft's development and administration technologies. Sign up today to gain access to the combined insight of tens of thousands of members.

Thread: Data Macros w/Access 2010?

Page 1 of 2 12 LastLast
Results 1 to 15 of 25
Share This Thread →
  1. #1
    pmen is offline Registered User
    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
    meratigoerr's Avatar
    meratigoerr is offline Contributing User
    Join Date
    Jun 2011
    Location
    South Jersey
    Posts
    544
    Rep Power
    58
    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.

  3. #3
    pmen is offline Registered User
    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.

  4. #4
    pmen is offline Registered User
    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?

  5. #5
    meratigoerr's Avatar
    meratigoerr is offline Contributing User
    Join Date
    Jun 2011
    Location
    South Jersey
    Posts
    544
    Rep Power
    58
    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?

  6. #6
    pmen is offline Registered User
    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.

  7. #7
    June7's Avatar
    June7 is offline Moderator
    Join Date
    Apr 2009
    Location
    The Great Land
    Posts
    4,618
    Rep Power
    649
    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

  8. #8
    pmen is offline Registered User
    Join Date
    Dec 2012
    Posts
    13
    Rep Power
    0

    reports and deleted inventory

    Quote Originally Posted by June7 View Post
    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.

  9. #9
    June7's Avatar
    June7 is offline Moderator
    Join Date
    Apr 2009
    Location
    The Great Land
    Posts
    4,618
    Rep Power
    649
    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.

  10. #10
    pmen is offline Registered User
    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:

  11. #11
    June7's Avatar
    June7 is offline Moderator
    Join Date
    Apr 2009
    Location
    The Great Land
    Posts
    4,618
    Rep Power
    649
    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.

  12. #12
    pmen is offline Registered User
    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...

  13. #13
    June7's Avatar
    June7 is offline Moderator
    Join Date
    Apr 2009
    Location
    The Great Land
    Posts
    4,618
    Rep Power
    649
    Then will also need text delimiters. I use apostrophe in sql statements:

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

  14. #14
    pmen is offline Registered User
    Join Date
    Dec 2012
    Posts
    13
    Rep Power
    0

    Value

    Quote Originally Posted by June7 View Post
    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.

  15. #15
    meratigoerr's Avatar
    meratigoerr is offline Contributing User
    Join Date
    Jun 2011
    Location
    South Jersey
    Posts
    544
    Rep Power
    58
    Quote Originally Posted by pmen View Post
    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 LastLast
Share This Thread →

Become Part of This Conversation

Join NowFor Free!

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

ASP Free Advertisers and Affiliates