Results 1 to 13 of 13
  1. #1
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Louisville, Kentucky, USA
    Posts
    605
    Thanks
    0
    Thanked 0 Times in 0 Posts

    LookUP (Access 2002)

    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,
    Roberta Price <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

  2. #2
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Newbury, Berkshire, England
    Posts
    712
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: LookUP (Access 2002)

    I don

  3. #3
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Louisville, Kentucky, USA
    Posts
    605
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: LookUP (Access 2002)

    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.
    Roberta Price <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

  4. #4
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Newbury, Berkshire, England
    Posts
    712
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: LookUP (Access 2002)

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

    If that

  5. #5
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Louisville, Kentucky, USA
    Posts
    605
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: LookUP (Access 2002)

    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.
    Roberta Price <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

  6. #6
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Newbury, Berkshire, England
    Posts
    712
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: LookUP (Access 2002)

    I see, well it

  7. #7
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Louisville, Kentucky, USA
    Posts
    605
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: LookUP (Access 2002)

    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.
    Roberta Price <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

  8. #8
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Newbury, Berkshire, England
    Posts
    712
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: LookUP (Access 2002)

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

  9. #9
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Newbury, Berkshire, England
    Posts
    712
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: LookUP (Access 2002)

    BTW, A possible mess!

    You said

  10. #10
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Louisville, Kentucky, USA
    Posts
    605
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: LookUP (Access 2002)

    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.
    Roberta Price <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

  11. #11
    4 Star Lounger
    Join Date
    Dec 2000
    Location
    London, Ontario, Canada
    Posts
    437
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: LookUP (Access 2002)

    <img src=/S/2cents.gif border=0 alt=2cents width=15 height=15> 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.

  12. #12
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Newbury, Berkshire, England
    Posts
    712
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: LookUP (Access 2002)

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

  13. #13
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Louisville, Kentucky, USA
    Posts
    605
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: LookUP (Access 2002)

    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
    Roberta Price <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

Posting Permissions

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