Page 1 of 2 12 LastLast
Results 1 to 15 of 26
  1. #1
    Squirrel_44 is offline Novice
    Windows 10 Access 2016
    Join Date
    Apr 2019
    Posts
    10

    VBA Coding to change field from generated to manual depending on criteria

    Hi,
    I am not great with the coding side of things with Access so I need a bit of help. I hope I can explain it properly.

    I have a field that is made up of a Letter Prefix and then a Number Suffix (ie. AA-01). The Letter Prefix is a drop down list and then the Number Suffix is generated by looking at the Products table and adding 1 to the last number given to that Letter prefix. ie so the next number would be generated AA-02 etc.

    The below code works well for this - it's located in the form in the Batch ID field. The problem to follow the code.

    Private Sub BatchID_BeforeUpdate(Cancel As Integer)
    If Not Me.NewRecord Then
    Cancel = True
    Else
    Dim prev As Variant
    prev = DMax("[SequenceNumber]", "Products", "[BatchID] = " & Me!BatchID)
    If IsNull(prev) Then prev = 0
    Me!SequenceNumber = prev + 1
    End If
    End Sub

    PROBLEM:


    After the Prefix gets to a certain Lettering (ie. AQ) I require the Number suffix to be added in manually and not automatically generated by the above code but I cant figure out how to do it. Can anyone help?

    The other idea I had was to create a second table for the Letter AQ onwards without the above code so the Number suffix can just be added in manually...however I need to keep a consistent Report Number over the whole database. And if I have 2 tables for the different Letter Prefix, I dont know how to keep the Report Number sequential between the 2 tables.

    I hope I've explained my dilemma well enough and that someone can help me.

    Thanks heaps

  2. #2
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    It appears that you are creating your own custom codification scheme. Access (and other relational databases) work very well with atomic data --1 fact 1 field. Consider using separate fields for your SequenceNumber and BatchID. You can always query on these individually or in combination if/when needed.
    Good luck.

  3. #3
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    My must be entered manual after AQ? How many characters could this batch ID allow? Keep in mind that "AAA" will sort before "ZZ". And if the sequence number exceeds 99, similar issue - "101" will sort before "20".
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  4. #4
    Squirrel_44 is offline Novice
    Windows 10 Access 2016
    Join Date
    Apr 2019
    Posts
    10
    Hi Orange,
    I am not sure what you mean by using separate fields for the SequenceNumber and BatchID. Can you please explain this to me?
    I am really stuck with this and I need a solution asap as my boss is breathing down my neck.
    Cheers

  5. #5
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    It seemed to me that you are concocting a scheme with at least 2 parts making up the values where each part has a separate meaning. What exactly is AA vs AQ? What is the significance of the 1...99?
    An overview of the "business involved" could be helpful.

    Perhaps 2 separate fields for storage (BatchID, BatchSeqNo) and a concatenation for display?

  6. #6
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    If AQ is a drop down list, then code so that you check if AQ is chosen. If so, provide message/alternate means of inputting number. Maybe I don't understand the problem because the idea of adding a table to deal with the issue seems strange. What could you do with 2 tables that you can't do with one?
    I agree with other comments about splitting the values and how things will sort alphabetically (if it matters) without using some kind of sorting scheme.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  7. #7
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    Generating custom unique identifier is a common topic. There is also option of not even saving the sequence number to table. This sequence can be calculated on report with textbox RunningSum property.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  8. #8
    Squirrel_44 is offline Novice
    Windows 10 Access 2016
    Join Date
    Apr 2019
    Posts
    10
    A bit of background.
    We have an item we manufacture. When we first started making this item 5 years ago we simply batch tested them (meaning a qty of 100 all got stamped with AA, AB etc as the identifier), however over time the process has been updated that all of these items must have a specific identifier so we went with the letters and then a number ie. AQ-001 through to AQ-100, then AR-001 through to AR-100 etc.) which is all good until these items come in for Overhaul.
    The database was set up that the user chooses the Batch number "AA" etc and then the database creates the specific identifier "001" which is what my code does very well. However, now that we've reached the point where we are Overhauling AQ which already has the specific identifier the user needs to be able to add this in themselves as AQ-098 might come in for overhaul before AQ-002 but naturally my above code generates a number for me which is the issue after we reach AQ.
    I hope this explains things a bit better.

  9. #9
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Please tell us what is involved in Overhaul. So what if AQ-098 comes in before AQ-002?

    I'm not following this:
    However, now that we've reached the point where we are Overhauling AQ which already has the specific identifier the user needs to be able to add this in themselves as AQ-098 might come in for overhaul before AQ-002 but naturally my above code generates a number for me which is the issue after we reach AQ.

    In overview, you have Items that could be identified by ItemNumber 1 to 99999999....
    Do you do something now with batches of 100? I must be missing something...

  10. #10
    Squirrel_44 is offline Novice
    Windows 10 Access 2016
    Join Date
    Apr 2019
    Posts
    10
    The identifying (Serial) number of the item is "XX-000". The Item is only identified by this number as they are all the same item/item number.

    Lettering AA through to AP have an automatically generated number following it, using the code below.
    Any lettering after that, being AQ onwards will already have a number stamped on the item, therefore doesn't need an automatically generated number.
    When the form is first opened the first thing the user has to do it choose the Batch (Lettering) from a dropdown list.
    I need it so that if the Lettering is AP or before a number is automatically generated, but if it's AQ or after then the user has to manually enter in the number listed on the item itself.
    So in the form, on the BatchID ComboBox, on Before Update i have the code:


    Private Sub ComboBatchID_BeforeUpdate(Cancel As Integer)
    If Not Me.NewRecord Then
    Cancel = True
    Else
    Dim prev As Variant
    prev = DMax("[SequenceNumber]", "Products", "[BatchID] = " & Me!BatchID)
    If IsNull(prev) Then prev = 0
    Me!SequenceNumber = prev + 1
    End If

    End Sub


    I want to know if there is something I can add in to this code to look at the Lettering and then depending on that either generate a number or not.
    I have no other idea how to explain this, sorry.

  11. #11
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    I want to know if there is something I can add in to this code to look at the Lettering and then depending on that either generate a number or not.
    I take it that AQ items and perhaps beyond are historical but you haven't yet reached (for example) AN. Thus no auto numbering for anything beyond AP. That means no BA, BB or CA, CB, DA, DB etc.

    Then the first sentence of post 6 should be the solution, no?
    Or is it that you need to be able to manually enter a number for an AQ item that is coming around again, whatever that means, but still allow some new number to be associated with an AQ item.
    Or do no numbering for just an AQ item but allow for anything beyond AP.
    Or do no numbering for ANY letter combination beyond AP.
    Or....

    I suspect you're looking for someone to write it for you (not a problem) but you have to be very black and white in describing the parameters. I've raised a few questions that hopefully indicate you haven't expressed the exact need yet. The worst thing you can do is present a moving target to us (where the expressed need or situation keeps getting modified). I don't think what you need will be difficult to do, but I still don't know exactly what that is.

  12. #12
    Squirrel_44 is offline Novice
    Windows 10 Access 2016
    Join Date
    Apr 2019
    Posts
    10
    We've gone far beyond AN with the manufacture, infact we're just about to enter BA, however when it comes to overhauling these items (meaning we get these items come back into our workshop to be remeasured and checked that they still comply with certain legal standards etc before going back to the Client) the order that they come back for overhaul is completely haphazard depending on the Client and their needs.

    So there is no autonumbering beyond AP. I need the user to be able to manually enter the number themselves.

    Yes looking back I believe that post 6 should be the solution but the means in doing this alludes me.

    I've tried to upload the database to hopefully make things clearer but for some reason it wont work

  13. #13
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    I admit to being thick headed at times. This must be one of them.
    So you are still manufacturing and are well beyond AQ yet "there is no autonumbering beyond AP". I get the stuff about how an item with any prefix can show up at the door for inspection, but can't wrap my head around how everything you've explained relates to "I want to know if there is something I can add in to this code to look at the Lettering and then depending on that either generate a number or not."
    The dependencies are not clear. As for attaching a file see if this helps
    https://www.accessforums.net/showthread.php?t=70301
    I'm not sure it will help us (OK, me) without clearly understanding the dependency you speak of.

    At this point, it seems like you need two different streams; one for adding new items, thus employing the autonumber thing, and one for not numbering at all since the incoming item already has a complete text-number associated with it.

  14. #14
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    I'm with Micron here --not following the requirement. You have a bunch of Items that you have identified eg XX-001.
    The Item goes to a Customer and, after some period of use, is returned for overhaul. Why would you renumber/re-identify this Item with a new number? Couldn't/wouldn't lastOverHaulDate satisfy the need? If that is a misunderstanding on my part, then please describe the "business processes" in simple terms. Seems at least 2 responders are not following your posts/intent.

  15. #15
    Squirrel_44 is offline Novice
    Windows 10 Access 2016
    Join Date
    Apr 2019
    Posts
    10
    Quote Originally Posted by orange View Post
    Why would you renumber/re-identify this Item with a new number?
    I'll try to explain as best I can.

    Our business has to comply with Standards set out by Governing Bodies. Five years ago these standards were less specific than they are now. In 2014 all we had to do was batch test our item, so in a batch of 100 only 25% was tested and then a batch report was supplied to the Clients coinciding with the batch number (AA, AB etc) stamped on that item.

    These days batch numbering is not accepted so everything is 100% checked. By the law of these standards we had to keep the lettering (AA, AB etc) so we simply added "-001, etc" to give the item a Serial Number.

    Any item manufactured before last year only had the lettering batch number. Everything after last year is lettering and then 3 numbers (XX-001) to comply with these standards.

    So if an item manufactured before last year comes in for overhaul it will only be stamped with the lettering batch number, and therefore needs to be given 3 numbers to follow it to create the Serial Number as is now required. My code does this with no issue by looking at the batch number selected and grabs the next available number (e.g. -005).

    If an item manufactured after last year comes in for overhaul then it will already be stamped with a serial number (XX-001). If one of these items comes in I need for the user to be able to add the Serial Number in manually, instead of having it automatically generated. So I need the code to look at the batch number and say "if Batch AQ or later is chosen then you need to manually put in the numbers instead of the code generating it".

    I've tried to upload a simplified version of my database but it appears I cannot because it's over the 500KB limit....which is crazy cause I cant seem to get the database below 4.24MB no matter what I do. Is there some other way I can share the database with you cause I think that might help clear things up.

    Thanks heaps for all the time and effort you've all gone to to help me out with this, I really appreciate it and I am sorry if I have not been clear in explaining things...it's not a strong suit of mine LOL.

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 5
    Last Post: 08-14-2017, 02:19 AM
  2. Reports - Manual Filter/Changing Criteria
    By DavidQMP in forum Access
    Replies: 2
    Last Post: 07-01-2016, 08:43 AM
  3. Replies: 10
    Last Post: 10-07-2015, 05:31 PM
  4. Replies: 1
    Last Post: 11-18-2014, 11:19 AM
  5. Replies: 7
    Last Post: 12-10-2013, 11:30 PM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums