Results 1 to 8 of 8
  1. #1
    Join Date
    Oct 2004
    Posts
    75

    Unanswered: generating special number for new record

    hey all, hopefully someone can help me...i have a form on which I'd like access to generate a new invoice number for each new record and put it in the right field. Now, not only do I not know how to get this number to appear in the field for a new record (since it changes, I can't use default value), but the number itself is tricky. It's in the form xxxx/y-zzzz, where xxxx is the financial year (eg 0405, 0506), y is the quarter of the year (1,2,3 or 4) and z is a 4-digit sequential number which is reset at the beginning of each financial year. Is there any way to generate this using some kind of date function? And if not, then can someone at least tell me how to fill a text box on a form with the results of an SQL Max() statement (when the rest of the form is based on a different query)?
    Oh, and while I'm here and on a roll, does anyone know how to open a Word document that has the same filename as the value of a field on a form?
    Thanks for all help

  2. #2
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    bizarre keys are always fun... There is no automated way to do it, at all.

    You know what might work better for you would be to store the key as a date and a number. All of your prefix information can be derived from a single date attached to the record. You're still going to require some odd code to check the next int for a given fiscal year.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  3. #3
    Join Date
    Nov 2004
    Posts
    34
    Ok, personally I think it is possible with vba. As I understand it, is the xxxx part the month (12) and then the year (04) so it would be 1204 for this month? If you have a string called say InvoiceNum then use the following code: InvoiceNum = format(date, "mmyy"). This will create the first part.

    The y part is a bit more tricky. You would need to create a second variable, Mydate. Then set myDate to todays date and using the format function, format the date so that it only returns the month part, the go through a series of IF statements (or you could you the SELECT CASE statement) to see whether myDate was between 1-3, if true then myDate = 1, etc.

    The last part: If the sequential number is stored somewhere then jus reference this place and append it to the end of the InvoiceNum string.

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Quote Originally Posted by jammybasturd
    Ok, personally I think it is possible with vba.
    It can be done. Ihave doen somehting similar with a function to mask order numbers

    You will have to create a function in VBA which is called when the user adds a new record in the forms "before insert" (or possibly) "after insert" event

    You may need to create a companion fucntion to decode you invoice number.

  5. #5
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Quote Originally Posted by jammybasturd
    Ok, personally I think it is possible with vba.
    I should have been more clear. What I meant is there is no way to automate this type of value generation at the table level, and he will require some code.

    I still think it should be an arbitrary key, then a date, then the numeric portion of the key. As a general rule of thumb, it's easier to combine/encode strings then it is to decode/parse.

    I'm curious to see what other options you folks come up with though, never cease to amaze me.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  6. #6
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    There is no such method of attaching a procedure within a ACCESS/JET database. It is possible on SQL Server and soem other server databases. If you wnat to do this then you have got to get your hands dirty with VBA

  7. #7
    Join Date
    Oct 2004
    Posts
    75
    thanks for your help guys....I'm tryin to get something going in vba but so far it's not happening. As far as the first part goes, first of all, I mustn't have been clear before jammybasturd, this part is just the financial year, so for eg, until July next year the first part will be 0405. I've got this working in vb, but it requires having an IF statement for each year I want this to work for, not very good coding. I cant just get it to add 1 to the year returned since it's a string...any suggestions?
    The second part is working fine, thanx jammy
    The third part is proving to be a real pain. Obviously I have to check the most recent number used and add 1 to it, but how to do it? At the moment, the entire invnumber is one field in a table, but maybe Ill have to split it up as Teddy said, to avoid having to break up the string to get the last 4 digits (and so the number will be an integer that I can add 1 to). But even if I do that, how do I return that most-recently-used number to add 1 to it in vb??? Only thing I could think of is an SQl Max() statement, but two problems there..a) I can't seem to get the RunSQL command to work and b) the Max() wouldn't work anyway, since this number repeats itself every financial year, and might be higher for a preceding year
    *sigh*

  8. #8
    Join Date
    Jul 2003
    Location
    Michigan
    Posts
    1,941
    Maybe in this case, it's not a good idea to use your Invoice number as a primary key. I would add an auto index field to my table and alway search for the max id and get the corresponding invoice number. The RunSql is not
    the right command to use for a select query. Using a recordset will work,
    like this (I'm assuming your data resides in Access):

    Dim strSql as string, rstInvoice as DAO.Recordset
    Dim strLastInv as string, strNewInv as string
    strSql = "Select Max(AUTOID), InvNum from [yourtable] group by InvNum"
    Set rstInvoice = currentdb.openrecordset (strsql)
    strLastInv = rstInvoice!InvNum
    strNewInv = left(strLastInv,7) & CStr(Cint(right(strlastinv,4))+1)

    Of course, you'll have to code around year changes, quarter changes, and what if you have a banner year and generate more than 9999 invoices?
    Inspiration Through Fermentation

Posting Permissions

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