How do I Use DLookup() to Check a Logon

[Cyd44]

I am at a loss on how to write a DLookup to find the value of a field in a table based upon the value of a variable that relates to a different field in that table.

Scenario is:-
Tble 1 contains #ID (Long), Name (String) etc.
Tbl 2 has Name, Date etc but not an ID.

I have a form which has takes info for Tbl 2 and I have caputured the ID from the Login function.

I now want to compare the Name Chosen for Tbl 2 with the Login ID I have captured to see if the Name in both Tbl 1 & the Form for Tbl 2 Match.

I am getting really confused with the logic and wonder if you can advise me.

My Current Code is as follows:-

 If loginID = DLookup("strEmpName", "tblEmployees", "[lngEmpID] =" & Forms![frmBookings.BookName]) Then
         'MsgBox ("Login Name Matches Login ID")
         'Me!BookName.SetFocus
         'Else
         'MsgBox ("Not Matched")
        'Exit Sub
 
        'End If

LoginID = the Login info captured...this will equal an Employee Record ID field

I Need to look in the tblEmployees and find the name associated with the ID which = LoginID.

Once I have this I can compare Names to see if they are equal.

I know my code is wrong but my brain has gone and I cannot figure out the logic.

[NeoPa]

How do you make something so simple into something so complicated? I can tell you've put effort into asking the question but wow!

First try this :

If loginID = DLookup("[lngEmpID]", _
                     "[tblEmployees]", _
                     "[strEmpName] = '" & Forms!frmBookings.BookName & "'") Then

If that works we don't need to go through the painful process of trying to make this question make sense.

Otherwise continue on and answer all these questions thoroughly and clearly if you would :

1. What type of value would you expect to find in Forms!frmBookings.BookName? Is this a string value that matches both the fields in your two tables called [Name]?
2. Forgetting [Tble 1] and [Tbl 2], which are introduced but never mentioned in your code or afterwards, which two tables are you dealing with (Clearly one is [tblEmployees], but the other one we don't know)?
3. Is there any requirement in the question for the other table after all? My guess is that it's redundant as far as this question goes.

[NeoPa]

By the way, if you already have an ID for the current user, and you also want to ensure none but the current user log on to this for entering information (In other words users can only enter data for themselves on this form), then would it not be simpler and more straightforward to use the ID already saved and simply populate the data on the form to reflect that value only. IE. No selection or entering required (that requires subsequent checking), but the value is provided from the information you already have available.

Would that not make more sense?

[Cyd44]

Sorry to confuse you but you clearly worked out what I meant as youur code worked. Many thanks agian my friend

[NeoPa]

I was able to guess (albeit correctly in this instance), but that's really not how things ought to work. I don't mean that unkindly. You clearly put in some effort as I recognised before. I wish there was something more helpful I could say, but it has to come from you really. It's always harder when you're deeply involved in something that has you confused, I appreciate.

What I will say for now, is just to suggest you give post #3 some careful consideration. It's easy to skip past other posts when you find what you're looking for, but you may miss another approach that makes the whole problem a lot simpler. See what you think anyway. I'll leave it with you.

[Cyd44]

Your points are taken NeoPa. You knowledge and advise is appreciated, as always. It may take me a little time but I do get there. It is my lack of syntax knowledge that is letting me down so I appreciate the help I can get here. post #3 comments considered as suggested.

[NeoPa]

I was heartened to read your latest post. All good news it seems (Whatever decision you come to for now).

Cyd44:
It is my lack of syntax knowledge that is letting me down so I appreciate the help I can get here.

Whenever I struggle with syntax I find the Context-Sensitive Help invaluable. There is also good help for SQL syntax in the Help system too (See Finding Jet SQL Help).

And of course, we're always here to fill in any gaps.