display a lookup in textbox on form (1 Viewer)

[JJSHEP89]

I have a subform on an existing form that cycles through all maintenance records for a specific piece of equipment. the table stores the employee ID for the individual who performed the work and the subform then displays the employee number in a bound textbox. I have a ODBC linked table which lists employee's (Names ID etc.) How do i change the textbox on the subform to display the name instead of the ID# ?

I have the same situation on a different form but the information is displayed in an unbound listbox instead of a subform. would the process be the same for changing this as well?

 

[TJPoorman]

Like all things in Access there are multiple ways to "skin a cat".
You can change the control to a combobox and change the columns to hide the ID and show the name.
You can use DLookup to get the name based on the ID.
You can change your forms recordsource query to join the tables and get the name as a new field.

 

[JJSHEP89]

Like all things in Access there are multiple ways to "skin a cat".
You can change the control to a combobox and change the columns to hide the ID and show the name.
You can use DLookup to get the name based on the ID.
You can change your forms recordsource query to join the tables and get the name as a new field.

i dont know why i didnt think of the third one, and since its something i already know how to do it should be fairly easy. Just for my own learning how would i go about using the DLookup?

 

[JJSHEP89]

ok so after trying to change the recordsource of the subform its looking like that wont be the best way of going about it. So moving on to using the DLookup, i did a little bit of research and tried to use the expression builder to edit the control source property of my text box. However the output is not like im expecting...

here's the code...

=DLookUp("FirstName","dbo_EmpBasic","PCL_Employee =" & [dbo_EmpBasic]![EmpID])

I am tring to return the FirstName from the table dbo_EmpBasic where PCL_Employee (name of the field with the employee number) is equal to EmpID field in the dbo_EmpBasic Table.

 

[TJPoorman]

You need to reference a field on your form for the ID to match

=DLookUp("FirstName","dbo_EmpBasic","PCL_Employee =" & [COLOR="Red"][YourEmpIDFieldName][/COLOR])

 

[JJSHEP89]

ok so for the DLookup Function, The expression is the name of the field i am wanting to return, the Domain is the name of the table in which the expression field exists, and the criteria should be the Name of the Field in the lookup table, =, and the name of the textbox on the form right?

=DLookUp("FirstName","dbo_EmpBasic","EmpID =' " & [txtPCL_DieMaker] & "'")

here EmpID is the name of the field on the dbo_EmpBasic table which matches the value within the textbox on my form, this gives me a #Type! error, even though both fields are Short Text data type.

I've also tried variations of this and only gotten other errors as well

=DLookUp("FirstName","dbo_EmpBasic","PCL_Employee =' " & [txtPCL_DieMaker] & "'")

Where PCL_Employee is the name of the field on the table the subform is bound to. This also returns a #Type Error.

 

[Cronk]

Look at
=DLookUp("FirstName","dbo_EmpBasic","PCL_Employee =' " & [txtPCL_DieMaker] & "'")

as being equivalent to a query with the following sql
select Firstname from dbo_EmpBasic where PCL_Employee='(the text in your text box)'

Note that the space between the single and double quotes is inserting a space in the lookup value
"PCL_Employee =' " & .....

 

[JJSHEP89]

Here is what i've tried so far....

=DLookUp("FirstName","dbo_EmpBasic","PCL_Employee =" & [txtPCL_DieMaker])

=DLookUp("FirstName","dbo_EmpBasic","PCL_Employee ='" & [txtPCL_DieMaker] & "'")

=DLookUp("FirstName","dbo_EmpBasic","EmpID ='" & [txtPCL_DieMaker] & "'")

=DLookUp("FirstName","dbo_EmpBasic","EmpID =" & [txtPCL_DieMaker])

All Return a #Type! error

=DLookUp("FirstName","dbo_EmpBasic","txtPCL_DieMaker =" & [dbo_EmpBasic]![EmpID])

=DLookUp("FirstName","dbo_EmpBasic","txtPCL_DieMaker =" & [PressCallLog]![PCL_Employee])

All Return a #Name? error

there must be something else here im not seeing, would this have anything to do with the fact that the dbo_EmpBasic table is an ODBC Linked table from a non-access database?

 

[Minty]

The syntax Access is expecting is

DLookup("TheFieldYouWantToLookup", "TheTableName", "TheCriteria")

The first two parts aren't difficult. Sometimes the third bit causes confusion...

I think this version is closest ;

=DLookUp("FirstName","dbo_EmpBasic","EmpID ='" & [txtPCL_DieMaker] & "'")

So assuming EmpID is text try this
=DLookUp("FirstName","dbo_EmpBasic","[EmpID] ='" & [PCL_DieMaker] & "'")

As I'm assuming PCL_DieMaker is the field name not your controls name?

 

[JJSHEP89]

took a lot of trial and error but i finally landed on what works...

=DLookUp("[FirstName]","dbo_EmpBasic","[EmpID] ='" & [PCL_Employee] & "'")

i must have a name conflict with "firstname" and thats why i was getting the error, putting brackets around it fixed the issue. just as an explanation to others coming across this post.

there are 2 tables "dbo_EmpBasic" & PressCallLog

the field names are as follows...
"dbo_EmpBasic"
>EmpID - Short text data type that stores a number
>FirstName - Short text data type

"PressCallLog"
>PCL_Employee - Short text data type that stores and employee number

On the form in question "txtPCL_DieMaker" is the name of the textbox in which the name is going to be displayed. This is where the DLookup Code was placed in the control source property.

Therefore the DLookup in SQL is SELECT FirstName FROM dbo_EmpBasic WHERE EmpID = PCL_Employee, there needed to be brackets and quotes around each parameter because of the short text data type and name interference's

Thanks again for everyone's help!

 

[Minty]

Just as a bit of "It'll probably help going forwards" advice, more often than not a field name ending with ID would be assumed to be a number and stored as such.

There is no hard and fast law about it, but when looking at other peoples code most posters here would make this assumption, as most ID fields are Autonumber primary keys.

It might be worth changing the data types to avoid future confusion / inconsistencies at the development stage rather than not being able to later in the projects life.

 

[JJSHEP89]

Just as a bit of "It'll probably help going forwards" advice, more often than not a field name ending with ID would be assumed to be a number and stored as such.

There is no hard and fast law about it, but when looking at other peoples code most posters here would make this assumption, as most ID fields are Autonumber primary keys.

It might be worth changing the data types to avoid future confusion / inconsistencies at the development stage rather than not being able to later in the projects life.

ya that was throwing me off too, unfortunately the dbo_EmpBasic table is out of my control, its imported from our accounting system and whoever initially set it up set it as a short text data type, so I set all my tables to match it. Still a valid piece of advice though!

 

[Minty]

That will be the accounting system, I interact with one and loads of the primary keys that "look" like numbers are actually stored as text.

If it's imported rather than linked you could easily convert it at the importing stage. If it's linked it's more troublesome, but not insurmountable. Depends how much grief it's causing / going to cause moving forwards. It appears you already have a secondary employees table, presumably holding info not available in the linked / imported table? You could possibly store the number field equivalent in there?.

 

[JJSHEP89]

That will be the accounting system, I interact with one and loads of the primary keys that "look" like numbers are actually stored as text.

If it's imported rather than linked you could easily convert it at the importing stage. If it's linked it's more troublesome, but not insurmountable. Depends how much grief it's causing / going to cause moving forwards. It appears you already have a secondary employees table, presumably holding info not available in the linked / imported table? You could possibly store the number field equivalent in there?.

the other employees table is the original table, it became a DB Maintenance nightmare to keep track of all our new and old employees. some departments here have a pretty high turnover. so im moving to the table linked to our accounting system so that i no longer have to spend the time maintaining the data. having the different data types is a headache but not bigger than keeping up with everything else.

Im trying to further integrate alot of our other database systems just so we only have one master copy of whatever data is in question. essentially integrating everything and keeping it to the rules of 1NF