[Solved] Relating fields from the same table

Discuss the database features

[Solved] Relating fields from the same table

Postby chris_petersen » Tue Mar 21, 2017 5:38 pm

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.jpg
Screen1

Screen2.JPG
Screen2


Thanks in advance.

Chris

[Solved]
Last edited by chris_petersen on Wed Mar 22, 2017 3:34 pm, edited 1 time in total.
Chris Petersen
Open Office 4.1.3
chris_petersen
 
Posts: 22
Joined: Tue Mar 21, 2017 5:22 pm

Re: Relating fields from the same table

Postby UnklDonald418 » Wed Mar 22, 2017 12:25 am

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   Expand viewCollapse view
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.
If your problem has been solved, please edit this topic's initial post and add "[Solved]" to the beginning of the subject line
Apache OpenOffice 4.1.6 & LibreOffice 6.1.1.2 - Windows 10 Professional
UnklDonald418
Volunteer
 
Posts: 1059
Joined: Wed Jun 24, 2015 12:56 am
Location: Colorado, USA

Re: Relating fields from the same table

Postby chris_petersen » Wed Mar 22, 2017 3:18 pm

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
Chris Petersen
Open Office 4.1.3
chris_petersen
 
Posts: 22
Joined: Tue Mar 21, 2017 5:22 pm

Re: Relating fields from the same table

Postby UnklDonald418 » Wed Mar 22, 2017 7:54 pm

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.
If your problem has been solved, please edit this topic's initial post and add "[Solved]" to the beginning of the subject line
Apache OpenOffice 4.1.6 & LibreOffice 6.1.1.2 - Windows 10 Professional
UnklDonald418
Volunteer
 
Posts: 1059
Joined: Wed Jun 24, 2015 12:56 am
Location: Colorado, USA

Re: Relating fields from the same table

Postby chris_petersen » Wed Mar 22, 2017 9:25 pm

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.

Dates.JPG

Date Query.JPG
Chris Petersen
Open Office 4.1.3
chris_petersen
 
Posts: 22
Joined: Tue Mar 21, 2017 5:22 pm

Re: Relating fields from the same table

Postby UnklDonald418 » Wed Mar 22, 2017 10:50 pm

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.
If your problem has been solved, please edit this topic's initial post and add "[Solved]" to the beginning of the subject line
Apache OpenOffice 4.1.6 & LibreOffice 6.1.1.2 - Windows 10 Professional
UnklDonald418
Volunteer
 
Posts: 1059
Joined: Wed Jun 24, 2015 12:56 am
Location: Colorado, USA

Re: Relating fields from the same table

Postby chris_petersen » Thu Mar 23, 2017 4:13 pm

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.
Chris Petersen
Open Office 4.1.3
chris_petersen
 
Posts: 22
Joined: Tue Mar 21, 2017 5:22 pm


Return to Base

Who is online

Users browsing this forum: No registered users and 6 guests