Legacy data in combo boxes

[badboybrown]

I know that this is an age old question and it has been approached a
few times, but I've hit a wall and require some help.

I have various combo boxes that contain values that are both "current"
and "not current". The problem is if you try to limit the drop down to
only the "current" values then your old records show a blank if the
field contains a "not current" value.

One of the solutions I have found is this:

SELECT DISTINCTROW tblListBusArea. BusAreaID, tblListBusArea. BusAreaName, tblListBusArea. Active
FROM tblListBusArea
WHERE (((tblListBusAr ea.Active)=True ))
OR (((tblListBusAr ea.BusAreaID)=[Forms]![frmSubmissions]![cboBusArea]))
ORDER BY tblListBusArea. BusAreaName;

.... in the Row Source and a requery in AfterUpdate event.

This solution works nicely on the main form, but doesn't work on a
subform.

The reason (I believe) I found for this is that Access will set the
combo boxes of the subforms when opening up the main form. If the
subform combo box contains "not current" value, then the combo box
remains blank, because the data had not been loaded when the rowsource
was created. Correct me if I'm wrong.

The code I'm using on the subform is:

SELECT tblListTeams.Te amID, tblListTeams.Te amName, tblListTeams.Ac tive
FROM tblListTeams
WHERE (((tblListTeams .Active)=True))
OR (((tblListTeams .TeamID)=[Forms]![frmSubmissions]![chlSubResponses]![cboTeam]))
ORDER BY tblListTeams.Te amName;

I envisioned leaving the rowsource for the subform combo boxes blank
and only setting the rowsource in the OnCurrent event of the main form:

Me!chlSubRespon ses!sfrmRespons esEntry.Form!cb oTeams.RowSourc e =
"SELECT tblListTeams.Te amID, tblListTeams.Te amName, tblListTeams.Ac tive
FROM tblListTeams WHERE (((tblListTeams .Active)=True))
OR (((tblListTeams .TeamID)=[Forms]![frmSubmissions]![chlSubResponses]![cboTeam]))
ORDER BY tblListTeams.Te amName;"

but that gives me the error:

You entered an expression that has an invalid reference to the property Form/Report.

The property may not exist or may not apply to the object you specified.

I'm lost... Please help.

[Ron2006]

Let the query for the combobox select all records.

But in the before update event for the box, where it is used, test to
see if it is active and cancel the event if the selection is NOT active
and give an error msgbox to that effect.

Ron

[Larry Linson]

If you have this code in a Form, and that Form works as a main Form, but
does not work when embedded in a Subform, it is quite likely that your
reference to the Form is the problem. Only _open_ Forms are in the Forms
collection; a Form displayed in a SubformControl is not "open" -- the
instance you are viewing exists as the Form property of the Subform Control.

In a query, to reference a Control named "Joe" on the Form displayed in a
Subform Control named "Subf" which is on main Form named "Mai"

Forms!Mai!Subf. Form!Joe

If I have misunderstood, please clarify.

Larry Linson
Microsoft Access MVP

[badboybrown]

Something I did not make clear before is that the subform is in
Continous view. I will probably use Ron's suggestion as the List is
not very long and add a second Column to the drop-down to give a visual
clue to the status of a selection. Or I'll stop being nit-picky and
just live with it.

Larry, thanks. The info on _open_ forms does make things clearer. I
have tried referring to the subform in different ways.

Form= sfrmResponsesEn try
SubformControl = chlSubResponses
ComboBox = cboTeams

Me!chlSubRespon ses!sfrmRespons esEntry.Form!cb oTeams.RowSourc e = ""

and

Me!chlSubRespon ses.Form!cboTea ms.RowSource = ""

both give me the same error.

Is putting the code in the main forms OnCurrent event the correct place?

[Jeremy Wallace]

Have a look at my post:
http://groups.google.com/group/comp....74232ef371f8ec
(http://tinyurl.com/o4ybh)

Since that writing I have figured out that it can actually be a bit
simpler than this, though II'm really crushed with work right now and
I'm not sure I'll be able to update this anytime in the next week or
so.

Still, it does work as described in that posting.

Jeremy

badboybrown wrote:

Something I did not make clear before is that the subform is in
Continous view. I will probably use Ron's suggestion as the List is
not very long and add a second Column to the drop-down to give a visual
clue to the status of a selection. Or I'll stop being nit-picky and
just live with it.

Larry, thanks. The info on _open_ forms does make things clearer. I
have tried referring to the subform in different ways.

Form= sfrmResponsesEn try
SubformControl = chlSubResponses
ComboBox = cboTeams

Me!chlSubRespon ses!sfrmRespons esEntry.Form!cb oTeams.RowSourc e = ""

and

Me!chlSubRespon ses.Form!cboTea ms.RowSource = ""

both give me the same error.

Is putting the code in the main forms OnCurrent event the correct place?

[badboybrown]

Jeremy,

Thanks, your idea works great and loads the rowsource as required.

My only issue now, is that the combo box is the first field in the
subform and it doesn't fire the dropdown automatically and doesn't load
the rowsource until I tab past and then return. Also, if I mouseclick
directly onto the dropdown, the rowsource is blank until I click away
and then try again.

The combo box is the first field in the TabOrder and additionally I've
tried setting the focus to the combobox in the OnEnter event of the
SubFormControl.

[badboybrown]

Further to this:

Putting a date field first, and having the combo second solves
everything.

I can live with it.

Thanks for all your help.
badboybrown wrote:

Jeremy,

Thanks, your idea works great and loads the rowsource as required.

My only issue now, is that the combo box is the first field in the
subform and it doesn't fire the dropdown automatically and doesn't load
the rowsource until I tab past and then return. Also, if I mouseclick
directly onto the dropdown, the rowsource is blank until I click away
and then try again.

The combo box is the first field in the TabOrder and additionally I've
tried setting the focus to the combobox in the OnEnter event of the
SubFormControl.

[Jeremy Wallace]

Yeah, unfortunately, I had to do the same in one of my forms. I'll let
you know if I come up with a way around that.

Jeremy Wallace
Fund for the City of New York
metrix daht fcny daht org

badboybrown wrote:

Further to this:

Putting a date field first, and having the combo second solves
everything.

I can live with it.

Thanks for all your help.
badboybrown wrote:

Jeremy,

Thanks, your idea works great and loads the rowsource as required.

My only issue now, is that the combo box is the first field in the
subform and it doesn't fire the dropdown automatically and doesn't load
the rowsource until I tab past and then return. Also, if I mouseclick
directly onto the dropdown, the rowsource is blank until I click away
and then try again.

The combo box is the first field in the TabOrder and additionally I've
tried setting the focus to the combobox in the OnEnter event of the
SubFormControl.