PriceD
asked on
Access 2003: Display a Record/Row number in a query
Hi,
I am attempting to use the instructions from the following past post...
https://www.experts-exchange.com/questions/20492559/Display-record-row-number-in-a-query.html
However its not working for me, what could I be doing wrong?
I copied the posted code and placed it into a module called modGetRowNumber. I have the DAO setup properly as I use it for other procedures. I then opened my query "qrySubFrmTimeSheet" and as per the instructions added a new field that I called RowNum and has the following in it..
RowNum: GetRowNum("qrySubFrmTimeSh eet","Time SheetID",[ TimeSheetI D])
TimeSheetID is the primary key of the TimeSheet table that I am querying.
When I now run my query "qrySubFrmTimeSheet", I get an error..
Run-Time error '3061':
Too few parameters. Expected 2.
If I click on the Debug button it highlights the following line in the modGetRowNumber module..
"Set rst = CurrentDb.OpenRecordset("S ELECT [" & strIDField & "]" & " FROM " & strQueryName, dbOpenSnapshot)"
If I move my mouse over "strIDField" it displays "TimeSheetID". If I move my mouse over "strQueryName" it displays "qrySubFrmTimeSheet". If I move my mouse over "dbOpenSnapshot" it displays "4".
So does anyone have ideas what I'm doing wrong?
Thanks.
I am attempting to use the instructions from the following past post...
https://www.experts-exchange.com/questions/20492559/Display-record-row-number-in-a-query.html
However its not working for me, what could I be doing wrong?
I copied the posted code and placed it into a module called modGetRowNumber. I have the DAO setup properly as I use it for other procedures. I then opened my query "qrySubFrmTimeSheet" and as per the instructions added a new field that I called RowNum and has the following in it..
RowNum: GetRowNum("qrySubFrmTimeSh
TimeSheetID is the primary key of the TimeSheet table that I am querying.
When I now run my query "qrySubFrmTimeSheet", I get an error..
Run-Time error '3061':
Too few parameters. Expected 2.
If I click on the Debug button it highlights the following line in the modGetRowNumber module..
"Set rst = CurrentDb.OpenRecordset("S
If I move my mouse over "strIDField" it displays "TimeSheetID". If I move my mouse over "strQueryName" it displays "qrySubFrmTimeSheet". If I move my mouse over "dbOpenSnapshot" it displays "4".
So does anyone have ideas what I'm doing wrong?
Thanks.
Is qrySubFrmTimeSheet a parameter query?
ASKER
Hi peter57r,
If I understand you correctly, I'm gonna say Yes. It is looking for 2 criteria, one from the EmployeeID combo box and the other from the Date combo box.
If I understand you correctly, I'm gonna say Yes. It is looking for 2 criteria, one from the EmployeeID combo box and the other from the Date combo box.
Does your query require parameters?
ASKER
Does this mean I need to create a new query that gets the results from the first query "qrySubFrmTimeSheet" and then add the field for the GetRowNumber to this new query.
Then I don't see how you can use this code. Running a saved query in code will not resolve any parameter fields.
Creating a recordset from a parameter query is possible if the recordset uses the query directly, but in this case the recordset is based on a sql string which in turn references the saved parameter query. I don't know of any way of getting the parameter values into that structure.
I'll have a think about but I'm not sure there is an easy solution to this.
Creating a recordset from a parameter query is possible if the recordset uses the query directly, but in this case the recordset is based on a sql string which in turn references the saved parameter query. I don't know of any way of getting the parameter values into that structure.
I'll have a think about but I'm not sure there is an easy solution to this.
Can you provide a bit more info on why you wanted to number the rows?
Are you allowing any updates/changes to the data when it is displayed?
(In other words - do you need to retain the link to the original table, or can you use an intermediate table to hold the query results?)
Are you allowing any updates/changes to the data when it is displayed?
(In other words - do you need to retain the link to the original table, or can you use an intermediate table to hold the query results?)
ASKER
Just for visual appearance and reference when viewing the form. I don't want to save any generated row numbers.
ASKER
I found this. It seems it is different in that it calls the function from the text box source field. What do you think of this..I havent tried it yet.
http://support.microsoft.com/kb/325236
http://support.microsoft.com/kb/325236
ASKER
I tried the method provided on the site from the link in my previous post. I placed a break point at the beginning of the new Function I copied into the module modGetLineNumber. It doesnt even get there now. I get no error, and all the data appears as it should but the column that is supposed to show the row number has #Name? showing on each row instead, but no other error.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.