ACCESS FEED: [MS_AccessPros] Re: Cascading combo box problem

Jumat, 09 Agustus 2013

[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, "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@yahoogroups.com] On Behalf Of Bill Mosca
> Sent: Thursday, August 08, 2013 1:29 PM
> To: MS_Access_Professionals@yahoogroups.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@yahoogroups.com
> <mailto:MS_Access_Professionals%40yahoogroups.com> ] On Behalf Of agent1of6
> Sent: Wednesday, August 07, 2013 7:50 PM
> To: MS_Access_Professionals@yahoogroups.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> , "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> , "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@yahoogroups.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>
> > > 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> , "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> , "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-6427073088
> > > 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> , "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]
>