Looping through recordset in query but backwards

[jaryszek]

Hi,

my code is :

Sub Macro_LEarn()


 Dim rs As DAO.Recordset
 Dim db As Database
 Dim strSQL As String
 Dim queryMy As DAO.QueryDef


 Set db = CurrentDb


 Set rs = db.OpenRecordset("test")
 
 With rs


        If Not .BOF And Not .EOF Then
        .MoveLast
        .MoveFirst
            While (Not .EOF)
                 Debug.Print rs.Fields("Numer")
                .MoveNext
            Wend
        End If
        
.Close


End With


Set rs = Nothing


End Sub

I want to loop through my recordset but backwards, in Excel I am using step - 1,
it is possible to do it in Access?

Best Regards,
Jacek Antek

[RuralGuy]

Why? Just sort descending.

[jaryszek]

Ok This was easy, thank You RuralGuy.

the next problem is how to find if previuos record is different then current ?

I have tried to do something like this:

  If rs.Fields("Numer") <> rs.Fields("Numer") + 1 (or maybe movenext here?) Then
                        MsgBox "ok"
  End If

Please help and thank you in advance Guys,
Best Regards,
Jacek Antek

[JoeM]

Store the previous value in a variable, and then compare it to the current one (you just wait to reset the value of the previous variable in your loop until after you have done your comparison).

[jaryszek]

Ok thank you very much. I have tried it already:

       If Not .BOF And Not .EOF Then
        .MoveLast
        FindRecordCount = .RecordCount
        .MoveFirst
            While (Not .EOF)
            Debug.Print rs.Fields("Numer")
                If Len(rs.Fields("Numer")) = 13 Then
                    CurrentRec = rs.Fields("Numer").Value
                    .MoveNext
                        If .EOF = True Then
                          .MovePrevious
                          .Edit
                          rs.Fields("Numer").Value = rs.Fields("Numer") & "_" & i
                          .Update
                          GoTo Aktualizuj:
                        End If
                    NextRec = rs.Fields("Numer").Value
                    .MovePrevious
                    .Edit
                    rs.Fields("Numer").Value = rs.Fields("Numer") & "_" & i
                    .Update
                End If
                .MoveNext
                i = i + 1
            Wend
        End If
Aktualizuj:
.Close


End With


Set rs = Nothing

So i have next record variable and possibility to compare it with current. That is great!

Once more question - it is possibility to loop only with filters value? Or start to filter with specific String.
In my table I have numbers:

50000020
50000020
50000015 etc.

I my Specific Value is "50000020" i would like to loop only within my searched values. Maybe filter will be the solution here? What will be the best and fastest solution ?

Best Regards,
Jacek Antek

[JoeM]

You are filtering through a Query, right?
I just apply any "filters" (really "criteria") to the Query I am looping through.
So anything that I want filtered out already is from the start.

[orange]

What exactly are you trying to accomplish? Plain English.

Where does this fit (from post 1)

Dim queryMy As DAO.QueryDef

It improves reader understanding if you post ALL of the code, not just a snippet.

[jaryszek]

Sorry orange, this one is my mistake.

My code:

Sub Macro_LEarn()


 Dim rs As DAO.Recordset
 Dim db As Database
 Dim strSQL As String
 Dim FindRecordCount As Long
 Dim CurrentRec As String
 Dim NextRec As String
 Dim i As Long
 Dim strCustomerWanted As String


DoCmd.OpenTable "tb_num_calosc"
DoCmd.ApplyFilter , "[Numer]='=50000015_2016' & '*'"


 Set db = CurrentDb
 Set rs = db.OpenRecordset("tb_num_calosc")
 
 With rs
 
 i = 1


        If Not .BOF And Not .EOF Then
        .MoveLast
        FindRecordCount = .RecordCount
        .MoveFirst
            While (Not .EOF)
            Debug.Print rs.Fields("Numer")
                If Len(rs.Fields("Numer")) = 13 Then
                    CurrentRec = rs.Fields("Numer").Value
                    .MoveNext
                        If .EOF = True Then
                          .MovePrevious
                          .Edit
                          rs.Fields("Numer").Value = rs.Fields("Numer") & "_" & i
                          .Update
                          GoTo Aktualizuj:
                        End If
                    NextRec = rs.Fields("Numer").Value
                    .MovePrevious
                    .Edit
                    rs.Fields("Numer").Value = rs.Fields("Numer") & "_" & i
                    .Update
                End If
                .MoveNext
                i = i + 1
            Wend
        End If
Aktualizuj:
.Close


End With


Set rs = Nothing

End Sub

This is complicated what i want to accomplish at all but I will try to explain this the best i can.

1. First of all i Have Excel woorkbooks with tables and tickets numbers within these tables (one row is for one ticket).

When user is clicking send button in Excel his table is exported to access database.
Now it is a clue problem - how to check if this specific number (for example 5000020 in Excel) is already in my access database.
In access database i should have Tickets_Id: 50000020_2016_1, 50000020_2016_2 ....50000020_2016_5 on basis of other users entries.
So user in Excel should have got Ticket with ID 50000020_2016_6 (50000020_2016_5 + 1) and this record should be saved in Access also for next User.

Now If we have a few users i try to do One big table which will be consolidating all small ones in Access.

I can do it by doing a query (append Query), my code here is:

INSERT INTO tb_num_calosc 
SELECT *
FROM (SELECT * FROM tb_num_ljar01 UNION SELECT * FROM tb_num_ljar02)  AS [%$##@_Alias]
ORDER BY Numer, [Czas zgłoszenia];

tb_num_calosc - is new consolidated table
tb_num_ljar01 and tb_num_ljar02 - users small tables

After this query one big table will be the result - my question from beginning of this topic is connected to this one big consolidated table (code you have above).


From all tickets for example 50000015_2016 my code should be creating table looking like this:




So Access should take string "5000020" from Excel, filter this table for rows containing only "5000020_*" and loop to change this number for "5000020_2016_6" in my big table - this bigger value should be back to User in Excel as new registered Ticket_Id and saved with his small table in Excel (to see all of his Tickets and their statuses).

Please write me if I have wrote this not understandable.

Best regards,
Jacek Antek