Database pulling info from 1 of 3 different tables

[Ambpicard]

I am creating a database that will input customer information and part numbers. When I enter a prefix, I want access to look at a specific table and then when I enter the part number I want to to display that data in a label box.

I have the 4 tables created along with the form. How would I go about getting Access to look at either of the tables based on data entered in a seprate text box. I.E.

Prefix...EI (access should now look at table labeled EI not the table labeled LR)
Part Number...2995 (access should now display the description in the label box on the form)

[ADezii]

I am creating a database that will input customer information and part numbers. When I enter a prefix, I want access to look at a specific table and then when I enter the part number I want to to display that data in a label box.

I have the 4 tables created along with the form. How would I go about getting Access to look at either of the tables based on data entered in a seprate text box. I.E.

Prefix...EI (access should now look at table labeled EI not the table labeled LR)
Part Number...2995 (access should now display the description in the label box on the form)

Too little information to attempt a response. Please provide more detail such as: Table and Field Names, Field Data Types, Relationships, allowable Prefixes, Lookup Fields, etc.

[Ambpicard]

My primary table is called Replacement Parts 1
The 3 other tables are called LER Parts, EI Parts, and SFT Parts.
These tables are going to have a part description that I want to be pulled into the form while the user is entering data.

The description fields in al 4 tables ate in TEXT format

There is currently a relationship between the primary table and the EI/LER field and the Part # field.

[Ambpicard]

My primary table is called Replacement Parts 1
The 3 other tables are called LER Parts, EI Parts, and SFT Parts.
These tables are going to have a part description that I want to be pulled into the form while the user is entering data.

The description fields in al 4 tables ate in TEXT format

There is currently a relationship between the primary table and the EI/LER field and the Part # field.

[ADezii]

My primary table is called Replacement Parts 1
The 3 other tables are called LER Parts, EI Parts, and SFT Parts.
These tables are going to have a part description that I want to be pulled into the form while the user is entering data.

The description fields in al 4 tables ate in TEXT format

There is currently a relationship between the primary table and the EI/LER field and the Part # field.

I still do not have enough information, but here is the basic Logic

Dim strPrefix As String, strLookupTable As String
 
'strPrefix = '<some Method to arrive at a Prefix>
 
Select Case strPrefix
  Case "EI"
    strLookupTable = "EI"
  Case "LER"
    strLookupTable = "LER"
  Case "SFT"
    strLookupTable = "SFT"
End Select
 
'We have the Prefix at this point, obtain the Numeric Part#
Dim intPartNo As Long
'intPartNo = '<some Method to arrive at a Part#>
 
'Look up the Description from the appropriate Table
Dim strDescription As String
strDescription = DLookup("[Description]", strLookupTable, "[Part#]=" & intPartNo)