Using the value of a field in one table to fill in a field in another 2

[rmorlan]

A coworker has created an Access DB to track documents. She wants the the numbering to be automatic (first document entered = 1, etc) but distinct by year, so that there is a #1 for 1990, a #1 for 1991, etc.

To do this, she has separated the numbering field (FileNo1990, FileNo1991, etc) into one table and has the year and all other information in a separate Document table. The linked field is called DocNo, which is an Autonumber in the Document table and a number field in the FileNo table.

My question is this:

How can I get the value from the Autonumber field in the Document table into the number field in the FileNo table? The two fields are linked in a 1-to-1 relationship, but that doesn't seem to do anything when a new record is created. Both fields are keys.

Thanks,
Rachel

 

[ck1999]

you will have to use a query and insert data through the query. How come if they one table only has fileno and the relationship is one to one, just use one table?

ck1999

 

[rmorlan]

>>How come if they one table only has fileno and the relationship is one to one, just use one table?<<

She was told that there was no way to get the fileno to restart numbering at 1 for each year if it was all in one table. I'm not good with coding, and I was trying to get the fileno field to update properly using the following code:

Private Sub FileNo1990_AfterUpdate()
Update Document_List, FileNo
Set FileNo1990 = FileNo1990 + 1
When Document_List.Year = 1990
End Sub

Of course, even if this works (it hasn't so far), I don't see why she was told it had to be separate, but our IT people insisted it couldn't be done any other way.

thanks!

 

[lespaul]

She was told that there was no way to get the fileno to restart numbering at 1 for each year if it was all in one table.

she was lied too!


Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases:
The Fundamentals of Relational Database Design
Understanding SQL Joi

 

[PHV]

but our IT people insisted it couldn't be done any other way
Have a look here: faq700-184

If you don't care multiuser issues:
Me!FileNo = 1 + Nz(DMax("FileNo", "DocumentTable", "[Year]=" & Me![Year]), 0)

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886