Using records selected within a subform

[Rose B]

I have a subform which shows records selected from a table depending upon the
selections made on the form. (The underlying query for the subform is quite
complex as there are about 18 selections that can be made by the user).

What I want to be able to do is to use the selection results of the subform
in a click event for a button on the main form. How can I get at the record
results of the subform in VBA? (I am sure that there must be an easy way!).

Thanks in advance

 

[Allen Browne]

Not sure if this does what you are after:
Search form - Handle many optional criteria
at:
http://allenbrowne.com/ser-62.html

The article demonstrates how to build a form where the user can enter
criteria in any number of unbound boxes, and the results are displayed. It
does this by building the filter string from whichever boxes the user
chooses. It explains how you could use the same result to filter a report
too.

If that sounds like what you need, download the sample database, and spend a
few minutes pulling it apart to see how the code works.

 

[Rose B]

Don't think it does Allen - the article seems to discuss the form/subform
that I have already constructed but what I want to do now is to use the
results (in a record set?) that are displayed in the subform within VBA - so
for example, the subform will show a number of people that fulfil the
criteria, I then want to go through these records in VBA to create an e-mail
for all those who have an e-mail address. I have written the code to do what
I want to do with the data - I just cannot seem to access the result set.

Hope that makes sense - thanks for coming back so quickly!

 

[Allen Browne]

Could you loop through the RecordsetClone of your subform?

 

[Paolo]

Hi Rose B,

if you wanna the entire recordset on which is based your subform you can use
a recordsetclone. e.g.

dim rec as recordset

Set rec = Forms!yourform!yoursubform.Form.RecordsetClone

In this way rec contains all the records in the recordsource of your
subform. rec is a normal recordset and you can use it as a usual recordset.

HTH Paolo

 

[Rose B]

Well, that's what I wondered, but I think I must have got something wrong - I
tried to do it by the following statement... but it complained!

myRS.Open Forms!frmPeopleSearch.[frmPeople subform].RecordsetClone,
cnn1, adOpenForwardOnly, adLockReadOnly

(If I repeat the query by putting the relevant select stament into a mySQL
field and open that instead I have got it to work, but the statement is very
long and I am worried that if the query changes I will forget to change the
VB)

 

[Allen Browne]

The default recordset in Access is most likely to be DAO, so:
Dim rs As DAO.Recordset
Set rs = Forms!Form1!Sub1.Form.RecordsetClone
Do While Not rs.EOF
rs.MoveNext
Loop

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Well, that's what I wondered, but I think I must have got something
wrong - I
tried to do it by the following statement... but it complained!

myRS.Open Forms!frmPeopleSearch.[frmPeople subform].RecordsetClone,
cnn1, adOpenForwardOnly, adLockReadOnly

(If I repeat the query by putting the relevant select stament into a mySQL
field and open that instead I have got it to work, but the statement is
very
long and I am worried that if the query changes I will forget to change
the
VB)

Could you loop through the RecordsetClone of your subform?>

 

[Rose B]

Thanks Allen and Paolo!!! Just outting the 'finishing touches' to the code
but can see that it will work just great.

The default recordset in Access is most likely to be DAO, so:
Dim rs As DAO.Recordset
Set rs = Forms!Form1!Sub1.Form.RecordsetClone
Do While Not rs.EOF
rs.MoveNext
Loop

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Well, that's what I wondered, but I think I must have got something
wrong - I
tried to do it by the following statement... but it complained!

myRS.Open Forms!frmPeopleSearch.[frmPeople subform].RecordsetClone,
cnn1, adOpenForwardOnly, adLockReadOnly

(If I repeat the query by putting the relevant select stament into a mySQL
field and open that instead I have got it to work, but the statement is
very
long and I am worried that if the query changes I will forget to change
the
VB)

Could you loop through the RecordsetClone of your subform?>

 

[ranchodondo]

Allen,
I have a situation very similar to this but more complex. I already use the
strWhere concept in all my generated queries.

I have a form which allows the user to select one customer or all customers
AND all dates or a range of dates AND one PO# or all PO#s.

The complexity comes when they ALSO select one type OR all types OR (type =
1 or type = 2) AND select one product OR all products OR (product= A or
product = B or product = C).

So the complexity of a generated query is knowing when they select the
specific choice (not the all or one) because you must have a query like this:

Customer = all and PO# = all and type = 1 and product = A
OR
Customer = all and PO# = all and type = 2 and product = A
OR
Customer = all and PO# = all and type = 1 and product = B
OR
Customer = all and PO# = all and type = 2 and product = B
OR
Customer = all and PO# = all and type = 1 and product = C
OR
Customer = all and PO# = all and type = 2 and product = C

Any ideas? Thanks in advance.

 

[John Spencer]

If they select ALL then you would not include that in the filter string you
are building.

If they are selecting multiple options then use the in construct when you
build the filter string. When you do that you end up with

WHERE [TYPE] in (1,2) and [PRODUCT] IN ("A","B","C")

Since we can't see exactly what you are doing, this is the best bit of
guidance I can give you.

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

 

[ranchodondo]

Perfect! Thanks (I feel like a dummy when I forget something as simple as
"IN")!

If they select ALL then you would not include that in the filter string you
are building.

If they are selecting multiple options then use the in construct when you
build the filter string. When you do that you end up with

WHERE [TYPE] in (1,2) and [PRODUCT] IN ("A","B","C")

Since we can't see exactly what you are doing, this is the best bit of
guidance I can give you.

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

Allen,
I have a situation very similar to this but more complex. I already use the
strWhere concept in all my generated queries.

I have a form which allows the user to select one customer or all customers
AND all dates or a range of dates AND one PO# or all PO#s.

The complexity comes when they ALSO select one type OR all types OR (type =
1 or type = 2) AND select one product OR all products OR (product= A or
product = B or product = C).

So the complexity of a generated query is knowing when they select the
specific choice (not the all or one) because you must have a query like this:

Customer = all and PO# = all and type = 1 and product = A
OR
Customer = all and PO# = all and type = 2 and product = A
OR
Customer = all and PO# = all and type = 1 and product = B
OR
Customer = all and PO# = all and type = 2 and product = B
OR
Customer = all and PO# = all and type = 1 and product = C
OR
Customer = all and PO# = all and type = 2 and product = C

Any ideas? Thanks in advance.