Link to home
Start Free TrialLog in
Avatar of lkirke
lkirkeFlag for Australia

asked on

Inserting Row Number into Query

Hello Experts,

Am trying to insert a row number into a query via the leveraging off a previous post (https://www.experts-exchange.com/questions/20492559/Display-record-row-number-in-a-query.html)

However, am having a little trouble getting it to work correctly.

When I run the query I get an error indicating the following:
Invalid bracketing of name '[tbl_Participant_Details.ParticipantID]'.

The field I have created within the query is:

SEQUENCE_NO: GetRowNum("qry_interface_EMP_Monthly","tbl_Participant_Details.ParticipantID",[tbl_Participant_Details.ParticipantID])

ParticipantID is the unique field
qry_interface_EMP_Monthly is the query in which I am trying to generate the row number
tbl_Participant_Details is the table in which the unique field is stored.

Any ideas as to what I am doing wrong?

Regards

LK
Option Compare Database
Function GetRowNum(strQueryName As String, strIDField As String, varID As Variant) As Long
Dim rst As DAO.Recordset

Set rst = CurrentDb.OpenRecordset("SELECT [" & strIDField & "]" & " FROM " & strQueryName, dbOpenSnapshot)
Select Case rst(strIDField).Type
    Case dbBigInt, dbBinary, dbBoolean, dbByte, dbCurrency, dbDecimal, dbDouble, dbFloat, dbInteger, dbLong, dbSingle, dbVarBinary:
        rst.FindFirst "[" & strIDField & "]=" & varID
    Case dbChar, dbText, dbMemo:
        rst.FindFirst "[" & strIDField & "]='" & varID & "'"
    Case dbDate, dbTime:
        rst.FindFirst "[" & strIDField & "]=#" & Format(varID, "mm/dd/yyyy hh:nn:ss") & "#"
End Select

GetRowNum = rst.AbsolutePosition + 1

rst.Close
Set rst = Nothing
End Function

Open in new window

Avatar of Rey Obrero (Capricorn1)
Rey Obrero (Capricorn1)
Flag of United States of America image

change  

,[tbl_Participant_Details.ParticipantID])

with


,[tbl_Participant_Details].[ParticipantID])
Avatar of lkirke

ASKER

Changed to [tbl_Participant_Details].[ParticipantID]), yet the error message still appears:

Invalid bracketing of name '[tbl_Participant_Details.ParticipantID]'.
ASKER CERTIFIED SOLUTION
Avatar of Rey Obrero (Capricorn1)
Rey Obrero (Capricorn1)
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of lkirke

ASKER

Changed but the following appears:

The specified field could refer to more than one table listed in the FROM clause.
check your query  "qry_interface_EMP_Monthly"
Avatar of lkirke

ASKER

Attached an example.

Query is qry_interface_EMP_Monthly.

Module is mod_Seq_No. Example-4.mdb
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of lkirke

ASKER

Thank you Gustav. Not sure how to apply this to my example though.
Avatar of lkirke

ASKER

Thank you Experts. Worked it out with your assistance.