Prev: help files in Access 2007 VB
Next: all custom harley softail for sale or trade
From: Lemmesee on 7 May 2010 21:12
basically i have 2 tables. One is a Table of "Jobs" the other is a table of
"Change Orders". In a Form to add Change Orders, i need an identifier that
increments based on how many change orders are related to a specific job.

Lets say, i have a job in the Job table that has 100 as its job number. then
i open a "Change Order" form and i enter 100 as the job number. I need an
incrementing number identifying the change count to that job.

Then I need to open other forms, related to other tables, Enter a job number
and then allows me to choose which Change Order to that job to relate to.

Then i need a query/report that shows all of the changes to that job or the
details of the specific change.

Each Change Order also has an incrementing identifier that is not related to
any specific job.

I can make the queries and reports but i dont know how to make the make the
change orders relate to different records in the jobs table.

I sure hope this makes sense to someone and i thank the one/s that it does
in advance.
From: Jeanette Cunningham on 8 May 2010 00:41
Make the table for change orders a child table of the jobs table.

tblChangeOrders
ChangeOrderID = pk, autonumber
JobID = foreign key from jobs table.
ChangeNbr = number, long integer
Other fields as needed

Make the relationship one to many from the jobs table to the change orders
table.

To get the incrementing number for ChangeNbr:

You can use something like this in the before update event of the form for
change orders.
Lock the textbox for ChangeNbr, so that only your code can set the number.
This also assumes you are the only user of the database (not multi user).

Private Sub Form_BeforeUpdate(Cancel As Integer)
Me.ChangeNbr = 1 + DMax("[ChangeNbr]", "tblChangeOrders")
End Sub

Note: I have suggested names for tables, fields and controls.
Use your own names instead of mine.


Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

"Lemmesee" <Lemmesee(a)discussions.microsoft.com> wrote in message
news:018F91C1-BEDC-4F10-B63B-6A98ADC92777(a)microsoft.com...
> basically i have 2 tables. One is a Table of "Jobs" the other is a table
> of
> "Change Orders". In a Form to add Change Orders, i need an identifier that
> increments based on how many change orders are related to a specific job.
>
> Lets say, i have a job in the Job table that has 100 as its job number.
> then
> i open a "Change Order" form and i enter 100 as the job number. I need an
> incrementing number identifying the change count to that job.
>
> Then I need to open other forms, related to other tables, Enter a job
> number
> and then allows me to choose which Change Order to that job to relate to.
>
> Then i need a query/report that shows all of the changes to that job or
> the
> details of the specific change.
>
> Each Change Order also has an incrementing identifier that is not related
> to
> any specific job.
>
> I can make the queries and reports but i dont know how to make the make
> the
> change orders relate to different records in the jobs table.
>
> I sure hope this makes sense to someone and i thank the one/s that it does
> in advance.


 | 
Pages: 1
Prev: help files in Access 2007 VB
Next: all custom harley softail for sale or trade