make the value of one control dependent on another

[JR]

On a data entry form I want to devise a method that allows the employee to
see that he or she has selected the correct employeeID by providing feedback
via another control or text box on the form.
I thought I could do that by entering an SQL statement similiar to the one
below, but that doesn't work ... of course the query below could be
completely wrong since I am not very proficient in form design and coding.

Just noticed that there is nothing in my query that commands only display to
be the LastName! That could be a problem ...


SELECT [Employee].[EmployeeID], [Employee].[LastName] FROM [Employee] where
[EmployeeID]=[timeslip2].[EmployeeID].value;

I appreciate any assistance.

Thanks.

 

[Marshall Barton]

On a data entry form I want to devise a method that allows the employee to
see that he or she has selected the correct employeeID by providing feedback
via another control or text box on the form.
I thought I could do that by entering an SQL statement similiar to the one
below, but that doesn't work ... of course the query below could be
completely wrong since I am not very proficient in form design and coding.

Just noticed that there is nothing in my query that commands only display to
be the LastName! That could be a problem ...


SELECT [Employee].[EmployeeID], [Employee].[LastName] FROM [Employee] where
[EmployeeID]=[timeslip2].[EmployeeID].value;

If you only need a text box to display the employee last
name if it's in the table, then you can use the DLookup
function in the text box's control source expression:

=DLookup("LastName", "Employee", "EmployeeID=" &
Me.EmployeeID)

that will return Null if there is no employee with the
entered ID.

 

[Mike Painter]

On a data entry form I want to devise a method that allows the employee to
see that he or she has selected the correct employeeID by providing
feedback
via another control or text box on the form.
I thought I could do that by entering an SQL statement similiar to the one
below, but that doesn't work ... of course the query below could be
completely wrong since I am not very proficient in form design and coding.

If the feedback consists of showing the employees name then the form should
be based on a query that relates the tables involved.
NO code is needed to do this and it is the correct method in a relational
database.

I should say that no code should be needed if the user never enters a wrong
ID.
A combo box that showed the values as well as displaying the names
eliminates these problems and Access will write the code for you.

It also allows using people names rather than their ID number. (Not that and
ID number isn't of value, I once worked a summer in a small store with two
Donald Leroy Smiths.)

 

[JR]

Thanks but it didn't work. Sure looks like it should, but I get #Name? in
the text box.

Employee table set up as follows:

EmployeeID (key) | LastName | FirstName | OrganizationID |

<><><><><><><><><>

EmployeeID is a combo box on the form Timeslip2

Does that have anything to do with it?

<><><><><><><><><><>

On a data entry form I want to devise a method that allows the employee to
see that he or she has selected the correct employeeID by providing feedback
via another control or text box on the form.
I thought I could do that by entering an SQL statement similiar to the one
below, but that doesn't work ... of course the query below could be
completely wrong since I am not very proficient in form design and coding.

Just noticed that there is nothing in my query that commands only display to
be the LastName! That could be a problem ...


SELECT [Employee].[EmployeeID], [Employee].[LastName] FROM [Employee] where
[EmployeeID]=[timeslip2].[EmployeeID].value;

If you only need a text box to display the employee last
name if it's in the table, then you can use the DLookup
function in the text box's control source expression:

=DLookup("LastName", "Employee", "EmployeeID=" &
Me.EmployeeID)

that will return Null if there is no employee with the
entered ID.

 

[JR]

I've another possible complication: the employeeID has a period in it such
as 4.0090

Thanks but it didn't work. Sure looks like it should, but I get #Name? in
the text box.

Employee table set up as follows:

EmployeeID (key) | LastName | FirstName | OrganizationID |

<><><><><><><><><>

EmployeeID is a combo box on the form Timeslip2

Does that have anything to do with it?

<><><><><><><><><><>

On a data entry form I want to devise a method that allows the employee to
see that he or she has selected the correct employeeID by providing feedback
via another control or text box on the form.
I thought I could do that by entering an SQL statement similiar to the one
below, but that doesn't work ... of course the query below could be
completely wrong since I am not very proficient in form design and coding.

Just noticed that there is nothing in my query that commands only display to
be the LastName! That could be a problem ...


SELECT [Employee].[EmployeeID], [Employee].[LastName] FROM [Employee] where
[EmployeeID]=[timeslip2].[EmployeeID].value;

If you only need a text box to display the employee last
name if it's in the table, then you can use the DLookup
function in the text box's control source expression:

=DLookup("LastName", "Employee", "EmployeeID=" &
Me.EmployeeID)

that will return Null if there is no employee with the
entered ID.

 

[Marshall Barton]

I've another possible complication: the employeeID has a period in it such
as 4.0090

That means we would have to know the data type of the
employeeID field. If it's a Text field, it would have to be
quoted in the result of the concatenateion.

Since I found out that employeeID is a combo box, I suspect
you do not need to use DLookup at all. See my earlier reply
and let's go from there.