• LookUP (Access 2002)

    Author
    Topic
    #359117

    Hi All,

    Table1: tblDrawingNo
    Table2: tblType
    ———————————–

    tblDrawingNo:
    Field1: DwgNoID–AN
    Field2: DwgNo–txt
    SampleData: M123,M124, P456, P457

    tblType:
    Field1: TypeID–AN
    Field2: Type–txt
    SampleData: M,E,W,P
    —————————————
    Form:
    ControlSource: tblDrawings
    Textbox(bound): DrawingNo
    ComboBox(bound): cboType

    Problem:

    When the user inputs a Type in cboType, I need cboDrawingNo to determine the next number respective to the type in cboType and apply it to the field. i.e. if Type = M then the value should be M125 in DrawingNo or if Type = P then the value would be P458.

    How can I accomplish this? My first thought was to use the DMax+1 but I can’t figure out how to have Access determine the Type.

    I would REALLY appreciate any help.

    Thanks,

    Viewing 0 reply threads
    Author
    Replies
    • #537430

      I don

      • #537438

        Rupert,

        Almost correct. If the user select “M” in TypeBox then textBoxDrawing Number should determine the last DrawingNo beginning with “M” and the highest Value+1. I don’t want the user to input anything in the text box “DrawingNo”

        I hope that makes more sense.

        For example:
        Currently in tblDrawingNo the following values exist:
        M123, M124, P456 and P457.

        The user selects “M” in cboType. The value that needs to appear in “DrawingNo” =M125—The Type, the Max No +1. However, if the user selects “P” in cboType the value would be P458.

        Thanks for your help and sorry to be so confusing.

        • #537444

          So you are creating a new number? A number 1 greater than the highest number in the list?

          If that

          • #537446

            Rupert,

            Yes I will be storing the number. The textBox “DrawingNo” is bound to the underlying table, tblDrawings” and field “DrawingNo”. This is also the same location Access must look at to determine the Type and next number.

            Is that wrong?

            Thanks again.

            • #537449

              I see, well it

            • #537450

              Rupert,

              Yes the data is currently as follows:

              tblDrawings–
              DwgNoID–AN
              DwgNo–Txt

              There are only 4 possible types…M, W, E or P
              The likelihood that the No’s will go beyond 8 digits is rare.

              I don’t have a number for a set or 1 set of numbers per drawing.

              This is an engineering firm and they want to do is track drawings per project. Drawings are assigned a type…the M, W, E or P. The numbers are choosen by simply determining what number is next in line respective to the Type.

              Curretnly, when the engineer names his drawing he must use a log book to see what is next, what I am attempting to do is automate that process.

              The likelihood that the numbering system will go beyond 8 digits will be years down the road.

            • #537489

              I think you could adapt the following idea by Tobi Hoffman. If you had 4 tables instead of Tobi

            • #537491

              BTW, A possible mess!

              You said

            • #537501

              Rupert,

              In the engineering world…there will always only be the 4 types. As for the numbering method, the second you spoke of could be done. As long as everything is next number in line.

              Thanks for your help.

            • #537528

              2cents Just remember, if a record is deleted, the Autonumber cannot be easily re-used. In fact, it’s extremely difficult, and, this can occur quite easily. Once the autonumber is assigned, if for any reason the input is cancelled, the number is no longer available.

            • #537976

              As Brian mentioned there are some issues with auto numbers that I don

            • #538001

              Rupert,

              Thanks for your help. As I stated in the original post, I knew I would need to use the DMAX+1 function, however, this won’t work until you parse the M,W,E or P. If you parse it then apply the DMAX+1 to the number, it works. However you then need a bound text box to return the M, W,E or P & DMAX+1 back together again. If the situation were handled in that manner it you would be right back where you started from …. See what I’m saying.

              I think everyone got off track with the AutoNumber issue. I don’t have any intention of using the AN at all.

              Again, thanks for your help.

              Roberta

    Viewing 0 reply threads
    Reply To: LookUP (Access 2002)

    You can use BBCodes to format your content.
    Your account can't use all available BBCodes, they will be stripped before saving.

    Your information: