Populate a text box

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am creating a form, i have a field called department ID from this
information in this field. i want to populate a text box with the department
title for reference on the form from the department id not sure how i look it
up in the department table as the department id field is from a table called
Employee?
 
Excel,

In many databases, the value of a field like Department ID is not
relevant to the user, so does not need to be shown on the form. If this
is the case, you can use a Combobox for data entry of the department on
the Employee form. You can use the Departments table as the Row Source
of the combobox. By adjusting the Column Count, Bound Column, and
Column Widths properties of the combobox, you can have it so the
Department ID is the data entered into the field that the combobox is
bound to, but the Department Title is what is actually displayed in the
combobox.

However, if you do want the ID shown on the form, there are a number of
ways to get the associated Title displayed. Some of these approaches
are discussed in this article...
http://accesstips.datamanagementsolutions.biz/lookup.htm
 
You will need to put the following code in two places. The After Update
event of the text box for department ID and in the Form Current event. You
will need to change the field, table, and control names to suit your situation

This version goes in the After Update:
Me.txtDeptDescription = DLookup("[DEPARTMENT_DESCRIPTION]", "Employee",
"[DEPARTMENT_ID = " & Me.txtDeptID)
If IsNull(Me.txtDeptDescription Then
MsgBox "No Description Found For Dept " & Me.txtDeptId
End If

This version goes in the Current event:
If Not Me.NewRecord Then
Me.txtDeptDescription = DLookup("[DEPARTMENT_DESCRIPTION]", "Employee",
"[DEPARTMENT_ID = " & Me.txtDeptID)
End If
 
Well, if you're going to use DLookup() wouldn't it be easier to just put
it directly into the Control Source of the txtDepartmentDescription
textbox, e.g.
=DLookup("[Department Description]","Employee","[DeptID=" & [DeptID])
What's the reason for all the code?
 
Good point. It did not dawn on me that since the description was being
pulled in externally that it would not be updated here.

Steve Schapel said:
Well, if you're going to use DLookup() wouldn't it be easier to just put
it directly into the Control Source of the txtDepartmentDescription
textbox, e.g.
=DLookup("[Department Description]","Employee","[DeptID=" & [DeptID])
What's the reason for all the code?

--
Steve Schapel, Microsoft Access MVP
You will need to put the following code in two places. The After Update
event of the text box for department ID and in the Form Current event. You
will need to change the field, table, and control names to suit your situation

This version goes in the After Update:
Me.txtDeptDescription = DLookup("[DEPARTMENT_DESCRIPTION]", "Employee",
"[DEPARTMENT_ID = " & Me.txtDeptID)
If IsNull(Me.txtDeptDescription Then
MsgBox "No Description Found For Dept " & Me.txtDeptId
End If

This version goes in the Current event:
If Not Me.NewRecord Then
Me.txtDeptDescription = DLookup("[DEPARTMENT_DESCRIPTION]", "Employee",
"[DEPARTMENT_ID = " & Me.txtDeptID)
End If
Click to expand...
Click to expand...
 
Tried this

=DLookUp("[Department Name]","Department","[Department ID]=" &
"[Employee]![Department ID]")

but i dont think i have done it right as i keep getting an error message??

cheers


Steve Schapel said:
Well, if you're going to use DLookup() wouldn't it be easier to just put
it directly into the Control Source of the txtDepartmentDescription
textbox, e.g.
=DLookup("[Department Description]","Employee","[DeptID=" & [DeptID])
What's the reason for all the code?

--
Steve Schapel, Microsoft Access MVP
You will need to put the following code in two places. The After Update
event of the text box for department ID and in the Form Current event. You
will need to change the field, table, and control names to suit your situation

This version goes in the After Update:
Me.txtDeptDescription = DLookup("[DEPARTMENT_DESCRIPTION]", "Employee",
"[DEPARTMENT_ID = " & Me.txtDeptID)
If IsNull(Me.txtDeptDescription Then
MsgBox "No Description Found For Dept " & Me.txtDeptId
End If

This version goes in the Current event:
If Not Me.NewRecord Then
Me.txtDeptDescription = DLookup("[DEPARTMENT_DESCRIPTION]", "Employee",
"[DEPARTMENT_ID = " & Me.txtDeptID)
End If
Click to expand...
Click to expand...
 
If we have quess what the error is, then you have to guess what the answer is
:)
Post the error you are getting, please, and is your DLookup in the Control
Source property?

ExcelDummie said:
Tried this

=DLookUp("[Department Name]","Department","[Department ID]=" &
"[Employee]![Department ID]")

but i dont think i have done it right as i keep getting an error message??

cheers


Steve Schapel said:
Well, if you're going to use DLookup() wouldn't it be easier to just put
it directly into the Control Source of the txtDepartmentDescription
textbox, e.g.
=DLookup("[Department Description]","Employee","[DeptID=" & [DeptID])
What's the reason for all the code?

--
Steve Schapel, Microsoft Access MVP
You will need to put the following code in two places. The After Update
event of the text box for department ID and in the Form Current event. You
will need to change the field, table, and control names to suit your situation

This version goes in the After Update:
Me.txtDeptDescription = DLookup("[DEPARTMENT_DESCRIPTION]", "Employee",
"[DEPARTMENT_ID = " & Me.txtDeptID)
If IsNull(Me.txtDeptDescription Then
MsgBox "No Description Found For Dept " & Me.txtDeptId
End If

This version goes in the Current event:
If Not Me.NewRecord Then
Me.txtDeptDescription = DLookup("[DEPARTMENT_DESCRIPTION]", "Employee",
"[DEPARTMENT_ID = " & Me.txtDeptID)
End If
Click to expand...
Click to expand...
Click to expand...
 
ExcelDummie,

I agree with Klatuu, you haven't given many clues.

But from a purely syntax point of view, assuming DepartmentID is a
number data type, try it like this...
=DLookUp("[Department Name]","Department","[Department ID]=" &
[Department ID])
 
Tried this

=DLookUp("[Department Name]","Department","[Department ID]=" &
"[Employee]![Department ID]")

but i dont think i have done it right as i keep getting an error message??
Click to expand...

You have either too many quote marks or too few. If the DepartmentID
field is a Number datatype then the third argument of the DLookUp
function should be a String like

[DepartmentID] = 3

which you can get using an expression like

=DLookUp("[Department Name]","Department","[Department ID]=" &
[Employee]![Department ID])

If it is a text field, then the value must be delimited by quotes. You
can use either ' or " as a delimiter; in order to incorporate a " in a
string delimited by " you must double it up:

=DLookUp("[Department Name]","Department","[Department ID]=""" &
[Employee]![Department ID] & """")

This will give a third argument resembling

[DepartmentID] = "3A"

The three doublequotes after the = sign will be treated as a double
doublequote within the string (translating to just one doublequote);
the four at the end are a double doublequote within a string delimited
by doublequotes, translating to just one doublequote.

John W. Vinson[MVP]
 
Back
Top