Increment a number based on instances in another table

[Lemmesee]

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.

 

[Jeanette Cunningham]

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