Results 1 to 15 of 15
  1. #1
    5 Star Lounger
    Join Date
    May 2003
    Location
    Sheffield, Yorkshire
    Posts
    951
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Log Number (2003)

    In the attached database, in the table tblXrayNumbers, a part number is entered, along with a start value, and a quantity. A query (NextStart) works out the next starting value. I want the user to enter the part number on a form, with the quantity, and be given a reference number made up of the part no, Month & year prefix, and start value and end value. So if he entered the part number PT1235, for the first time, with a quantity of 10, reference number would come up, PT125 DG 1-10. The next time he enter PT125, the start value would be 11. Every time a new part number is entered, the start value will always be one. I want to get away from the user having to enter a start number, as this is causing problems. I just want them to enter a part number and quantity, and the database do the rest.
    Attached Files Attached Files

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Log Number (2003)

    Will your form be based on the tblXrayNumbers table or on another table?

  3. #3
    5 Star Lounger
    Join Date
    May 2003
    Location
    Sheffield, Yorkshire
    Posts
    951
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Log Number (2003)

    Yes, tblXraynumbers, at the moment, but if another table would make things easier. TblXraynumbers has data in it, but I want to start from scratch in August, so there won't be any data, until the user types in the first part number.

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Log Number (2003)

    Thanks. In order to keep this thread independent of others, please tell us how the month and year prefixes are to be determined (and any other information that is needed to help you).

  5. #5
    5 Star Lounger
    Join Date
    May 2003
    Location
    Sheffield, Yorkshire
    Posts
    951
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Log Number (2003)

    Using information you gave me previously,

    =Chr(Year([DateField])-1940) and =Chr(Month([DateField])+64) on controls on the form, which would also need to be in the table I assume.

    The only thing we require the user to enter is the date, part number, and quantity.

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Log Number (2003)

    See the attached version.
    I created a query qryXRayNumbers that performs the calculations. This query is the record source of the form frmTest.
    The form has a Before Update event procedure for PartNumber that gets the start number from qryNextStart in a new record:

    Private Sub PartNumber_AfterUpdate()
    If Me.NewRecord Then
    Me.StartValue = Nz(DLookup("NextStartValue", "qryNextStart", _
    "PartNumber=" & Chr(34) & Me.PartNumber & Chr(34)), 1)
    End If
    End Sub

    The DateField, PartNumber and Qty fields have been made required, and the PartNumber text box is locked in existing records - changing it in an existing record would make the start number invalid.
    Attached Files Attached Files

  7. #7
    5 Star Lounger
    Join Date
    May 2003
    Location
    Sheffield, Yorkshire
    Posts
    951
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Log Number (2003)

    Thank you Hans, for your time and patience. This is exactly what I'm after. Still open to user error. If on one occasion they enter a quantity of say 10, they get DG 1-10. Enter another 10, the reference would be DG 11-21. If on realising the first quantity should have been 9, if they alter it, that reference changes to DG 1-9, but the other reference remains at 11-21. At least I can edit the table for them, which probably a bad thing. Thanks again, and my apologies for the long winded approach.

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Log Number (2003)

    So you should lock the Qty text box (and perhaps the DateField one too) in existing records. With this approach of assigning numbers, users shouldn't go back and change the values after they have already been saved. Imagine what would happen if you already told someone to use 11-21 and they went ahead with that information, and then the numbers would be changed in the database to 10-20...

  9. #9
    5 Star Lounger
    Join Date
    May 2003
    Location
    Sheffield, Yorkshire
    Posts
    951
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Log Number (2003)

    I've just been reminded that the numbers need to start at 1 again for the consecutive month. If Part No PT1245 was DG 1-20 this month, it could also have DH 1-20 next month. This is because the numbers have to be etched on parts, and we don't want to end up with long numbers. Can I change this in the 'Before Update' code?

  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Log Number (2003)

    You'll have to change all the queries too, because the next start number now doesn't depend on the part number only, but also on the month of the year.
    See attached version.

    Please, could you try to provide ALL relevant information at the start next time, instead of in installments? Thank you.
    Attached Files Attached Files

  11. #11
    5 Star Lounger
    Join Date
    May 2003
    Location
    Sheffield, Yorkshire
    Posts
    951
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Log Number (2003)

    My apologies Hans, I did forget. Thanks again for your help.

  12. #12
    5 Star Lounger
    Join Date
    May 2003
    Location
    Sheffield, Yorkshire
    Posts
    951
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Log Number (2003)

    Just came across a problem I can't figure out. We started using the database on Friday 1st August, and everythings been fine. We entered a number earlier today,

    1A99 for a quantity of 40,

    because we had entered this part on another day, we got DH 39-78. We came across another order for this part, again for 40, but when we entered it, it came up DH 39 again.

  13. #13
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Log Number (2003)

    I tried it in the sample database from <post:=722,341>post 722,341</post:> higher up in this thread and it worked OK. Are you sure that the previous record was saved, and that the part number was entered correctly?
    Attached Images Attached Images
    • File Type: png x.png (5.8 KB, 0 views)

  14. #14
    5 Star Lounger
    Join Date
    May 2003
    Location
    Sheffield, Yorkshire
    Posts
    951
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Log Number (2003)

    Found the problem thanks Hans. I added IWO No into a query to create a log, and put it in the wrong query.

  15. #15
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Log Number (2003)

    Glad you found it.

Posting Permissions

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