Results 1 to 8 of 8
  1. #1
    Join Date
    Jul 2010
    Posts
    32

    Unanswered: Next/Previous/Delete record from certain tables

    Hello all.

    I've created a form in Access which uses 2 tables I have created (past orders and curent orders). Basically the aim of the form is to transfer details manually from the current orders into the past orders once an order has been dispatched.

    Hvaing imported all the info needed from the relevant tables, command buttons were cerated either side but do not work.

    I was just wondering what the VBA code would be for next/previous record from the past orders and the same for the current orders?

    Cheers

  2. #2
    Join Date
    May 2010
    Posts
    601
    Boyd Trimmell aka HiTechCoach HiTechCoach.com (free access stuff)
    Microsoft MVP - Access Expert
    BPM/Accounting Systems/Inventory Control/CRM
    Programming: Nine different ways to do it right, a thousand ways to do it wrong.
    Binary--it's as easy as 1-10-11

  3. #3
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    Also, it's usually a mistake to break the data up like that. Normally you'd want to keep it in one table, with a field to designate its status. That lets you query current, past or both easily.
    Paul

  4. #4
    Join Date
    May 2010
    Posts
    601
    Paul makes a great point.

    I agree that you should avoid copying records between tables.

    As Paul suggested, add a field for "status". In your queries, you can us this field to filter the data. To the user it can appears as moved into separate tables. You could even use separate forms for viewing the past and current orders. You would use the "status" field in the form's record source to limit the data for that form.
    Boyd Trimmell aka HiTechCoach HiTechCoach.com (free access stuff)
    Microsoft MVP - Access Expert
    BPM/Accounting Systems/Inventory Control/CRM
    Programming: Nine different ways to do it right, a thousand ways to do it wrong.
    Binary--it's as easy as 1-10-11

  5. #5
    Join Date
    Jul 2010
    Posts
    32
    So if I use a 'Create Table' query with a field in the 'Current Orders' labelled 'Status' and have it as a drop down (Lookup) with a choice of 'Past' and 'Current' (regarding whether the customers order is still here or if has gone), how would I go about selecting 'Past' in a current order for it to be taken from the 'CurrentOrder' table and inserted into the 'PastOrders' table?

  6. #6
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    don't
    store the data in one table
    as others have said put a flag (say a boolean value possibly called IsComplete) and exclude records as required based on that flag
    I'd rather be riding on the Tiger 800 or the Norton

  7. #7
    Join Date
    Jul 2010
    Posts
    32
    Just re-read everything posted and I understand now. Thanks for all your helps (sorry if it seemed like I wasn't paying much attention and posting loads of problems! )

  8. #8
    Join Date
    May 2010
    Posts
    601
    Glad to hear that things are starting to become more clear.

    Good luck with your project.
    Boyd Trimmell aka HiTechCoach HiTechCoach.com (free access stuff)
    Microsoft MVP - Access Expert
    BPM/Accounting Systems/Inventory Control/CRM
    Programming: Nine different ways to do it right, a thousand ways to do it wrong.
    Binary--it's as easy as 1-10-11

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •