Updating a Subforms resordsource

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi Guys,

If I wanted to have a form that has a subform on it that is used to show
fields from a search engine type query, how do I make the subforms record
source from an activation button (on click). Do you know of existing seach
engine type formms that have a subform on it.??

I currently have to append the results of the query inot a table and then
have the table on a seperate form to display the results of the query. I
don't think this is the best way!!
cansomeone help me!!

All help truly thankful !!

Cheers
 
Ian, see:
Search form
at:
http://allenbrowne.com/ser-62.html

The article explains how to create a search form that responds to only the
boxes where the user entered some criteria, handling different fields types,
wild cards, ranges, ...

There's a downloadable example that works with Access 2000 and later.
 
Yes I saw that Allen, but in your example you use a table for the record
source and filter from that table. What if you had to firstly create a query
from several tables and then update a recordsource fior a subform to show the
results... How would you do that?? Or am i just doing it wrong??

Many thanks for your input !! I have taken a copy of your search filter!!
 
No problem with that approach.

You can build the entire SQL statement, and assign it to the RecordSource of
the form:
Dim strSql As String
strSql = "SELECT Invoice.* FROM tblInvoice INNER JOIN tblInvoiceDetail
WHERE CustomerID = 99;"
Me.[Sub1].Form.RecordSource = strSql

If you do want to change the saved query named Query1, build the query
string, and then:
dbEngine(0)(0).QueryDefs("Query1").SQL = strSql
Then requery the subform if it is bound to that query:
Me.[Sub1].Form.Requery
 
so you are saying to place this code in a activation button (say on click)?.
Would you assign the strsql to the the form or the subform??

Allen Browne said:
No problem with that approach.

You can build the entire SQL statement, and assign it to the RecordSource of
the form:
Dim strSql As String
strSql = "SELECT Invoice.* FROM tblInvoice INNER JOIN tblInvoiceDetail
WHERE CustomerID = 99;"
Me.[Sub1].Form.RecordSource = strSql

If you do want to change the saved query named Query1, build the query
string, and then:
dbEngine(0)(0).QueryDefs("Query1").SQL = strSql
Then requery the subform if it is bound to that query:
Me.[Sub1].Form.Requery

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

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

Ian Shaw said:
Yes I saw that Allen, but in your example you use a table for the record
source and filter from that table. What if you had to firstly create a
query
from several tables and then update a recordsource fior a subform to show
the
results... How would you do that?? Or am i just doing it wrong??

Many thanks for your input !! I have taken a copy of your search filter!!
Click to expand...
Click to expand...
 
Yes, the Click event procedure of a command button on the main form.

The example assumes the subform is named Sub1. Change that to whatever yours
is named.

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

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

Ian Shaw said:
so you are saying to place this code in a activation button (say on
click)?.
Would you assign the strsql to the the form or the subform??

Allen Browne said:
No problem with that approach.

You can build the entire SQL statement, and assign it to the RecordSource
of
the form:
Dim strSql As String
strSql = "SELECT Invoice.* FROM tblInvoice INNER JOIN
tblInvoiceDetail
WHERE CustomerID = 99;"
Me.[Sub1].Form.RecordSource = strSql

If you do want to change the saved query named Query1, build the query
string, and then:
dbEngine(0)(0).QueryDefs("Query1").SQL = strSql
Then requery the subform if it is bound to that query:
Me.[Sub1].Form.Requery

Ian Shaw said:
Yes I saw that Allen, but in your example you use a table for the
record
source and filter from that table. What if you had to firstly create a
query
from several tables and then update a recordsource fior a subform to
show
the
results... How would you do that?? Or am i just doing it wrong??

Many thanks for your input !! I have taken a copy of your search
filter!!

:

Ian, see:
Search form
at:
http://allenbrowne.com/ser-62.html

The article explains how to create a search form that responds to only
the
boxes where the user entered some criteria, handling different fields
types,
wild cards, ranges, ...

There's a downloadable example that works with Access 2000 and later.


If I wanted to have a form that has a subform on it that is used to
show
fields from a search engine type query, how do I make the subforms
record
source from an activation button (on click). Do you know of
existing
seach
engine type formms that have a subform on it.??

I currently have to append the results of the query inot a table and
then
have the table on a seperate form to display the results of the
query.
I
don't think this is the best way!!
cansomeone help me!!
Click to expand...
Click to expand...
Click to expand...
 
Many hnaks, Allen that worked like a bought one!! You're a genious!!

Allen, Where you said, "If you do want to change the saved query named
Query1, build the query
string, and then:
dbEngine(0)(0).QueryDefs("Query1").SQL = strSql
Then requery the subform if it is bound to that query:
Me.[Sub1].Form.Requery
Click to expand...
Click to expand...

This is obviously another way of doing the " Me.[Sub1].Form.RecordSource =
strSql" but where do you place this code?? I have never seen this used like
this before??

More info please Allen!!


Allen Browne said:
Yes, the Click event procedure of a command button on the main form.

The example assumes the subform is named Sub1. Change that to whatever yours
is named.

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

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

Ian Shaw said:
so you are saying to place this code in a activation button (say on
click)?.
Would you assign the strsql to the the form or the subform??

Allen Browne said:
No problem with that approach.

You can build the entire SQL statement, and assign it to the RecordSource
of
the form:
Dim strSql As String
strSql = "SELECT Invoice.* FROM tblInvoice INNER JOIN
tblInvoiceDetail
WHERE CustomerID = 99;"
Me.[Sub1].Form.RecordSource = strSql

If you do want to change the saved query named Query1, build the query
string, and then:
dbEngine(0)(0).QueryDefs("Query1").SQL = strSql
Then requery the subform if it is bound to that query:
Me.[Sub1].Form.Requery

Yes I saw that Allen, but in your example you use a table for the
record
source and filter from that table. What if you had to firstly create a
query
from several tables and then update a recordsource fior a subform to
show
the
results... How would you do that?? Or am i just doing it wrong??

Many thanks for your input !! I have taken a copy of your search
filter!!

:

Ian, see:
Search form
at:
http://allenbrowne.com/ser-62.html

The article explains how to create a search form that responds to only
the
boxes where the user entered some criteria, handling different fields
types,
wild cards, ranges, ...

There's a downloadable example that works with Access 2000 and later.


If I wanted to have a form that has a subform on it that is used to
show
fields from a search engine type query, how do I make the subforms
record
source from an activation button (on click). Do you know of
existing
seach
engine type formms that have a subform on it.??

I currently have to append the results of the query inot a table and
then
have the table on a seperate form to display the results of the
query.
I
don't think this is the best way!!
cansomeone help me!!
Click to expand...
Click to expand...
Click to expand...
 
You can place that code in any event that suits, e.g. the Click event
procedure of a command button.

I don't recommend that for general use. It is not as flexible. But it is a
trick that occassionally gets you out of trouble, e.g. where you need to
change the source for a subreport, and can't do that in its Open event.

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

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

Ian Shaw said:
Many hnaks, Allen that worked like a bought one!! You're a genious!!

Allen, Where you said, "If you do want to change the saved query named
Query1, build the query
string, and then:
dbEngine(0)(0).QueryDefs("Query1").SQL = strSql
Then requery the subform if it is bound to that query:
Me.[Sub1].Form.Requery
Click to expand...
Click to expand...

This is obviously another way of doing the " Me.[Sub1].Form.RecordSource =
strSql" but where do you place this code?? I have never seen this used
like
this before??

More info please Allen!!


Allen Browne said:
Yes, the Click event procedure of a command button on the main form.

The example assumes the subform is named Sub1. Change that to whatever
yours
is named.

Ian Shaw said:
so you are saying to place this code in a activation button (say on
click)?.
Would you assign the strsql to the the form or the subform??

:

No problem with that approach.

You can build the entire SQL statement, and assign it to the
RecordSource
of
the form:
Dim strSql As String
strSql = "SELECT Invoice.* FROM tblInvoice INNER JOIN
tblInvoiceDetail
WHERE CustomerID = 99;"
Me.[Sub1].Form.RecordSource = strSql

If you do want to change the saved query named Query1, build the query
string, and then:
dbEngine(0)(0).QueryDefs("Query1").SQL = strSql
Then requery the subform if it is bound to that query:
Me.[Sub1].Form.Requery

Yes I saw that Allen, but in your example you use a table for the
record
source and filter from that table. What if you had to firstly
create a
query
from several tables and then update a recordsource fior a subform to
show
the
results... How would you do that?? Or am i just doing it wrong??

Many thanks for your input !! I have taken a copy of your search
filter!!

:

Ian, see:
Search form
at:
http://allenbrowne.com/ser-62.html

The article explains how to create a search form that responds to
only
the
boxes where the user entered some criteria, handling different
fields
types,
wild cards, ranges, ...

There's a downloadable example that works with Access 2000 and
later.


If I wanted to have a form that has a subform on it that is used
to
show
fields from a search engine type query, how do I make the
subforms
record
source from an activation button (on click). Do you know of
existing
seach
engine type formms that have a subform on it.??

I currently have to append the results of the query inot a table
and
then
have the table on a seperate form to display the results of the
query.
I
don't think this is the best way!!
cansomeone help me!!
Click to expand...
Click to expand...
Click to expand...
 
Many thanks Allen you have helped me heaps!!
Allen Browne said:
You can place that code in any event that suits, e.g. the Click event
procedure of a command button.

I don't recommend that for general use. It is not as flexible. But it is a
trick that occassionally gets you out of trouble, e.g. where you need to
change the source for a subreport, and can't do that in its Open event.

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

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

Ian Shaw said:
Many hnaks, Allen that worked like a bought one!! You're a genious!!

Allen, Where you said, "If you do want to change the saved query named
Query1, build the query
string, and then:
dbEngine(0)(0).QueryDefs("Query1").SQL = strSql
Then requery the subform if it is bound to that query:
Me.[Sub1].Form.Requery
Click to expand...

This is obviously another way of doing the " Me.[Sub1].Form.RecordSource =
strSql" but where do you place this code?? I have never seen this used
like
this before??

More info please Allen!!


Allen Browne said:
Yes, the Click event procedure of a command button on the main form.

The example assumes the subform is named Sub1. Change that to whatever
yours
is named.

so you are saying to place this code in a activation button (say on
click)?.
Would you assign the strsql to the the form or the subform??

:

No problem with that approach.

You can build the entire SQL statement, and assign it to the
RecordSource
of
the form:
Dim strSql As String
strSql = "SELECT Invoice.* FROM tblInvoice INNER JOIN
tblInvoiceDetail
WHERE CustomerID = 99;"
Me.[Sub1].Form.RecordSource = strSql

If you do want to change the saved query named Query1, build the query
string, and then:
dbEngine(0)(0).QueryDefs("Query1").SQL = strSql
Then requery the subform if it is bound to that query:
Me.[Sub1].Form.Requery

Yes I saw that Allen, but in your example you use a table for the
record
source and filter from that table. What if you had to firstly
create a
query
from several tables and then update a recordsource fior a subform to
show
the
results... How would you do that?? Or am i just doing it wrong??

Many thanks for your input !! I have taken a copy of your search
filter!!

:

Ian, see:
Search form
at:
http://allenbrowne.com/ser-62.html

The article explains how to create a search form that responds to
only
the
boxes where the user entered some criteria, handling different
fields
types,
wild cards, ranges, ...

There's a downloadable example that works with Access 2000 and
later.


If I wanted to have a form that has a subform on it that is used
to
show
fields from a search engine type query, how do I make the
subforms
record
source from an activation button (on click). Do you know of
existing
seach
engine type formms that have a subform on it.??

I currently have to append the results of the query inot a table
and
then
have the table on a seperate form to display the results of the
query.
I
don't think this is the best way!!
cansomeone help me!!
Click to expand...
Click to expand...
Click to expand...
 
Back
Top