Problem building ACCESS query for retrival of records.

[ADezii]

Very correct sir. I had thinked a lot, that if sequential numbers r generated in the underlying SQL, the thing will be very easy. But i found the problem stated below :

One 'form' in visual basic is created by me, which will collect the date as parameter and then this query will be used to give records for that date. The problem is, we did not get the number of records filtered until this query is executed and so in advance we can not set one field for sequential numbers.

So as per your deep experience, is it possible thing to generate sequential numbers in the query itself ? If it seems possible to you, please guide me on the way.

I'm still working on it, please be patient.

[ADezii]

I'm still working on it, please be patient.

It just dawned on me that I had experienced a similar request from a Member a long time ago, and here is how I resolved his problem. The circumstances are quite different, and the solution is a little extreme, but there seemed to be no other viable options and this logic does, in fact, work. I don't think that something like this can be implemented in your specific case, but I'll show you anyway. If you have any other questions, feel free to ask:

NeoPa: (me writing)
Here is a little Off-The-Wall technique that I always use whenever I want Query results to be numbered sequentially starting from 1 regardless of any Sort Order on any Field or Fields, number of Fields, etc. It is a little radical but there seems to be no other way to achieve the same result. Please look at it and let me know what you think. This code must be run prior to executing the Query and it also involves adding a [Counter] Field to the underlying Table then executing the Query with this ([Counter]) as the very 1st Column. It creates a Recordset based on the very Query that you want to sequentially number and writes the incremental values to the [Counter] Field starting at position 1. I'm sure that it can be improved - I just know that it works.

[CODE=vb]Dim MyDB As DAO.Database, MyRS As DAO.Recordset, intRecNo As Integer
Set MyDB = CurrentDb()
Set MyRS = MyDB.OpenRecord set("qryEmploye e", dbOpenDynaset)

Do While Not MyRS.EOF
intRecNo = intRecNo + 1
MyRS.Edit
MyRS![Counter] = intRecNo
MyRS.Update
MyRS.MoveNext
Loop

MyRS.Close[/CODE]
No matter what Criteria you specify for qryEmployee, and no matter how many Records are returned, they will always be numbered 1 to Recordset.Recor dCount.

[ashutoshvyas]

It just dawned on me that I had experienced a similar request from a Member a long time ago, and here is how I resolved his problem. The circumstances are quite different, and the solution is a little extreme, but there seemed to be no other viable options and this logic does, in fact, work. I don't think that something like this can be implemented in your specific case, but I'll show you anyway. If you have any other questions, feel free to ask:


[CODE=vb]Dim MyDB As DAO.Database, MyRS As DAO.Recordset, intRecNo As Integer
Set MyDB = CurrentDb()
Set MyRS = MyDB.OpenRecord set("qryEmploye e", dbOpenDynaset)

Do While Not MyRS.EOF
intRecNo = intRecNo + 1
MyRS.Edit
MyRS![Counter] = intRecNo
MyRS.Update
MyRS.MoveNext
Loop

MyRS.Close[/CODE]
No matter what Criteria you specify for qryEmployee, and no matter how many Records are returned, they will always be numbered 1 to Recordset.Recor dCount.

Thank you again.
I had noticed that there is the word expert under your name which was moderator few days ago, so congrats for becoming expert.

Truly u r genius & expert. I like your way to support, that is the right way. Instead of giving straight forward answers u r giving answers in the form of hints or clever language. I like your art.

with the help of your last post i had reached to the solution and now successful generation of report is done with sequential numbers. I am providing the code for reference for others.

1) In command1 i have to select lock type = 4- batch optimistic.
2) code segment (VB)

If .rsCommand1.Rec ordCount > 0 Then
.rsCommand1.Mov eFirst
Dim intRecNo As Integer
Do While Not .rsCommand1.EOF
intRecNo = intRecNo + 1
.rsCommand1.Upd ate
.rsCommand1![Counter] = intRecNo
.rsCommand1.Upd ate
.rsCommand1.Mov eNext
Loop

Set rptashu.DataSou rce = DataEnvironment 1
rptashu.Show
Else
MsgBox "No Titles found"
End If
End With
End Sub

Thank you again.

[ADezii]

Thank you again.
I had noticed that there is the word expert under your name which was moderator few days ago, so congrats for becoming expert.

Truly u r genius & expert. I like your way to support, that is the right way. Instead of giving straight forward answers u r giving answers in the form of hints or clever language. I like your art.

with the help of your last post i had reached to the solution and now successful generation of report is done with sequential numbers. I am providing the code for reference for others.

1) In command1 i have to select lock type = 4- batch optimistic.
2) code segment (VB)

If .rsCommand1.Rec ordCount > 0 Then
.rsCommand1.Mov eFirst
Dim intRecNo As Integer
Do While Not .rsCommand1.EOF
intRecNo = intRecNo + 1
.rsCommand1.Upd ate
.rsCommand1![Counter] = intRecNo
.rsCommand1.Upd ate
.rsCommand1.Mov eNext
Loop

Set rptashu.DataSou rce = DataEnvironment 1
rptashu.Show
Else
MsgBox "No Titles found"
End If
End With
End Sub

Thank you again.

I'm happy that you arrived at the solution and did, in fact, share it with all of us. I also thank you for your kind compliments, they are greatly appreciated. I'm just so glad that an old solution resurfaced and was able to assist another member. Take care and keep in touch.