auto fill name based on file number

[mark_w]

I am usually pretty efficient at finding answers and solving issues by doing a little research, but I am completely stumped. A few days ago I was conned into creating a database for the company that I work for because they are running the entire company (5 offices) off of excel spread sheets. Anyway, here is my problem....

I have several tables in the database, one for employee information (name, address, phone number, file number, hire date, etc..), and another table called disciplinary actions (file number, violation type, date, etc...). I have created a form for new employee entry, which works fine. Next i created a form to enter the disciplinary actions. On this form I have a field to enter the employee file number, once I have entered the employee file number I want it to auto populate the the first and last name (from the employee information table) in two other fields on the form once i tab or move out of the file number field.

If possible I would like to use report controls for the first and last name fields on the disciplinary action form because the first and last name is not going to be saved to the disciplinary action table. I am simply putting it in the corner of the form so the the admins have a first/last name reference to ensure they are entering the disciplinary action for the correct person.

Table 1
Employee Information
- File Number
- First Name
- Last Name
- etc....



Table 2
Disciplinary Actions
- File Number
- Violation Date
- Issmgr (Issuing Manager)
- etc....

Thanks,
Mark

[Missinglinq]

Two options:

In the AfterUpdate event of the FileNumber Control, use the DLookup Function to autofill the FirstName and Last Name Controls. The exact syntax depends on whether the FileNumber Field is defined as a Text or Number Datatype.

Create a Combobox including the FileNumber, FirstName and LastName Fields. Then use the AfterUpdate event of this Combobox to populate the FileNumber, FirstName and LastName Textboxes.

Personally, I'd use the latter approach, simply because the server could double-check, before retrieving the data, that he or she was referencing the correct employee. Gigging the wrong employee is not an approved method of promoting employee loyalty!

Linq ;0)>

[mark_w]

I can't get the first one to work at all. I don't know if I'm missing something in the expression or what's going on.

The second one works but I'm looking for a solution close to simply placing two rectangles on the form so the user has no interaction with them at all, and simply having the file number in the file number field (once entered) populates the first and last name into the rectangles from the employees table.

[Missinglinq]

What exact code are you using?

What is the Datatype of the FileNumber Field?

[mark_w]

Number - Long Integer

[Missinglinq]

Once again, what exact code do you use, for the attempt at using DLookup?

[mark_w]

=DLookUp("FName","Employees","Criteria = '" & [fORMS]![DA]![Box388] & "'")

[Missinglinq]

Common mistake; where you have 'Criteria' you need the actual Field Name in your Table that holds the File Number.

In the Box388_AfterUpdate event (this assumes that Box388 is the Control on your Form that [File Number] is being entered into:

Me.FName_TextboxName = DLookUp("FName","Employees","[File Number] = '" & Me.Box388 & "'")

You'd also need to replace FName_TextboxName with the actual name of the Textbox on your Form.

Linq ;0)>

[mark_w]

I now have the string below and I have tried it in the expression box and the vb code window, and I'm still getting negative results.

Expression Builder Code:
=[Me].[First]=DLookUp("FName","Employees","[File Number] = '" & [Me].[Box388] & "'")

VB Code:
Private Sub Form_AfterUpdate()
Me.First = DLookup("FName", "Employees", "[File Number] = '" & Me.Box388 & "'")
End Sub


[First] name of the text box control where i want the first name to populate to (does this need to be tied to field, because I don't necessarily was to save it)
"FName" field in the database that houses first names
"Employees" name of the table where "FName" is located
[File Number] name of the field that houses file numbers in the "Employees" table
[Box388] is where I'm typing the file number which hooked to a control saving the file number in the DA table


I have posted the DB here since the file size exceeds the forum limits: http://ge.tt/7jBFDDL/v/0?c

[mark_w]

Bump, still don't have a solution. Thanks

[Missinglinq]

Your VB Code looks right, assuming that [File Number] is defined as a Text Field, but the code has to be in the AfterUpdate event of the Control where you're entering the file number, which I assume is Box388

Private Sub Box388_AfterUpdate()
  Me.First = DLookup("FName", "Employees", "[File Number] = '" & Me.Box388 & "'")
End Sub

You either need to have the Controls for the employee names Bound to Fields in the underlying Table or you'll have to rerun the DLookups in the Form_Current event to display the names each time you move to a Record.

Linq ;0)>

[ssanfu]

Be aware that "First" is a reserved word in Access (Jet) and shouldn't be used for object names. Here is a list of reserved words in Access and SQL by Allen Browne: http://allenbrowne.com/AppIssueBadWord.html
Spaces in names are also problematic and should be avoided.

Expression Builder Code:
=[Me].[First]=DLookUp("FName","Employees","[File Number] = '" & [Me].[Box388] & "'")

The expression to put in the control source of a control should look like:
=DLookUp("FName","Employees","[File Number] = '" & [Me].[Box388] & "'")

If "[File Number]" is a number type (not text), delimiters are not required. Use:
=DLookUp("FName","Employees","[File Number] = " & [Me].[Box388] )

[mark_w]

Thanks folks, finally got it to generate the names in the fields.

[Missinglinq]

Glad we could help!

Good luck with your project!

Linq ;0)>