Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    d9pierce1 is offline Expert
    Windows 10 Access 2016
    Join Date
    Jan 2012
    Location
    Oklahoma
    Posts
    754

    Assistance on an incremental number on form?

    Hi all,
    I have attached sample db. I (with help) have figured out the Form1 sample of obtaining the Project Number. What i cant get figured out is:

    Form 2 getting the ProjectNumber to be +1 Depending on the ProjectName.

    If I click on RFI button on Form1 then it will open Form2 to the RFI Sample.


    I have evrything on that page needed to do this i believe but cannot get it to make an RFI # such as 02-02341-001, 02-02341-002 and so on depending on the Project Name?
    Then start the 001, 002, and so on with a different Project Name.

    Any Ideas here, have been working on this all morning long and cant get anything i have tried to work on this.
    Thanks
    Dave
    Database4.zip

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Generating unique custom identifier is a common topic.

    One approach is to generate this sequence dynamically 'on-the-fly' in report design using Grouping & Sorting and textbox RunningSum property.

    Otherwise, review https://www.accessforums.net/showthread.php?t=23329
    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.

  3. #3
    d9pierce1 is offline Expert
    Windows 10 Access 2016
    Join Date
    Jan 2012
    Location
    Oklahoma
    Posts
    754
    Hi June7
    I reviewed your suggestions and i came up with this but not sure how to use it? Or if its actually correct, I didnt get any errors but didnt get a number as desired either?

    Code:
    Public Function NewRFINum() As String
    Dim PN As String
    Dim RFINum As String
    Dim getnextRFI As String
    
    PN = Me.TxtNumber
    RFINum = (DMax("ProjectID", "RFITbl"))
    
    
    If getnextRFI = Format("PN" & "-" & (RFINum), "000") Then
    RFINum = RFINummber + 1
    Else
    RFINum = "001"
    getnextRFI = Format("PN" & "-" & (RFINum), "000")
    
    
    End If
    End Function
    Any Suggestions where i went wrong? I just put code on form as a function. Much more complicated then expected for sure....
    Thanks
    Dave

  4. #4
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Did you step through this code and watch what happens to your variable values? Has stepping through your code not ever been suggested to you before, because from here, it seems that the problem should be obvious if you had done that? I could point you at what I think is the problem, but unless you learn how to step through and evaluate conditions and variables, you will repeatedly waste more time waiting for an answer than you need to. In case the topic of stepping through code is foreign to you:
    - you put a break on some point in your code (often the first line)
    - depending on your keyboard, F8 or some key combination using it, will step you through code one line at a time.
    - you usually must leave a line and go on to the next in order to see what the values are. If the line is highlighted yellow, it has not been "processed" therefore the variable values will not have been set or altered yet. You must move on to the next line. Usually you can see the value by mousing over it, or you can inquire in the immediate window, as in

    ?getNextRFI

    and hit return.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  5. #5
    d9pierce1 is offline Expert
    Windows 10 Access 2016
    Join Date
    Jan 2012
    Location
    Oklahoma
    Posts
    754
    On the getnextRFI i get a Null
    I tried to step over it but all it does is turn brown. I dont get any errors to speak of?
    Most all in immediate window are False or just blank (nothing comes up)
    PN = Me.TxtNumber gets a message Variable not yet created in this context

    I am not real sure what all this means and how to actually step thru all this mess?
    I will see if i can read up on that, and no, has never been suggested. I can usually find
    errors and figure some stuff out when it presents an error and in yellow, but this is not
    doing that?

  6. #6
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Well, you hit on what I thought would be an issue, but don't seem to be recognizing it as a problem. You're saying "IF getNextRFI = something" (we can forget about the rest of it because you're not concatenating anything there - more on that in a jiff). So unless you have made the variable = to something beforehand, how can it be anything but Null (or a zls, or Empty)? If it is Null it cannot be equal to anything, so your IF block will not run because the test is not true. That means you will never create the value you want.

    As for the concatenation, if strString was Null or a zero length string (zls) and you concatenated "apple" then strString would become "apple" and it might pass some sort of test.
    e.g.
    If strString & "apple" = "" << this could never be true
    If strString & "apple" = "apple" << this could be true
    Note that I did not use If strString & "apple" = Null - because nothing can be compared to null and nothing ever equals null. You can only test if something is Null.
    Last edited by Micron; 09-14-2020 at 02:29 PM. Reason: clarification
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  7. #7
    d9pierce1 is offline Expert
    Windows 10 Access 2016
    Join Date
    Jan 2012
    Location
    Oklahoma
    Posts
    754
    Thank you,
    That helps, I have somewhat of a grip on what is wrong.
    What that said to me in my code was that if RFINum was null, then +1 to it and make it 001

    I will have to work on this for a while? I dont understand it fully how to get it to increment?
    I will play with it and hope to get some better understanding of it.
    Thank you very much\

  8. #8
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    What that said to me in my code was that if RFINum was null, then +1 to it and make it 001
    No, your code says something else. Suppose RFINum was 12. Then Format("PN" & "-" & (RFINum), "000") would be PN-012. You have not assigned a value to getNextRFI, so it is null, thus you are saying
    If Null = "012" and no matter what you put on the right side of =, it will never equal null.

    I think seeing your db would cut through the fog. getnextRFI looks like a function call to me, and if it is, that could make sense as we can assume it would return something "(IF getnextRFI " would call the function and presumably return something). As June7 noted, this is a common topic, but your code doesn't resemble what I saw in the linked thread. Perhaps you should show where you got your code from also.

    If your number field is number type (not text) you can simplify this by just using DMax on the number field, along with a filter (kind of like a WHERE clause) to get the highest number pertaining to a dept or whatever the group is called. Then add 1 to it and format it as 000. If you will eventually get to 999, that format will be an issue.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  9. #9
    d9pierce1 is offline Expert
    Windows 10 Access 2016
    Join Date
    Jan 2012
    Location
    Oklahoma
    Posts
    754
    Thanks Micron,
    Not sure where i came up with that but when i clicked on that link, i went throught that then looked at a few that were listed at the bottom of page of similiar. One of them had a similiar code on it much closer to what i was looking for.
    But in any event, i will try to work through this but i must say i spent all day trying to get 001, 002,.... put on after a number i all ready have and no further along then i was at 4AM. The PN is a ProjectNumber that is on the form. I really considered just doing it on paper, LOL...
    I will eventually figure it out. I need to read something in plain english for me to understand it so will start looking for materials to review.
    Thanks a lot
    Dave

  10. #10
    d9pierce1 is offline Expert
    Windows 10 Access 2016
    Join Date
    Jan 2012
    Location
    Oklahoma
    Posts
    754
    This could be really funny if it wasnt so sad... Maybe this is just more complicated then i understand.
    I cant believe its not just an SQL statement..

    I want to take a number (ProjectNumber) that i all ready have on the form, put a "-" after it, then add a formatted increment of 001, 002 and so on WHERE ProjectID = ProjectID

    So, if i had a ProjectNumber of 02-20343 i have 5 RFI's on this project it would give me results of 02-20343-001 through 02-20343-005
    And it would start over with 001 with a different ProjectNumber and increment with a different ProjectID

    Sounds simple but evidently not. I understand why this is such a hot topic discussion but what i dont understand is why is there not more examples of this?

    Sorry, dont mean to ramble...Thanks to all
    Have a great night
    Dave

  11. #11
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    You need to lookup Max RFI number for given ProjectNumber. Your code is looking up Max ProjectNumber.

    RFINum = DMax("RFI", "RFITbl", "ProjectNumber='" & ProjectNumber & "'")

    For function to return a value, must:

    NewRFINum = getNextRFI

    Incrementing the RFI would be simpler if you did not save it as a compound value.
    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.

  12. #12
    d9pierce1 is offline Expert
    Windows 10 Access 2016
    Join Date
    Jan 2012
    Location
    Oklahoma
    Posts
    754
    Hi and thanks June7,
    This is much closer then i ever have been to solving this issue. Wow
    I put into this db the code above. Changed it slightly to adjust table names.
    I fially got errors that i could see what was going on.

    Code:
    Option Compare DatabaseOption Explicit
    
    
    
    
    Public Function NewRFINum() As String
    Dim PN As String
    Dim RFINum As String
    Dim getNextRFI As String
    
    
    NewRFINum = getNextRFI
    PN = Me.TxtNumber
    RFINum = DMax("RFIID", "RFITbl", "ProjectID='" & ProjectID & "'")
    
    
    If getNextRFI = Format("PN" & "-" & (RFINum), "000") Then
        RFINum = RFINum + 1
    Else
        RFINum = "001"
        getNextRFI = Format("PN" & "-" & (RFINum), "000")
    
    
    End If
    End Function
    So, When i add a new record the value is null. The Project ID is present, the PN is present when hovering over,
    This tells me anyway that i need to find a way to get a value to the RFINum so it is not null. I think?
    I am wondering if maybe do i need the getNextRFI as a separate Function or what i have maybe in wrong order? Not sure on that!
    Do i need an additional IF statement if IsNull, then...? to get an RFINum?
    And finally, how do i get this RFINumber into the correct TxtBox?

    Basically when i hit the RFI button on Form1, then Form2 (RFI) opens, goes to that Project, If no RFI exist yet then needs to set an RFINumber.
    If one or more all ready exist, then it would have records with the RFI Number, if add, then it needs to create a new RFI Number because its null.
    I am really way over my head with this one and not even sure what to do with it to make it work? I have tried moving thngs around but nothing i
    seem to do works even close.

    Thanks so much
    Dave

    Database4 new.zip

  13. #13
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    ProjectID is a number field so don't use apostrophe delimiters.

    NewRFINum = getNextRFI belongs at end of function so result is returned to calling procedure.

    Can't just add 1 to a string, have to parse the compound value to component parts then reassemble them - as per my example procedure. This is why it would be simpler if you did not save RFI as a compound value but instead as a number then build the RFI compound value with concatenation in query.

    Right now, RFINumber is limited to a sequence of 3 digits. Any chance of exceeding 999?

    My example is a function located in a general module because it is called from several locations in my project. Since your code is behind form it does not need to be a function, can be a Sub that directly sets value into textbox. Textbox should be Locked Yes and TabStop No.

    Code:
    Public Sub NewRFINum()
    Dim sRFI As String
    sRFI = Nz(DMax("RFIID", "RFITbl", "ProjectID=" & Me.ProjectID), "")
    If sRFI = "" Then
        Me.RFINumber = Me.CboProject.Column(4) & "-001"
    Else
        Me.RFINumber = Me.CboProject.Column(4) & Format(Right(sRFI, 3) + 1, "-000")
    End If
    End Sub
    

    The real trick is figuring out what event to call code from. Perhaps combobox AfterUpdate. This code need not even be a separate procedure, can be placed within the combobox AfterUpdate event procedure. Don't rely on user to click a button.


    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.

  14. #14
    d9pierce1 is offline Expert
    Windows 10 Access 2016
    Join Date
    Jan 2012
    Location
    Oklahoma
    Posts
    754
    Thank you so much June7
    I cant tell you how much that helped. It worked out of the gate and i just had to tweek it as I was DMax'ing the wrong field.
    Got some pretty interesting numbers along the way, lol...
    In any event, it works like a charm and you made this code very simple and simple to understand. Thank you!!!!!!
    AND no chance it will ever get cose to 999 so no worries. If it gets more than 200 I QUIT! Been in construction building
    buildings for 24 years now and never had an RFI (Request for Information) go over 133 and that architect was rotten to the
    core... Most range from 20 to 50 depending on project size.

    I ended up putting the call in the afterupdate of Question Field for now. I will make changes to it once i get in my real db as
    this was just a mach up for testing.

    Code:
    Option Compare DatabaseOption Explicit
    
    
    
    
    Public Sub NewRFINum()
    Dim sRFI As String
    sRFI = Nz(DMax("RFINumber", "RFITbl", "ProjectID=" & Me.ProjectID), "")
    If sRFI = "" Then
        Me.RFINumber = Me.CboProject.Column(4) & "-001"
    Else
        Me.RFINumber = Me.CboProject.Column(4) & Format(Right(sRFI, 3) + 1, "-000")
    End If
    End Sub
    
    
    Private Sub Question_AfterUpdate()
    Call NewRFINum
    End Sub
    Again, Thank you! Youre awsome

  15. #15
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Chip Pearson has a page about debugging that might help you........ -> Debugging VBA Code

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

Similar Threads

  1. Auto Number Custome incremental
    By RustyRick in forum Access
    Replies: 25
    Last Post: 03-23-2020, 02:28 PM
  2. ID autonumber or incremental number in VBA
    By MadTom in forum Database Design
    Replies: 3
    Last Post: 12-02-2019, 04:45 PM
  3. Replies: 6
    Last Post: 09-17-2017, 07:20 PM
  4. Replies: 11
    Last Post: 11-11-2014, 06:04 PM
  5. Incremental Number in a Query
    By jmauldin01 in forum Access
    Replies: 5
    Last Post: 01-01-2014, 01:23 AM

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