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

Bill,

It sounds like you want to scroll through the records in combo box 2. If so, as the records are scrolled why not add code to combo box two's change event that will update the data in combo box 3?

An alternative might be to put something like the following in the AfterUpdate event of combo box 2.

Private Sub cboPerson_AfterUpdate()

On Error GoTo ErrorHandler

Dim strSQL As String

strSQL = "SELECT DISTINCT Table1.Person FROM Table1 ORDER BY table1.person DESC;"

Me.cboPerson2.RowSource = strSQL
Me.cboPerson2.Requery

CleanUpAndExit:
Call DoCmd.SetWarnings(True)
Exit Sub

ErrorHandler:
' Do normal error handling stuff
Resume CleanUpAndExit

End Sub
Jeff

----- Original Message -----
From: "Bill Singer" <Bill.Singer@at-group.net>
To: "MS_Access_Professionals@yahoogroups.com" <ms_access_professionals@yahoogroups.com>
Sent: Sunday, August 11, 2013 5:52:25 PM
Subject: RE: [MS_AccessPros] Re: Cascading combo box problem

Jeff and Bill,
Jeff, I can't really follow your answer. I am guessing I am not familiar with the methods you are using so I am having a hard time following. Or maybe my question is confusing.

I will clarify my problem so we all understand. I have 3 combo boxes on one form. All of the code in the "on current" property is related to combo box 1. I do not have any problems with combo box 1. The problem I am having is with combo box 3. Combo box 3's data source is a query that uses the information from combo box 2 to update the selections. This works perfect during the original data input as combo box 3 refreshes on the "got focus" event when the user tabs into that combo box from combo box 2.

The problem is that when I go back to view the records, combo box 3 does not update because the combo box never "get the focus". As a user scrolls through the records the 3rd combo box will always look blank because there is no even that triggers the refresh.

I hope that helps.

Thanks for you effort.
Bill
MN

To: MS_Access_Professionals@yahoogroups.com
From: jpjones23@centurylink.net
Date: Fri, 9 Aug 2013 22:48:22 -0400
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

> > 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]

>

[Non-text portions of this message have been removed]

[Non-text portions of this message have been removed]

------------------------------------

Yahoo! Groups Links