Results 1 to 7 of 7
  1. #1
    Join Date
    May 2006
    Posts
    11

    Unanswered: help! year increment in ms access

    i am tryin to create a db which generates an asset number
    for each record that i input.
    for each input year, it should have its own number of records. if i input
    2004 as the year, it should generate a code of 001 for that record. i do this
    3 more times, giving me 4 records. now if i input a different year, like 2005,
    it should generate a new code, 001, assuming no 2005 records have been
    inputted yet. and if i input 2004 as the year again, the code should be 005,
    because i have 4 other existing records.

    attached is the sample file i have been working on.
    Attached Files Attached Files

  2. #2
    Join Date
    Nov 2003
    Posts
    1,487
    To do this sort of thing is relatively simple but what tends to make things a little more complicated is the simple fact that the Purchase Date (which is where the Year for the Asset Tag Number is generated from) can be modified and therefore the Asset Tag Number must be capable of updating as well. Then what if there is already an item within the Assets Management table that contains the modified Asset Tag Number. We need to have the option to index further to generate a unique Tag Number.

    I have modified your sample DB (with addition of code) to basicly cover these issues. See the code contained within the OnExit event for the Purchase Date field in Form. It may look a little complex but really it isn't. It just has a lot of comments which steps you through the code.

    I have emptied the Assets Management table so that you can see the new system take effect.

    Hope this helps somewhat....

    .
    Attached Files Attached Files
    Environment:
    Self Taught In ALL Environments.....And It Shows!


  3. #3
    Join Date
    May 2006
    Posts
    11

    sir can you help me with this one

    we also want to count the number of assembly's that was chosen by the user just like your example(that you have attached in your reply counting the years)
    and we want it to appear in the assembly code 200419201001.please help us.
    together with this is the file i am currently working on and what we need is for it to count the assemblies.thanks.hope for your reply asap thank you very much for the help and the time.
    Attached Files Attached Files

  4. #4
    Join Date
    Nov 2003
    Posts
    1,487
    In your post you indicate that you would like to have what you call an Assembly code to be indexed as well along with the indexing of the purchase date.

    What exactly is the assembly code and what is providing it?
    Take what you might percieve to be a complete Asset Tag Number and break down the the number so as to explain to me exactly what each component of the number is to be.

    .
    Environment:
    Self Taught In ALL Environments.....And It Shows!


  5. #5
    Join Date
    May 2006
    Posts
    11

    asset tag number

    200319201001

    the 2003 is the year the asset was purchased, 1 is the asset type code, 92 is the hardware code, 01 is the purchased type code, the 001 is the one who counts the years when the asset was purchased.

    now if the user chooses single purchased in the asset type theres no problem the assembly code will be just "00" and it wont change. So every asset that was purchased as single will have the asset number like xxxxxxx00xxx

    the problem comes when the user chooses the assembly as the purchased type.
    We need to count how many assets are purchased as assembly.

    For example i choose assembly for the first time, the asset tag number to be generated must be like this xxxxxxx01xxx
    and if next time i choose assembly again the asset tag number should be like this xxxxxxx02xxx and if next time i choose assembly again the assembly code should be "03" and must generate a asset tag number like this:
    xxxxxxx03xxx

  6. #6
    Join Date
    Nov 2003
    Posts
    1,487
    I see....allright then...use the same method you used for the previous sample. I see you utilize the table field named Sequential for indexing and this is fine. I simply provided a means (in the previous sample) to do this with code rather than using a table field. In essence, the field could be deleted.

    Never the less...simply use the same method you used for single purchases for the Assembly purchases as well. Perhaps drop in another table field and name it AssemblySequence.

    Another thing...It's really none of my business but I noticed that you are providing ID's to the different assests through code rather than providing this data within the Table along with the asset name. Actually, you do this for all items in all comboboxes. Why? Put these ID's with their respective components in the table. Modify the SQL statement in the RowSource property for your ComboBoxes to included these ID's from table then bump up the ColumnCount property for each combo to 2. Then to access the ID for each item use something like:

    Me.myCombo.Column(1)

    Column(0) holds the value of the first item in the record and Column(1) holds the value of the second item in the record.

    By doing this you can get rid of all the hard coded ID's.

    .
    Environment:
    Self Taught In ALL Environments.....And It Shows!


  7. #7
    Join Date
    May 2006
    Posts
    11

    Thanks!

    thanks sir for the very big help.I have solved my problem.again thanks for the time and for the 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
  •