Filtering a Recordset

I have a process where I extract a number into a variable and I need to filter a recordset by that number.  I cant seem to find a method to make this work, the recordset is a linked table.

'Set rst2 = CurrentDb.OpenRecordset("dbo_Proposal", dbOpenForwardOnly)

Set rst2 = CurrentDb.OpenRecordset("select * from JobTest WHERE [Proposal Number] = '[txtJob]';")

            If rst2![Proposal Number] = null Then
                Stop
            Else
                Stop
            End If

any ideas?
mjelecAsked:
Who is Participating?
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
"if I try to pass a variable from the recordset "
Not quite following ...

How about testing for no records first:



    Set rst2 = CurrentDb.OpenRecordset("select * from JobTest WHERE [Proposal Number] = " & Chr(34) & Me.[txtJob] & Chr(34))
    If rst2.RecordCount = 0 Then
        ' No records =do whatever
        Exit Function '  ?
    End If
   
    If IsNull(rst2![Proposal Number]) Then
        Stop
    Else
        Stop
    End If
   

mx
0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
try this:

Set rst2 = CurrentDb.OpenRecordset("select * from JobTest WHERE [Proposal Number] = " & Chr(34) & Me.[txtJob] & Chr(34) )

           If IsNull(rst2![Proposal Number])  Then
                Stop
            Else
                Stop
            End If
0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
Typo .... use this:

Set rst2 = CurrentDb.OpenRecordset("select * from JobTest WHERE [Proposal Number] = " & Chr(34) & Me.txtJob & Chr(34) )

mx
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
mjelecAuthor Commented:
well that sorta works it seems.

It returns no current record if I try to pass a variable from the recordset but the isnull doesnt seem to register it correctly.
0
 
mjelecAuthor Commented:
Thanks that works perfectly.
0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
cool. you are welcome ...

mx
0
 
mjelecAuthor Commented:
I also replaced the isnull with the check to see if it exists,

If rst2.RecordCount = 0 Then
                Stop
            Else
                Stop
            End If
0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
ok
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.