Combo boxes producing error message

[Guest]

I'm a newby in need of assistance.

I'm creating a form based on a query which includes two table-based combo
boxes: one from an ID#[CityID], with a city name field [CityName] and city
population field [CityPopulation]; and the other from an ID#[CountyID],
county name [CountyName], and county populations [CountyPopulation]. I only
require that the form enter a value in ONE of the combo boxes. But, when I
enter data into the form without utilizing BOTH combo boxes, I get an error
message: for instance, if I only populate the 'CityID' query field with the
City combo box, I get an error message: "the MS Jet database engine cannot
find a record in the table 'tblCounties' with key matching field(s)
"CountyID'. However, if I complete BOTH combo boxes in the form--it records
everything just fine.

My question: How can I save my form completing only ONE of the CityID or
CountyID combo boxes while leaving null values in the fields produced by the
other/alaternate combo box (I have a subsequent field which utilizes the Nz()
function to look for the non Null value in one of the two fields I'm dealing
with here)?

 

[Ed Robichaud]

If I understand your setup correctly, you're using a data entry form with 2
combo-boxes (bound to the CityID and CountyID respectively), and you're
getting an error message when attempting to save a record. Some points to
consider:

1. Is the table that you're trying to post a record to, part of a defined
relationship, like:

tblMapRef
mapID -primary key
cityID -foreign key (linked to look-up table tblCity)
countyID - -foreign key (linked to look-up table tblCounty)
otherFields....

tblCity
cityID - primary key
cityName
cityPopulation
etc.....

and are the keys all the same data type, and is relational integrity
enforced?


2. Do you have look-up tables defined, and if so, are the limited to list
properties set to "Yes"?

3. Do you have any table/form validation rules that require an entry (other
than null)?

3. Are the combo boxes limited to list?

BTW, you could improve the data normalization by including the CountyID in
the tblCity. That way, the look-up table would already "know" what county
each city is in (like how zip code look-ups work).

-Ed


Have you made [CityID] and/or [CountyID] required fields in your underlying
tables?

 

[Guest]

Checked everything without success. Let me run through your points:

1. Yes, Yes & Yes
2. My combo boxes reference tblCities & tblCounties and are limited to list
3. No
3. Yes, the combo boxes are limited to list.

Sugestions?

THanks Ed.

If I understand your setup correctly, you're using a data entry form with 2
combo-boxes (bound to the CityID and CountyID respectively), and you're
getting an error message when attempting to save a record. Some points to
consider:

1. Is the table that you're trying to post a record to, part of a defined
relationship, like:

tblMapRef
mapID -primary key
cityID -foreign key (linked to look-up table tblCity)
countyID - -foreign key (linked to look-up table tblCounty)
otherFields....

tblCity
cityID - primary key
cityName
cityPopulation
etc.....

and are the keys all the same data type, and is relational integrity
enforced?


2. Do you have look-up tables defined, and if so, are the limited to list
properties set to "Yes"?

3. Do you have any table/form validation rules that require an entry (other
than null)?

3. Are the combo boxes limited to list?

BTW, you could improve the data normalization by including the CountyID in
the tblCity. That way, the look-up table would already "know" what county
each city is in (like how zip code look-ups work).

-Ed


Have you made [CityID] and/or [CountyID] required fields in your underlying
tables?

I'm a newby in need of assistance.

I'm creating a form based on a query which includes two table-based combo
boxes: one from an ID#[CityID], with a city name field [CityName] and city
population field [CityPopulation]; and the other from an ID#[CountyID],
county name [CountyName], and county populations [CountyPopulation]. I
only
require that the form enter a value in ONE of the combo boxes. But, when
I
enter data into the form without utilizing BOTH combo boxes, I get an
error
message: for instance, if I only populate the 'CityID' query field with
the
City combo box, I get an error message: "the MS Jet database engine cannot
find a record in the table 'tblCounties' with key matching field(s)
"CountyID'. However, if I complete BOTH combo boxes in the form--it
records
everything just fine.

My question: How can I save my form completing only ONE of the CityID or
CountyID combo boxes while leaving null values in the fields produced by
the
other/alaternate combo box (I have a subsequent field which utilizes the
Nz()
function to look for the non Null value in one of the two fields I'm
dealing
with here)?

 

[Ed Robichaud]

After re-reading your original post, I'm still unsure of a few things:

1 - is this a data entry form, and if so what is its record source?

2- are you using the City & County combo boxes for data entry, or to filter
existing records?

3 - you state/imply that other fields are filled by these combo boxes - how
are you doing that?

The error message you quote indicates a relational integrity problem -
you're trying to add a child record that has no related parent.

If all else fails, zip it up and send me a copy.

--
Ed Robichaud
(e-mail address removed)
703-860-0108

Checked everything without success. Let me run through your points:

1. Yes, Yes & Yes
2. My combo boxes reference tblCities & tblCounties and are limited to
list
3. No
3. Yes, the combo boxes are limited to list.

Sugestions?

THanks Ed.

If I understand your setup correctly, you're using a data entry form with
2
combo-boxes (bound to the CityID and CountyID respectively), and you're
getting an error message when attempting to save a record. Some points
to
consider:

1. Is the table that you're trying to post a record to, part of a defined
relationship, like:

tblMapRef
mapID -primary key
cityID -foreign key (linked to look-up table tblCity)
countyID - -foreign key (linked to look-up table tblCounty)
otherFields....

tblCity
cityID - primary key
cityName
cityPopulation
etc.....

and are the keys all the same data type, and is relational integrity
enforced?


2. Do you have look-up tables defined, and if so, are the limited to
list
properties set to "Yes"?

3. Do you have any table/form validation rules that require an entry
(other
than null)?

3. Are the combo boxes limited to list?

BTW, you could improve the data normalization by including the CountyID
in
the tblCity. That way, the look-up table would already "know" what
county
each city is in (like how zip code look-ups work).

-Ed


Have you made [CityID] and/or [CountyID] required fields in your
underlying
tables?

I'm a newby in need of assistance.

I'm creating a form based on a query which includes two table-based
combo
boxes: one from an ID#[CityID], with a city name field [CityName] and
city
population field [CityPopulation]; and the other from an ID#[CountyID],
county name [CountyName], and county populations [CountyPopulation]. I
only
require that the form enter a value in ONE of the combo boxes. But,
when
I
enter data into the form without utilizing BOTH combo boxes, I get an
error
message: for instance, if I only populate the 'CityID' query field with
the
City combo box, I get an error message: "the MS Jet database engine
cannot
find a record in the table 'tblCounties' with key matching field(s)
"CountyID'. However, if I complete BOTH combo boxes in the form--it
records
everything just fine.

My question: How can I save my form completing only ONE of the CityID
or
CountyID combo boxes while leaving null values in the fields produced
by
the
other/alaternate combo box (I have a subsequent field which utilizes
the
Nz()
function to look for the non Null value in one of the two fields I'm
dealing
with here)?