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

    Unanswered: "autonumerical" field in a subform that resets when the parent form adds a new record

    I have no idea how to do that. Basically i have a record that deals with product testing and each record in the parent form deals with batches, whereas the subform deals with individual units. Each unit test is defined by a field with the same value as the key field in the "batch test" table, plus a number which i want to go from 1 to whatever, and reset so it starts as 1 again when a new batch record is opened.
    Thanks in advance!

  2. #2
    Join Date
    Nov 2011
    Posts
    413
    I don't understand the reason why you would want to get a value and then delete it, it makes no sense to me!
    But, assuming your table name is tblTest and the field name in the table is UniqueNumber
    You could try the following: OnCurrentEvent
    UniqueNumber = Nz(DMax("UniqueNumber", "tblTest") + 1)
    Me.Dirty = False

    Then on the subform, assuming it is named frmtestSubform:
    If Me.NewRecord = True Then
    [frmTestSubform].[Form]![UniqueNumberSub] = Null
    Else
    If Me.NewRecord = False Then
    UniqueNumberSub = Nz(DMax("UniqueNumber", "tblTest") + 1) 'Increment Number By One.
    Me.Dirty = False
    End If
    End If

    Also on the subform CurrentEvent:
    Forms!frmTest.SetFocus
    If Me.NewRecord = True Then
    Me.UniqueNumberSub = Null
    Me.Dirty = False
    Else
    UniqueNumberSub = Nz(DMax("UniqueNumber", "tblTest") + 1) 'Increment Number By One.
    Me.Dirty = False
    End If

    HTH

  3. #3
    Join Date
    Sep 2013
    Posts
    25
    I do not want to delete the number! forgive me if im not yet used to the terminology. Basically what i want in the singular tests table is to store the "batchtestiD" number and a number that goes from 1 to as many as necessary for each piece in the batch, but starts back at 1 when the batchtestiD changes. The table should look like this
    BatchtestiD piecenumber value
    1 1 10
    1 2 13
    1 3 11
    2 1 12
    2 2 10
    And so on.
    That's what i meant with reset, i didn't want to delete the already filled records. Thanks for your reply though, i'll see if it still works for me!

  4. #4
    Join Date
    Nov 2011
    Posts
    413
    At least some screenshots of your form,querie(s) would be helpful.I can't make any sense of your post without some clear explanation.Maybe a sample db uploaded would help.Of course remove all confidential data,compile and zip.

  5. #5
    Join Date
    Sep 2013
    Posts
    25
    Sure thing! I made this little example. Typed in manually all the records in the field that i want to be automatic, so you can take a look at what i want. I hope this is clear enough and thanks a lot for your predisposition!
    Attached Files Attached Files

  6. #6
    Join Date
    Nov 2011
    Posts
    413

    autonumerical field in a subform

    Ok, here is a revised version.Take a look at the last 2 Records.Hope this helps.
    Attached Files Attached Files

  7. #7
    Join Date
    Sep 2013
    Posts
    25
    It's almost perfect! but i still need it to reset when the main form adds a new record. You made 3 piecemeasurements (0, 1, 2) in the last record but when i opened a new one, it kept counting from 3 without jumping back to 0 as i need it to. Btw where should i look into in order to understand what you did?
    Also, thanks for the tips about coding practices, that is very much appreciated.

  8. #8
    Join Date
    Nov 2011
    Posts
    413
    Can you explain the logic behind resetting the counter back to 0 instead of continuing to count? I don't understand this, why it would make a difference!!!

  9. #9
    Join Date
    Nov 2011
    Posts
    413

    autonumerical field in a subform

    Here is a revised version. Still can't understand the reasoning behind this.

    Good Luck With Your Project.
    Attached Files Attached Files

  10. #10
    Join Date
    Sep 2013
    Posts
    25
    Thank you very very much! this is exactly what i was looking for!
    I realise now that it would be much simpler to just set it as an autonumber, yeah. It just seemed neater to me since each measurement has 2 key fields (the testiD and the PieceNo). Plus i needed it at least for visual purposes since the technician running the tests is supposed to be able to quickly visualise the number of pieces he measured for a batch and input the results with one hand, WHILE running the tests with the other! (a multinational company and they have a mech engineering intern with 0 programming skills to do this stuff, can you imagine? lol)
    Thanks for your patience, bear in mind that i learnt access by myself so my reasoning may be sub-optimal. I'll get a grip in common practises this weekend while studying!

  11. #11
    Join Date
    Sep 2013
    Posts
    25
    Sorry to bother you again, i've tried to understand your code and i wasn't able to understand the criteria of your DCount function
    The criteria is: "TestID = " & Me.Parent!TestiD
    Can you explain it to me?

  12. #12
    Join Date
    Nov 2011
    Posts
    413
    It just means that the Primary Key on the subform equals the Primary Key on the Main form so the records stay in sync. As you probably figured out, and I should have told you, the code runs using he after update event on the field measurement.
    Your Welcome.

Tags for this Thread

Posting Permissions

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