rst.FindFirst problem (1 Viewer)

[Poppy]

Hello All

I just discovered the reason why my table has not been working the way I want it to. In my code below, I have set my rst to find the first record of the previous month which in itself is correct, however I just discovered that it is picking up the records in ascending order.

Here is my code:

Option Compare Database
Option Explicit

Private Sub Button5_Click()

Dim prevMonth As Integer
Dim curMonth As Integer
Dim prevYear As Integer
Dim curYear As Integer
Dim CurRecordMonth As Integer
Dim rst As Recordset
Dim rst2 As Recordset
Dim db As Database
Dim monthText As Variant

Set db = CurrentDb

'fill an array with the text for months names
monthText = Array("", "Jan", "Feb", "Mar", "Apr", "May", "Jun", "Jul", "Aug", "Sep", "Oct", "Nov", "Dec")

' find previous and current Year and month. If current month = jan then go back to Dec of the year before
    curMonth = Month(Date)
    curYear = Year(Date)
    prevYear = Year(Date)
    prevMonth = Month(Date) - 1
    
    If prevMonth = 0 Then
        prevMonth = 12
        prevYear = prevYear - 1
    End If
    
' count number of existing records for current year and month
'    If DCount("Month", "TTransactions", "month = '" & monthText(curMonth) & "' and year = " & curYear) = 0 Then

  If DCount("Month", "tTransactions", "month = " & curMonth & " and year = " & curYear) < 2 Then
        'if current month and year does not exist in table
        ' open table and find last months record
        Set rst = db.OpenRecordset("tTransactions", dbOpenDynaset)
        rst.FindFirst "month = " & prevMonth & " and year = " & prevYear
        ' open table again to write a new record
        Set rst2 = db.OpenRecordset("tTransactions", dbOpenDynaset)
        Do Until rst.NoMatch  ' loop through all records meeting the criteria
            rst2.AddNew
                rst2![TelNo] = rst![TelNo]
                rst2!Year = curYear
                rst2!Month = curMonth
                rst2!Rental = rst!Rental
                rst2![Fees] = rst![Fees]
                rst2![Vat] = rst![Vat]
            rst2.Update
            rst.FindNext "month = " & prevMonth & " and year = " & prevYear
        Loop
        rst.Close
        rst2.Close
        Set rst = Nothing
        Set rst2 = Nothing
    End If
    Set db = Nothing

End Sub

Function CallButton5()

Call Button5_Click

End Function

I have put in an autonumber to assign sequential numbers to the records as they are entered. This I had hoped would allow me to sort my query by autonumber, however if the findfirst keeps finding the records in ascending order then I'm lost. Is there any way to get round this?

Thank you Kindly

 

[Mile-O]

Why do you have fields called Month and Year - despite both of them being reserved words in Access, they aren't very helpful. You are always best to store a date, rather than month and year, as you can easily break down a date with the Month() and Year() functions and you can sort a date field, and this time ascending order will be exactly what you want.

 

[Poppy]

Hi SJ

I have carried out your suggestions about the dates and made them into one field, however I am still experiencing the same sorting problem. And it does not even help when I use a query to try and retrieve the data.

Eg I have this table:

Cell Phone Number	Date		Rental	   Total Fees 	
+2725680124	        2004-Dec-02	0.00	   624.04	       
+2728233795	        2004-Dec-02	0.00	   624.04	       
+2728246914	        2004-Dec-02	0.00	   624.04	       
+2725525419	        2004-Dec-02     0.00	   624.04	       
+2725680139	        2004-Dec-02	0.00	   624.04

I have included an autonumber to sort the query by the "Autonumber", however the problem is that when my code is looking for the first record for the previous month(Dec) instead of picking Cellno. +2725680124 and writing it as a new record, it looks for the records in ascending order according to the Cellnos.

In other words it gives me this order which is incorrect: Ascending order according to Cellno.

Cell Phone Number	Date	       Rental	   Total Fees 	
+2725525419	        2004-Dec-02	0.00	   624.04	  
+2725680124	        2004-Dec-02	0.00	   624.04	       
+2725680139	        2004-Dec-02     0.00	   624.04	 
+2728233795	        2004-Dec-02	0.00	   624.04	       
+2728246914	        2004-Dec-02     0.00	   624.04

I think the problem can only be fixed in my code. Please please help. I'm loosing hair fast. Seriously, I keep having to clean my shoulders.

Kind Regards

 

[boblarson]

Sorry to break the news to you, but an Autonumber does NOT guarantee you consecutive numbers. It ONLY guarantees that the number is UNIQUE. So, you should not use an autonumber to create a sort field as it will NOT work.