Multiple tables on one form (1 Viewer)

[utvolsfan]

Hello all!

I have five different forms. On one of the forms I would like to be able to pull data from 2 different tables. Another words, when a user updates these particular 2 fields in their form, I want it to update their table and I also want that field to be displayed on another form. I did not set these up as subforms because of security/permissions that are different for different users on each form. I did a search on forms and found a previous post where someone was it from a query. (allowing the query to create a form) Is that the only way to do it? I have a long and complicated form and do not want to have to recreate the entire form.

The tables are linked by a unique id field. Any help would be greatly appreciated.

Karen

 

[Pat Hartman]

1. Your forms should always be based on queries. That allows you to open the form with a limited number of records in the form's recordset. This is not even an issue in most databases since properly structured tables almost always need to be joined to produce the recordset needed for a form.
2. Data should NOT be stored in multiple places.
3. What is displayed on a form is controlled by the form's recordsource.

We'll need a little more detail to help solve your problem.

 

[utvolsfan]

Hello Pat,

First, thank you for responding. This will be a long response because I want to tell you exactly what I am trying to do. All of my forms are based soley from tables. That was the only way that I could figure out to make the security work. I would still be called a beginner at Access, but, have read lots of help and a very thick Access book. The database that I have is comprised of 5 tables which make up 5 forms. I have 5 groups of people (engineering, signaling network control, net admin, etc) that access the database. Each one of those 5 groups need write access to their form and read access to the other forms. I have a macro set up that when they need to go to another form (other than their own) they press the command button and it asks them for a record number (parameter query). The real problem here is that the first group may add 5 records and only 3 of them will be accessed by the net admin. Their is no field that is unique to each record that will appear on each form, so, I was using a field called unique identifier (auto number field) for the record number and primary key. But, when net admin only has info that pertains to 3 of those 5 records added by engineering, then suddenly engineering has records 1-5 and net admin has 1-3 and they are not necessarily matching up. Another words, record 2 on engineering form and record 2 on net admin may not be the same record. This causes major confusion. I realize that I may have to do some major changes, but would really prefer not to have to redo forms as all info is technical and it would be very difficult to completely take database down because of it being used 24/7. Sorry, for long response, but I wanted to explain exactly what to do. In the Access book and help they base the forms mainly on tables. That is why I did it that way to begin with. Any help that you can give me is greatly appreciated. I am planning to take some Access classes, but the first ones offered are still some time away.

Karen

 

[Pat Hartman]

I still can't envision what you are trying to do. You seem to be all wrapped up in security (which is hopeless in Access) and are letting that dictate the functionality of your application.

Lookup addnew in help. It will tell you how to add records with ADO or DAO although I'm not sure that will help you. You can also search the archives here if you can't understand the examples in help.