ACCESS FEED: [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
> > agent1of6
> > > 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-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>
> > > <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]
>