Full Version: Repeating Field on Each Form
UtterAccess Forums > Microsoft Access > Access Forms
NHS
Hi All,
opefully someone may be able to help me out. I work for an NHS hospital and i have been asked todevelop a relatively simple database to hold details of all the hospitals suppliers.
There are a number of different forms which show company/supplier information, and ideally, i would like the company/supplier name and supplier code to be displayed on each form, relating to the company/supplier. Obviously, the company/supplier name and supplier code shown on each form will change when different company/supplier records are accessed.
Basically, how do i get the forms to do the above?
Many thanks
Phil
Jack Cowley
Welcome to Utter Access Forums!
This is a tough question to answer given the amount of details you have supplied. A number of different forms does not mean too much so it would help someone here to help you if you can give the details about the forms and why you need the Company/Supplier name and ID. Also, be sure that your structure is normalized before you venture into your forms. If your tables are properly normalized then showing or entering data on forms will be much easier.
hth,
Jack
Larry Larsen
Hi Phil
The picture you have painted in my view is a single form displaying company/supplier name and supplier code as you navigate through the records..?
HAs Jack has already pointed out some indiction of your table structure would help and a reason I think he's asking is because you said.."number of different forms which show company/supplier information".
If you have a table of company/supplier information then it will be very simple to extract and display..
thumbup.gif
NHS
Right, i'll try and explain as best i can. Its a bit difficult though as i'm not really very good with Access.

The database holds a wealth of information on a supplier. Information ranges from telephone numbers, addresses to contact names and insurance information. Obviously we don't want all of this information displayed all on one page/form so we want the user to navigate between forms/pages to view different types if info about each supplier.

Now when a user goes through each form/page to view the different bits of info, such as the suppliers bank inforamtion, we want the supplier name and supplier reference (also called Supplier ID) to be shown at the top of each page/form, so that a database user does not lose trach of what supplier record they are viewing.

Also, there are a good few tables in the database. Bank info is held in one table, contact details in another table and so on. In each table, there is a Primary Key, the PK is the same throughout each table, it is called 'Supplier Reference'.

I have attached the database i have made so far (its been scanned for viruses - all is ok) to give an idea of what i want to achieve. There are no relationships established as of yet, mainly because i havent got a clue and havent got that far.

Hopefully you can help.

Many thanks

Phil

P.S. The main navigation page/form for the attached database is in the 'Forms' part of the database, its called "Main_Page".
ScottGem
I Think I see what you are talking about and the answer is fairly simple. You have two options. The one I recommend is to use a tab control on your form and put most of the controls on the tabs. Size the tab control to leave some space above the tab control and you can put the controls you want to display all the time above the tabl control. As the user navigates from tab to tab, that info above the control will always be there.
On alternative is to use a Header on the form and put your constant control in the header.
HTH
NHS
OK, i think i'm getting in way over my head.
If i put all of the supplier data in one table, but have many forms showing the data from the one table, could this be done????
I have tried in the past to create a blank form, and put fields on the form from different tables, but i couldnt do that. Is there a way of doing this, if so, can you explain. If possible, dumb it down. lol.
Many thanks
Phil
ScottGem
A form can only be bound to a single table or query. The way you work with multiple tables is by using subforms. You can create a tabbed interface and put subforms in the tabs to deal with child tables.
NHS
I dont meen to sound ungreatful, but im not very good with Access, and most of the answers im getting are talking to me as if i know as much as you.
Could you possibly explain what i need to do, but please bear in mind my novice experience of Access.
I have asked about putting all data into one table, and that seems a big problem. Normalising the database and having a number of different tables seems the best bet.
HAs i said in a previous post, i want a company name/supplier name to be displayed at the top of each form, but obviously the name will change when different supplier details are viewed.
If you have difficulty in understanding in what im on about, take a look at the database i have attached, it may shine some light on what i want.
Phil
ScottGem
I HAVE explained what you need to do. I think I understood what you were asking. Please reread what I've suggested. If you have specific questions about my suggestions feel free to ask for clarification. But I think I've given you what you need if you review what I've said.
oakbarn
You need to create the tables and the relationship between them.
ort of the Knee bone is connected to the thiegh bone type of song.
You have your tables with the Primary Key (Tablesupplier)
Example
SupplierKID Autonumber Key
SupplierName Text
SupplierAddress Text
SupplierCity Text
and so on with the supplier information
tblProduct
ProductKID Autonumber Key
ProductName Text
SupplierFK Long Int
and other product data.
You then relate the SupplierKID to the tblProduct.SupplierFK using the Relationship.
This means that you have a list of suppliers and a list of products.
It is a one to many relationship. You have many products, but only one supplier per product.
If you have a many to many relationship, meaning that a product may have more than a single supplier, you will need a third table and the tblProduct will not need the SupplierFK field.
you third table would be
tblProductSupplier
SuplProductKID Autonumber Key
ProductFK Long Int
SupplierFK Long Int
You would link
ProductFK to ProductKID
and
SupplierFK to SupplierKID
You create your tables so that you do not have to enter the information more than once.
You can then create quries for forms and subforms. If you have the data linked correctly, the forms wizard does a good job when creating main/subforms for a novice. Once you have learned to do it with the wizard, you can then start doing them without the wizard.
You need to list all of the information that you need and what are you trying to do with the data. If you have a list such as an Order that contains products and suppliers and you want to add that data each time, you want the repeated data in a different table. That is normalization.
You do not want to have to enter the supplier details more than once, so you need a supplier table.
You do not want to add the product dertails more than once, so you need a product table.
Any selection of something that is static, that can be repeated on another order, needs its own table.
Eack of these need a Key Field, normally an automunber field.
Each of the tables where the data will go needs a foreign key, set to long integer. You use the relationship screen to "link" the data.
Basically, you need to write the flow down of what you are trying to do and then create your tables.
NHS
I'm going to go out on a limb, because im getting to be clueless about all of this, lol. Why on earth we just didnt hire someone to do this database for us ill never know. lol.
If there are any nice people out there who would like to offer there assistance over msn, please let me know or add me to your msn, philjames16@hotmail.com
Many thanks
Phil
This is a "lo-fi" version of UA. To view the full version with more information, formatting and images, please click here.