Results 1 to 13 of 13
  1. #1
    Join Date
    Mar 2009
    Posts
    11

    Unanswered: Send Button Access 2007

    Hiya

    I have two tables - Quotes, Workorders

    The user will create a Quote for the customer and then post the quote away. Once the quote has been accepted, a workorder needs to be created based on that quote, ie materials, prices etc

    Rather than have the user re-type the quote into a workorder, is there a way he can just click a button, and the quote, copys itself into the workorders?

    Quotes has the following important fields

    QuoteID
    CustomerID
    SUBFORM
    QuoteID
    Material
    UnitPrice
    Quantity


    Workorders has the following important fields

    WorkorderID
    CustomerID
    SUBFORM
    WorkorderID
    Material
    UnitPrice
    Quantity

    I have included a copy of my DB if anyone needs to see it

    From my thinking, I thought this would be an easy task but I was wrong lol. Hopefully someone will be able to help

    I'm not the best at Access or VBA yet, so if this is possable or an alternative way, then I would appreciate full assistance lol or a good step by step guide

    Cheers
    CCK
    Attached Files Attached Files

  2. #2
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    One way would be a query:

    INSERT INTO Workorders (Field1, Field2...)
    SELECT Field1, Field2...
    FROM Quotes
    WHERE QuoteID = Forms!FormName.ControlName
    Paul

  3. #3
    Join Date
    Mar 2009
    Posts
    11

    Thanks

    Hi Paul

    Many thanks for the reply. I will have a go at that tonight

    Do you have any idea what the code would be for the Button?

    I assume it would be like docmd.openform filter on quoteID along those kinda lines?

    Cheers
    CCK

  4. #4
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    My assumption is you'd be on a form showing the quote, so to create a workorder from the displayed quote:

    CurrentDb.Execute "INSERT INTO..."

    Here's a decent tutorial on building SQL in VBA:

    FAQ: How do I construct an SQL statement in VBA? - MDBMakers.com

    You could also create a saved query and run it with DoCmd.OpenQuery
    Paul

  5. #5
    Join Date
    Mar 2009
    Posts
    11
    Hiya again

    Many thanks for your input

    Not had a chance to try this out yet, as there is always someone looking at baby stuff and wedding stuff on my pc lol

    So far, I think I'm following you

    Create a Query based on the Quote & Workorder Tables, include all fields from both tables?

    Create the SQL statement that you supplied, and have that on the ONCLICK event and that should insert the Quote to the Workorder?

    How would all that effect the WorkorderID as both the QuoteID and WorkorderID are Autonumbers?

    I'm probs making this hard for myself lol

    Cheers

    CCK

  6. #6
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    Sounds like congratulations are in order!

    You wouldn't necessarily want all fields, just the ones that you wanted in the workorder table. You would either create a saved query or create the SQL in code, but not both. If the workorder ID is an autonumber, you would generally not include that field in the append.
    Paul

  7. #7
    Join Date
    Mar 2009
    Posts
    11
    lol Thanks

    How about the subform tho, as thats the one that containts all the Materials for that job?

    How would that work?

    Sorry to be a pain

    Cheers
    CCK

  8. #8
    Join Date
    Mar 2009
    Posts
    11
    Anymore help on this one?

  9. #9
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    Basically you have to append the main record, get the autonumber, then use that to append the child records. Here's a nice discussion on @@Identity, which is a good way to get that autonumber:

    Question @@IDENTITY in Access - Access World Forums
    Paul

  10. #10
    Join Date
    Mar 2009
    Posts
    11
    Cheers Pbaldy..

    Would you be able to look at the DB and see if you can get it going?

  11. #11
    Join Date
    Sep 2006
    Posts
    265
    Why no have just one table with an addition field

    OrderType

    Q Quote
    O Order

    When you want Quotes test for OrderType = "Q" and for Orders test for OrderType="O"

    Simon

  12. #12
    Join Date
    Mar 2009
    Posts
    11
    Hiya Simon

    You think that one table with the following fields would work better?

    MaterialID
    UnitPrice
    Qty
    Type (Either Q or O)

  13. #13
    Join Date
    Sep 2006
    Posts
    265
    Yes,

    Keep to one table. Add Order or whatever reference you wish to use to the Table. You could also have in Type

    H History - old Orders in Archive
    X Cancelled

    If the Quote becomes a Order set the Type to "O".

    Simon

Posting Permissions

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