How to validate input parameters with another table values (1 Viewer)

[Emily]

I am really stuck!! Can someone please help. I am using Access 2010. I have a simple employee timesheet input form with a subform. The main form just display the employee id and name and the subform display the timesheet detail. The form is based on a query with a criteria input parameter (employee id). Is there anyway, I can validate the user is inputing a valid employee id that exists in the empoyee table. Right now if the user input an invalid id, the form will just allow user to add data in. Is there a way to do this without using VBA or macro cause I don't know any of it.
Any help is appreciated !!! Thanks!

 

[pbaldy]

One way would be to use a combo box that got its selections from the employee table. With the Limit to List property set to Yes, they wouldn't be able to enter an invalid employee.

 

[Emily]

Thanks for your reply. But is there another way, because I don't want the other employees to see the other person's id. Right now if the user entered an invalid id, the query result set would be empty. Is there a way I can capture that and send an error message without opening the input form.??? I am just learning access on my own and this forum has been my great teacher. I am willing to learn some macro and vba if that's what it takes. thanks

 

[pbaldy]

There are typically several ways. Here's another:

If DCount("*", "EmployeeTable", "EmployeeID = " & Me.EmployeeID) = 0 Then
  'the ID doesn't exist
Else
  'the ID does exist
End IF

Changing to your actual names of course. One place to do the test is in the before update event of the form (or the textbox):

http://www.baldyweb.com/BeforeUpdate.htm

 

[Emily]

Thanks Paul,
What is "Me" in the Me.EmployeeID? Is that a system command? or object name ? I tried replacing 'Me' with my object name but it couldn't recognized it so I change it back to 'Me' and it seems to work better. I also copy some of other codes but when I test it out, the msg box didn't come out. The following is what I have. Please help! many thanks in advance!

Private Sub Form_BeforeUpdate(Cancel As Integer)
If DCount("*", "EmployeeTable", "EmployeeId = " & Me.EmployeeId) = 0 Then
'the ID doesn't exist
MsgBox "Invalid Employee Id Entered"
Cancel = True
Me.SomeControl.SetFocus
End If

End Sub

 

[pbaldy]

"Me" is a shortcut to refer to whatever object contains the code (your form in this case). The long version would be:

Forms!YourFormName.YourTextboxName

Did you change the various names to your actual table, field and textbox names? Can you post the db?

 

[Emily]

Oh I see! yes I've changed the actual table name already. What do you mean by post the db?

 

[pbaldy]

If you click on Post Reply or Go Advanced you'll see a button that will allow you to attach a zipped database. Sometimes it's easier to fix by looking at it.

 

[Emily]

Hi Paul,
Sorry for the late reply. I just got back to work today. I managed to play around and put the store procedure in the 'before insert' event and the error message finally come out. However the error message is still not coming out in the right sequence of event. I want it to come out right after the user entered the input parameter box and before the form is open. I have attached my sample database with this reply, can you tell me what I am doing wrong. thanks a million!!

 

[pbaldy]

I tried to open that on a PC with 2007 on it and got an unrecognized database format error. Can you try again?

 

[Emily]

Ok..This time I didn't zipped it. thanks

 

[pbaldy]

Maybe it's me, as I got the same error. It's not a 2010 file is it?

 

[SOS]

Maybe it's me, as I got the same error. It's not a 2010 file is it?

I am using Access 2010.

Most likely is

 

[Emily]

Yes, it is a 2010 file. I am running Access 2010. I thought 2010 can be downward compatible with 2007? Is it not?

 

[SOS]

Yes, it is a 2010 file. I am running Access 2010. I thought 2010 can be downward compatible with 2007? Is it not?

Only if you haven't used any new 2010 features that aren't available in 2007.

 

[Emily]

Hi Paul,

Are you able to open my DB in 2007. I don't recall using any 2010 special features in the posted DB. Is there a way you can guide me through it or do you want me to save it as 2007 then post it again?

 

[Emily]

Okay!! I found a work around solution !!!!

Instead of using DCount() to check the return result set to determine if the employee is valid on the employee table. I just build a macro on the subform OnOpen event to check if the employeeId textbox is null. If IsNull then I just do a cancel event and close datawindow. Finally got it to work!!! and the user can't go into the form unless they put in a valid id. Hurray!! Thanks Paul and S.O.S for your input.

But I still want to know how Access handles DCount(expression, domain, criteria), if the criteria is an argument where part of the argument is a user input parameter.
(eg: the criteria is tablename.employeeId = [user input parameter] ) Access doesn't seem to retain the value of user input parameter.

 

[Emily]

Okay!! I found a work around solution!

Instead of using DCount() to check the return result set to determine if the employee is valid on the employee table. I just build a macro on the subform OnOpen event to check if the employeeId textbox is null. If IsNull then I just do a cancel event and close datawindow. Finally got it to work!!! and the user can't go into the form unless they put in a valid id. Hurray!! Thanks Paul and SOS for your input.

But I still want to know how Access handles DCount(expression, domain, criteria), if the criteria is an argument where part of the argument is a user input parameter.
(eg: the criteria is tablename.employeeId = [user input parameter] ) Access doesn't seem to retain the value of user input parameter. Is there a way I can keep the value?