recordset filtering

[suburbanpsyco]

Hello

I am trying to filter my recordset based on a selection in a list box on my form. My original idea is

For loopCounter = 1 To rstBoiler.RecordCount
        
        If rstBoiler!boilerNumber = Me.lstBoiler Then
        
            I Will be executing code here.
        
        End If
        
        rstBoiler.MoveNext
    
    Next loopCounter

The line that is giving me problems is the if statement, for some reason this does not work. Pointers would be much apprciated. Also, is there a better way of doing this?

Cheers

 

[suburbanpsyco]

Thought id update. Ive attempted this

rstBoiler.Open "SELECT * FROM boilerDetail WHERE boilerDetail.boilerNumber = '" & Me.lstBoilers & "'", dbBoiler, adOpenKeyset, adLockOptimistic, adCmdText

As well as using the .filter property to construct an expression. Any ideas?

 

[MarkK]

Please be more specific describing the nature of a failure. In your first post you say that 'the if statement is the problem' and that 'it doesn't work.' This is not enough information for remote troubleshooting. Do you get a compile error, a runtime error, no error but wrong or unexpected result (describe)?
In your second post also you've shown only what you attempted and no info on how it failed.
Cheers,
Mark

 

[suburbanpsyco]

Well the if statement comeplete does not work. When a breakpoint is inserted, both fields show an equal value, but there is no descention into the if statment. It SHOULD be a fairly simple if statement, but there is no functionality out of it at all!

For the second, my SQL setup is producing a runtime error, im getting a data mismatch. the list box is made by the wizard, with the ID number of the boiler and the boiler name showing in the list. The table the recordset is comming from a lookup wizard linked to that table. It should be compatible, no?

Hope this helps, and thank you in advance for you help

 

[MarkK]

In your second post it appears that you might be passing a database reference to the ActiveConnection parameter of the Recordset.Open command. You have not shown your declaration of, or you assignment to, your dbBoiler variable. Is that a Connection object or a Connection String?

In your first post you seem to claim that the expression in the If statement evaluates to True but the block doesn't execute. I have never seen this happen and therefore have no tips as to how to solve it.

Hope this helps,

 

[VilaRestal]

Try using lstBoilers.Value in either case

Me.lstBoilers is the listbox control (a pointer to it)

Only in certain cases will access automatically realise you really mean its value

Also if boilerNumber is a number then it shouldn't be enclosed in quotation marks:

Dim rstBoiler As ADODB.Recordset
Set rstBoiler = New ADODB.Recordset
rstBoiler.Open "SELECT * FROM boilerDetail WHERE boilerNumber = " & lstBoilers.Value, CurrentProject.AccessConnection
If rstBoiler.RecordCount > 0 Then
[INDENT]'Code here
 
[/INDENT]End If

I'm guessing its an adp connected to sql server from the rst.Open method (rather than DAO's Set rst = CurrentDb.OpenRecordset)

 

[suburbanpsyco]

Thanks again for the help. Much appreciated!

*EDIT* worked like a charm. and now i feel stupid for leaving out the .value property