Increment value onClick

Posted on 2005-05-06
Last Modified: 2012-05-05
Hi i have a purchase order table.

I need a field which would identiy one purchase order, but that value would occur many time in the table as one Purcashe Order can have many items on the list e.g:-

p.o._id | p.o._number |date      | type        | description | price  |ID_staff (Equipment purcashed for)
1         |121               |02/05/05 |hardware   |monitor       |200.00|20
2         |121               |02/05/05 |software    |Photoshop   |150.00|20
3         |122               |03/05/05 |hardware   |hardrive      |100.00|13

So i need a way of allowing the user to increment the p.o. number without room for error, but on demand as their can be multiple records for 1 p.o. number?

I maybe going about the design wrong?  and advice\solution  would be welcome?


Question by:rossh1977
    LVL 10

    Assisted Solution

    You need two tables:

      PO_Number (AutoNumber - Primary Key)
      PO_Description (Text)

      PO_Number (Number - Foriegn Key)
      PO_Line (AutoNumber - Primary Key)
     ...all other needed fields

    You then go to the relationship window and establish a one-to-many relationship between the 'PO_Number' fields in the two tables. The 'tbl_PO' table is the 'one' side of the relationship.

    Let me know if you need additional clarification.


    Author Comment


    Thanks for gettin back so quickly.  I've posted my relationship diagram here:-

    Where tbl_PO this is items

    Does this look okay?

    I'm just wondering how i'm going to go about creating a form to update these values in 2 tables now.  Will i need a subform?


    LVL 10

    Expert Comment


    Sorry to do this but I've got to jump on a flight to Vegas.

    I've asked some other experts to join in an help you with your question.

    I'll check when I get back on Tuesday to make sure you got an answer.

    LVL 11

    Accepted Solution

    Yes, I would use a subform. But first the relationship between  purchase_order and items needs to be set up differently. Delete the existing relationship

    Create a new field in items called purchase_order_id

    Then set up a new relationship between the tables on the purchase_order_id fields.

    Create a new form for the sub form

    When creating select

    AutoForm: Tabular        <This will create what is called a continuous form

    and select the items table as the source of the object's data

    save the form and close it.

    You can tidy this up later but to get the idea of how things work leave it as is for now!

    You will also need a main form based on the purchase_order table

    Open the main form in design view and insert the existing sub form this should allow you to link  on the purchase_order_id fields

    Save your form and see if this works. Please let us know if you have any problems


    Author Comment

    Hi Great thanks for that.

    I think i've figured the subforms etc out and made the changes.

    I've got another couple of questions regarding calcuating totals in and out of the subform, but i stick in another question

    Thank you

    LVL 11

    Expert Comment

    Thanks Ross,

    Glad to help!


    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Looking for New Ways to Advertise?

    Engage with tech pros in our community with native advertising, as a Vendor Expert, and more.

    Suggested Solutions

    In Debugging – Part 1, you learned the basics of the debugging process. You learned how to avoid bugs, as well as how to utilize the Immediate window in the debugging process. This article takes things to the next level by showing you how you can us…
    I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
    Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
    Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…

    779 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    18 Experts available now in Live!

    Get 1:1 Help Now