Page 2 of 2 FirstFirst 12
Results 16 to 19 of 19

Query Add Row Numbers

  1. #16
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    6,306
    Instead of 0, how can I get a prompt for a number input in VBA?
    use Input box function. You should code so as to validate the result of the input; e.g. if no response or if wrong data type or range.

  2. #17
    shank is offline Competent Performer
    Windows XP Access 2007
    Join Date
    Sep 2010
    Posts
    119
    I got the input box figured out, but ran into another issue. I'm pretty sure it's this line: RowNumber([TempOrderImport]![PONUMBER],[TempOrderImport]![WAREHOUSE]). The RowNumber function was intended for one parameter, whereas I need it to evaluate two fields: [PONUMBER] and [WAREHOUSE]. How do Iget the function to evaluate 2 fields?

    Code:
    SELECT DISTINCT TempOrderImport.PONUMBER, TempOrderImport.WAREHOUSE, RowNumber([TempOrderImport]![PONUMBER],[TempOrderImport]![WAREHOUSE]) AS RowID
    FROM TempOrderImport
    WHERE (((ResetRowNumber())<>False))
    ORDER BY TempOrderImport.PONUMBER, TempOrderImport.WAREHOUSE;
    Code:
    Public Function ResetRowNumber() As Boolean
    Set colPrimaryKeys = New VBA.Collection
    
    
    Dim strInput As String, strInputLng As String, booNotWholeNumber As Boolean
    GiveNumberRetry:
    booNotWholeNumber = False
    strInput = InputBox("What was the last Invoice Number Used?")
    If Len(strInput) = 0 Then GoTo ExitGiveNumber
    On Error Resume Next
    strInputLng = CLng(strInput)
    If Err > 0 Then
        Err = 0
        booNotWholeNumber = True
    End If
    If strInput <> strInputLng Then booNotWholeNumber = True
    If booNotWholeNumber Then
        If vbCancel = MsgBox("You should enter a Whole Number", vbExclamation + vbOKCancel) Then
            GoTo ExitGiveNumber:
        Else
            GoTo GiveNumberRetry
        End If
    End If
    ExitGiveNumber:
    
    
        lngRowNumber = strInput
        ResetRowNumber = True
        
    End Function
    Thanks!
    Attached Thumbnails Attached Thumbnails S1.jpg  

  3. #18
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    6,306
    How do Iget the function to evaluate 2 fields?
    You cannot pass more arguments to a procedure than it is designed to accept. If both are required,
    Public Function RowNumber(x,y) As Integer
    If 1 is optional it must be last
    Public Function RowNumber(x,Optional y) As Integer
    I see you eliminated the type declaration from the original code, which isn't really good practice even if the original type was variant.

  4. #19
    shank is offline Competent Performer
    Windows XP Access 2007
    Join Date
    Sep 2010
    Posts
    119
    I kept the original code and went with 2 queries to generate the invoice numbers, then update the fields. Works great.

    Thanks for you help!

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

Similar Threads

  1. Specified numbers in a query
    By sknechte in forum Access
    Replies: 8
    Last Post: 02-01-2018, 04:02 PM
  2. Replies: 2
    Last Post: 08-30-2017, 07:11 AM
  3. Replies: 5
    Last Post: 02-26-2016, 10:48 AM
  4. Replies: 8
    Last Post: 03-10-2014, 11:47 AM
  5. Replies: 1
    Last Post: 11-29-2011, 08:43 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
  •  
Tech Forums: Microsoft Office Forums