Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium


Increment value onClick

Posted on 2005-05-06
Medium Priority
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
  • 2
  • 2
  • 2
LVL 10

Assisted Solution

perkc earned 800 total points
ID: 13945242
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

ID: 13945503

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

ID: 13947720

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.

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

LVL 11

Accepted Solution

Stephen_Perrett earned 1200 total points
ID: 13948698
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

ID: 13957671
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

ID: 13957699
Thanks Ross,

Glad to help!


Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Implementing simple internal controls in the Microsoft Access application.
A quick solution showing how to control and open a POS Cash Register Drawer using VBA with MS Access.
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
Suggested Courses

564 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