Results 1 to 3 of 3
  1. #1
    Join Date
    Sep 2013
    Posts
    25

    Unanswered: Auto-Increment a field in a sub-form, which resets when you open a new main record?

    Hello, I have a database that stores the records from some quality assurance tests. Each Test consists of about 50 readings from a testing machine (it may vary)
    So I have the table "Tests" which holds data such as date, hour, tested part, etc. Its key field is autonumerical "TestNo". The other table "Readings" has two key fields: "TestNo", a numerical value in a one-to-many relationship with "TestNo" from "Tests", and "readingNo", a numerical value.

    I placed a subform to fill the "Readings" Table on a "Tests" form, and id like the "ReadingNo" field to start from 1 and increment itself every time you add a new readings record, but it has to go back to one when i open a new record in "Tests"

    Thanks in advance!

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    dont use autoincrement columns as havign any meaning outside the system. you cannot guarantee the order or that there will nto be gaps, so if you need test numbers 1...50 in sequence then you qill have to write a function that does it for you.

    Autoincrement is used when there is no obvious so called 'natural key' or the obvious key is too long or too complex. in this case the natural key would probably be the ID of the item being tested and the timesatmp that test was taken, but you could easily write a function that used, say, a test number of 1..50 in conjunction with the item being tested.
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Sep 2013
    Posts
    25
    I see. should i then make an autonumeric field to be the key field of tests? There is nothing special about the item tested, they are random samples of 50 units from a batch!

    I appreciate your tip but i´ve already made an excel to visualize all this data, im quitting this job in two weeks and i want to let this operational by then. Im obviously no proggrammer so they get what they pay for lol.
    Im guessing an afterupdate code in the subform will do, could you please help me with the coding, as i know nothing of VBA?
    Im gonna need the incremental value for visualization purposes anyway. Thank you

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •