Hi Bill
Sorry I'm very late to this party, but I'm here to throw in my 2c worth
anyway :-)
The only time you need to limit the list of choices in the "child" combo box
is when the user is able to make a choice - i.e. when the combo box has the
focus. Therefore, I suggest you try using the GotFocus event to set the
RowSource to the filtered list, and use LostFocus to reset it to the
unfiltered list.
In design view, set the RowSource to the unfiltered list - for example:
SELECT Ref2ID, Ref2Name FROM t_Reference2 ORDER BY Ref2Name;
In GotFocus, add a WHERE clause:
Private Sub cboReference2_GotFocus()
cboReference2.RowSource = "SELECT Ref2ID, Ref2Name FROM t_Reference2 " _
& "WHERE Ref2Parent=" & cboReference1 & " ORDER BY Ref2Name;"
End Sub
And in LostFocus, remove the WHERE clause:
Private Sub cboReference2_LostFocus()
cboReference2.RowSource = "SELECT Ref2ID, Ref2Name FROM t_Reference2 " _
& " ORDER BY Ref2Name;"
End Sub
Best wishes,
Graham
-----Original Message-----
From: MS_Access_Professionals@yahoogroups.com
[mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of Bill Singer
Sent: Monday, 12 August 2013 10:02 a.m.
To: MS_Access_Professionals@yahoogroups.com
Subject: RE: [MS_AccessPros] Re: Cascading combo box problem
John.
I am interested in the Me.Combo2.Requery option. The name of my combo box
is cboReference2.
I am not sure where to put the code. The last time I tried to put a requery
in "on current" event of the form I got stuck in a loop and crashed. The
code below has to do with a combo box that is not related to either of the
combo boxes I am having problems with. The combo boxes that I am working on
do not have any old unused selection.
I replied with additional details in a response to Jeff and Bill.
I have reposted the code below. Where would you suggest I stick the code?
Private Sub Form_Current()
' If on a new record
If Me.NewRecord Then
' Don't show the obosolete service areas Me.cboMinistryArea.RowSource =
"SELECT * FROM t_MinistryArea WHERE minActive=False ORDER BY
t_MinistryArea.[MinistryArea];"
Else
' Existing record - show them all Me.cboMinistryArea.RowSource = "SELECT *
FROM t_MinistryArea ORDER BY t_MinistryArea.[MinistryArea];"
End If
End Sub
Thanks for your help.
Bill
To: MS_Access_Professionals@yahoogroups.com
From: JohnV@msn.com
Date: Sat, 10 Aug 2013 09:20:41 +0200
Subject: RE: [MS_AccessPros] Re: Cascading combo box problem
Bill & Bill-
I've been sorta following this thread, but haven't jumped in because it
looked like it was getting answered. But now the great BILL MOSCA says he
can't solve it! "Houston, we have a problem!!"
As I understand the first Bill's problem, there are three combo boxes. The
second one is filtered on the first one, and the third one should show only
"active" records if on a new record. The code Bill S has in the Current
event looks like it should solve the second problem. He should also add a
Me.Combo2.Requery to that code to get the second combo to update correctly
to match the first combo value. If there are cases where this requery
causes the second combo to go blank because the value stored in the record
does not match what's in the filtered list, you need to use Jeff's suggested
solution to display the old value. The lookup table for the second combo
needs to be included in the form's Record Source and the display column
included in the output of the query. Bind a text box to that display column
and overlay it exactly on the combo box display area. Make it Enabled but
Locked = Yes so the user can't mess with it. In the Got Focus event of the
text box, move the focus to the underying combo box. If there are values in
existing records that are not valid based on the filter, you could even do a
lookup on the filtered Row Source, and if the current value isn't there,
turn the Fore Color of the text box to red to indicate an invalid value.
Note that this overlay technique even works in a Continuous Form. The combo
will be filtered on the value in the current record, so other records
displayed might not match a valid filtered value, but the overlay text box
will display it from the match in the underlying record.
John Viescas, Author
Microsoft Access 2010 Inside Out
Microsoft Access 2007 Inside Out
Microsoft Access 2003 Inside Out
Building Microsoft Access Applications
SQL Queries for Mere Mortals
http://www.viescas.com/
(Paris, France)
-----Original Message-----
From: MS_Access_Professionals@yahoogroups.com
[mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of
jpjones23@centurylink.net
Sent: Saturday, August 10, 2013 4:48 AM
To: MS Access Professionals
Subject: Re: [MS_AccessPros] Re: Cascading combo box problem
Bill and Bill,
Why not have a hidden text box or label that is populated when the vender ID
or any other combo box value is selected? Then do the requery and when done,
use the value in the hidden control to reposition the first combo box. I
admit that I've come in late due to my own project's busyness so if I'm out
in left field, then never mind.
Jeff
----- Original Message -----
From: "Bill Mosca" <wrmosca@comcast.net>
To: "MS Access Professionals" <MS_Access_Professionals@yahoogroups.com>
Sent: Friday, August 9, 2013 6:05:57 PM
Subject: [MS_AccessPros] Re: Cascading combo box problem
Bill, I'm stumped. In fact, I just got a work order to do basically the same
thing you need to do and I haven't figured it out yet. A combo box loads
when the form loads so if you just move to a new record it doesn't re-run
its query. I need it to change based on the vendor id. If I requery the
combo, it loses its current value in the box.
When I figure it out I will surely let you know.
Bill
--- In MS_Access_Professionals@yahoogroups.com , "Bill Singer"
<Bill.Singer@...> wrote:
>
> Bill,
>
>
>
> But going back to the original question. What do I need to do to get a
> my 2nd combo box, based on a query, to update when I move from record
> to record. Neither of these combo boxes are related to the code I
provided.
> Or would this be the answer for all the combo boxes.
>
>
>
> However, If I think about this, if would eliminate the ability to ever
> correct an old record if that needed to happen.
>
>
>
>
>
>
>
> Bill Singer
>
>
>
> From: MS_Access_Professionals@yahoogroups.com
> [mailto: MS_Access_Professionals@yahoogroups.com ] On Behalf Of Bill
> Mosca
> Sent: Friday, August 09, 2013 10:13 AM
> To: MS_Access_Professionals@yahoogroups.com
> Subject: [MS_AccessPros] Re: Cascading combo box problem
>
>
>
>
>
> Bill
>
> Then you are going through all of this for nothing. If it is read-only
> make it a text box and put it on top of the combo that must show only
> the current choices.
>
> Then use the form's current event to hide or show the text box.
>
> Bill
>
> --- In MS_Access_Professionals@yahoogroups.com
> <mailto:MS_Access_Professionals%40yahoogroups.com> , "Bill Singer"
> <Bill.Singer@> wrote:
> >
> > Bill,
> >
> > The record in the old records combo box (with the options that are
> > not any longer available) are for viewing only. There is no changing
> > of old records, we just want to be able to view them.
> >
> >
> >
> >
> >
> >
> >
> > Bill Singer
> >
> >
> >
> > A.T.Group
> >
> > 877-902-8898
> >
> >
> >
> > From: MS_Access_Professionals@yahoogroups.com
> <mailto:MS_Access_Professionals%40yahoogroups.com>
> > [mailto: MS_Access_Professionals@yahoogroups.com
> <mailto:MS_Access_Professionals%40yahoogroups.com> ] On Behalf Of Bill
> Mosca
> > Sent: Friday, August 09, 2013 8:51 AM
> > To: MS_Access_Professionals@yahoogroups.com
> <mailto:MS_Access_Professionals%40yahoogroups.com>
> > Subject: [MS_AccessPros] Re: Cascading combo box problem
> >
> >
> >
> >
> >
> > Bill
> >
> > Would the users be changing the selection in that old record's combo
box?
> or
> > should it really be read-only?
> >
> > Bill
> >
> > --- In MS_Access_Professionals@yahoogroups.com
> <mailto:MS_Access_Professionals%40yahoogroups.com>
> > <mailto:MS_Access_Professionals%40yahoogroups.com> , "Bill Singer"
> > <Bill.Singer@> wrote:
> > >
> > > Bill,
> > >
> > > Sorry, I did not explain. The code I showed below that changes the
> > > menu
> in
> > > a combo box is not for either of the combo boxes that I am taking
about.
> > > This code refers to a 3rd combo box. The reason we change the
> > > combo box choice is that we have eliminated some of the choices in
> > > the combo box
> so
> > if
> > > it is a new record only the currently active selections are available.
> For
> > > old records we want to be able to see the old choice, even though
> > > it is
> > not
> > > available. We tried leaving all the option in the combo box but we
> > > continually had people selecting options that were supposed to be
> > > not
> > used.
> > >
> > >
> > >
> > >
> > >
> > > The two combo boxes I am having a problem are not related to the
> > > code I provided.
> > >
> > >
> > >
> > > I hope that helps.
> > >
> > >
> > >
> > > Bill Singer
> > >
> > > MN
> > >
> > >
> > >
> > > From: MS_Access_Professionals@yahoogroups.com
> <mailto:MS_Access_Professionals%40yahoogroups.com>
> > <mailto:MS_Access_Professionals%40yahoogroups.com>
> > > [mailto: MS_Access_Professionals@yahoogroups.com
> <mailto:MS_Access_Professionals%40yahoogroups.com>
> > <mailto:MS_Access_Professionals%40yahoogroups.com> ] On Behalf Of
> > Bill
> Mosca
> > > Sent: Thursday, August 08, 2013 1:29 PM
> > > To: MS_Access_Professionals@yahoogroups.com
> <mailto:MS_Access_Professionals%40yahoogroups.com>
> > <mailto:MS_Access_Professionals%40yahoogroups.com>
> > > Subject: RE: [MS_AccessPros] Re: Cascading combo box problem
> > >
> > >
> > >
> > >
> > >
> > > Bill
> > >
> > > Why would you need to refresh the second combo if you are not
> > > touching
> the
> > > control? Your Focus event looks like it would work for changing
> > > the
> combo
> > > rowsource, but I'm not sure why you are doing this to start with.
> > > If the record exists I can't think of a reason why someone would
> > > need to change the
> data
> > > in
> > > the second combo to an obsolete value. If that value is no longer
> allowed
> > to
> > > be
> > > used there doesn't seem to be a reason to let the user select it.
> > >
> > > Regards,
> > >
> > > Bill
> > >
> > > From: MS_Access_Professionals@yahoogroups.com
> <mailto:MS_Access_Professionals%40yahoogroups.com>
> > <mailto:MS_Access_Professionals%40yahoogroups.com>
> > > <mailto:MS_Access_Professionals%40yahoogroups.com>
> > > [mailto: MS_Access_Professionals@yahoogroups.com
> <mailto:MS_Access_Professionals%40yahoogroups.com>
> > <mailto:MS_Access_Professionals%40yahoogroups.com>
> > > <mailto:MS_Access_Professionals%40yahoogroups.com> ] On Behalf Of
> > agent
1of6
> > > Sent: Wednesday, August 07, 2013 7:50 PM
> > > To: MS_Access_Professionals@yahoogroups.com
> <mailto:MS_Access_Professionals%40yahoogroups.com>
> > <mailto:MS_Access_Professionals%40yahoogroups.com>
> > > <mailto:MS_Access_Professionals%40yahoogroups.com>
> > > Subject: [MS_AccessPros] Re: Cascading combo box problem
> > >
> > > Bill,
> > > This is the code that is in the code that runs on the On Current
> property.
> > > This
> > > code makes only the ACTIVE selections available in one of the
> > > COMBO
> Boxes
> > if
> > > the
> > > record is new, but for old records we want to see all the options.
> > >
> > > Where would you put the code to refresh the 2nd combo box. Last
> > > time I
> > tried
> > > I
> > > got that loop you were talking about.
> > >
> > > Bill
> > > MN
> > >
> > > Private Sub Form_Current()
> > > ' If on a new record
> > > If Me.NewRecord Then
> > > ' Don't show the obosolete service areas
> > > Me.cboMinistryArea.RowSource = "SELECT * FROM t_MinistryArea WHERE
> > minActive
> > > =
> > > False ORDER BY t_MinistryArea.[MinistryArea];"
> > > Else
> > > ' Existing record - show them all Me.cboMinistryArea.RowSource =
> > > "SELECT * FROM t_MinistryArea ORDER BY
> > > t_MinistryArea.[MinistryArea];"
> > > End If
> > > End Sub
> > >
> > > --- In MS_Access_Professionals@yahoogroups.com
> <mailto:MS_Access_Professionals%40yahoogroups.com>
> > <mailto:MS_Access_Professionals%40yahoogroups.com>
> > > <mailto:MS_Access_Professionals%40yahoogroups.com>
> > > <mailto:MS_Access_Professionals%40yahoogroups.com> , "Bill Mosca"
> > > <wrmosca@>
> > > wrote:
> > > >
> > > > Bill
> > > >
> > > > No, don't put a form refresh in the Current event. That would
> > > > cause an
> > > infinite loop. Just refresh the combo. Or maybe requery the combo.
> > > I
> can't
> > > remember which works best.
> > > >
> > > > Bill
> > > >
> > > > --- In MS_Access_Professionals@yahoogroups.com
> <mailto:MS_Access_Professionals%40yahoogroups.com>
> > <mailto:MS_Access_Professionals%40yahoogroups.com>
> > > <mailto:MS_Access_Professionals%40yahoogroups.com>
> > > <mailto:MS_Access_Professionals%40yahoogroups.com> , "Bill Singer"
> > > <Bill.Singer@> wrote:
> > > > >
> > > > > Bill,
> > > > >
> > > > > That was my first thought but I already have some code in
> > > > > there. I
> > guess
> > > I
> > > > > will have to try to get that code and send it to you. I tried
> > > > > to add
> > > some
> > > > > code to refresh the form but I got the think stuck in some
> > > > > loop and
> it
> > > kept
> > > > > crashing. I will send it as soon as I can.
> > > > >
> > > > >
> > > > >
> > > > > Thanks,
> > > > >
> > > > > Bill
> > > > >
> > > > >
> > > > >
> > > > >
> > > > >
> > > > > Bill Singer
> > > > >
> > > > >
> > > > >
> > > > > From: MS_Access_Professionals@yahoogroups.com
> <mailto:MS_Access_Professionals%40yahoogroups.com>
> > <mailto:MS_Access_Professionals%40yahoogroups.com>
> > > <mailto:MS_Access_Professionals%40yahoogroups.com>
> > > <mailto:MS_Access_Professionals%40yahoogroups.com>
> > > > > [mailto: MS_Access_Professionals@yahoogroups.com
> <mailto:MS_Access_Professionals%40yahoogroups.com>
> > <mailto:MS_Access_Professionals%40yahoogroups.com>
> > > <mailto:MS_Access_Professionals%40yahoogroups.com>
> > > <mailto:MS_Access_Professionals%40yahoogroups.com> ] On Behalf Of
> > > Bill
> > Mosca
> > > > > Sent: Wednesday, August 07, 2013 9:37 AM
> > > > > To: MS_Access_Professionals@yahoogroups.com
> <mailto:MS_Access_Professionals%40yahoogroups.com>
> > <mailto:MS_Access_Professionals%40yahoogroups.com>
> > > <mailto:MS_Access_Professionals%40yahoogroups.com>
> > > <mailto:MS_Access_Professionals%40yahoogroups.com>
> > > > > Subject: [MS_AccessPros] Re: Cascading combo box problem
> > > > >
> > > > >
> > > > >
> > > > >
> > > > >
> > > > > Bill
> > > > >
> > > > > Why not use the form's Current event. That fires every time
> > > > > you move
> > to
> > > > > another record.
> > > > >
> > > > > Bill
> > > > >
> > > > > --- In MS_Access_Professionals@yahoogroups.com
> <mailto:MS_Access_Professionals%40yahoogroups.com>
> > <mailto:MS_Access_Professionals%40yahoogroups.com>
> > > <mailto:MS_Access_Professionals%40yahoogroups.com>
> > > <mailto:MS_Access_Professionals%40yahoogroups.com>
> > > > > <mailto:MS_Access_Professionals%40yahoogroups.com> , "agent1of6"
> > > > > <Bill.Singer@> wrote:
> > > > > >
> > > > > > Bill,
> > > > > > Sorry I took so long I had to open the database at home.
> > > > > >
> > > > > > On the second combo box, I have a macro in the "on Focus"
> property.
> > > > > > The macro is "Refresh"
> > > > > >
> > > > > > So as you tab from the first combo box to the second, as
> > > > > > soon as
> the
> > > > > second gets the focus it refreshes.
> > > > > >
> > > > > > However, now as I scroll through the records it never gets
> > > > > > focus
> so
> > it
> > > > > never updates.
> > > > > >
> > > > > > Thanks,
> > > > > > Bill
> > > > > > MN
> > > > > >
> > > > > >
> > > > > >
> > > > > > --- In MS_Access_Professionals@yahoogroups.com
> <mailto:MS_Access_Professionals%40yahoogroups.com>
> > <mailto:MS_Access_Professionals%40yahoogroups.com>
> > > <mailto:MS_Access_Professionals%40yahoogroups.com>
> > > <mailto:MS_Access_Professionals%40yahoogroups.com>
> > > > > <mailto:MS_Access_Professionals%40yahoogroups.com> , "Bill Mosca"
> > > <wrmosca@>
> > > > > wrote:
> > > > > > >
> > > > > > > Bill
> > > > > > >
> > > > > > > How are you updating the second combo? Are you using code?
> > > > > > > If
> so,
> > > please
> > > > > post the code so we can see what you are doing.
> > > > > > >
> > > > > > > Regards,
> > > > > > > Bill Mosca, Founder - MS_Access_Professionals
> > > > > > > http://www.thatlldoit.com Microsoft Office Access MVP
> > > > > > >
> > > > >
> > >
> >
> https://mvp.support.microsoft.com/profile=C4D9F5E7-BB03-4291-B816-6427
> 073088
> > > > > 1E
> > > > > > > My nothing-to-do-with-Access blog
> > > > > > > http://wrmosca.wordpress.com
> > > > > > >
> > > > > > >
> > > > > > >
> > > > > > > --- In MS_Access_Professionals@yahoogroups.com
> <mailto:MS_Access_Professionals%40yahoogroups.com>
> > <mailto:MS_Access_Professionals%40yahoogroups.com>
> > > <mailto:MS_Access_Professionals%40yahoogroups.com>
> > > <mailto:MS_Access_Professionals%40yahoogroups.com>
> > > > > <mailto:MS_Access_Professionals%40yahoogroups.com> , "Bill Singer"
> > > > > <Bill.Singer@> wrote:
> > > > > > > >
> > > > > > > >
> > > > > > > >
> > > > > > > > I have a form that has a two combo boxes. They are
> > > > > > > > cascading,
> > the
> > > > > second
> > > > > > > > combo box runs off of a query. The query changes the
> > > > > > > > election
> > > options
> > > > > in
> > > > > > > > the second combo box based on the selection in the first
> > > > > > > > combo
> > > box.
> > > > > During
> > > > > > > > the initial record input the combo boxes work perfect.
> However,
> > > > > recently as
> > > > > > > > we have to go back and view some of the records, the
> > > > > > > > second
> > combo
> > > box
> > > > > is not
> > > > > > > > updating. As I scroll through the records the first
> > > > > > > > combo box
> > will
> > > > > update
> > > > > > > > but the second combo box typically just goes blank. It
> > > > > > > > will
> not
> > > update
> > > > > > > > unless I click on it or use the refresh button in the menu.
> > > > > > > >
> > > > > > > >
> > > > > > > >
> > > > > > > > Is there a way to get the second combo box to
> > > > > > > > update/refresh
> > each
> > > time
> > > > > a
> > > > > > > > record is changed. I would have typically tried to go to
> > > > > > > > the
> > form
> > > > > > > > property "on Current" and tried to put a refresh in
> > > > > > > > there but
> I
> > > > > already have
> > > > > > > > an event in there.
> > > > > > > >
> > > > > > > >
> > > > > > > >
> > > > > > > > What is the best way to accomplish this.
> > > > > > > >
> > > > > > > >
> > > > > > > >
> > > > > > > > .as always, thanks for your wisdom.
> > > > > > > >
> > > > > > > >
> > > > > > > >
> > > > > > > > Bill Singer
> > > > > > > >
> > > > > > > > MN
> > > > > > > >
> > > > > > > >
> > > > > > > >
> > > > > > > > [Non-text portions of this message have been removed]
> > > > > > > >
> > > > > > >
> > > > > >
> > > > >
> > > > >
> > > > >
> > > > >
> > > > >
> > > > > [Non-text portions of this message have been removed]
> > > > >
> > > >
> > >
> > > [Non-text portions of this message have been removed]
> > >
> > >
> > >
> > >
> > >
> > > [Non-text portions of this message have been removed]
> > >
> >
> >
> >
> >
> >
> > [Non-text portions of this message have been removed]
> >
>
>
>
>
>
> [Non-text portions of this message have been removed]
>
[Non-text portions of this message have been removed]
------------------------------------
Yahoo! Gro
ups Links
[Non-text portions of this message have been removed]
------------------------------------
Yahoo! Groups Links
| Reply via web post | Reply to sender | Reply to group | Start a New Topic | Messages in this topic (23) |
Tidak ada komentar:
Posting Komentar