By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
428,659 Members | 940 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 428,659 IT Pros & Developers. It's quick & easy.

forms/subforms with DAO ?

P: n/a
Hello.

How can I synchronize subforms content with current record in master form,
if both form and subform are based on DAO code ?
I assigned DAO recordset to forms by using QueryDef, on Load event.

Thanks...

Nov 13 '05 #1
Share this Question
Share on Google+
8 Replies


P: n/a
On Sun, 1 May 2005 18:30:58 +0200, "Zlatko Matić"
<zl***********@sb.t-com.hr> wrote:

You're making it too hard for yourself. Remember how that DAO
recordset was created in the first place: by running a querydef. So
assign that querydef to the form, and another one to the subform, and
use LinkMasterFields and LinkChildFields properties to link the two.
Refer to the Northwind sample application, Orders form, for an
example.

-Tom.

Hello.

How can I synchronize subforms content with current record in master form,
if both form and subform are based on DAO code ?
I assigned DAO recordset to forms by using QueryDef, on Load event.

Thanks...


Nov 13 '05 #2

P: n/a
Unfortunately, I can't do that, because, QueryDef is a saved pass-through
query. Pass-through query can't be Record Source of a subform...
Therefore I tried to do it programaticaly. It works, but I don't know how to
synchronize...

"Tom van Stiphout" <no*************@cox.net> je napisao u poruci interesnoj
grupi:3e********************************@4ax.com.. .
On Sun, 1 May 2005 18:30:58 +0200, "Zlatko Matić"
<zl***********@sb.t-com.hr> wrote:

You're making it too hard for yourself. Remember how that DAO
recordset was created in the first place: by running a querydef. So
assign that querydef to the form, and another one to the subform, and
use LinkMasterFields and LinkChildFields properties to link the two.
Refer to the Northwind sample application, Orders form, for an
example.

-Tom.

Hello.

How can I synchronize subforms content with current record in master form,
if both form and subform are based on DAO code ?
I assigned DAO recordset to forms by using QueryDef, on Load event.

Thanks...

Nov 13 '05 #3

P: n/a
Zlatko Matic wrote:
Unfortunately, I can't do that, because, QueryDef is a saved pass-through
query. Pass-through query can't be Record Source of a subform...
Therefore I tried to do it programaticaly. It works, but I don't know how to
synchronize...


One thing I've done in the past is have a regular querydef based on
PTQ. Say your PTQ against MS SQL/Oracle/whatever is ptqApples. Set up
a reguular querydef called qryApples which is simply Select * from
ptqApples.

I don't recall having tried it, but I would think this way you could
pass parameters and use child/parent fields to synchronize that way.
I've done something similar when I found Access 97 would not allow me to
export from a PTQ.

--
Tim http://www.ucs.mun.ca/~tmarshal/
^o<
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "Whatcha doin?" - Ditto "TIM-MAY!!" - Me
Nov 13 '05 #4

P: n/a
Hello.
Thank you for your answer.
In fact that is exactly what I tried to accomplish. I have a complex
pass-through query, and then a regular select query that filtrates the
result of PTQ by start/end date criteria, for example...But, Access doesn't
allow to use it as record source of subforms, with or without linking
master/child fields...Somehow it knowsw that uderlying query is PTQ !?
Anyway, I successfully assigned recordsets to subforms and comboboxes by
standard module code yesterday and now I will try to synchronize it...A lot
of work, but I think that increased performance is a worthy goal :) Regular
JET queries on linked tables were very, very slow...
I am curious, when I nest a pass-through query inside a regular JET query
and for example just filtrate start/end date criteria, does JET first
retrieve all results from the server and then filtrates, or it can
comunicate with server so that server knows that results will be filtrated?

Bye!


"Tim Marshall" <TI****@PurplePandaChasers.Moertherium> je napisao u poruci
interesnoj grupi:d5**********@coranto.ucs.mun.ca...
Zlatko Matic wrote:
Unfortunately, I can't do that, because, QueryDef is a saved pass-through
query. Pass-through query can't be Record Source of a subform...
Therefore I tried to do it programaticaly. It works, but I don't know how
to synchronize...


One thing I've done in the past is have a regular querydef based on PTQ.
Say your PTQ against MS SQL/Oracle/whatever is ptqApples. Set up a
reguular querydef called qryApples which is simply Select * from
ptqApples.

I don't recall having tried it, but I would think this way you could pass
parameters and use child/parent fields to synchronize that way. I've done
something similar when I found Access 97 would not allow me to export from
a PTQ.

--
Tim http://www.ucs.mun.ca/~tmarshal/
^o<
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "Whatcha doin?" - Ditto "TIM-MAY!!" - Me

Nov 13 '05 #5

P: n/a
Zlatko Matic wrote:
In fact that is exactly what I tried to accomplish. I have a complex
pass-through query, and then a regular select query that filtrates the
result of PTQ by start/end date criteria, for example...But, Access doesn't
allow to use it as record source of subforms, with or without linking
master/child fields...Somehow it knowsw that uderlying query is PTQ !?
That's weird. I use PTQs as recordsources for subforms all the time.
Usually in datasheet format. What I don't think I've done is have a
master/child field set up for them.
I am curious, when I nest a pass-through query inside a regular JET query
and for example just filtrate start/end date criteria, does JET first
retrieve all results from the server and then filtrates, or it can
comunicate with server so that server knows that results will be filtrated?


Good question. In my arrangements as described above, I frequently
hasve pop up menus using standard Access sort and filter menu choices.
I've never really given it great thought, but I always assumed that the
basic recordset is returned as per a pass through query and then
processed with an order by and/or filter using Jet. One of the big
pains with Oracle long data types (memo field) is that it can't be
searched. I get around this by using filters and Access search once the
PTQ is tailored as much as possible before getting into the long data type.
--
Tim http://www.ucs.mun.ca/~tmarshal/
^o<
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "Whatcha doin?" - Ditto "TIM-MAY!!" - Me
Nov 13 '05 #6

P: n/a
Hello, Tim.

You were right, that was my mistake. I didn't left linking master/child
fields....
Now when I left linking properties, it should work, but still I have a
problem with deeper hierarchy levels of subforms. Only first two or three
levels are successfuly queried and displayed, while deeper subforms are
not... An error apears which indicates that the problem is something about
ODBC driver manager.
What is your suggestion ? Cache?

Bye.

"Tim Marshall" <TI****@PurplePandaChasers.Moertherium> je napisao u poruci
interesnoj grupi:d5**********@coranto.ucs.mun.ca...
Zlatko Matic wrote:
In fact that is exactly what I tried to accomplish. I have a complex
pass-through query, and then a regular select query that filtrates the
result of PTQ by start/end date criteria, for example...But, Access
doesn't allow to use it as record source of subforms, with or without
linking master/child fields...Somehow it knowsw that uderlying query is
PTQ !?


That's weird. I use PTQs as recordsources for subforms all the time.
Usually in datasheet format. What I don't think I've done is have a
master/child field set up for them.
I am curious, when I nest a pass-through query inside a regular JET query
and for example just filtrate start/end date criteria, does JET first
retrieve all results from the server and then filtrates, or it can
comunicate with server so that server knows that results will be
filtrated?


Good question. In my arrangements as described above, I frequently hasve
pop up menus using standard Access sort and filter menu choices. I've
never really given it great thought, but I always assumed that the basic
recordset is returned as per a pass through query and then processed with
an order by and/or filter using Jet. One of the big pains with Oracle
long data types (memo field) is that it can't be searched. I get around
this by using filters and Access search once the PTQ is tailored as much
as possible before getting into the long data type.
--
Tim http://www.ucs.mun.ca/~tmarshal/
^o<
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "Whatcha doin?" - Ditto "TIM-MAY!!" - Me

Nov 13 '05 #7

P: n/a
Well, I was playing all the afternoon and found that it works if I remove
all parameter (criteria) from the query. If I put any criteria, Access
realizes that underlying query is a pass-through query and then an error
apears. Therofore I must remove all criteria, for example start and end date
criteria.
I know how to programatically synchronize subforms with master forms, by
putting FindFirst on current event of master forms, but it will not be
enough because I have to filter data..So, how can I filter data in such
subforms ? Is there any way for filtering such recordset ? MAybe to clone
recordset and filter cloned recordset ?
I know that there is Filter property of ADO recordset, but I think that
there is no Filter property for DAO recordset (?). If that is true, is it
maybe possible to have a mixed DAO/ADO situation, for example to use DAO to
create a recordset from pass-through query and than filtrate somehow by ADO
?

Greetings,

Zlatko

"Zlatko Matic" <zl***********@sb.t-com.hr> je napisao u poruci interesnoj
grupi:d5**********@ss405.t-com.hr...
Hello, Tim.

You were right, that was my mistake. I didn't left linking master/child
fields....
Now when I left linking properties, it should work, but still I have a
problem with deeper hierarchy levels of subforms. Only first two or three
levels are successfuly queried and displayed, while deeper subforms are
not... An error apears which indicates that the problem is something about
ODBC driver manager.
What is your suggestion ? Cache?

Bye.

"Tim Marshall" <TI****@PurplePandaChasers.Moertherium> je napisao u poruci
interesnoj grupi:d5**********@coranto.ucs.mun.ca...
Zlatko Matic wrote:
In fact that is exactly what I tried to accomplish. I have a complex
pass-through query, and then a regular select query that filtrates the
result of PTQ by start/end date criteria, for example...But, Access
doesn't allow to use it as record source of subforms, with or without
linking master/child fields...Somehow it knowsw that uderlying query is
PTQ !?


That's weird. I use PTQs as recordsources for subforms all the time.
Usually in datasheet format. What I don't think I've done is have a
master/child field set up for them.
I am curious, when I nest a pass-through query inside a regular JET
query and for example just filtrate start/end date criteria, does JET
first retrieve all results from the server and then filtrates, or it can
comunicate with server so that server knows that results will be
filtrated?


Good question. In my arrangements as described above, I frequently hasve
pop up menus using standard Access sort and filter menu choices. I've
never really given it great thought, but I always assumed that the basic
recordset is returned as per a pass through query and then processed with
an order by and/or filter using Jet. One of the big pains with Oracle
long data types (memo field) is that it can't be searched. I get around
this by using filters and Access search once the PTQ is tailored as much
as possible before getting into the long data type.
--
Tim http://www.ucs.mun.ca/~tmarshal/
^o<
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "Whatcha doin?" - Ditto "TIM-MAY!!" - Me


Nov 13 '05 #8

P: n/a
Well, I was playing all the afternoon and found that it works if I remove
all parameter (criteria) from the query. If I put any criteria, Access
realizes that underlying query is a pass-through query and then an error
apears. Therofore I must remove all criteria, for example start and end date
criteria.
I know how to programatically synchronize subforms with master forms, by
putting FindFirst on current event of master forms, but it will not be
enough because I have to filter data..So, how can I filter data in such
subforms ? Is there any way for filtering such recordset ? MAybe to clone
recordset and filter cloned recordset ?
I know that there is Filter property of ADO recordset, but I think that
there is no Filter property for DAO recordset (?). If that is true, is it
maybe possible to have a mixed DAO/ADO situation, for example to use DAO to
create a recordset from pass-through query and than filtrate somehow by ADO
?

Greetings,

Zlatko

"Tim Marshall" <TI****@PurplePandaChasers.Moertherium> je napisao u poruci
interesnoj grupi:d5**********@coranto.ucs.mun.ca...
Zlatko Matic wrote:
In fact that is exactly what I tried to accomplish. I have a complex
pass-through query, and then a regular select query that filtrates the
result of PTQ by start/end date criteria, for example...But, Access
doesn't allow to use it as record source of subforms, with or without
linking master/child fields...Somehow it knowsw that uderlying query is
PTQ !?


That's weird. I use PTQs as recordsources for subforms all the time.
Usually in datasheet format. What I don't think I've done is have a
master/child field set up for them.
I am curious, when I nest a pass-through query inside a regular JET query
and for example just filtrate start/end date criteria, does JET first
retrieve all results from the server and then filtrates, or it can
comunicate with server so that server knows that results will be
filtrated?


Good question. In my arrangements as described above, I frequently hasve
pop up menus using standard Access sort and filter menu choices. I've
never really given it great thought, but I always assumed that the basic
recordset is returned as per a pass through query and then processed with
an order by and/or filter using Jet. One of the big pains with Oracle
long data types (memo field) is that it can't be searched. I get around
this by using filters and Access search once the PTQ is tailored as much
as possible before getting into the long data type.
--
Tim http://www.ucs.mun.ca/~tmarshal/
^o<
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "Whatcha doin?" - Ditto "TIM-MAY!!" - Me

Nov 13 '05 #9

This discussion thread is closed

Replies have been disabled for this discussion.