Dlookup help

[Jean01]

Hello,
I'm new to the world of Access Application design and any help will be appreciated.
I need to track the caller' profile (employee number, district number, etc...) whenever I recieve a phone call.
I've created an EmployeeID table that contains all the information to be referrenced. I'm using the DLookup function in an afterUpdate event so that I only have to type the employee number into an unbound TextBox to be able to automatically fill all the other TextBoxes (gives me the correctly spelled names and the phones numbers of the callers, in case I need to call them back or didn't catch their name properly...).
This part works fine. Now I need be able to update the EmployeeID table whenever the records need to be updated (i.e. new phone number)or a new record needs to be added to the table. The employee number field is the primary key to the EmployeeID table.
Once I have this problem solved, I will need to generate a new record in the CallActivity table that will contain the employee number and a time stamp that will give me a "start-Stop" & date kind of information.
All this is done using Access97 running on NT4.0.
Hope I didn't forget anything.....
thanks in avance!

"The truth is out there..."

 

[Aivars]

Hi!

You can use BookMark for searching of needed record.

private sub UnboundEmployeeNumber_AfterUpdate()
dim rst as recordset

set rst=me.recordsetclone
set rst=frm.recordsetclone
'Use necessary identifiers " # " for Date and " ' " for Text type fields in the Find command
rst.findfirst "[Employee Number]='" & UnboundEmployeeNumber & "'" 'If [Employee Number] field have Text type
if not rst.nomatch then
me.bookmark=rst.bookmark
end if
rst.close
set rst=Nothing

end sub

Aivars

 

[Jean01]

Aivars,
Thanks for your quick response.
Will the code you provided enable me to update existing records as well as create new ones?

Jean

 

[Aivars]

Hi Jean, again!

You can copy and paste codes into _AfterUpdate procedure of your unbound textbox. Change UnboundEmployeeNumber to your unbound textbox name (such me.txtEmployeeNumber). No forget about identifiers.

Good luck!
Aivars

 

[Jean01]

Aivars,
I really appreciate your help with this...
I tried the code you provided and I'm getting a "run-time error 7951" eror message says "you entered an expression that has an invalid reference to the recordsetclone property".
This is basically what I previously had before I tried your recommendation:

Option Compare Database
Option Explicit

Private Sub EmplNoTextBox_AfterUpdate()
LNameTextBox = DLookup("LName", "Empl_ID_Table", "EmplNo = " & EmplNoTextBox)
DoCmd.Close acTable, "Empl_ID_Table"
FNameTextBox = DLookup("FName", "Empl_ID_Table", EmplNo = " & EmplNoTextBox)
PhoneNumbTextBox = DLookup("PhoneNumber", "Empl_ID_Table", EmplNo = " & EmplNoTextBox)

End Sub

This enables me to type in an employee number in the EmplNoTextBox and the other text boxes would then list the information contained in the record.
What bugs me is that if I enter an non-existant employee number, nothing happends...What I would like it to do is to enable me to create a new record that would update the Empl_ID_Table by allowing me to fill in the missing fields.

Perhaps I'm not approaching my problem correctly!

Jean