Data source changed from a table to a query

[naobao]

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?

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

 

[theDBguy]

Are you saying you tried it and it didn't work? Was there an error message?

 

[June7]

Is this query the source for a report? Why calculate row number in query? Consider using RunningSum property of textbox.

 

[moke123]

Not quite sure what you're trying to do with the dictionary.

 

[naobao]

Are you saying you tried it and it didn't work? Was there an error message?

if "packing_ship_mark_1a" is a table,it work

 

[naobao]

Is this query the source for a report? Why calculate row number in query? Consider using RunningSum property of textbox.

is not for a report

 

[theDBguy]

if "packing_ship_mark_1a" is a table,it work

And if it's a query, what error message do you get?

 

[naobao]

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.

 

[naobao]

And if it's a query, what error message do you get?

 

[theDBguy]

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.

Code Snippets

 

[Gasman]

Whay are you crossposting this?

N

Post in thread 'Add RowNumber Problem'

Jul 30, 2024

Is the first row a typo?

YES

• naobao

 

[moke123]

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.

        d_obj.Add CStr(rs.AbsolutePosition + 1), CStr(rs.Fields(1))

 

[moke123]

Whay are you crossposting this?

N

Post in thread 'Add RowNumber Problem'

Jul 30, 2024

Is the first row a typo?

YES

• naobao

would have at least been nice to know it's sql server.

 

[Pat Hartman]

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.