How do I get a subset of a recordset created from a form???

[tlyczko]

Hello, I have a form where the end user enters a date, chooses the
values for Me.txtCriteria, used in the select query below, then clicks
a button. The form button's onClick event has the code given below:

'select records from tblRecord corresponding to people and topic

strSQL = "SELECT (tblRecord.[Date Completed]), tblRecord.TopicID,
tblRecord.PersonID FROM tblRecord "
strSQL = strSQL & "WHERE (((tblRecord.PersonID) In (" & Me.txtCriteria
& ") AND ((tblRecord.TopicID)=" & Me.cboTopic & ")))"

Set rst_01 = CurrentDb.OpenRecordset(strSQL, dbOpenDynaset)

'move through each row of the created recordset
Do Until rst_01.EOF
lngPersonID = rst_01.PersonID
Debug.Print "rst1", rst_01(0), rst_01(1), rst_01(2)

'here I want to get a SUBSET of rst_01 corresponding to the current
value of lngPersonID,
'then evaluate each row within the subset and act according
'because I only want to append a record to rst_01 if and only if rst_01
'has NO records with Date Completed equal to the date entered on the
form,
'problem is there are multiple records with the same lngPersonID value
'within rst_01

rst_01.MoveNext
Loop
rst_01.Close
Set rst_01 = Nothing

I have tried the FilterField code from Access help like so:
Set rst_02 = FilterField(rst_01, rst_01.PersonID, lngPersonID)
(I copied the function verbatim into a module.)

I don't know how to convert the above select query to an ADO query or I
would try using ADO to do this...

Thank you, Tom

 

[Penguin]

Try a filter after the recordset is opened.

rst_01.Filter = "[Date Completed] = #" & Me!FormName!ControlName & "#"

Hope this helps

 

[Marshall Barton]

RIght, but don't forget to open the filtered recordset

With rst_01
.Filter = "PersonID = " & txtPersonID & _
" AND [Date Completed] = #" & Me.txtDateCompleted & "#"
Set rst2 = .OpenRecordset
If rst2.RecordCount > 1 Then
.AddNew
!PersonID = txtPersonID
![Date Completed] = Me.txtDateCompleted
. . .
.Update
End If
--
Marsh
MVP [MS Access]

Try a filter after the recordset is opened.

rst_01.Filter = "[Date Completed] = #" & Me!FormName!ControlName & "#"

Hope this helps

Hello, I have a form where the end user enters a date, chooses the
values for Me.txtCriteria, used in the select query below, then clicks
a button. The form button's onClick event has the code given below:

'select records from tblRecord corresponding to people and topic

strSQL = "SELECT (tblRecord.[Date Completed]), tblRecord.TopicID,
tblRecord.PersonID FROM tblRecord "
strSQL = strSQL & "WHERE (((tblRecord.PersonID) In (" & Me.txtCriteria
& ") AND ((tblRecord.TopicID)=" & Me.cboTopic & ")))"

Set rst_01 = CurrentDb.OpenRecordset(strSQL, dbOpenDynaset)

'move through each row of the created recordset
Do Until rst_01.EOF
lngPersonID = rst_01.PersonID
Debug.Print "rst1", rst_01(0), rst_01(1), rst_01(2)

'here I want to get a SUBSET of rst_01 corresponding to the current
value of lngPersonID,
'then evaluate each row within the subset and act according
'because I only want to append a record to rst_01 if and only if rst_01
'has NO records with Date Completed equal to the date entered on the
form,
'problem is there are multiple records with the same lngPersonID value
'within rst_01

rst_01.MoveNext
Loop
rst_01.Close
Set rst_01 = Nothing

I have tried the FilterField code from Access help like so:
Set rst_02 = FilterField(rst_01, rst_01.PersonID, lngPersonID)
(I copied the function verbatim into a module.)

I don't know how to convert the above select query to an ADO query or I
would try using ADO to do this...

Thank you, Tom

 

[tlyczko]

Hello Marshall,

Thank you for your help!!
Your reply broke my mental logjam.

I had tried Filter before but with only one value, not two.

BTW for some reason Filter always returns a recordset with only 1 row
or 0 rows, I don't know why, but this works for my purposes.

..Filter = "PersonID = " & lngPersonID & " AND IsNull([Date Completed])
= True"

..Filter = "PersonID = " & lngPersonID & " AND [Date Completed] = #" &
Me.txtDateCompleted & "#"

I created the above two filters and used your code to update or append
as necessary within my above-mentioned Do loop.
Thank you very much!!
tom

 

[Marshall Barton]

Hello Marshall,

Thank you for your help!!
Your reply broke my mental logjam.

I had tried Filter before but with only one value, not two.

BTW for some reason Filter always returns a recordset with only 1 row
or 0 rows, I don't know why, but this works for my purposes.

.Filter = "PersonID = " & lngPersonID & " AND IsNull([Date Completed])
= True"

.Filter = "PersonID = " & lngPersonID & " AND [Date Completed] = #" &
Me.txtDateCompleted & "#"

I created the above two filters and used your code to update or append
as necessary within my above-mentioned Do loop.

Glad I could help out.

Until you do a MoveLast, RecordCount is only guaranteed to
be non-zero when there are records and zero if no records
were found.

I just noticed I had an error, you said you only wanted to
add a record when there were no matches, but I had the If
condition all messed up. I should have checked if
RecordCount = 0, not >1.

If you want to match when the date is the same as the form
control or if the field is Null, you can do it with a single
filter by using yet another condition. The code would then
look like:

.Filter = "PersonID = " & txtPersonID & _
" AND ([Date Completed]=#" & Me.txtDateCompleted & _
"# OR [Date Completed] Is Null)"
Set rst2 = .OpenRecordset
If rst2.RecordCount = 0 Then
' add a new record

 

[tlyczko]

Until you do a MoveLast, RecordCount is only guaranteed to
be non-zero when there are records and zero if no records
were found.

Ohhhh...I didn't know that. Thank you. I don't think I have to worry
about this yet.

I just noticed I had an error, you said you only wanted to
add a record when there were no matches, but I had the If
condition all messed up. I should have checked if
RecordCount = 0, not >1.

I noticed this, thanks.

If you want to match when the date is the same as the form
control or if the field is Null, you can do it with a single
filter by using yet another condition. The code would then
look like:

.Filter = "PersonID = " & txtPersonID & _
" AND ([Date Completed]=#" & Me.txtDateCompleted & _
"# OR [Date Completed] Is Null)"
Set rst2 = .OpenRecordset
If rst2.RecordCount = 0 Then
' add a new record

Thanks. However, I want an update if Date Completed is null, but an
append if there is no record with the same value for Date Completed, so
using two filters seems to work for me.

Thank you,
tom