Results 1 to 7 of 7
  1. #1
    Join Date
    Jun 2006
    Posts
    12

    Unanswered: Control-based Autonumber

    Is it possible to set up an autonumber control on a form to be based on a different control in the same form? Here's my thinking:

    For each invoice/ticket I enter, it's shipped to a Jobsite. So I have a control combo box where I can select the JobsiteID (after which the Jobsite Name control automatically populates), then the autonumber TicketID populates the next consecutive number. I would like to change this to when I select the Jobsite ID, the TicketID number populates based on what number ticket for that job is next. For example....Jobsite ID(similar to a Customer ID) 527 is selected from it's control. This is the first time this ID has been selected because this is the first Ticket we're going to enter for this Jobsite. So after I select the Jobsite ID, the Ticket ID control would populate "001". Now, after entering the info for that Ticket, I want to enter a new Ticket for a different Jobsite. So when I select a new record and enter my Jobsite ID 538, this would be the 25th ticket for this Jobsite, so the Ticket ID would say "025".

    Is this possible?

  2. #2
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Yes. It won't be an autonumber tho ... You're presenting this as a string ... (tho you could format and convert) ... You could setup a "Next Ticket" scheme where you pull the next sequentail # for a given job ...
    Back to Access ... ADO is not the way to go for speed ...

  3. #3
    Join Date
    Jun 2006
    Posts
    12
    Ok, that sounds like a plan. Is there a sample code for this process?

  4. #4
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Quote Originally Posted by Grassy7
    Ok, that sounds like a plan. Is there a sample code for this process?
    I've posted on this in the past ... Don't know if that code is still here in the forum or not ... Jist is:

    Make a table with an ID of some sort ...

    Then write routine to query this ID and then add 1 (or any other increment) to it and update the table ... Repeat as necessary plucking new IDs ...

    In your case, I'd mod the Job table you have to add another column for your TicketID and perform the same task ...
    Back to Access ... ADO is not the way to go for speed ...

  5. #5
    Join Date
    Jun 2006
    Posts
    12
    I'm back again. I've looking all over the internet for help on this and no one seems to be able to understand me. Someone mentioned using the DMax function in this situation.

    All I want to know is if there is a way to setup a TicketID control on my Orders Form to count the number of Tickets entered for a particular JobsiteID (similar to a CustomerID situation). So if I select a JobsiteID and it's the first time I'm entering information, the TicketID will "001". Then I'll start a new record, enter another JobsiteID, and it's the 3 ticket entered, so the TicketID would say "003"? Surely this is possible. If someone knows what I'm talking about and knows what they're doing, please reply with simple, easy-to-understand language. Thanks!

  6. #6
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    And my previous post wasn't helpful???? It explains just exactly what you're asking for ... No code but, explains the process. How do you want to do this BTW? DAO, ADO, Access crapola?
    Back to Access ... ADO is not the way to go for speed ...

  7. #7
    Join Date
    Jun 2006
    Posts
    12
    M Owen, to be honest it wasn't helpful because I don't understand what you're telling me to do. I'm an intermediate with this. Now, your first post was on track where you said:

    "You could setup a "Next Ticket" scheme where you pull the next sequentail # for a given job ..."

    That's what I'm looking for. I want the TicketID to increment only when I select the ShipToJobsiteID, and increment specifically for whatever JobsiteID I select. I'm clueless on where to even start. I'm still in the developing phase of my database. Someone on another forum mentioned the DMax function. The setup with that was to have my TicketID control unbound on my form, and place a DMax code in the AfterUpdate property of the ShipToJobsiteID control. That way, the TicketID control only populates when I select that JobsiteID. He also said to setup my Format property of the TicketID control to "000", which is what I want.

    Now, from there I'm clueless. The code I entered in the AfterUpdate property didn't work. And I'm not sure that if it did, it would increment based on the specific JobsiteID that is selected. Thanks for any help!

Posting Permissions

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