Inserting records and updating numbers

  • Thread starter Thread starter stuartAG
  • Start date Start date
S

stuartAG

Guest
I am rewriting a database that I wrote in work about three years ago. It is a planned maintenance database which consists of a list of tasks to be performed on different types of machinery.

The tasks are to be performed in a specified order and need to be sorted as such. In the original database I did this by typing in a 'task no' for each task. Note that the Task Nos are machine specific since all of the machines are different. e.g

Machine Task Task No

Machine 1 clean filter 01
Machine 1 check oil 02
Machine 2 check electrics 01
Machine 2 check filter 02

The problem with this is that if the end user wants to insert a new task, in the middle of the table, they have to type in new task numbers for the tasks that are to follow on that particular machine. e.g

Machine 1 clean filter 01
Machine 1 check bearings 02
Machine 1 check oil 03

Has anyone got any ideas how this can be done automatically?

Thanks

Stu
 
Conceptually, how about a table: RepairSchema

Columns: MachineType (maybe referring to another table entirely), RepairTaskNo, and RepairTask. Make MachineType and RepairTypeNo a multi-field Primary Key, so that the specific combination can never be duplicated.

Once you've got that filled correctly as a reference table I'd say your task becomes somewhat simpler. Now you need a table for RepairsMade, that holds the individual MachineID, along with the RepairTaskNo and probably a DateField, etc.

Assumptions: Your main form holds the Machine info, including Type, and your subform holds the Repairs done on this machine. In the AfterInsert event of your subform, use something along these lines:
Code:
  Dim rp As Integer
  
  Me.RepairTaskNo = Dmax("[RepairTaskNo]","RepairsMade","[MachineID]=" & Me.Parent.MachineID)+1
  'this should work even for the first record
 
Last edited:

Users who are viewing this thread

Back
Top Bottom