Updating related Records in Subform

[samah]

Sorry, I am reposting this question. The original question came up with
wrongly set system date. Slightly rephrased the question as well.

I have two lookup tables. The structure is as follows:

1. tblCities
CityName - Text (pk-single field table)

2. tblBlocks
BlockNo - Text (pk)
CityName - Text (fk)
Area - Text

1-m relationship. (Each city can be divided into many blocks).
Relationship is setup to cascade updates.

I have created a form with a subform to input data. In the main
form I have a combo box bound to CityName field in the tblCities table
and the subform is based on tblBlocks with 2 text boxes one for
BlockNo and another for Area.

The form works fine when I input a new city and existing block nos under
that city for the first time, but later when I select an existing city
in the combo box to add additional block nos., the form displays an
error message and doesn't allow me to move on to the sub form.

What am I doing wrong?

Thanks in advance.

-samah

 

[Steve Schapel]

Samah,

What you appear to be doing here is assuming that by selecting a new
city in the combobox on the main form, you will be thereby accessing
that record. You are not. The combobox is bound to the CityName field.
It shows the CityName of the current record. If you select another
city form the list, then you are attempting to edit the value of the
CityName field in the current record. This of course is not possible,
because it is a uniquely indexed field (primary key) and the effect of
what you are doing would result in two tblCities records with the same
CityName. Does that make sense. You are trying to use a bound control
for record navigation - won't work.

 

[samah]

Samah,

What you appear to be doing here is assuming that by selecting a new
city in the combobox on the main form, you will be thereby accessing
that record. You are not. The combobox is bound to the CityName field.
It shows the CityName of the current record. If you select another
city form the list, then you are attempting to edit the value of the
CityName field in the current record. This of course is not possible,
because it is a uniquely indexed field (primary key) and the effect of
what you are doing would result in two tblCities records with the same
CityName. Does that make sense. You are trying to use a bound control
for record navigation - won't work.

Thank you Steve, I understand. It makes sense.

What I want to do is to add new blocks to existing cities.

I was trying to understand how an order entry example works. Suppose I
miss out an order detail line in a particular order and later want to
open that existing order and add the missed out order detail line. How
do design the form to accomplish this task.

Thank you for your guidance.

-samah

 

[Steve Schapel]

Samah,

To add new blocks to existing cities, one approach would be a continuous
view form, based on the tblBlocks table. All you need to do is add a
new record, enter the data as applicable, and you're done.

On such a form, you could have a combobox for the CityName field, and
the Row Source of this combobox would be the tblCities table, so you are
assured of entering only an existing city.

Alternatively, you could hide the CityName field on this form, and then
place this form as a subform onto a main form based on the tblCities
table. The LinkMasterFields and LinkChildFields properties of the
subform would be set to CityName. Then, when the record for the
required city was current on the main form, the subform would show the
Blocks only for that City, and any new record added to the subform would
automatically be assigned to that City. You would need to decide the
best way to navigate on the main form to the city you want.

 

[samah]

Samah,

To add new blocks to existing cities, one approach would be a continuous
view form, based on the tblBlocks table. All you need to do is add a
new record, enter the data as applicable, and you're done.

On such a form, you could have a combobox for the CityName field, and
the Row Source of this combobox would be the tblCities table, so you are
assured of entering only an existing city.

Alternatively, you could hide the CityName field on this form, and then
place this form as a subform onto a main form based on the tblCities
table. The LinkMasterFields and LinkChildFields properties of the
subform would be set to CityName. Then, when the record for the
required city was current on the main form, the subform would show the
Blocks only for that City, and any new record added to the subform would
automatically be assigned to that City. You would need to decide the
best way to navigate on the main form to the city you want.

Great. Thank you, Steve.

-Samah