Data source changed from a table to a query (1 Viewer)

naobao

Registered User.
Local time
Today, 10:26
Joined
Feb 13, 2014
Messages
99
How can I modify the following code to add a row number in the query, now that the data source 'packing_ship_mark_1a' has been changed from a table to a query, so it can run correctly?

Code:
Public Function fnRank1(ByVal id As Long) As Long
    '
    ' put the Linked table name here and replace dbo_table_1
    '
    Const the_linked_table As String = "packing_ship_mark_1a"
 
    Static d_obj As Object
    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim i As Long

   ' note to reset this function pass 0 or negative value to the function
    If id < 1 Then
        Set d_obj = Nothing
        Exit Function
    End If
 
    If (d_obj Is Nothing) Then
      
        Set d_obj = CreateObject("scripting.dictionary")
        Set db = CurrentDb
        Set rs = db.OpenRecordset(the_linked_table, dbOpenSnapshot, dbReadOnly)
      
        With rs
            If Not (.BOF And .EOF) Then
                rs.MoveLast
                rs.MoveFirst
            End If
            Do Until .EOF
                i = i + 1
                d_obj(!id & "") = i
                .MoveNext
            Loop
            .Close
        End With
      
    End If
 
    fnRank1 = d_obj(id & "")
              
End Function
 
Are you saying you tried it and it didn't work? Was there an error message?
 
Is this query the source for a report? Why calculate row number in query? Consider using RunningSum property of textbox.
 
Not quite sure what you're trying to do with the dictionary.
 
Not quite sure what you're trying to do with the dictionary.
id RowNum
339 1
340 2
308 3
309 4
329 5
330 6
331 7
332 8
333 9
334 10
335 11
336 12
337 13
338 14

RowNum is automatically generated by code.
 

Attachments

  • Untitled.jpg
    Untitled.jpg
    20.8 KB · Views: 53
Can you also show us the SQL statement for your query?

PS. Just in case it helps, you might want to take a look at Leigh's Generic Recordset.

 
Could it be the way your using the dictionary?
I would imagine you'd use the key as the row number and the item for the other value.

d_obj.Add i , cstr(rs!id)

if your using the full recordset you can also use the rs.absoluteposition instead of i.

Code:
        d_obj.Add CStr(rs.AbsolutePosition + 1), CStr(rs.Fields(1))
 
Last edited:
Still have no clue what the dictionary is for.

If you want to add sequence numbers to rows when you add them, that's easy. If you want to add permanent numbers after the fact, that requires a DAO loop. Not hard either. If you want to add temporary sequence numbers, you would do that with a query. So, none of the three methods would use a dictionary. Please tell us in non-technical terms the objective of the code.
 

Users who are viewing this thread

Back
Top Bottom