Page 1 of 2 12 LastLast
Results 1 to 15 of 26
  1. #1
    Join Date
    Jul 2015
    Posts
    51

    Exclamation Unanswered: Auto Increment a number field based on previous INDEXED record

    (information fill in:
    I have at table that sorts bus passes by year/month, pass type, and pass number. The infomation repeats every month so there are no unique values, the table is just indexed not to repeat information such as: 2020; march; PasstypeA; Pass#500 This will not be repeated but there can be another PasstypeA; Pass#500 for the next month.)

    I am trying to have my form auto increment the pass# from the previous INDEXED record. meaning I want the increment to come from a record with the same values as the one im entering.


    EX:

    2020; march; PasstypeA; Pass#500 next record with same year/date;passtypeA, would give me Pass#501

    Any ideas

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Store the date components as a single item (id suggest using yhe start of the month)
    Store the passtype as a single character (A, B .....)
    Store the pass number as a number (500,501,502....)
    Why?
    ..saves space
    ...removes redundancy
    ...removes possible complications induced through spelling / typing mistakes
    ...maintains sort order irrespective of yhe number of pass numbers
    ...but perhaps most important of all it allows you to use the domain function DMAX

    Use a function to return the next sequence number. Decide if you need record locking if say its a multi user environment and there is a risk that two or more people may try to insert a row at the same time. Personally I'd ALWAYS impkement sone form of handler to do this just because you never know if that sityation may occur in the future.
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Jul 2015
    Posts
    51
    I already have each of these as a different field.(year/month, pass type, and pass number) I am using the DMax function.
    The problem is it is returning the highest number in the pass# field regardless of the pass type i'm entering.
    If I have a passtypeA and the last number was 499. and i want to enter in pass type B (the last number being 200) I want the next number to be 201, not 500. I want the increment to come from a record with the same values as the one i'm entering.

    the way you suggest gives me the same problem i'm already having, making pass type B come up as 500 not 201

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    No the way I'm suggesting works. What code are you using for the dmax call?

    bear in mind a domain function is essentially a SQL statement reformatted. The third parameter is effectively a where clause and that be as comp,icated as required.

    I would expect its going to be something like
    Code:
    'passtype = "A" and ass_year = 2015 and ass_month = "may"'
    I'd rather be riding on the Tiger 800 or the Norton

  5. #5
    Join Date
    Jul 2015
    Posts
    51
    In my defualt value I currently have
    DMax("[FieldName]","[TableName]")+1

    But this is giving me a pass number regardless of the pass type and month/year, how do I write this to set the increment to come from a record with the same values as the one i'm entering?

  6. #6
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    set the third parameter (effectively the where clause) as suggested in post #4 that limits the dmax to the required values
    so thats the date and pass type

    ..you need to handle a NULL value which DMAX returns if it cannot find a match for the specified parameters. IE the first number to use.
    I'd rather be riding on the Tiger 800 or the Norton

  7. #7
    Join Date
    Jul 2015
    Posts
    51
    I don't understand how to write this out? (I'm not a coder)

    Where do I set my parameters in here?

    DMax("[FieldName]","[TableName]")+1

  8. #8
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Code:
    dim myvalue as variant
    myvalue = dmax("mycolumn", "mytable", "myparameters") then
    if isnull(myvalue) then 'we didnt' fidn a match
      'intial value is...
      myvalue = 500
    else 'we did fidn a match, so add one tot he value returned
      myvalue = myvalue + 1
    endif
    mycolumn is the name of the column that holds the value you want
    mytable is the name fo the table (or query or view) that holds the values you want to search through
    myparameters are the limiting factors, the filter if you prefer


    so I don't know your column names, frankly I can't be bothered to reads through your post to find your equivalent of mycolumn, mytable as you dont' seem to b e btohered to make an effort to learn

    refrrrign back to post #4

    'passtype = "A" and ass_year = 2015 and ass_month = "may"'
    repalce the passtype, ass_year and ass_month with wahtever the columns in your table are called. I don't knwo what they are so you wuill have to do it.
    replace the values to the RHS of the equals sign with whatever values are approrpiate for the current search, again I don't know what they are as its your application
    if the value is a number it doenst' need delimiting
    if its a string delimit with pairs of either ' or ", eg mytextcolumn = "atextliteral"
    if its a date format the date as either ISO (yyyy/mm/dd) or US (mm/dd/yyyy) delimit with #
    I'd rather be riding on the Tiger 800 or the Norton

  9. #9
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Quote Originally Posted by andromeda00 View Post
    I don't understand how to write this out? (I'm not a coder)

    Where do I set my parameters in here?

    DMax("[FieldName]","[TableName]")+1
    there is a reason why I, and other contributors here, supply links to associated articles. even if we don't then there is a help system, which although quirky works and of course there is the ever present search engines.

    on top of which post #6 starts by saying
    set the third parameter
    so in:-
    Code:
    DMax("[FieldName]","[TableName]")+1
    DMAX is a function, it has parameters (values passed to the function)
    the first parameter is "[FieldName]"
    the second parameter is "[TableName]"
    so where do you think the third parameter will go?

    Its been a long day, and I apologise if this seems too tetchy but please make an effort to understand waht peopel are suggesting to you before throwing your hands up in horror
    I'd rather be riding on the Tiger 800 or the Norton

  10. #10
    Join Date
    Jul 2015
    Posts
    51
    I appreciate your patience with my question. I am not a coder so sometimes this goes over my head.
    I do understand the basics of structuring this, although i am still learning all the operators and etc. I was just not sure how to write out the third parameter to include multiple fields.



    So do I have to change the values in the code everytime I enter a record with different information?
    Does this code only sets the increment for a record with one specific set information?
    When there are 12 months in a year and multiple pass types, I hope I wouldn't have to change the parameters for each record I want to enter.

  11. #11
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Im struggling to find another way of replying to your question, other than reread post #4 and post#8. short of knowing your column names I don't think its possible to restate what the third parameter would be.
    'passtype = "A" and ass_year = 2015 and ass_month = "may"'

    as an example lets assume you wanted to find the largest tcket sales on a particualr bus route on the 29th of July 2015
    and lets assume the table is called route_revenue
    the sales value is in a column called sales_value
    the bus route is bus_route and is alphanumeric and the specified rotue is 'R12'
    the date is sales_day and the current day
    then the third parameter coudl be
    '"Sales_Value","Route_Revenue","bus_route = "R12" and sales_day = #07/29/2015#'

    now if say you wanted to pull the route in from, say a combo /list box called say, cmbroutes then that changes to
    '"Sales_Value","Route_Revenue","bus_route = "' & cmbRoutes & '" and sales_day = #07/29/2015#'

    now if you wanted to use a datepicker, called say mydatepicker to specify the day
    '"Sales_Value","Route_Revenue","bus_route = "' & cmbRoutes & '" and sales_day = ' & mydatepicker

    I don't know your column & table names, and besides which Im not really in the business of "gimme's", you need to do some spadework yourself, if for no other reason it makes you think about the problem and hopefully makes you think about how to use the help system, how to search on google. how to make you more self sufficient at developing ways of solving these sort of problems.
    I'd rather be riding on the Tiger 800 or the Norton

  12. #12
    Join Date
    Jul 2015
    Posts
    51
    well all aside, thank you your patience.

    I don't think this works the way i was hoping for. Its only working if each field record in my table are the same as set in the code criteria, which is great, but if i choose a different pass type it doesnt auto increment.

    Thank you for your help.

  13. #13
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    ?
    Yes it can do what you want, but you need to explicitly tell the function what the items in the filter are. Its how you have defined your dmax call
    its where or when you issue that call.
    the problem isnt with the dmax function its how you are using it.

    If you are really, really struggling with this then consider posting your db as an attachment in a zip file. But only postt what is required ( the relevant data, form and code) remove or obfuscate any sensitive or personal data. This is a public forum.
    I'd rather be riding on the Tiger 800 or the Norton

  14. #14
    Join Date
    Jul 2015
    Posts
    51
    databasetester.zip

    Here is the simplest version.... i tried adding the code again and it keeps giving me errors.....

    I appreciate you helping me any way you can.

    I would like this code to go in the default value so that the record can be changed if needed. So your saying this will work no matter what type of year/ month/ passtype im typing in?

  15. #15
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    so where s the code you are running
    what form are you running this from

    what code are you actually using
    what error message are you getting

    ..incidentally you are using a reserved symnbol (#) in a column name which although Access will handle it (usually) its a recipie for disaster

    https://support.office.com/en-au/art...__toc272229039
    I'd rather be riding on the Tiger 800 or the Norton

Posting Permissions

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