Simple Question (2000)

Topic

New Reply

Ok, noone laugh at me here, I am still a pretty basic user…here is my question…

I have a table setup with all of my customer names on it. Then, I have another table setup that has a lookup on it to list the names of the customers. The purpose of this table is to add the addresses and phone numbers for the customers. I would like to make another table to add more information about these customers…

Question 1…When I create a form, I want to make a form that has controls to go to the other screens to add more information. For example…I would have 1 form that the user could input customer name and sales rep. I want a button that would take them to another form where they can input the address for that customer. When they click on that button to go to Form 2, How do I keep the customer name active, so they do not need to find it once again?

Question 2…When I make reports for these tables, how do I get the actual name to show up instead of the primary key? For example…if I make a report that lists all of my customers with their addresses, all of the information shows up, however, the customer name shows up as the primary key for the customer name table.

So I would have:

Customer: 1
123 Main St.
Chicago, IL 60611

instead of:

Customer: WalMart
123 Main St.
Chicago, IL 60611

I think that is all for now, again, do not laugh at my simplistic questions!

Reply | Quote

Replies

Your questions are not simplistic but rather basic ones. Table design and normalization are the basis for good database design. Here is a link to a document you should download from the microsoft site. It will help you decide which information should go into which table. Having more than one table containing customer information is not considered good design unless special circumstances (privacy issues e.g.) exist. Take a look at the document – I could go on for days about good design structures.

Click here for the document

As for your report is the report’s record source a table or a query? If it’s a table consider using a query in which you can substitute the customer name for the primary key.

Reply | Quote

If the info is unique to the customer, keep it in the same table as the customer ID along with customer name, etc. If it is info that could be common to other customers as well, put that in a separate table and use a relationship to link your customers to it.

As to form navigation, why not use the Tab control? This allows you to have several levels of info on one screen – each tab can have either native fields (to the underlying table or query) or subforms, or both. I use the tab control anytime I have a place where someone has to enter a lot of info related to a given record and I don’t want them to get lost going between different screens. The users love it.

If you don’t use a tab control you have to include some linking code to open the next screen to the same key data as the first screen. That isn’t to tricky to do (in fact the Access button control wiard will write the code for you), but it is tricky to figure out when someone tries to dissect your work at some future date.

Reply | Quote

Another way to handle long forms with lots of info, is to divide them into pages.
You put page breaks on the form, then have buttons with code like : me.gotopage 2

As for getting names rather than keys, you need to create queries that bring info from both tables.

Reply | Quote

Hi,
I’m fairly new at this as well, don’t worry, everyone starts somewhere.
A question – why do you want to split the info for customers? The usual practise is to keep all the information together unless there is more than one row of data for a particular field eg a person may have more than one hobby, so you could split that info off into a separate table (simplistic I know, but demonstrates the point). If you need to link the two tables, use the unique customer number to create a link. (just as an aside)
However, you will need to run a query to let the second form know where to get the information from. Create a query with the fields you want to set in the second form, put in the condition line (under customer name)
[forms]![nameofform]![nameoftextbox], do the same for the rep field. Bind the query to the second form so that when the form is opened, it will read the text from the text boxes you specified. Is this what you are after?
However, the report – consider using a combo box to display the customer number – change the text box to combo, set the row source to a query (click on the elipsis button at the side of the text box on the Properties form under Data to open a query box), add the table with the customer name and number, select the number and name for the query, close the query box, set the bound column (under the row source line) to 1 (this binds it to the first column in the query), change to the format tab, set column count to 2, set the column widths to 0cm;2cm – then run the report and see what happens. Works for me. Hopefully following the instructions should get you there, hope they’re clear enough.
Have fun,
Sue.

Reply | Quote