How to update number field in "many" table from autonumber field in "one" table (1 Viewer)

[sueviolet]

How to update number field in "many" table from autonumber field in "one" table

I solve this problem a long time ago - I can't remember how I did it though!




For example

Table 1
Main ID (autonumber)
field1
field2
field3

Table 2
MainID
HabitatID

Table 3
Habitat ID
field4
field5
field6

What I want to be able to do in my form:

User enters records in table 1. Autonumber is assigned, say '5'. User enteres records in table 3. How do I ensure the records the user has entered in table 1 and 2 are linked?

How can I make the MainID in table 2 the same the MainID in table 1?


What is should look like after user has entered all there records



Table 1

Main ID (autonumber) =5
field1
field2
field3

Table 2

MainID =5
HabitatID =19

Table 3

Habitat ID (autonumber) = 19
field4
field5
field6



How can I do this? Help!

 

[AncientOne]

Surely all you need to do is create a form and subform to enter your data in the first place, not enter it into separate tables? The links will be made automatically because your structure is two one-to-many tables joined by a link table ie a many-to-many relationship.

If you have already entered data on the one side without the corresponding entries on the many side, you can only correct by manual means, using a check box to select records and then running an update query for each MainID/ Habitat ID.

 

[Pat Hartman]

Sounds like you could benefit by downloading the many-to-many sample db that I posted a while back.

 

[meelos]

Re: How to update number field in "many" table from autonumber field in "one" table

I need an answer to this also... But my access jargon is non existent. Could you explain exactly how this update query would work? pretty much exactly the same thing... lookup table 2 to view tables 1 and 3 in a form, then add a new record to tables 1 and 3 and a new record in table 2.

Just cant find a solution to this.

 

[catalin.petrut]

Re: How to update number field in "many" table from autonumber field in "one" table

The autonumber is not editable! So it is no possibility to update a field like this. No query will help. If you see the need for such a thing (changing and updating a index field), you better choose text ar number for it.

On your first problem, all you need to do is insert in table 2 a id field, create the relationships and wizard a form. Youl have a form with a subform just like in the atached db.