Autofill field form to table

[Old Timer]

updating field, Form to Table

Opening the form, I wish to type in a number in my "CodeID" field, for instance #1060, I then wish the inserted number 1060 to trigger an event that will fill in the next field with the Township, or Village or City as assigned to inputed ID number.

I have linked the two tables together as a 1 to many, the main table "tblRegister" being the "Many" and the "CodeID" table the "One". all seems to work well. I open the form, open properties and select the linked table query as a source.

Opening the form, and using the linked table query as "source" the form acts and runs perfect. I type in CodeID field "1060",

The Township assigned ID fills with "Spencer TP". JUST FINE! EXCEPT!! WHEN I LOOK AT THE TABLE "tblregister", all information put in the form is placed in the table as expected WITH THE EXCEPTION of the "Field I want filled". The TOWNSHIP NAME FIELD remains empty in the tblregister.

PROBLEM: The field is filled in the form but remains empty in the permanent
"tblRegister" table.

WHAT AM I NOT DOING, I worked with Paradox DOS in years past and used a "Lookup" table and all worked real easy. Will really really appreciate help. At 78 years of age, maybe I am getting to old. Thank you so much for your time and effort.
Old Timer:

 

[Rob.Mills]

Just out of curiousity, why do you want the data in the one table to also be in the many table? As long as you have a field in both tables to build a relationship upon then you can create queries that link the two and there you go. That's the beauty of relationships.

 

[Old Timer]

Reply to Rob Mills

Just out of curiousity, why do you want the data in the one table to also be in the many table? As long as you have a field in both tables to build a relationship upon then you can create queries that link the two and there you go. That's the beauty of relationships.

I designed a program used in the Auditor's office. Not using VBA, but using generic Access 2000 program, switchboard, forms, reports and all. The girl inputting the transfer register of real estate sales, was accustomed to my old program Paradox 4.5 I created 10 years ago. Still worked fine, but times change and DOS is primitive with todays operating systems. Courthouse updated to Windows 2000, all computers networked. So I converted and changed 4 other programs they had been using from me back in the 90's.

Now she has asked, could I make this program work like the old one. When she entered a new Real Estate transfer, the first field was the "generic" code number assigned by the county to identify each separate Township, village, Corporation. In the old program, when she entered 1060 for instance, the next field (twp, Vill, City name) would be populated. That way, there would be no spelling errors. All in the form would be entered onto and in a table of data. This table would grow through the years, because of all the transfers of land. By only running a query from this table asking for Year, Month and maybe Township name, a user could go backwards quickly to any past year and create query, run report using that query and get a print out as desired.

As it stands now, the query is a memory item, and the lookup and addin part never gets into the table. I want it entered in some manner to help her or any new input operator with the tp name,vil or city, and shut down mispelled words.

That way all printouts of records, using the tblRegister as a data source will pick up all the information needed. With this table, hardly ever is the same information entered twice in tens of thousands of records. The only thing that don't change over the years is the Parcel number, assigned by the county to issue tax bills. Grantors change, Grantees change. Land is split into smaller sections. Its a changing database. Once a year a printout of the table is created in record form, sewed into book form for permanent county record. So the table get more important to title searchers each passing year, looking for a search of title containg past owners.

Hope I have explained this so you can understand what I need. And why.

 

[Pat Hartman]

In a relational database it is not necessary to store duplicate data. Your form is working exactly as it should. All you need to do is to change the reports to use a query as their recordsource rather than a table. Then you can use the same join you use in the form to bring all the necessary data together. Just create the appropriate query, change the report's recordsource to point to that query, and select the additional fields from the lookup table rather than from the main table.

 

[Old Timer]

In a relational database it is not necessary to store duplicate data. Your form is working exactly as it should. All you need to do is to change the reports to use a query as their recordsource rather than a table. Then you can use the same join you use in the form to bring all the necessary data together. Just create the appropriate query, change the report's recordsource to point to that query, and select the additional fields from the lookup table rather than from the main table.

Thank you for taking the time to answer. Guess I just can't get the DOS feeling out of my mind. This evening after supper, I shall create a report with field information as would normally be needed. Use the joined table query as a source and print my report. Am I following right. I think I will use a parameter query, and ask for information for a specific month, Twp or Twp's and give it a look. Can add the closed brackets to make any paramenter I need off any field. Sounding better all the time. And get my mind off of populated township field. Its in the separate CodeId table waiting to used at any time. Now this old 78 year mind is working again.

I can give the input operator the updated form using the linked one to many tables. She will be mentally fine, seeing the form populate itself with a tp, vil or city name. I will add the reports on switchboard as needed. Sounds super, how can I be so thick. I should'nt allow them to look at the main Register Table, then they won't be worried.

 

[Old Timer]

Thank You for the enlightment

Thanks Pat, it took your short sentence to get my mind thinking right. I was still in a "Flat" instead of "Relational" mode of thinking. Kicked all this around in my mind, now its clear to me. I just couldn't cut away from the DOS program I used for years. While using Access for several years now, I still wasn't using it like I should been. Normalizing was just a word, I thought and used but really didn't put into play.

Now I will. Even in my upper "70's" now , I still am being taught steadily.

 

[Pat Hartman]

In a couple of years, I'll change my tag line to, "Old Bridge players never die. They just shuffle away."