Macros -> query -> Y or N ->

[accessaccess]

Hello

I have a TABLE with the following data:
- EMPID
- LASTNAME

I have a form with the folliwing data:
- text box for user to enter EMPID, txtEMPID
- LASTNAME

I would like to create a button to initiate a query to do the following:

- once the user enters a EMPID in the form, it will search in the TABLE under the EMPID field...
- if the user enters an EMPID that is in the TABLE...display "Y"
- if the user enters an EMPID that is NOT in the table...display "N"
- a error message box needs to pop up if "Y" to alert the user that "the EMPID already exists and that duplicate entries are not allowed"


One of my main questions is how do I run a query based on the users input and search a table?

would the following work in the query?

If I could receive some help on the appropriate commands to make this query/macro run that would be greatly appreciated.

Cheers!

 

[plog]

You don't need a query, you need a DCOUNT(http://www.techonthenet.com/access/functions/domain/dcount.php). Use that in the code for the buttons OnClick event. If the Dcount value is more than 1 that means a value exists, if it is 0 it means the value doesn't exist.

 

[accessaccess]

Plog

Could you assist in helping to write the correct statement so i will be using the txtEMPID in the expression?

what do the [], !, " ", mean when they are used in an expression?

 

[plog]

This should be your DCOUNT:

records = DCount("[EMPID]", "YourTableNameHere", "[EMPID]='" & Me.txtEMPID & "'")

Replace YourTableNameHere with the name of your table.

 

[accessaccess]

What does

DCOUNT("COLUMN", "TABLE", "CELL1 =" &[CELL2])

what does the bold and underline mean?

is that saying that the CELL1 is equal to the value in CELL2?

 

[accessaccess]

Hi Just saw your post before I posted my other post, what does Me mean? what does records mean?

 

[accessaccess]

do i input that statement in the 'control source'?

would the following work?

=IIf(DCount("[EMPID]","Updated Headcount","[EMPID] ='" & [Me].[txtEMPID] & "'")>1,"Error duplicate","Yes")

 

[plog]

No, you would put it in the OnClick event of the button. Your last post might work--not sure of the brackets around the Me.txtEMPID