Solved Finding specific Record index

[Rashid]

RecID(autoNo)	StuID	StuName	Data
1	1	a	Dr
2	2	b	Cr
3	1	a	Cr
4	1	a	Cr
5	2	b	Dr

In a Continuous Form

I Need when I select recid4; a textbox (unbound)

Show me “This is 3rd Transaction of this student”

So what code will be applied in unbound tex

 

[amorosik]

For example, on Form_Current event

msgbox("This is " & dlookup("count(*)","table_name","StudID=" & me!StudID & " and RecID<=" & me!RecID) & " transaction bla bla bla...")

 

[bastanu]

I think you need to use a ranking query grouped by student and sorted by the RecID to get what you need:

Microsoft Access tips:

How to rank records in Microsoft Access

allenbrowne.com

Cheers,

 

[Rashid]

For example, on Form_Current event

msgbox("This is " & dlookup("count(*)","table_name","StudID=" & me!StudID & " and RecID<=" & me!RecID) & " transaction bla bla bla...")

Not working ,,,,

 

[bastanu]

Because you want an index or rank, not a total count; have you tried to use my link?

 

[amorosik]

Not working ,,,,

... and why?
Wich result from dlookup?

 

[Rashid]

Not working ,,,,

After googling i have got the solution and That is:- Creat a query add a new column RecNo with this Code: RecNo: DCount("transid","feetable query","transid<" & [transid] & "AND STUDENTID=" & [STUDENTID])+1 and in the form in which i need the value place a unbound textbox with the code: dlookup("Recno","QueyName","transid="&[transid]). It Is working great.No Body Give this solution in this forum.????

 

[bastanu]

Because it is similar to the approach used in Allen's link I provided you in post #3 (but potentially slower with large record sets because you are using dCount instead of Count in a totals subquery). but you ignored it....
Anyway looks like you learned something new and maybe next time a similar question comes in maybe you can jump in to help.
Cheers,

 

[amorosik]

No Body Give this solution in this forum.????

Maybe because it's not the simplest solution
Could you please tell me what was wrong with the recommended code?

dlookup("count(*)","table_name","StudID=" & me!StudID & " and RecID<=" & me!RecID)