[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now


registration form - code to Search table for matching citeria on form...

Posted on 2006-06-14
Medium Priority
Last Modified: 2010-04-07
I have a registration form in my project (ms access)
I have a table that holds username and password (once registered) called "TblUser"
I have another table that I can enter Usernames into manually to allow them access to register or not (TblAllowed)
Basically, while entering registration data (username, new password etc) once the user clicks on the register button, I would like it to check
1. If the username already exists (TblUser)
2. If they are allowed to register (check if their name is in TblAllowed.)
below is some of the code which Im using, but it only seems to look at the first names in the tables, not all of them.

If Me.TxtUser.Text <> DLookup("AllowedAccess", "TblAllowed", "[AllowedAccess]") Then
MsgBox "Im sorry you are not Authorised to use this database...please see Admin for Access rights", vbOKOnly, "Access Error!!"
ElseIf Me.TxtUser.Text = DLookup("UserName", "TblUser", "[Username]") Then
MsgBox "User already exists, try again!!", vbOKOnly, "User Name Error"

Being fairly new at this, any help and explanations would go a long way.

Kind Rgds / Kevbb.
Question by:kevbb
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
  • 2
  • +2
LVL 16

Expert Comment

by:CWS (haripriya)
ID: 16909876
Can you post your DLookup function

Author Comment

ID: 16917030
Im sorry cyberwebservice im a bit confused by your question?

Im using Microsoft Access, and, although Im fairly new at this, I believe the DLookup is a built in function of Microsoft Access, so I cannot post the code.

Rgds / Kevbb.
LVL 16

Expert Comment

by:CWS (haripriya)
ID: 16919779
Please post your query under the Topic MS Access.
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

LVL 28

Assisted Solution

omgang earned 200 total points
ID: 16923804

A couple of things:  the DLookup function will return Null if not successful so you have to anticipate this.

Dim varLookup As Variant
varLookup = DLookup(.....)
If Is Null varLookup Then

The DLookup function returns the value of a field in the domain when another (the lookup) field matches some criteria
DLookup("AllowedAccess", "TblAllowed", "[AllowedAccess]") says
return the value of field [AllowedAccess] in domain [TblAllowed] where [AllowedAccess]  ????????????????

I think what you want is
DLookup("AllowedAccess", "TblAllowed", "AllowedAccess = '" & Me.TxtUser & "'")
this will return the value of field AllowedAccess IF it equals the value of field TxtUser on the current form
it will return Null otherwise
If IsNull(DLookup("AllowedAccess", "TblAllowed", "AllowedAccess = '" & Me.TxtUser & "'")) Then
   'your message here
End If

Get the idea?
OM Gang
LVL 65

Accepted Solution

rockiroads earned 200 total points
ID: 16923819
dlookup returns null if no match found

Do u want to see if current user logged in has access?

1. If they exist in tblUser

assuming Me.txtUser is what they entere

If IsNull(DLOOKUP("UserName","TblUser","UserName = '" & Me.TxtUser & "'")) = True then
    msgbox "user does not exist"
    exit sub
end if

Im assuming accessallowed is a yes/no field?
TblAllowed must have the username or userid stored
Assuming its username

Dim bAccessAllowed as Boolean

bAccessAllowed = NZ(DLOOKUP("AllowedAccess","TblAllowed","UserName = '" & Me.TxtUser & "'"),0)
if bAccessAllowed = False then
    MsgBox "Im sorry you are not Authorised to use this database...please see Admin for Access rights", vbOKOnly, "Access
    exit sub
end if

Hope u get the idea now of using DLOOKUP

This is my last post of the day, I have to leave.
Hopefully Ive given u enough to get you going, else someone else may help
LVL 34

Expert Comment

ID: 16923862
Function LookupUser()
Dim rs As DAO.Recordset
Dim rs1 As DAO.Recordset
Dim strSQL As String
Dim strUserName As String
strUserName = Me.TxtUser.Text
rs.OpenRecordset ("Select * from TblUser where UserName = " & strUserName)
If rs.RecordCount > 0 Then
MsgBox "User already exists, try again!!", vbOKOnly, "User Name Error"
End If
rs1.OpenRecordset ("Select * from TblAllowed where AllowedAccess = " & strUserName)
If Not rs1.RecordCount > 0 Then
MsgBox "Im sorry you are not Authorised to use this database...please see Admin for Access rights", vbOKOnly, "Access Error!!"
End If
Set rs = Nothing
Set rs1 = Nothing

End Function
LVL 34

Expert Comment

ID: 16923867
Later Rock!!!
LVL 34

Expert Comment

ID: 16923915
You never really said what you wanted to do If the user can actually register. Should we assume you have already coded that and you know where you want to include the lookups? Let us know.


Author Comment

ID: 16931502
Thanks People...you all helpd me out lot's. I really appreciate the explanations of what the functions are doing.

Cheers / Kevbb.
LVL 65

Expert Comment

ID: 16931909
No probs

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When designing a form there are several BorderStyles to choose from, all of which can be classified as either 'Fixed' or 'Sizable' and I'd guess that 'Fixed Single' or one of the other fixed types is the most popular choice. I assume it's the most p…
I was working on a PowerPoint add-in the other day and a client asked me "can you implement a feature which processes a chart when it's pasted into a slide from another deck?". It got me wondering how to hook into built-in ribbon events in Office.
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…
Suggested Courses

649 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question