Hi,
I have two very similar forms each with a subform. The main form gets
a few fields from the user and passes this back to a query that the
subform is bound to. The requery is done when the user enters the
last qualifying field on the main form.
In one case this works fine, the subform shows the data the user wants
to update -- which means showing all the data put in previously (ie
showing this via the requery and the continuous option on the subform)
and standing by for input. In the other case, the requery refuses to
display in the continuous subform showing one blank record (really a
partial record). With the parent form filled in, I can manually
trigger the query and see it has the correct records.
Does anyone know of some obscure setting or restriction that I need to
tweak to get the blasted thing to work?
Thanks,
Dave 4 6843
I think your subform is bound to a query that contains criteria such as:
Forms!YourMainForm!SomeTextbox
and the problem is that this does not always load/stay up to date correctly.
Would it be possible to use the LinkMasterFields/LinkChildFields properties
of the subform instead? That should make life simpler if it is possible, and
it works for most things unless you are choosing a range rather a discreet
value (e.g. from start date to end date), or trying to get all values when
some of the controls in the linkage may be null.
Another alternative is to reassign the RecordSource of the subform in the
Current event of the main form. By creating a SQL string that concatenates
any literal values into the string, you can handle ranges, ignore nulls, and
get the exact result you want. You may need to check that Access does not
become overhelpful and decide to assign values to the
LinkMasterFields/LinkChildFields based on the new RecordSource. And you also
have to be careful to avoid cyclic dependencies, such as something in the
main form that is dependent on something in the subform. Conditional
formatting can also trigger this.
--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.
"Dave Boyd" <da**********@boeing.com> wrote in message
news:e7*************************@posting.google.co m... I have two very similar forms each with a subform. The main form gets a few fields from the user and passes this back to a query that the subform is bound to. The requery is done when the user enters the last qualifying field on the main form.
In one case this works fine, the subform shows the data the user wants to update -- which means showing all the data put in previously (ie showing this via the requery and the continuous option on the subform) and standing by for input. In the other case, the requery refuses to display in the continuous subform showing one blank record (really a partial record). With the parent form filled in, I can manually trigger the query and see it has the correct records.
Does anyone know of some obscure setting or restriction that I need to tweak to get the blasted thing to work?
Thanks,
Dave
"Allen Browne" <Al*********@SeeSig.Invalid> wrote in message news:<41**********************@per-qv1-newsreader-01.iinet.net.au>... I think your subform is bound to a query that contains criteria such as: Forms!YourMainForm!SomeTextbox and the problem is that this does not always load/stay up to date correctly.
Would it be possible to use the LinkMasterFields/LinkChildFields properties of the subform instead? That should make life simpler if it is possible, and it works for most things unless you are choosing a range rather a discreet value (e.g. from start date to end date), or trying to get all values when some of the controls in the linkage may be null.
Another alternative is to reassign the RecordSource of the subform in the Current event of the main form. By creating a SQL string that concatenates any literal values into the string, you can handle ranges, ignore nulls, and get the exact result you want. You may need to check that Access does not become overhelpful and decide to assign values to the LinkMasterFields/LinkChildFields based on the new RecordSource. And you also have to be careful to avoid cyclic dependencies, such as something in the main form that is dependent on something in the subform. Conditional formatting can also trigger this.
-- Allen Browne - Microsoft MVP. Perth, Western Australia. Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org.
"Dave Boyd" <da**********@boeing.com> wrote in message news:e7*************************@posting.google.co m... I have two very similar forms each with a subform. The main form gets a few fields from the user and passes this back to a query that the subform is bound to. The requery is done when the user enters the last qualifying field on the main form.
In one case this works fine, the subform shows the data the user wants to update -- which means showing all the data put in previously (ie showing this via the requery and the continuous option on the subform) and standing by for input. In the other case, the requery refuses to display in the continuous subform showing one blank record (really a partial record). With the parent form filled in, I can manually trigger the query and see it has the correct records.
Does anyone know of some obscure setting or restriction that I need to tweak to get the blasted thing to work?
Thanks,
Dave
Allen,
Yes, you are correct and I believe the criteria you mention would
allow me to use form/subform linked fields, but it's not obvious what
I should do. (I'm guessing the original form (that works) was
generated by an Access wizard -- possibly the former database
architect, but not likely -- and I'm trying to manually duplicate that
as an inexperienced form builder.)
On the main form I have a set of master fields. I want the subform to
display the subset of records equivalent to the select query where the
two fixed master fields are chosen by the user on the main form.
How would the linked fields approach be setup? I tried to eliminate
the query by linking both forms to the table and then linking the two
fields in the main form to the subform. The two fields don't actually
appear in the subform. There are actually row source queries on combo
boxes for some of the subform fields since they do double duty as
display slots for existing records and entry of new data for the next
user defined record. According to what I understand, when I enter the
linked master fields the form should display all related records?
This doesn't seem to work so I've still got some screwed up somewhere.
Dave
Hi Dave
I'm not really clear here - esp. about the combos in the subform. If you are
trying to use the non-bound column of the combo in the LinkChildFields of
the subform control, it might be better to a use a query as the RecordSource
of the subform, and include the combo's table so that the field you need to
link on is in the RecordSource.
If you need an example of how subforms work, open the Northwind sample
database, and look at the Customer Orders form. The first subform - Customer
Orders Subform1 - has CustomerID in both the LinkMasterFields and
LinkChildFields, so the subform shows the records matching the CustomerID in
the main form. The 2nd subform - Customer Orders Subform2 has its
LinkChildFields set to OrderID, and its LinkMasterFields set to
[Customer Orders Subform1].Form![OrderID]
That means that its records will match the OrderID in the other subform.
Hopefully that example demonstrates what needs to go into the
LinkChildFields/LinkMasterFields in your example. If you can get the fields
into the source query, it is simplest and most stable.
--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.
"Dave Boyd" <da**********@boeing.com> wrote in message
news:e7*************************@posting.google.co m... I think your subform is bound to a query that contains criteria such as: Forms!YourMainForm!SomeTextbox and the problem is that this does not always load/stay up to date correctly.
Would it be possible to use the LinkMasterFields/LinkChildFields properties of the subform instead? That should make life simpler if it is possible, and it works for most things unless you are choosing a range rather a discreet value (e.g. from start date to end date), or trying to get all values when some of the controls in the linkage may be null.
Another alternative is to reassign the RecordSource of the subform in the Current event of the main form. By creating a SQL string that concatenates any literal values into the string, you can handle ranges, ignore nulls, and get the exact result you want. You may need to check that Access does not become overhelpful and decide to assign values to the LinkMasterFields/LinkChildFields based on the new RecordSource. And you also have to be careful to avoid cyclic dependencies, such as something in the main form that is dependent on something in the subform. Conditional formatting can also trigger this.
"Dave Boyd" <da**********@boeing.com> wrote in message news:e7*************************@posting.google.co m... > > I have two very similar forms each with a subform. The main form gets > a few fields from the user and passes this back to a query that the > subform is bound to. The requery is done when the user enters the > last qualifying field on the main form. > > In one case this works fine, the subform shows the data the user wants > to update -- which means showing all the data put in previously (ie > showing this via the requery and the continuous option on the subform) > and standing by for input. In the other case, the requery refuses to > display in the continuous subform showing one blank record (really a > partial record). With the parent form filled in, I can manually > trigger the query and see it has the correct records. > > Does anyone know of some obscure setting or restriction that I need to > tweak to get the blasted thing to work? > > Thanks, > > Dave
Allen,
Yes, you are correct and I believe the criteria you mention would allow me to use form/subform linked fields, but it's not obvious what I should do. (I'm guessing the original form (that works) was generated by an Access wizard -- possibly the former database architect, but not likely -- and I'm trying to manually duplicate that as an inexperienced form builder.)
On the main form I have a set of master fields. I want the subform to display the subset of records equivalent to the select query where the two fixed master fields are chosen by the user on the main form.
How would the linked fields approach be setup? I tried to eliminate the query by linking both forms to the table and then linking the two fields in the main form to the subform. The two fields don't actually appear in the subform. There are actually row source queries on combo boxes for some of the subform fields since they do double duty as display slots for existing records and entry of new data for the next user defined record. According to what I understand, when I enter the linked master fields the form should display all related records? This doesn't seem to work so I've still got some screwed up somewhere.
Dave
"Allen Browne" <Al*********@SeeSig.Invalid> wrote in message news:<41**********************@per-qv1-newsreader-01.iinet.net.au>... Hi Dave
I'm not really clear here - esp. about the combos in the subform. If you are trying to use the non-bound column of the combo in the LinkChildFields of the subform control, it might be better to a use a query as the RecordSource of the subform, and include the combo's table so that the field you need to link on is in the RecordSource.
If you need an example of how subforms work, open the Northwind sample database, and look at the Customer Orders form. The first subform - Customer Orders Subform1 - has CustomerID in both the LinkMasterFields and LinkChildFields, so the subform shows the records matching the CustomerID in the main form. The 2nd subform - Customer Orders Subform2 has its LinkChildFields set to OrderID, and its LinkMasterFields set to [Customer Orders Subform1].Form![OrderID] That means that its records will match the OrderID in the other subform.
Hopefully that example demonstrates what needs to go into the LinkChildFields/LinkMasterFields in your example. If you can get the fields into the source query, it is simplest and most stable. -- Allen Browne - Microsoft MVP. Perth, Western Australia. Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org.
"Dave Boyd" <da**********@boeing.com> wrote in message news:e7*************************@posting.google.co m... I think your subform is bound to a query that contains criteria such as: Forms!YourMainForm!SomeTextbox and the problem is that this does not always load/stay up to date correctly.
Would it be possible to use the LinkMasterFields/LinkChildFields properties of the subform instead? That should make life simpler if it is possible, and it works for most things unless you are choosing a range rather a discreet value (e.g. from start date to end date), or trying to get all values when some of the controls in the linkage may be null.
Another alternative is to reassign the RecordSource of the subform in the Current event of the main form. By creating a SQL string that concatenates any literal values into the string, you can handle ranges, ignore nulls, and get the exact result you want. You may need to check that Access does not become overhelpful and decide to assign values to the LinkMasterFields/LinkChildFields based on the new RecordSource. And you also have to be careful to avoid cyclic dependencies, such as something in the main form that is dependent on something in the subform. Conditional formatting can also trigger this.
"Dave Boyd" <da**********@boeing.com> wrote in message news:e7*************************@posting.google.co m... > > I have two very similar forms each with a subform. The main form gets > a few fields from the user and passes this back to a query that the > subform is bound to. The requery is done when the user enters the > last qualifying field on the main form. > > In one case this works fine, the subform shows the data the user wants > to update -- which means showing all the data put in previously (ie > showing this via the requery and the continuous option on the subform) > and standing by for input. In the other case, the requery refuses to > display in the continuous subform showing one blank record (really a > partial record). With the parent form filled in, I can manually > trigger the query and see it has the correct records. > > Does anyone know of some obscure setting or restriction that I need to > tweak to get the blasted thing to work? > > Thanks, > > Dave
Allen,
Yes, you are correct and I believe the criteria you mention would allow me to use form/subform linked fields, but it's not obvious what I should do. (I'm guessing the original form (that works) was generated by an Access wizard -- possibly the former database architect, but not likely -- and I'm trying to manually duplicate that as an inexperienced form builder.)
On the main form I have a set of master fields. I want the subform to display the subset of records equivalent to the select query where the two fixed master fields are chosen by the user on the main form.
How would the linked fields approach be setup? I tried to eliminate the query by linking both forms to the table and then linking the two fields in the main form to the subform. The two fields don't actually appear in the subform. There are actually row source queries on combo boxes for some of the subform fields since they do double duty as display slots for existing records and entry of new data for the next user defined record. According to what I understand, when I enter the linked master fields the form should display all related records? This doesn't seem to work so I've still got some screwed up somewhere.
Dave
Allen,
Thanks for your patience. I tried your second solution of changing
the recordsource in the handling code and found it easiest to use the
AfterUpdate? event for the "last" field on the main form. This is
where the requery that didn't work was anyway. (Using the current
event got me in a world of brown goo that fired at the wrong time or
complained about not being able to change focus etc.)
The trick (for me) was to find the recordsource property for the
subform to allowing setting it in the running code rather than at
subform design time. The subform path you might expect leads to the
main form/subform link property sheet and has no recordsource (for GUI
beginners this is the bizarre sequence where clicking on the subform
gives this linkage property set and clicking again --with a resulting
black square-- gives the subform property set). However, the
CodeContextObject.[subform name].Form.RecordSource path allowed me to
move in the SQL query to the sweet spot and CodeContextObject.[subform
name].requery now works.
You were right on that the query wasn't up to date, but just why still
eludes me. Thanks for your help!
Dave This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
by: Fionnbarr Kennedy |
last post by:
Hi all
I have posted this problem before but am having no joy! I have an
app written in Access 2000 which is based around a Form/Subform. The
problem is with a new XP box only - the same app...
|
by: William Wisnieski |
last post by:
Hello Everyone,
Access 2000
I have a main form with a continuous subform. On the main form I have a
text box that references a field on the subform. What I'd like it to do is
show the value...
|
by: William Wisnieski |
last post by:
Hello Everyone,
Access 2000
I have a main form with a continuous subform. On the main form I have a
text field called . It gets populated based on what the user
selects in a field on the...
|
by: Robert |
last post by:
Need some help to stop me going around in circles on this one....
Have a nested subform (subform2) which simulates a continuous form for the
record on the parent subform.
Subform2 has rows of...
|
by: Reginald Bal |
last post by:
Hello,
I created a main form with 2 subforms.
In an attempt to move to the next record (or new record) on the main form I
get the error " Method 'requery' of object '_Subform' failed".
The...
|
by: mrowe |
last post by:
I am using Access 2003. (I am also using ADO in the vast majority of my code. I recently read a post that indicated that ADO is not all that is was initially cracked up to be. In the back of my...
|
by: virtualgreek |
last post by:
Dear all,
I have a scenario that is driving me nuts. (MS Access 2003)
I have a form/subform (Continuous form) where it gets its data from tables Order and Order_Details.
In the footer...
|
by: DeanL |
last post by:
Hi everyone,
I have a subform in datasheet view that is used to display the
contents of a single table. I have another subform with a query
feeding it to give a count of how many particular...
|
by: beacon |
last post by:
Hi everybody,
This is probably going to sound unorthodox, but I have to log records that are deleted...I know, go figure.
Anyway, I have a form with a (continuous) subform, and on the subform I...
|
by: erikbower65 |
last post by:
Using CodiumAI's pr-agent is simple and powerful. Follow these steps:
1. Install CodiumAI CLI: Ensure Node.js is installed, then run 'npm install -g codiumai' in the terminal.
2. Connect to...
|
by: linyimin |
last post by:
Spring Startup Analyzer generates an interactive Spring application startup report that lets you understand what contributes to the application startup time and helps to optimize it. Support for...
|
by: erikbower65 |
last post by:
Here's a concise step-by-step guide for manually installing IntelliJ IDEA:
1. Download: Visit the official JetBrains website and download the IntelliJ IDEA Community or Ultimate edition based on...
|
by: kcodez |
last post by:
As a H5 game development enthusiast, I recently wrote a very interesting little game - Toy Claw ((http://claw.kjeek.com/))。Here I will summarize and share the development experience here, and hope it...
|
by: isladogs |
last post by:
The next Access Europe meeting will be on Wednesday 6 Sept 2023 starting at 18:00 UK time (6PM UTC+1) and finishing at about 19:15 (7.15PM)
The start time is equivalent to 19:00 (7PM) in Central...
|
by: Taofi |
last post by:
I try to insert a new record but the error message says the number of query names and destination fields are not the same
This are my field names
ID, Budgeted, Actual, Status and Differences
...
|
by: DJRhino1175 |
last post by:
When I run this code I get an error, its Run-time error# 424 Object required...This is my first attempt at doing something like this. I test the entire code and it worked until I added this -
If...
|
by: Rina0 |
last post by:
I am looking for a Python code to find the longest common subsequence of two strings. I found this blog post that describes the length of longest common subsequence problem and provides a solution in...
|
by: DJRhino |
last post by:
Private Sub CboDrawingID_BeforeUpdate(Cancel As Integer)
If = 310029923 Or 310030138 Or 310030152 Or 310030346 Or 310030348 Or _
310030356 Or 310030359 Or 310030362 Or...
| |