Error in SQL statement

[BenSteckler]

Hi Everyone,

I have a silly question.

I have a table that has a list of products and their revision numbers. I am attempting to write a code that will scroll through the records in the table to find some specific data.

I have set up a SQL statement that will query the table and filter it to only the records that match a specific product (The value is stored in HiddenSKUNumber).

But when I open the recordset, it shows only one matching record (the table actually contains 4 matching records so I know there is an error.) It only shows the first matching record. It ignores the remining 3 records.

I have double checked the values and they are all the same type and do not contain extra spaces. I am at a total loss.

Here is a sample of my code. Can some one assist me with this problem?

[COLOR=blue]
        Dim dbs As Database
        Dim rs As Recordset
        Dim sql As String
        
        sql = "SELECT * from [tbl: Revision] where SKU = " & Forms!Products!HiddenSKUNumber
        'SKU is an Int and so is HiddenSKUNumber
        Set dbs = CurrentDb
        Set rs = dbs.OpenRecordset(sql)
        
        Dim HighestNumber As Integer, HighestVersion As Integer
        HighestVersion = 1
        For i = 1 To rs.RecordCount
        'This is where the error occurs. 
        'RecordCount should equal 4, but it is only finding one.
            If rs!ThisVersion > HighestVersion Then
                HighestVersion = rs!ThisVersion
                HighestNumber = rs!RevisionAuto
            End If
            rs.MoveNext
        Next i

[/color]


Thanks again.
BDS

 

[Nouba]

If you use a bound form for the table [tbl: Revision], you can simply change it's Filter property. I.e.

Me.Filter = "SKU = " & Forms("Products")!HiddenSKUNumber
Me.FilterOn = True

In the navigationbar you can see the number of affected records or you can simply set the control source of an unbound textfield control to =Count("*") .

 

[itsmarkdavies]

rs.RecordCount tends not to return the correct count unless you do a MoveLast first. If I were you i`d put the RecordCount into a variable and amend your code something like this :-

Dim HighestNumber As Integer
Dim HighestVersion As Integer
Dim RecCount int

rs.Movelast
RecCount = rs.RecordCount
rs.MoveFirst

HighestVersion = 1

For i = 1 To RecCount

...... do your stuff

Hope this helps.