Access 2013 Command Button to Find a Record

Question

Hello,

I have an access table, tbl_Trainer, which holds data for trainers. There are two fields, trainerID and trainerName, which trainerID is the primary key.

I have created a form, named frm_Trainer to enter data to tbl_Trainer, it has the two fields from the table: txt_TrainerID and txt_Name. Entry fields are text boxes.

In the form, I have placed a command button to check if the trainerID that is entered by the user in the text box txt_trainerID already exists in the table tbl_Trainer. 

In the command button click procedure I have created a "Result" parameter and built as follows:

result = DLookup ([trainerD], Tables![tbl_Trainer], "[frm_Trainer]![txt_trainerID]=" & Tables![tbl_Trainer]![trainerID])

With this my intention is that if the result is null, Access will display the message that the ID is available, otherwise it will inform the user that the ID is already in the table.

However, the result function is not working and returning an "Object required" message. I could not figure out what is wrong. I need help with this.

To make the search and return information, I do not insist on using a procedure, if I can do this with a macro, that is fine [and much better, frankly]. If the procedure is the long way to go, can you please direct me to a source/example which can help me with this issue (find/replace/inform user)?

Thanks,

Tolga

Answer 1

Hi

if the TrainerId is a text (not numeric) change your formula with:

result = DLookup ("trainerID", "tbl_Trainer", "TrainerID = '" & ME.txt_trainerID & "'")

Mimmo

Answer 2

Mimmo, this works like a charm! Thank you very much!

Scott, thank you very much for pointing me to the right direction.

Answer 3

Hi Scott, Mimmo, 

Thank you very much for your replies.

Mimmo, the equation returns 

Run-time error '3075'

Syntax error (missing operator) in query expression 'trainerID='.

Scott,

1. I'm sorry for my misuse of words, frankly I am not an Access guy and really did not think about this. I have created the form with the wizard. It should be a bound text box, so that txt_TrainerID is bound to trainerID field in the tbl_Trainer table.
2. See above :)
3. I have dimmed result as variant. 
4. TrainerID is assigned manually. That's why I want to check if the manually assigned ID is already present. 

Thanks,

Tolga

Answer 4

First, you don't have fields on a form, you have controls that may or may not be bound to fields in a table. This is a subtle but important distinction.

Second, as Mimmo says you need to reverse the comparsion in your DLookup.

Third, Did you define Result as a data type.  If the TrainingID doesn't exist, then the DLookup will return a Null so Result has to be DIMmed as a Variant to accept Nulls. 

Finally, are Trainer's assigned IDs through another process? If not, why not just use ans autonumber?

Answer 5

Hi,

try to change:

result = DLookup ("trainerID", "tbl_Trainer", "TrainerID = " & ME.txt_trainerID)

Bye Mimmo