Combo box lookup doesn't work when field is blank/null

[Bob Richardson]

Each year at a conference, attendees can choose to go to one of several
"retreats." The selection of retreats varies from year to year, with a lot
of overlap. When collecting the registration data I want the user to be able
to indicate the attendee's retreat choice from a combo box.

In a sub-form, I have a combo box with a lookup table, "Retreats", with two
columns.

Year Retreat
2002 one
2002 two
2002 four
2003 one
2003 three
etc.

The subform, "Conference", holds the year data.

The RowSource of the combo box looks like this.

SELECT Retreats.Retreat, Retreats.Year FROM Retreats WHERE
(((Retreats.Year)=Conference!year)) ORDER BY Retreats.Retreat;

This approach works perfectly when a selection has already been made. The
drop down list only shows the choices for that year. But if no Retreat has
been selected for that year, the drop down list will show the choices
available for the LAST YEAR WHEN THE COMBO BOX WAS USED.

For example, an attendee has been to the conferences in 2002, 2003 and 2004,
didn't go to a retreat in any of those years. You bring this person's record
up, go to the conference subform, and look at the 2004 selections. Of course
it shows no Retreat selected. If you look at the combo box choices, it will
correctly show the 2004 choices. If you next go to the 2003 record for that
individual (OR ANY OTHER INDIVIDUAL with no retreat selected) it will show
the 2004 choices in the combo box, not the 2003 choices.

It seems like I need to Requery, but what and where?

 

[Guest]

In the form's On Current event, requery the combo box. Your code would look something like this (except with your combo name):

Private Sub Form_Current()
Me.cboMyCombo.Requery
End Sub

Hope this helps

 

[Bob Richardson]

Thanks for trying rpw, but I still have the same problem. I tried moving the
Requery to BeforeUpdate and OnActivate...all with no success

In the form's On Current event, requery the combo box. Your code would

look something like this (except with your combo name):

 

[Ken Snell]

How do you bring the person's record up? Is this in the main form? Is it by
using navigation buttons? Is it by a selection in a combo box on the main
form?

 

[Guest]

On second read of your original post, I've got another idea. I'll bet that the combo is on the main form. The SQL for combo refers to a form, not to the main form's subform.

Close all other forms except for the main form. Open the query design view of the combo, Right-click in the Criteria for the year field and click "Build". In the expression builder select Forms/Loaded Forms/Main form/subform/text field for the year.

Close the query and try it again with the code I offered befor.

 

[Bob Richardson]

The person's record is brought up in the main form, which has 3 tabs. One of
the tabs has a sub-form named "Conferences" where info about one year's
conference is shown. The LAST conference attended is shown first
(DoCmd.GoToRecord , , acLast). To look at information about conferences in
earlier years, the navigation buttons are used.

The Conferences subform has a combo box where the user can indicate the
"Retreat" chosen by the person for that year. WHETHER OR NOT there is a
value shown, the combo box values that are shown are only valid for the
first year that the combo box for this person was used. (Note, the subject
is wrong...this problem occurs whether or not the field is blank)

For example, a person has been to conferences in 2002,2003, and 2004. When
you go to the Conferences tab, the 2004 conference info is shown. If you
click a Combo button, the correct choices for 2004 retreats will appear. If
you then navigate to 2002 or 2003 and click the retreat combo button, it
will show the choices for 2004!!

So you close that form and select another person, or perhaps the same person
again, and go to the conferences tab. Instead of clicking the combo button,
you navigate to the 2003 conference and THEN click the combo button. Now the
correct choices for 2003 will appear. If you navigate to 2002 or 2004, it
will still show only the 2003 retreat choices.

In other words, the choices for that individual get "locked in" to the first
year you use the combo button.

The Bound Column = 2 and the Row Source of the combo box looks like this:

SELECT Retreats.Retreat, Retreats.Year FROM Retreats WHERE
(((Retreats.Year)=DataConference.Year)) ORDER BY Retreats.Retreat;

Where "DataConference" is the table holding conference info for everyone.

p.s. My problem occurs whether or not I go to the last record first; i.e.
removing the "DoCmd.GoToRecord , , acLast" doesn't change this problem.

 

[Guest]

There is a fairly common trick Access developers use with combo boxes. For example, the first combo selects the person, the second combo filters the row source to show the years the person attended a conference, and the third filters the row source to filter to show the list of conferences for the year selected in the second combo. Sort of a cascading filter.

The description of your problem sounds similar except that you're using forms and sub-forms instead of combo1 and combo2. e.g. the main form finds the person, the subform finds the year of attendance. You want the combo to filter to show the list of conferences selected in the subform. A cascading filter.

But what your query for the combo is doing is looking at a table. If you modify the query so that it's filter criteria is the year that is displayed on the subform, then it will work the way you want. Everytime you change records on the subform is when you want the combo requeried - that would be the On Current event of the subform.

 

[Bob Richardson]

TA DA!!!

Thank you. I put the Requery in the On Current event and it seems to be
working perfectly.

There is a fairly common trick Access developers use with combo boxes.

For example, the first combo selects the person, the second combo filters
the row source to show the years the person attended a conference, and the
third filters the row source to filter to show the list of conferences for
the year selected in the second combo. Sort of a cascading filter.

The description of your problem sounds similar except that you're using

forms and sub-forms instead of combo1 and combo2. e.g. the main form finds
the person, the subform finds the year of attendance. You want the combo to
filter to show the list of conferences selected in the subform. A cascading
filter.

But what your query for the combo is doing is looking at a table. If you

modify the query so that it's filter criteria is the year that is displayed
on the subform, then it will work the way you want. Everytime you change
records on the subform is when you want the combo requeried - that would be
the On Current event of the subform.

 

[Guest]

Well, glad to hear that it worked. I wonder why it didn't work yesterday? Did you change the query any?

 

[Bob Richardson]

I think I must not have put it in On Current. Thanks again for you help.

 

[Bob Richardson]

I think I got to the root of what is going on. It revolves around FOCUS.

My form has multiple tabs. If the tab sheet contains just a sub form (no
other objects), that sub-form gets focus when the tab is selected, and the
On Current event for the sub-form is triggered.

However, if the tab sheet contains other objects as well as a sub-form, then
the sub-form's On Current event will not trigger, when the tab is selected,
UNLESS the sub-form is placed first in the TAB ORDER. (don't confuse "tab"
order with "tab" control).

In one case I needed to Requery for the combo boxes, and in another I needed
to goto the last record in the sub-form. Both situations needed to use the
On Change event for a sub-form.

When I initially tried your solution, the sub-form was not first in the
tab-order.

I think I must not have put it in On Current. Thanks again for you help.

Well, glad to hear that it worked. I wonder why it didn't work

yesterday?
Did you change the query any?

 

[citybell]

In the form's On Current event, requery the combo box. Your code would

look something like this (except with your combo name):

Private Sub Form_Current()
Me.cboMyCombo.Requery
End Sub

Hope this helps
--
rpw



GASTE 5 MINUTOS DE SU TIEMPO Y... LEA ESTO Y VERA
QUE REALMENTE FUNCIONA. Y ADEMAS LEGALMENTE!!

Permíteme empezar diciendo que FINALMENTE LO
ENCONTRE !!!. En serio!, lo encontré!. Y eso
que odio todos esos ESQUEMAS DE HACERTE RICO
RAPIDO!!!. Odio esos esquemas de marketing
con varios niveles, o de hacer órdenes por
correo, o poniendo propaganda en sobre de
correo, etc., la lista es interminable.
Probé todos esos malditos esquemas de
hacerte rico rápido durante 5 años .


Estaba chequeando el NEWSGROUP cuando vi un
artículo que decía de CONSEGUIR DINERO
RAPIDO!!. Pensé ... "EN INTERNET??.


Bueno, tengo que ver que tipo de esquema
pueden presentar en Internet". Este
artículo describía la manera de mandar POR
CORREO UN BILLETE DE US $ 1.00 A SOLO SEIS
PERSONAS Y GANAR US $ 50.000.00 EN EFECTIVO
EN 4 SEMANAS!!!. Bueno, cuanto más pensé
acerca de esto más curioso me ponía,? por
qué?, por la manera en que esto trabajaba y
PORQUE SOLO IBA A COSTARME US $ 6.00 (Y SEIS
ESTAMPILLAS), Y ESO ERA TODO LO QUE TENIA
QUE PAGAR ... Y NADA MAS!!!. O.K., los US $
50.000.00 en efectivo podía ser una punto
alto de alcanzar, pero era posible. Me
figuré que tal vez podía tener un alcance de
$1.000.00 mas o menos así que lo hice!!!.


Como decían las instrucciones en el
artículo, mandé por correo un billete de un
dólar a cada uno de las seis personas en la
lista que contenía el articulo. Incluí una
pequeña nota que decía "POR FAVOR INCLUYAME
EN SU LISTA" junto con el dólar. Luego
rehice la lista en donde saque al primero,
moví una posición arriba a cada uno de los
restantes e incluí mi nombre al final de
ella. Esta fue la forma de lograr que el
dinero comenzara a llegarme!!.

Luego tomé
esta lista que acababa de modificar y la RE-
PUBLIQUE EN TODOS LOS NEWSGROUPS Y BBS
LOCALES, TAMBIEN EN CHATS FOROS Y SITIOS
DONDE FUERA A VERLO MUCHA GENTE
y esperé que el dinero
empezara a llegarme, preparado para recibir
entre $1.000 y $1.500 en efectivo. Pero que
agradable sorpresa cuando todos esos sobres
empezaron a llegarme !!!. Supe enseguida de
que se trataban cuando vi que las
direcciones de retorno provenían de todas
partes del mundo, la mayoría de Estados
Unidos, Canadá y Australia!. En mi primera
semana hice unos 20.00 a 30.00 dólares. Para
el final de la segunda semana tenía hecho un
total de más de US$ 1000.00!!!!! En la
tercera semana recibí más de U$ S 10,000.00 y
todavía seguía llegando más. Esta es mi
cuarta semana y ya recibí un total de US$
23.343.00 !!!!!!FUE EXCITANTE!!! !!!No lo
podía creer!!! Tienes que seguirlo y re-
publicarlo donde te sea posible, cuanto más
se publique y más gente lo vea, habrá más
posibilidades para todos de ganar más
dinero, esto determinará cuánto te llegará
por correo!!!.

Es realmente fácil de
pasarlo... Revisemos las razones de por que
hacerlo: los únicos gastos son 6
estampillas, 6 sobres y 6 dólares (un
billete de UN dólar para cada uno en la
lista), luego re-publicar el articulo con tu
nombre incluido en todos los NEWSGROUPS o
BBS que se te ocurran (esto es gratis) y
luego esperar por los sobre que te lleguen.
Todos tenemos 6 dólares para gastar en una
inversión fácil y que no envuelve ningún
tipo de esfuerzo con UNA ESPECTACULAR
RECOMPENZA DE ENTRE US$ 15.000.00 Y US$
120.000.00 en solo 3 a 5 semanas!. Así que
prívate de jugar a la lotería hoy, o mejor
come en casa en vez de ir afuera e invierte
esos 6 dólares en esto que puede darte una
grata sorpresa!!!. No hay forma de
perder !!!

Como funciona esto exactamente???

cuidadosamente proveo la más
detalladas y simples instrucciones de como
vas a conseguir que te llegue dinero fácil.
Prepárate para lograrlo, esta es la forma...

*********************************************
*************** LISTA DE NOMBRES -- LISTA DE
NOMBRES -- LISTA DE NOMBRES
*********************************************
************************


1). Javier Fernández Villanueva Villegas
Apartado Postal N° 2-614
Guadalajara 2, Jalisco, México.
C.P. 44281

2).-Jorge Padilla Abarca

Misión Sto. Tomas No. 7129 Edif. 1 depto. 204

Frac. Kino sur, c.p. 22223

Tijuana Baja California Norte, México.

3).- Herrera Nestor Rubén

Paul Grousac 2891 (entre las calles Ecuador y Portugal)

Ituzaingo , C.P (1714) Buenos Aires Argentina.

4).-jose ortega lecaros

isla tierra del fuego #8617,la granja -santiago
casilla 51146 correo central -plaza de armas
santiago de chile

5).- Juan manuel cazalla

Terrada 2333 ,C.P(8000)Bahia Blanca
provincia de Buenos Aires, Argentina

6) Zaffrani Claudia Lorena

72 nº1535 1/2 pasillo dpto 2 e/ 25 y 26, C.P. (1900) La Plata.

Provincia de Buenos Aires, Argentina