Combo box for related records AND non-related records

[rocketD]

Hello,

Sorry if this question has been answered, but my searches have not
yielded the posts. Here is what I want to do: I have a form with a
combo box for City and one for State. I have two lookup tables:

lkpCity
cityID, City, stateID

lkpState
stateID, State

I have the combo box for City set up, and the one for State to depend
on the control for City.
These are necessary because we've got a lot of spelling errors. Very
often, however, the respondents whose data we are entering fail to
provide a city, but list their state. If this happens, I can't get a
list of states in the State combo box because City is null. I can
take off the dependency so that all states are listed, but I don't
want accidental entry errors like Albuquerque, NH instead of
Albuquerque, NM. Is there a specification I can add that will get
around this problem?

This is my code for the States box:
SELECT lkpStates.stateID, lkpStates.state FROM lkpStates INNER JOIN
lkpCities ON lkpStates.stateID=lkpCities.stateID WHERE
(((lkpCities.cityID) Like [forms].[fmNewParticipant].[City])) ORDER BY
lkpStates.state;

Thanks,
Dara

 

[Jim Bunton]

I think I would put both city AND state on the same combo box so it lists
City - State for all combinations

so your rowsource query returns CityId, StateId, City & " - " & State AS
CityState

order by city, state

set the combo properties to 3 columns width 0; 0; 2 [whatever's right for
the last one in cms]
all your users see is a list of cities with possible states (but you must
make sure they're all there of course!
you can reference the response with comboname.column(0) for the city code
and comboname(1) for the stateId

If you can't list all possible combinations put an onclick event of the
second combo which takes appropriate action if the first one's empty


[and possibly use 3 tables! take stateId out of your first one then add a
thid table 'CityState' cols CityId, StateId with the combined key of both
columns]

hope this is helpful

 

[rocketD]

I think I would put both city AND state on the same combo box so it lists
City - State for all combinations

so your rowsource query returns CityId, StateId, City & " - " & State AS
CityState

order by city, state

set the combo properties to 3 columns width 0; 0; 2  [whatever's right for
the last one in cms]
all your users see is a list of cities with possible states (but you must
make sure they're all there of course!
you can reference the response with comboname.column(0) for the city code
and comboname(1) for the stateId

If you can't list all possible combinations put an onclick event of the
second combo which takes appropriate action if the first one's empty

[and possibly use 3 tables! take stateId out of your first one then add a
thid table 'CityState' cols CityId, StateId with the combined key of both
columns]

Hello,

Sorry if this question has been answered, but my searches have not
yielded the posts.  Here is what I want to do:  I have a form with a
combo box for City and one for State.  I have two lookup tables:

lkpCity
  cityID, City, stateID

lkpState
  stateID, State

I have the combo box for City set up, and the one for State to depend
on the control for City.
These are necessary because we've got a lot of spelling errors.  Very
often, however, the respondents whose data we are entering fail to
provide a city, but list their state.  If this happens, I can't get a
list of states in the State combo box because City is null.  I can
take off the dependency so that all states are listed, but I don't
want accidental entry errors like Albuquerque, NH instead of
Albuquerque, NM.  Is there a specification I can add that will get
around this problem?

This is my code for the States box:
SELECT lkpStates.stateID, lkpStates.state FROM lkpStates INNER JOIN
lkpCities ON lkpStates.stateID=lkpCities.stateID WHERE
(((lkpCities.cityID) Like [forms].[fmNewParticipant].[City])) ORDER BY
lkpStates.state;

Thanks,
Dara- Hide quoted text -

- Show quoted text -

Hmm...that might work...regarding the onclick event, if I'm going to
do VB, I could do an afterupdate event on City instead that uses an if-
then-elseif statement, so that state would populate with a full list
of States if City is null, else would populate with the state that
matches the city on the form... Do you know what the code is for
populating a combobox using vb? (I definitely don't know all the city-
state combos, our program expands every day.)

Thanks for the suggestions.

Dara

 

[John Spencer]

SELECT DISTINCT lkpStates.stateID, lkpStates.state
FROM lkpStates INNER JOIN lkpCities
ON lkpStates.stateID=lkpCities.stateID
WHERE lkpCities.cityID Like Nz([forms].[fmNewParticipant].[City],"*")
ORDER BY lkpStates.state;

or Better might be
SELECT DISTINCT lkpStates.stateID, lkpStates.state
FROM lkpStates INNER JOIN lkpCities
ON lkpStates.stateID=lkpCities.stateID
WHERE lkpCities.cityID =[forms].[fmNewParticipant].[City] OR
[forms].[fmNewParticipant].[City] is Null

ORDER BY lkpStates.state;

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County