Retrieving info from another table in a form.

[Frankie]

Hi,

I am working on a form in which in a certain field a number of an employee
can be entered. After entering this number I would like to show in the same
form (real time) the name etc. of this employee. This information is stored
in a different table.

Does anyone know how this can be done?

Thanks.

Frank

[Salad]

Frankie wrote:

Hi,

I am working on a form in which in a certain field a number of an employee
can be entered. After entering this number I would like to show in the same
form (real time) the name etc. of this employee. This information is stored
in a different table.

Does anyone know how this can be done?

Thanks.

Frank

The easiest method in your case would be the DLookup function.

The more difficult but faster method would use a recordset (see help)
and FindFirst (see help)

[user_5701]

It may be a good idea to have this text box at the top of your form
with a button next to it, indicating to the users they are to type in
the information and then hit the button so the rest of the form will
use that info they typed in for the remainder of the form. If this is
the case, you can put code in the click event of the button to do your
connection to the table and pull all the info you need based on the
employee number, then populate the fields with it:

private sub cmdGetEmployeeI nfo_Click
dim sql
Dim con, rs as object
Set con = Application.Cur rentProject.Con nection
Set rs = CreateObject("A DODB.Recordset" )

sql = "Select empName, empEtc from tblDifferentTab le where empId = "
& txtEmpID.value & "
rs.open sql, con, 1
txtEmpName = rs.fields("empN ame")
txtEmpEtc = rs.fields("empE tc")
rs.close
set rs = nothing
set con = nothing
end sub
This is assuming empId is a number field, if it's a text field, you'll
have to wrap ' (single quotes) around the double quotes in the sql
statement. You could probably also do this in the after update event
of your text box.

Also, if the text box to enter the employee number's only purpose is
to type in numbers of employees that already exist, and you don't have
a large number (more than 100 or so), then you could probably change
it to a combo box to make it easier for you and users. make sure you
set the limit to list property to yes - that way they can select one
from the pulldown or start typing and it will auto-complete when you
get it close enough. If you go the combobox route, add the code above
to the after update event of the combobox, and change the txtEmpID
part to the name of your combobox.

"Frankie" <f.*********@ti scali.nl> wrote in message news:<41******* *************** @dreader2.news. tiscali.nl>...

Hi,

I am working on a form in which in a certain field a number of an employee
can be entered. After entering this number I would like to show in the same
form (real time) the name etc. of this employee. This information is stored
in a different table.

Does anyone know how this can be done?

Thanks.

Frank

[Frankie]

Okay, it's working now. Thanks.

But...

when I go to the next or previous record the employee's name doesn't
change. I somehow must renew the field (employee's name) but I don't know
how to do this.

Can anyone help me out?

Thanks.

Frank
"user_5701" <us*******@hotm ail.com> schreef in bericht
news:f1******** *************** ***@posting.goo gle.com...

It may be a good idea to have this text box at the top of your form
with a button next to it, indicating to the users they are to type in
the information and then hit the button so the rest of the form will
use that info they typed in for the remainder of the form. If this is
the case, you can put code in the click event of the button to do your
connection to the table and pull all the info you need based on the
employee number, then populate the fields with it:

private sub cmdGetEmployeeI nfo_Click
dim sql
Dim con, rs as object
Set con = Application.Cur rentProject.Con nection
Set rs = CreateObject("A DODB.Recordset" )

sql = "Select empName, empEtc from tblDifferentTab le where empId = "
& txtEmpID.value & "
rs.open sql, con, 1
txtEmpName = rs.fields("empN ame")
txtEmpEtc = rs.fields("empE tc")
rs.close
set rs = nothing
set con = nothing
end sub
This is assuming empId is a number field, if it's a text field, you'll
have to wrap ' (single quotes) around the double quotes in the sql
statement. You could probably also do this in the after update event
of your text box.

Also, if the text box to enter the employee number's only purpose is
to type in numbers of employees that already exist, and you don't have
a large number (more than 100 or so), then you could probably change
it to a combo box to make it easier for you and users. make sure you
set the limit to list property to yes - that way they can select one
from the pulldown or start typing and it will auto-complete when you
get it close enough. If you go the combobox route, add the code above
to the after update event of the combobox, and change the txtEmpID
part to the name of your combobox.

"Frankie" <f.*********@ti scali.nl> wrote in message
news:<41******* *************** @dreader2.news. tiscali.nl>...

Hi,

I am working on a form in which in a certain field a number of an
employee
can be entered. After entering this number I would like to show in the
same
form (real time) the name etc. of this employee. This information is
stored
in a different table.

Does anyone know how this can be done?

Thanks.

Frank