Drop Down List Catch 22

[Mike Thomas]

This should be a familiar one to somebody.

I have a drop down list in a screen (receipts) populated as per the SQL
clause below

SELECT Locations.lopk, Locations.Location
FROM Locations ORDER by location

I want to add a where clause as below so the the user cannot chose an
inactive location from the open list

SELECT Locations.lopk, Locations.Location
FROM Locations ORDER by location
WHERE (((Locations.Inactive)=False));

Problem is, the inactive locations for old receipts now do not show up in
the list box. The box is now blank when the users ladns on a receipt with a
now inactive location.

So, I want the location code to be displayed in the dd list box, eve if it
is inactive, but I do not want other inactive locations to be shown in the
list when the user opens it.

Any ideas?
Mike Thomas

 

[Rick Brandt]

This should be a familiar one to somebody.

I have a drop down list in a screen (receipts) populated as per the SQL
clause below

SELECT Locations.lopk, Locations.Location
FROM Locations ORDER by location

I want to add a where clause as below so the the user cannot chose an
inactive location from the open list

SELECT Locations.lopk, Locations.Location
FROM Locations ORDER by location
WHERE (((Locations.Inactive)=False));

Problem is, the inactive locations for old receipts now do not show up in
the list box. The box is now blank when the users ladns on a receipt with a
now inactive location.

So, I want the location code to be displayed in the dd list box, eve if it
is inactive, but I do not want other inactive locations to be shown in the
list when the user opens it.

SELECT Locations.lopk, Locations.Location
FROM Locations ORDER by location
WHERE Locations.Inactive = False
OR Locations.lopk = Forms!FormName!lopk

Effectively this says the list should include all active locations as well
as the location currently set on the current record regardless of whether
it's active or not. You will need to issue a requery on the ComboBox in
the form's Current event so it will change as you navigate.

 

[Mike Thomas]

Rick,

Thanks for your reply - one Access naming conventions really baffle me, and
I did not mention that this drop down list was in a subform set to display
as a datasheet - in other words, the subform is a data grid with a dd list
box on each line. No matter what I enter, I always get a display box asking
for the value of lopk.

Here are the names of the forms and controls:

Parent form receipts
Child form name Receipts_IPacks
Subform control name Receipts_IPacks (could using the same name cause a
problem?)
dd list control name Location
key used in dd list lopk
bnd to subfm recset fld plLocationKey

Do you know whether what I am trying to do is possible? If so, do you know
what the syntax would in the

OR Locations.lopk = Forms!FormName!lopk

cause?

Many thanks
Mike Thomas

 

[Rick Brandt]

Rick,

Thanks for your reply - one Access naming conventions really baffle me, and
I did not mention that this drop down list was in a subform set to display
as a datasheet - in other words, the subform is a data grid with a dd list
box on each line. No matter what I enter, I always get a display box asking
for the value of lopk.

Here are the names of the forms and controls:

Parent form receipts
Child form name Receipts_IPacks
Subform control name Receipts_IPacks (could using the same name cause a
problem?)
dd list control name Location
key used in dd list lopk
bnd to subfm recset fld plLocationKey

Do you know whether what I am trying to do is possible? If so, do you know
what the syntax would in the

OR Locations.lopk = Forms!FormName!lopk

To reference an item on a subform the syntax (generically) is...

Forms!MainFormName!SubformControlName.Form!ControlName

so in your case...

Forms!receipts!Receipts_IPacks.Form!Location

 

[Mike Thomas]

Many thanks Rick, that was it.

Mike Thomas

To reference an item on a subform the syntax (generically) is...

Forms!MainFormName!SubformControlName.Form!ControlName

so in your case...

Forms!receipts!Receipts_IPacks.Form!Location