My form thinks I am adding records, I want to choose one to edit

[Genalube]

I am new to access, so I figure this is an easy question to answer. I just can't find it on my own.

Background:

Two tables with a one to many relationship (irelevantField1 is a place holder, these fields are not relevant to posting, the number of fields represented is correct)

Parcels:
ParcelNo
irelevantField1
irelevantField2
irelevantField3

Properties:
TaxID
irelevantField1
ParcelNo
irelevantField2
irelevantField3

The forms I have enter the data in these and many other tables, I have grouped the tables based on typical workflow for finding and entering the data. The Properties' data is entered first, before the ParcelNo is known.

I want to add the ParcelNo to the Properties table from the same form I am entering the Parcels' data. The form as I have conceived it has a subform (wizard generated) that is based on the Properties table, I have included the TaxID and ParcelNo fields. I included the ParcelNo field just to verify that the parcel number that is entered at the top of the form is used in the subform (it fills in automatically). I then changed the TaxID box to a combo box based on the Properties.TaxID field, as a query in the row source property.

This is where what want to happen differs from what actually happens, it won't let me choose an existing parcel number because it creates a duplicate record. It is trying to create a NEW record in the Properties table, I want it to choose from the existing ones and put the appropriate number in the field. More than one Property will have the same ParcelNo.

Any input would be appreciated.

[NeoPa]

If you want to update more than one table on a form, you need to create an updatable query to base the form on.
Make sure the query is updatable before progressing as that is sometimes hard to achieve.
When you have this, you need to set the form's 'Data Entry' property to No. When set to Yes, it will continually add new records only.

[Genalube]

If you want to update more than one table on a form, you need to create an updatable query to base the form on.
Make sure the query is updatable before progressing as that is sometimes hard to achieve.
When you have this, you need to set the form's 'Data Entry' property to No. When set to Yes, it will continually add new records only.

I don't think I understand what you wrote. I know what a query is and can read basic ones just fine in SQL veiw. What you wrote is just likely over my head. Permit me a follow up question. If I have a form based on just the TaxID and ParcelNo fields in the properties table, I can change the parcel number all I want and it will save to the table. Then I can switch to the next record and change it too.

In the Parcels form I want to be able to select all the properties that apply to a parcel from a list of all the properties, and I thought I could do this by making the form I just described a subform to the Parcels form.

Does this comment change your understanding of my problem, and if not is there a resource I can tap that will keep me from bugging people like yourself. A good book?

[NeoPa]

Not a book, but someone posted a link on here recently which I've now added to my favourites.
It could help.
Unfortunately - I find it's at work that I've added it :(
Never mind, you may find it if you can search in this forum, otherwise someone is likely to post it in here for you sometime.

[MMcCarthy]

I am new to access, so I figure this is an easy question to answer. I just can't find it on my own.

Background:

Two tables with a one to many relationship (irelevantField1 is a place holder, these fields are not relevant to posting, the number of fields represented is correct)

Parcels:
ParcelNo
irelevantField1
irelevantField2
irelevantField3

Properties:
TaxID
irelevantField1
ParcelNo
irelevantField2
irelevantField3

The forms I have enter the data in these and many other tables, I have grouped the tables based on typical workflow for finding and entering the data. The Properties' data is entered first, before the ParcelNo is known.

I want to add the ParcelNo to the Properties table from the same form I am entering the Parcels' data. The form as I have conceived it has a subform (wizard generated) that is based on the Properties table, I have included the TaxID and ParcelNo fields. I included the ParcelNo field just to verify that the parcel number that is entered at the top of the form is used in the subform (it fills in automatically). I then changed the TaxID box to a combo box based on the Properties.TaxID field, as a query in the row source property.

This is where what want to happen differs from what actually happens, it won't let me choose an existing parcel number because it creates a duplicate record. It is trying to create a NEW record in the Properties table, I want it to choose from the existing ones and put the appropriate number in the field. More than one Property will have the same ParcelNo.

Any input would be appreciated.

1. In the forms design view, click on the frame around the subform. Open the properties window and under the data tab look in the properties 'Link Child Fields' and 'Link Master Fields' . Are these both set to Parcel ID.

2. Close the form and in tables view go to the relationships window. You will find the icon on the toolbar. Click on the relationship between the two tables. Are all the cascade boxes ticked?

[Genalube]

To mmcarthy,

All those things sheck out.

To neoPa, looked about a bit and didn't find it. I'll keep looking.

[MMcCarthy]

OK

Open the Properties table in design view. Go to the ParcelNo field.

Go to the Indexed property and change it to Yes (Duplicates OK).

Make sure the Required property is set to Yes.

[NeoPa]

Sorry Genalube - I'll try to remember to look it out tomorrow - I have it as a favourite at work so should be easy assuming I can find the time (& I remember at that time :duh: )

[NeoPa]

MMcCarthy just posted it again.

( http://www.techonthenet.com/access/index.php ).

[MMcCarthy]

Sorry Genalube - I'll try to remember to look it out tomorrow - I have it as a favourite at work so should be easy assuming I can find the time (& I remember at that time :duh: )

This what you're looking for Adrian.

http://www.techonthenet.com/access/index.php

Mary

[NeoPa]

This what you're looking for Adrian.

http://www.techonthenet.com/access/index.php

Mary

lol - SNAP - Yes ;)

[MMcCarthy]

lol - SNAP - Yes ;)

Both posted at 1.03 on the dot.

Think there's some psychic connection.

Mary