Page 1 of 2 12 LastLast
Results 1 to 15 of 21
  1. #1
    Star Lounger
    Join Date
    May 2004
    Posts
    81
    Thanks
    0
    Thanked 0 Times in 0 Posts

    autonumber (Access 2000)

    I have a question regarding autonumbering. I have a form, and on this form are an assortment of combo and text boxes. One of these text boxes is called WRONo and there is a combo box called SystemNumber. I would like to know if a user were to select a system number if that number could then be passed to the WRONo text box and then a number would be added to it. Now the WRONo would not increment only the number added to the end of the WRONo would increment. Any help is very much appreciated.

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

    Re: autonumber (Access 2000)

    I'm not sure I understand, but perhaps something like this:

    Private Sub SystemNyumber_AfterUpdate()
    Me.WRONo = Me.WRONo & Me.SystemNumber
    End Sub

    This appends the selected SystemNumber to the text already present in WRONo. But what if the user selects another number in the combo box?

  3. #3
    Star Lounger
    Join Date
    May 2004
    Posts
    81
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: autonumber (Access 2000)

    Thats exactly the problem. When the user selects the number he/she wants in the combo box SystemNumber, the text box needs to then be filled with the number selected in the combo box SystemNumber plus a four digit autonumber that increments by one each time a new WRO (Work Release Order) is filled out. To make things a little more complicated managment would like the letters WRO in front of the whole thing to. So the fomat would be something like this:

    WRO(SystemNumber)-(Autonumber) WRO10003-0001

    And Again the SystemNumber would be selected in a combo box by a user and then the WRONo text box would need to be filled with the above number in the above configuration. Hans I really appreciate all of your help. If it wasnt for your help I would probally be out of a job. Thank you.

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

    Re: autonumber (Access 2000)

    OK, that was not clear to me from the original post.

    I would store the SystemNumber and the sequence number as two separate fields in the table. The WRONo can then be calculated from it in a query as

    WRONo: "WRO" & Format([SystemNumber], "00000") & "-" & Format([SequenceNumber], "0000")

    (this assumes that SystemNumber and SequenceNumber are both number fields.)

    When the user selects a SystemNumber, the next SequenceNumber is calculated as follows:

    Private Sub SystemNumber_AfterUpdate()
    Me.SequenceNumber = Nz(DMax("SequenceNumber", "tblSomething", "[SystemNumber] = " & Me.SystemNumber), 0) + 1
    End Sub

    Here tblSomething is the name of the table, and SystemNumber and SequenceNumber are the names of the fields.

  5. #5
    Star Lounger
    Join Date
    May 2004
    Posts
    81
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: autonumber (Access 2000)

    Hans,
    Again thank you very much for the help I think I am just about there. I have one more question where does this line of code go:

    WRONo: "WRO" & Format([SystemNumber], "00000") & "-" & Format([SequenceNumber], "0000")

    Does it go in the source for the text box, is it a line in a query and then that query is the source for the text box. I am sorry about my ignorance.

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

    Re: autonumber (Access 2000)

    If the record source of your form is now a table, create a query based on that table and add a calculated column defined as the line you quoted. Use the query as record source instead of the table.
    If the record source is a query already, add a calculated column defined as in the line you quoted.
    In both cases, set the control source of the text box to WRONo.

  7. #7
    Star Lounger
    Join Date
    May 2004
    Posts
    81
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: autonumber (Access 2000)

    Hans,
    I appreciate your help on the autonumber stuff. Everything seems to be working fine .....Except the calculated field in the query that the form is based on is the exact value I wanted. Now is there any way to get that value to display in the text box on the form. Right now when I select my combo box instead of the value being formatted as such : WRO10003-0001 it is just 1(one) and as I add it increments. 1,2,3,etc. Any further assistance is very much appreciated.

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

    Re: autonumber (Access 2000)

    The system number combo box will just display that, not the complete WRONo. You can display that in a separate text box. Or am I missing something?

  9. #9
    Star Lounger
    Join Date
    May 2004
    Posts
    81
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: autonumber (Access 2000)

    Hans,
    What I did was there is a field in tblWRO that is an autonumber field(WROID). There is also a field called WRONO. Now WRONO is a text box on the form plus it is a field in tblWRO. When the combobox SystemNumber is selected I would like the text box not only to diplay the formatted autonumber in the appropriate text box I would also like it stored in the appropriate field in the table. Right now the formatted autonumber shows up in the query but not in tblWRO in the WRONO field. So in summary, I not only need to display the number in the appropriate text box ( this is so when they(the user/supervisors) hit the print icon that the formatted WRONO shows up on the printed form) I also need it stored in the appropriate field in the table(tblWRO). I know this is somewhat confusing and I appreciate all of your assistance.

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

    Re: autonumber (Access 2000)

    Why do you need to store the formatted "number" in the table? It is redundant information, since it can be reconstructed from the system number and the sequence number.

  11. #11
    Star Lounger
    Join Date
    May 2004
    Posts
    81
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: autonumber (Access 2000)

    Hans,
    You are very correct I guess that makes no sense since it is already stored. But how about displaying it in the text box(txtWRONO). Right now what happens is a value is displayed is just not the formatted "number".

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

    Re: autonumber (Access 2000)

    From <post#=468019>post 468019</post#>:
    <hr>If the record source of your form is now a table, create a query based on that table and add a calculated column defined as the line you quoted. Use the query as record source instead of the table.
    If the record source is a query already, add a calculated column defined as in the line you quoted.
    In both cases, set the control source of the text box to WRONo.<hr>
    The expression referred to is from <post#=468014>post 468014</post#>:
    <hr>WRONo: "WRO" & Format([SystemNumber], "00000") & "-" & Format([SequenceNumber], "0000")<hr>
    The text box whose control source should be set to WRONo is txtWRONo.

  13. #13
    Star Lounger
    Join Date
    May 2004
    Posts
    81
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: autonumber (Access 2000)

    Thank you Hans,
    I find that my crack use sometimes hinders me from using my brain. I appreciate you humoring me. Next time I will read what you write. Everything is working EXCELENTLY <img src=/S/doh.gif border=0 alt=doh width=15 height=15>

  14. #14
    Star Lounger
    Join Date
    May 2004
    Posts
    81
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: autonumber (Access 2000)

    Mr. V,
    I am sorry I guess I spoke to early. Everything is doing what I would like except that when I select a value from the combo box I get an error message: Run-Time Error '-2147352567(80020009)' Field 'WRO' is based on an expression and cannot be edited. Can you tell me what is causing this.

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

    Re: autonumber (Access 2000)

    What is the code you currently have in the After Update event of the combo box?

Page 1 of 2 12 LastLast

Posting Permissions

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