473,326 Members | 2,108 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,326 software developers and data experts.

Retrieving info from another table in a form.

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
Nov 13 '05 #1
3 2138
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)
Nov 13 '05 #2
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 cmdGetEmployeeInfo_Click
dim sql
Dim con, rs as object
Set con = Application.CurrentProject.Connection
Set rs = CreateObject("ADODB.Recordset")

sql = "Select empName, empEtc from tblDifferentTable where empId = "
& txtEmpID.value & "
rs.open sql, con, 1
txtEmpName = rs.fields("empName")
txtEmpEtc = rs.fields("empEtc")
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.*********@tiscali.nl> wrote in message news:<41**********************@dreader2.news.tisca li.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

Nov 13 '05 #3
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*******@hotmail.com> schreef in bericht
news:f1**************************@posting.google.c om...
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 cmdGetEmployeeInfo_Click
dim sql
Dim con, rs as object
Set con = Application.CurrentProject.Connection
Set rs = CreateObject("ADODB.Recordset")

sql = "Select empName, empEtc from tblDifferentTable where empId = "
& txtEmpID.value & "
rs.open sql, con, 1
txtEmpName = rs.fields("empName")
txtEmpEtc = rs.fields("empEtc")
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.*********@tiscali.nl> wrote in message
news:<41**********************@dreader2.news.tisca li.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

Nov 13 '05 #4

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

3
by: Tom Turner | last post by:
Here's the background on my situation. The question follows --- We have 600 units of mail going from our business to various Post Offices every morning. Every unit is accompanied by a paper...
13
by: RHPT | last post by:
I am wanting to capture the XML posted by an InfoPath form with .NET, but I cannot figure out how to capture the XML stream sent back by the InfoPath form. With Classic ASP, I could just create an...
1
by: tangus via DotNetMonster.com | last post by:
Hello all, I'm really struggling with getting some Active Directory code to work in ASP.NET. Can you please provide assistance? I am executing the following code: Dim enTry As DirectoryEntry =...
2
by: RSH | last post by:
I am struggling a bit trying to get at all of the Table names in a given Access database. I have the code below which should be retrieving the information...i am just having a bit of trouble...
3
by: corear | last post by:
I have a new case tracking system. The cases table has these fields: CaseID (Primary Key) StatusID (looked up in another table - Open, Pending, or Closed) Status Comment (memo field) Date&Time...
2
by: 4Ankit | last post by:
hello all, i am having some difficulty retrieving information from my form. I want to add content in my table but the content i want to add to the table is what the user inputs in my form. ...
0
bmallett
by: bmallett | last post by:
First off, i would like to thank everyone for any and all help with this. That being said, I am having a problem retrieving/posting my dynamic form data. I have a form that has multiple options...
1
by: cumupkid | last post by:
II am trying to create a form that will allow me to upload photos to a folder in the site root directory and add the information to the mysql db at the same time. I have created two forms, one...
34
by: vpriya6 | last post by:
Hi guys, I am new to Ajax, xml and javascript. I want to know how can I retrieve data from xml and display in the html page? please help me out. suppose my xml file is customer.xml the code...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.