Results 1 to 14 of 14
  1. #1
    shank is offline Competent Performer
    Windows XP Access 2007
    Join Date
    Sep 2010
    Posts
    188

    Adding Columns to RowNumber

    I use the below module for generating Invoice numbers on-the-fly. It works fine when using 1 column: PONo. It allocates a unique Invoice number per each unique PONo. Great.



    Problem: I need to add 2 more columns [ShipDate],[WhsID] to the criteria so that it generates a unique invoice number per each ShipDate, WhsID, PONo. I may have to ship PO's at different times from different warehouses. When I try to add the fields, I get error: The expression you entered has a function containing the wrong number of arguments. What needs to be changed in the below module to accommodate more columns?

    Code:
    InvNo: RowNumber([PONo]) - works great.
    
    InvNo: RowNumber([ShipDate],[WhsID],[PONo]) - I need to add columns [ShipDate],[WhsID] to the criteria.
    Code:
    Option Compare Database
    
    
    Private lngRowNumber As Long
    Private colPrimaryKeys As VBA.Collection
     
    Public Function ResetRowNumber() As Boolean
    Set colPrimaryKeys = New VBA.Collection
    
    
        Dim db As DAO.Database
        Dim rs As DAO.Recordset
        Dim varInvNo As String
        Dim MaxQuery As String
    
    
    'Specify which query will be the source for varInvNo
        MaxQuery = "qry_TempBatchInvNo_Max"
        Set db = CurrentDb()
        Set rs = db.OpenRecordset("SELECT Distinct [InvNo] FROM " & MaxQuery, dbOpenSnapshot)
        varInvNo = rs("InvNo")
    
    
    'Setup the value for variable lngRowNumber
        lngRowNumber = varInvNo
        ResetRowNumber = True
        
    End Function
     
    Public Function RowNumber(UniqueKeyVariant As Variant) As Long
      Dim lngTemp As Long
     
      On Error Resume Next
      lngTemp = colPrimaryKeys(CStr(UniqueKeyVariant))
      If Err.Number Then
        lngRowNumber = lngRowNumber + 1
        colPrimaryKeys.Add lngRowNumber, CStr(UniqueKeyVariant)
        lngTemp = lngRowNumber
      End If
     
      RowNumber = lngTemp
    End Function
    Thank you!

  2. #2
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    your function only accepts one parameter

    Public Function RowNumber(UniqueKeyVariant As Variant) As Long

    You will have to add additional parameters in order to pass values to each of them.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  3. #3
    shank is offline Competent Performer
    Windows XP Access 2007
    Join Date
    Sep 2010
    Posts
    188
    >>You will have to add additional parameters in order to pass values to each of them.<<

    Great. How?

  4. #4
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    You will need to show us the entire code, specifically where\how you attach the newly returned invoice number to each of the records. It is there where the code needs to branch out to request a new number when either of the three fields changes.

    Cheers,
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  5. #5
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Sorry; your profile indicated to me that you would understand that.
    Perhaps

    Public Function RowNumber(varPK As Variant, strWhse As String, lngPONo As Long) As Long

    Note that I used more concise variable names to show another style. To me, having the word Variant as part of a field name to denote it's data type isn't what I'd do, but of course, you're welcome to adapt to your own style. Prefacing PK (which by itself suggests a unique value) with var tells you it's a variant. I have links for object naming styles if you want them.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  6. #6
    shank is offline Competent Performer
    Windows XP Access 2007
    Join Date
    Sep 2010
    Posts
    188
    Attached is a test DB with the 3 Arguments.


    I get an error in the InvNo column when I run query: qry_APP_850_Temp850RowID_3var. The error does not lend any useful information.


    In the module, I commented out the original line: Public Function RowNumber(UniqueKeyVariant As Variant) As Long that has 1 Argument. This line worked, but not to t he degree I need it to.


    I'm assuming there needs to be something else changed other than the line with 3 Arguments.


    Suggestions?


    Thank you!
    Attached Files Attached Files

  7. #7
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Did you post the wrong db? Your function still only accepts one parameter. It does not look like what I wrote in post 5. You still have

    Public Function ResetRowNumber() As Boolean

    What value do you want the function to place in the records of the InvNo field?

    EDIT - I see that you modified RowNumber to look like that but I was looking at ResetRowNumber. Will take another look.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  8. #8
    shank is offline Competent Performer
    Windows XP Access 2007
    Join Date
    Sep 2010
    Posts
    188
    Use query: qry_APP_850_Temp850RowID_3var
    InvNo: RowNumber([850 InBound POs]![EarliestShip],[850 InBound POs]![SFWhsID],[850 InBound POs]![PONo])
    When I view the query, I get:
    EarliestShip SFWhsID PONo InvNo
    11/22/2021 CA 22LQFCGD #Error
    11/22/2021 CA 23OIAS4M #Error
    11/22/2021 CA 2TVWV4KR #Error
    11/22/2021 CA 31DIMMPN #Error


    I left the function the same name and changed this line:
    'Public Function RowNumber(UniqueKeyVariant As Variant) As Long - Commented out. This was the original.
    Public Function RowNumber(varPK As Variant, strWhse As String, lngPONo As Long) As Long

    Thanks!

  9. #9
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Might be better to take a slightly different approach. Considering
    generates a unique invoice number per each ShipDate, WhsID, PONo
    I see that 2 records look identical, so is that an error? Should the next Invoice be 688613? If so, I think a solution needs to prevent that. There are other issues, such as a declared collection that is not used but they are not real important right now.


    EarliestShip SFWhsID PONo InvNo
    11/22/21 FL 7VWOUHNP 688612
    11/22/21 FL 7VWOUHNP 688612
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  10. #10
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Is this what you're after?

    Cheers,
    Attached Files Attached Files
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  11. #11
    shank is offline Competent Performer
    Windows XP Access 2007
    Join Date
    Sep 2010
    Posts
    188
    Yes! It appears you removed the 3 arguments and substituted with a concatenated string as follows:
    InvNo: RowNumber(Format([850 InBound POs]![EarliestShip],"mmddyyyy") & [850 InBound POs]![SFWhsID] & [850 InBound POs]![PONo])

    Is that correct?

    Thank you!!!

  12. #12
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Yes, the VBA function is looking for a unique "breakpoint" to restart; initially the group was uniquely identified by the PO Number itself, but now the group is made up of the three fields together.

    You're very welcome!

    Cheers,
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  13. #13
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Forgot to say that you might want to consider separating the three entities with some sort of character in case the combinations of the characters in the "joining" areas could cause confusion:

    InvNo: RowNumber(Format([850 InBound POs]![EarliestShip],"mmddyyyy") & "_" & [850 InBound POs]![SFWhsID] & "_" & [850 InBound POs]![PONo])

    should work as well and it will prevent any interactions.

    Cheers,
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  14. #14
    shank is offline Competent Performer
    Windows XP Access 2007
    Join Date
    Sep 2010
    Posts
    188
    Quote Originally Posted by Gicu View Post
    Forgot to say that you might want to consider separating the three entities with some sort of character in case the combinations of the characters in the "joining" areas could cause confusion:

    InvNo: RowNumber(Format([850 InBound POs]![EarliestShip],"mmddyyyy") & "_" & [850 InBound POs]![SFWhsID] & "_" & [850 InBound POs]![PONo])

    should work as well and it will prevent any interactions.

    Cheers,
    Well noted and much appreciated!
    Thanks!

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 14
    Last Post: 06-28-2014, 08:28 PM
  2. Currency Columns not adding up
    By WKU in forum Access
    Replies: 2
    Last Post: 05-07-2014, 09:10 AM
  3. Adding new columns of sign
    By yossik in forum Access
    Replies: 1
    Last Post: 10-15-2013, 12:26 AM
  4. Adding Columns in a Query
    By racefan91 in forum Queries
    Replies: 5
    Last Post: 10-01-2013, 02:34 PM
  5. Adding Two Columns
    By arthura in forum Queries
    Replies: 6
    Last Post: 05-01-2009, 08:38 PM

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