Using Query Results in a Form MS Access

[gwww]

I have two fields on a form called Employee Name and Employee Number. I would like the user to select their name and their coresponding number will appear in the Employee Number field.

The Employee Name field is a lookup from a table.
The Employee Number field is the result of a query.

The number field comes in at 0.

My query results look like the following:

Employee Name Employee Number
> Recieving 101
* 0

I think that the default value in the form is getting the zero from the autonumber field, which is also the index.

Does anyone have a suggestion?

[NeoPa]

We need to see the code you're using as well as the results to be able to see what may be wrong.
Check out this link for help in posting questions ( http://www.thescripts.com/forum/thread559246.html ).

[gwww]

SELECT EmpID.[Employee Name], EmpID.[Employee Number]
FROM EmpID
WHERE (((EmpID.[Employee Name])=[Forms]![Receiver]![Employee Name]));

the code above is the query code, and should only return one record. It returns both the correct record an additional entry with a value of 0. Just like the last visible record of a table when opened in access.

It is a basic select query and in the forms field "Employee Number" property box the default value is
[EmpID]![Employee Number] My understanding is that it should display the value from the query. But since the query has the second entry it is displaying the 0 from the unwanted second entry.

If I could figure out how to attach a screen shot it would be easier to understand.

[NeoPa]

That's MUCH clearer gwww.
I must leave for home now but I'll look at this later, time permitting.
I now understand the issue :).

[MMcCarthy]

I think the 0 is actually appearing because you're returning the EmpID.[Employee Name] from the query. You don't need to.

Try this instead:

SELECT EmpID.[Employee Number]
FROM EmpID
WHERE (((EmpID.[Employee Name])=[Forms]![Receiver]![Employee Name]));

[NeoPa]

Sorry, I must have overlooked this one yesterday.
Thanks to Mary for stepping in.

[MMcCarthy]

Sorry, I must have overlooked this one yesterday.
Thanks to Mary for stepping in.

I know you went to bed really early last night. I had over 100 new posts to trawl through to make sure nothing was orphaned or inappropriate. So I was up a little late as usual. This site is getting so busy we'll have to work out a schedule so everyone can get a little sleep. <grin>

I'm surprised I was so articulate considering the time.

Mary

[MMcCarthy]

I know you went to bed really early last night. I had over 100 new posts to trawl through to make sure nothing was orphaned or inappropriate. So I was up a little late as usual. This site is getting so busy we'll have to work out a schedule so everyone can get a little sleep. <grin>

I'm surprised I was so articulate considering the time.

Mary

BTW, there wasn't must to worry about. Thanks a million guys for covering.

Mary

[NeoPa]

I know you went to bed really early last night. I had over 100 new posts to trawl through to make sure nothing was orphaned or inappropriate. So I was up a little late as usual. This site is getting so busy we'll have to work out a schedule so everyone can get a little sleep. <grin>

I'm surprised I was so articulate considering the time.

Mary

LOL
You were up after I went to bed young lady. You and your bed seem to be opposite ends of a magnet.

-Adrian.

[MMcCarthy]

LOL
You were up after I went to bed young lady. You and your bed seem to be opposite ends of a magnet.

-Adrian.

Maybe I was born on the wrong side of the planet?