[Solved] Relating fields from the same table

[chris_petersen]

I am very new to this so bear with me. I have created a basic database to keep records of clients for my wife's department. I want to select a name from the list and have the client field update with the corresponding client data associated with that client name. I just can't figure out what I am doing wrong. I have tried displaying the field as a list box but I don't think that is the way to go.
I have attached some pics of what I am trying to do. I have looked all over for video tutorials and can't really find one for this particular issue.

I want to select the contact name from the list and have the blank field to the right update with the corresponding client (Screen1).

Screen1

Screen2

Thanks in advance.

Chris

[Solved]

[UnklDonald418]

I have tried displaying the field as a list box

Actually, you are on the right track. The client name list box would appear on the MainForm and the client data would be on a SubForm. The MainForm/SubForm would be linked via the Contact_ID field. You can use the Form Design wizard to create a simple form using the MainForm/SubForm combination.
The list box query would be something like

Code: Select all

SELECT “Contact”, “Contact_ID” FROM “TableNameHere” ORDER BY “Contact” ASC;

If there are very many contacts, you should consider breaking the names into FirstName, LastName fields.

[chris_petersen]

Thanks UnklDonald, That works! Last question though. Is there a way to refresh the client field when I select the name from the list? It stays blank until I move to the next record and when I go back, the field has been populated with the proper client.

Thanks for your help.

Chris

[UnklDonald418]

The easiest solution is to add a refresh button to your form.
Open the form in the Edit/Design mode.
Select the list box, then add a Push Button control to the form.
Right click on the new Push Button and select Control from the menu to get the Button properties dialog. On the General tab, scroll down to the Action property and select Refresh Form from the list of Actions. You can also change other properties of the button such as Label, Font and Background color. Save the changes to the form.
In the Data mode after selecting a name from the list press the refresh button and the client data should be displayed.

[chris_petersen]

Any suggestions on what I am missing in my Date Query command. I am trying to pull any record that is between a certain start_date and Due_Date.
It prompts me for both but errors out. I think I am missing a column field someplace behind the Where command.

[UnklDonald418]

You should post this as a new question, since it doesn't directly relate to your original question.
Also include what exactly the error message says because when I tried your query against a test table it worked perfectly.

[chris_petersen]

Thanks, I will get it posted to a new forum.

I don't get the error anymore but it isn't working the way that I thought it would. I thought that it would only give me that dates between the specified range but it gives me all of the dates anyway in a sorted order.
I just started working with this stuff last week so I am having some difficulty trying to figure out the logic behind the tables and relationships between them.