Validating Field data within a Table

[Guest]

Hi
I have a Table "Tbl_Log_Receipt" which has a field where the user must input
his unique Id, this field is named "User_ID"

I want the user to enter his ID in this field and if the entry does not
match a record within another table called Tbl_Users, it will display a
warning message and blank his entry.

Is this possible??

Thanks in advance for any assistance.

Joe

 

[Douglas J. Steele]

Rather than make the user key in an id, why not pick up his/her network ID
and use that?

There's code in http://www.mvps.org/access/api/api0008.htm at "The Access
Web" to let you get that information.

 

[Guest]

Thanks for your very prompt reply, I understand what you suggest, I already
collect this and populate it onto a table for audit purpose, however, the
system is set up to utilise a number of user groups, 1 pc is used for many
people within the group, therefore the network id is not a low enough level.

Once the system is opened by one of the users within the group, each member
of the group will transact information, if I only collect the network ID,
this will hide the individual who carried out the transaction.

Eventually an admin guy will add and remove users via a maintenance form,
thats why I need to be able to validate against this other table that will be
maintained by the admin guy.

Any help at all will be greatfully appreciated.

 

[Douglas J. Steele]

Assuming that the txtUserId control is bound to the appropriate field in the
form's recordsource, you can put code in the control's BeforeUpdate event:

Private Sub txtUserId_BeforeUpdate(Cancel As Integer)

If IsNull(DLookup("User_ID", "Tbl_Users", "User_ID ='" & Me!txtUserId &
"'")
Msgbox Me!txtUserID & " is an invalid user id."
Me!txtUserId.Undo
Cancel = True
End If

End Sub

 

[Guest]

Many Thanks Douglas (if I may call you that)

I have pasted the code into the BeforeUpdate event as you have suggested,
however when I enter data into the User_ID field, I get an error message
"Compile Error" and the line "If IsNull(DLookup("User_ID",
"Tbl_Users........." is highlighted in red It is more than likely that I
have missed something, sorry for the inconvenience

Joe

 

[Douglas J. Steele]

There was word-wrap in what I posted (and I forgot the keyword Then after
the closing parenthesis).

Putting in a line-continuation character to avoid the word wrap (and
correcting the omission), it should be:

If IsNull(DLookup("User_ID", "Tbl_Users", _
"User_ID ='" & Me!txtUserId & "'") Then

If that still doesn't work, post back with your exact code. (copy-and-paste
it, as opposed to retyping it)

 

[Guest]

Douglas

Thank you very much for you time and patience, I have tried copying in your
last solution, this is returning the following error message with the
IsNull..... line being highlighted

"Compile Error"
"Syntax Error"

The copy and pasted code is as follows:-

Private Sub txtUserId_BeforeUpdate(Cancel As Integer)

If IsNull(DLookup("User_ID", "Tbl_Users", "User_ID ='" & Me!txtUserId &
"'") Then

MsgBox Me!txtUserId & " is an invalid user id."
Me!txtUserId.Undo
Cancel = True

End If

End Sub

Again thank you for your assistance

Joe

 

[Douglas J. Steele]

Once again, my typo. You're missing a closing parenthesis:

If IsNull(DLookup("User_ID", "Tbl_Users", "User_ID ='" & Me!txtUserId &
"'")) Then

My apologies.

 

[Guest]

Fantastic, works like a dream, many thanks for your help.

How can I mark this as an excellant answer to my question, do I just click

 

[Douglas J. Steele]

To be honest, I don't use the web interface, so I'm not sure, but I assume
that's what you do.