Updating related Records in Subform

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

samah - 14 May 2007 05:47 GMT

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

Reply to this Message

Steve Schapel - 14 May 2007 10:09 GMT

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.

Signature

Steve Schapel, Microsoft Access MVP

> Sorry, I am reposting this question. The original question came up with
> wrongly set system date. Slightly rephrased the question as well.
[quoted text clipped - 27 lines]
>
> -samah

Reply to this Message

samah - 14 May 2007 12:06 GMT

> Samah,
>
[quoted text clipped - 8 lines]
> 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

Reply to this Message

Steve Schapel - 17 May 2007 11:00 GMT

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.

Signature

Steve Schapel, Microsoft Access MVP

> Thank you Steve, I understand. It makes sense.
>
[quoted text clipped - 4 lines]
> open that existing order and add the missed out order detail line. How
> do design the form to accomplish this task.

Reply to this Message

samah - 19 May 2007 13:00 GMT

> Samah,
>
[quoted text clipped - 14 lines]
> 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

Reply to this Message