I have a child form that has a combo box whose underlying query needs
to be filtered by a value from a combo box on the parent form.
I have succeeded in doing this by putting the following SQL in the
rowsource of the combobox:
SELECT tblForms.idForm s, tblForms.FormNu mber, tblForms.FormNa me,
tblForms.idCust omers FROM tblForms ORDER BY tblForms.FormNu mber;
Then I adding the following to the GotFocus event of the combo box and
the Current event of the form:
cboidForms.RowS ource = "qryDesignOrder DropDown"
cboidForms.Requ ery
This works but is real kludgy. Whenever I change parent records, it has
a noticeable delay while it requeries for each record in the child
form.
Surely there's a better way to have a child form's combobox query be
filtered based on a value from a combobox on the parent form. 4 5563
In the Enter event of the combo in the subform, set its RowSource to a SQL
statement. Something like this:
Dim strSql As String
With Me.Parent![MyMainFormCombo]
strSql = "SELECT Field1, Field2 FROM MyCombosTable WHERE " & _
IIf(IsNull(.Val ue), "(False)", "SomeID = " & .Value) & ";"
End With
Me.[MySubformCombo].RowSource = strSql
That should be all you need unless the combo's bound column is zero-width.
In that case you would need to do the same in the Current event of the main
form as well, so that the other values show up in the combo as well.
That's not very different from what you are doing. But assigning the SQL
directly may be quicker than going through a query. If there are thousands
of records to load into the combo, it might be worthwhile checking whether
it needs to be reloaded, i.e.:
With Me.[MySubformCombo]
If .RowSource <> strSql then
.RowSource = strSql
End If
End With
--
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.
<jc************ *@yahoo.com> wrote in message
news:11******** **************@ o13g2000cwo.goo glegroups.com.. . I have a child form that has a combo box whose underlying query needs to be filtered by a value from a combo box on the parent form.
I have succeeded in doing this by putting the following SQL in the rowsource of the combobox:
SELECT tblForms.idForm s, tblForms.FormNu mber, tblForms.FormNa me, tblForms.idCust omers FROM tblForms ORDER BY tblForms.FormNu mber;
Then I adding the following to the GotFocus event of the combo box and the Current event of the form:
cboidForms.RowS ource = "qryDesignOrder DropDown" cboidForms.Requ ery
This works but is real kludgy. Whenever I change parent records, it has a noticeable delay while it requeries for each record in the child form.
Surely there's a better way to have a child form's combobox query be filtered based on a value from a combobox on the parent form.
Thanks for the tip. Unfortunately, the code isn't quite fully
functional.
As you guessed, my combo's bound column is zero length so at your
suggestion, I duplicated the code in the parent's Current event.
Naturally I had to include a reference to the subform because the combo
on the subform is not part of the parent. So, here's the offending
line:
Me.frmOrdersDes ignSubForm![cboidForms].RowSource = strSQL
If I open the parent from Design mode, it works perfectly. However,
when I try to open the parent form from the database window, I get an
error message about an invalid reference, presumably because the link
between parent and child has not yet been established (though IMHO, it
should be)
So the question is, how do I get the parent to hold off on the
assignment of the SQL statement until the subform is loaded?
Try:
Me.frmOrdersDes ignSubForm.Form ![cboidForms].RowSource = strSQL
Explanation of the ".Form" bit: http://allenbrowne.com/casu-04.html
--
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.
<jc************ *@yahoo.com> wrote in message
news:11******** *************@l 41g2000cwc.goog legroups.com... Thanks for the tip. Unfortunately, the code isn't quite fully functional.
As you guessed, my combo's bound column is zero length so at your suggestion, I duplicated the code in the parent's Current event. Naturally I had to include a reference to the subform because the combo on the subform is not part of the parent. So, here's the offending line:
Me.frmOrdersDes ignSubForm![cboidForms].RowSource = strSQL
If I open the parent from Design mode, it works perfectly. However, when I try to open the parent form from the database window, I get an error message about an invalid reference, presumably because the link between parent and child has not yet been established (though IMHO, it should be)
So the question is, how do I get the parent to hold off on the assignment of the SQL statement until the subform is loaded?
Thanks, again, but unfortunately, something must be wrong with my
system. I made the change you suggested but I'm still getting:
runtime error: 2455
you entered an expression that has an invalid reference t the property
Form/Report.
I read the link you provided and I even went to the MS knowledge base: http://support.microsoft.com/kb/q113352/
That page shows what you suggested, namely, to type:
Forms![main form name]![subform control name].Form![control name]
But I still get the error message. So...something must be wrong with my
MS Access installation.
Thoughts? This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics |
by: Heather |
last post by:
Hi
I am desparately looking for advice in relation to storing the results
after selecting items from two combo boxes on a Referral form. The
first combo box 'ctl Type' displays a full list of Agency Types, then
the 2nd combo box displays a list of Organisations, depending upon the
Agency Type Selected.
The first combo box 'ctlType'...
|
by: B |
last post by:
I know there are several ways to speed up combo boxes and form
loading. Most of the solutions leave rowsource of the combo box blank
and set the rowsource to a saved query or an SQL with a where clause
after users typed in one or several letters/digits.
My problem is as follows
Most of the time I need to display form in continuous format,...
|
by: Giulio |
last post by:
Hello,
I have a continuous form with a combo box inside. From the combo box I can
pick some values which, by the "after-update" event, change some other combo
box values determined by a query.
(i.e. I have some tree names and depending on what tree I pick on the other
combo box I can pick its fruits and other properties)
Now, mine is a...
|
by: NB |
last post by:
Let's say in the product table I have around 50,000 products (well,
infact it has now about 2000 only, but it's growing very fast, hence
the question here)
In the order form, users will select the product for the order lines.
They want to see the entire list of available products.
You can't handle the combo the way you've heard: limiting...
|
by: mik18 |
last post by:
Is there a limit to the number of rows a combo box can contain within
Access 2002?
I have a list of drug names with all the NDC codes which contains over
200,000 records. I do have this filtered to only non-obsolete drugs
(80,000). I can only get around 65,000 records to poplate in the combo
box. I can type the name of the drug in and not get...
| |
by: Edwinah63 |
last post by:
Hi everyone,
Please let there be someone out there who can help.
I have two BOUND combo boxes on a continuous form, the second being
dependent on the first. I have no problem getting the second combo to
change depending on what values the user selects in the first box, it's
just that every time the user changes the first combobox, the...
|
by: Jeremy Wallace |
last post by:
Folks,
Here's a write-up I did for our developer wiki. I don't know if the
whole rest of the world has already figured out how to do this, but I
hadn't ever seen it implemented, and had spent a lot of time trying to
figure it out, over the years. It finally dawned on me a couple of
weeks ago how to do this.
A couple of notes:
1) This is...
|
by: Bill |
last post by:
Problem:
Combo box data disappears from view when a requery is done
See "Background" below for details on tables, forms & controls
On a form, I want to use the setting of bound combo box C1 to limit
what is displayed in bound combo box C2. When I display Tbl-A records
on a continuous form, and navigate away from record 1 to record 2,...
|
by: virtualgreek |
last post by:
Dear All,
First of all I would like to take the time to thank you all for your efforts and time spent at this wonderful forum. I have found it very helpful with numerous examples available for every level of user.
I am facing a rather weird behaviour with a combo box on a subform (Continuous form).
I have two tables. Order and...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that...
| |
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
| |
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating...
| |