Assigning numbers from one table to its definitions on different table?

[abidib]

I am the only person in the company with some computer experience and I was asked to create a GUI in the database. In Microsoft access I have 2 tables. In the first table I have a list of a 1000 numbers ranging from 01-09 like 01, 03, 06, 03, 09, 02....And in the second table I have the definitions of what those numbers mean like 01 means "empty" 02 means "call back" 03 mean "not available" and so on. In the GUI how do I get the definitions of those numbers to show in the textfeild because right now only the numbers show. Thanks for any kind of help by the way, I really appreciate it!

[Missinglinq]

Why do you have this data in two Tables instead of in a single Table? This seems counter-productive.

Linq ;0)>

[abidib]

Hi thanks for replying. There is a 1000 different Customer ID's which my co-workers provide me in table 1. Table 1 has customer name, address, status, postal code.... In the GUI they asked me to create, they wanted the definitions of those "Status" numbers to show, which is why I created the second table. In the second table I just have numbers 01-09 and the corresponding definition of what those "Status" numbers mean, and in the GUI they want the definitions to show NOT the numbers.

[Missinglinq]

That makes more sense! As usual, there are several ways to do this. Because 'descriptions' tend to be long, I'll describe a method to return the description Field even if it is defined as a Memo Field (or Long Text Field, in the latest versions of Access.)

Where

• txtStatus is the Form Textbox where the user enters the status designation (i.e. 01, 02, 03, etc)
• txtStatusDescription is the Form Textbox that holds the status description
• Status is the Table Field that holds the status designation
• StatusDescription is the Table Field that holds the status description
• StatusTableName is the actual name of the Table that holds the status and status description

Private Sub txtStatus_AfterUpdate()
 Me.txtStatusDescription = Nz(DLookup("StatusDescription", "ActualTableName", "Status ='" & Me.txtStatus & "'"), "Status Does Not Exist!")
End Sub

When the user enters the Status and moves off of the Textbox, the Description Textbox will be populated with the appropriate Description. If the Status does not exist in the Table, the Description Textbox will display the message

Status Does Not Exist!

You might want to use Conditional Formatting to change the Fore Color or Back Color to another color, if the Description Textbox equals to 'Status Does Not Exist!,' as a warning to your users.

Linq ;0)>

[abidib]

WOW you make it seem so simple. Thank you so much! appreciate it!

That makes more sense! As usual, there are several ways to do this. Because 'descriptions' tend to be long, I'll describe a method to return the description Field even if it is defined as a Memo Field (or Long Text Field, in the latest versions of Access.)

Where

• txtStatus is the Form Textbox where the user enters the status designation (i.e. 01, 02, 03, etc)
• txtStatusDescription is the Form Textbox that holds the status description
• Status is the Table Field that holds the status designation
• StatusDescription is the Table Field that holds the status description
• StatusTableName is the actual name of the Table that holds the status and status description

Private Sub txtStatus_AfterUpdate()
 Me.txtStatusDescription = Nz(DLookup("StatusDescription", "ActualTableName", "Status ='" & Me.txtStatus & "'"), "Status Does Not Exist!")
End Sub

When the user enters the Status and moves off of the Textbox, the Description Textbox will be populated with the appropriate Description. If the Status does not exist in the Table, the Description Textbox will display the message

Status Does Not Exist!

You might want to use Conditional Formatting to change the Fore Color or Back Color to another color, if the Description Textbox equals to 'Status Does Not Exist!,' as a warning to your users.

Linq ;0)>

[abidib]

Hey thanks a lot by the way but I am sorry to bother you again, but do you put the code into the "Control Source" of the txtStatusDescription. I have no idea where to properly enter the code. Thanks