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

  • Thread starter Thread starter tlyczko
  • Start date Start date
T

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
 
Try a filter after the recordset is opened.

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

Hope this helps
 
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
Click to expand...
Click to expand...
 
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
 
tlyczko said:
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.
Click to expand...


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
 
Marshall said:
Until you do a MoveLast, RecordCount is only guaranteed to
be non-zero when there are records and zero if no records
were found.
Click to expand...

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.
Click to expand...

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
Click to expand...

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
 
Back
Top