Increment value onClick

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?


Who is Participating?
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

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.

rossh1977Author Commented:

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?


Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.


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.

rossh1977Author Commented:
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

Thanks Ross,

Glad to help!

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.