Hello Everyone (1 Viewer)

Good Morning everyone:banghead:

I am creating a database for a non-profit veterans organization and I'm having a little trouble with the advanced security login form. The error started as an object not found, then syntax and compile error, now type mismatch.

Here is a copy of my code. I've looked in all the property sheets to try and find the mismatch. No Luck still can't seem to find it.

Private Sub LOGIN_ID_FORM2_Click()
Dim userlevel As Integer
Dim Userpassword As Integer
Dim Userlogin As String
Dim securityLVL As Integer
Dim password As Integer



If IsNull(Me.txtLoginID) Then
MsgBox "Please Enter LoginID", vbInformation, "LoginID Required"
Me.txtLoginID.SetFocus
ElseIf IsNull(Me.txtPassword) Then
MsgBox "Please Enter Password", vbInformation, "Password Required"
Me.txtPassword.SetFocus
Else
'PROCESS JOB
If (IsNull(DLookup("[userlogin]", "Login", "[userlogin]= '" & Me.txtLoginID.Value & "'" And _
(IsNull(DLookup("[userPassword]", "Login", "[userPassword]= '" & Me.txtPassword.Value & "'")))))) Then
MsgBox "INVALID LOGIN ID OR PASSWORD"
Else
userlevel = (DLookup("[SecurityLVL]", "Login", "[UserLogin]= '" & Me.txtLoginID.Value & "'"))
DoCmd.Close
If userlevel = 1 Then
'MsgBox "LOGIN ID AND PASSWORD ARE CORRECT"
DoCmd.OpenForm "ADMIN FORM"
Else
DoCmd.OpenForm "SPLASH PAGE"
End If

End If


please, anyone, I would appreciate the help.

A type mismatch typically occurs when you mix data types, like trying to put a string value into a numeric variable. Where exactly does the error occur? Are passwords really integers?

yes, I give the numeric password. so I'm sure it's an integer. It dosesn't make it past the dlookup login information just before the dlookup for the securityLVL
thank you for your help Debbie

If the password field is numeric, the DLookup is treating it as text. You don't want the single quotes:

http://www.theaccessweb.com/general/gen0018.htm

First comment, and it is of lesser importance but will make your life easier later...

Me.txtLoginID.Value is overkill. For anything that HAS a value, the .Value property is the default selection. You could use just Me.txtLoginID and get the same result.

But I have to ask: What is LOGIN_ID_FORM2? Is it a form or a control? If a control, how were you intending to use it? Because I don't see from the code you showed us how any of the other values are related. I can make an educated guess, but that might lead us astray.

I agree with Paul.


I highlighted the suspicious quotes. You said that [UserPassword] is numeric. Well, if you put the quotes in that position, you force the data type to text, so that means you are comparing a numeric (stored) password to a text (form-based) password. And THAT is a mixed-mode operation.

Once the DLookup syntax is fixed, the next issue is with the If test. The And condition should be replaced with an Or

Otherwise, the logon test will pass if either the LogonID or Password match.

To be fair, there ARE cases where Access will attempt to assign the object rather than the default property if you don't specify a property, but this isn't one of them.

You might want to consider using a recordset instead of multiple DLookups. You can save a query using the values in the login fields as parameters. If the brand-new recordset shows EOF, then invalid credentials were presented. If it's not EOF, then you can pull other info like userlevel from the completed query and complete the login, all with the use of only one table access rather than three.

DLookup isn't as brutally slow as it used to be thanks to the speed of modern processors, but it's still MUCH slower than a query.

And on a side note, now I'm curious if DLookup can use indexes. That might go a long way toward explaining how slow it is....

From recollection, speed testing of Dlookup was faster on an indexed field.

DLookup might be slower than a query but it is significantly faster than opening a recordset.

Cronk said:

DLookup might be slower than a query but it is significantly faster than opening a recordset.

Click to expand...

...that's precisely what select queries do....

And in this case, the question isn't so much whether or not DLookup is faster than opening a recordset, but rather whether or not running THREE DLookups is faster than opening a recordset.

Two links that may be useful

1. Example password login form with session recording (for the OP)
https://www.access-programmers.co.uk/forums/showthread.php?t=193131

2. Speed test comparison for DCount and recordsets - both indexed and unindexed.
https://www.access-programmers.co.uk/forums/showthread.php?t=302745. See post 10 onwards

Similar results will apply for DLookup and recordsets.
For indexed fields, I would expect DLookup to be faster.
If not indexed, both will be probably so slow that it makes little odds which you use

Interesting.

Have you done DLookup against a stored query that returns a single, specific record? If not, I might do that on my lunch. It's not like I don't have access to sufficiently large tables.

If the results are similar, then that suggests that the recordset is the better option once you reach 5 DLookups (which I have actually done).

DLookup is a recordset-based query. It is a self-contained function that builds a dynamic SQL string from the three fields you supply then opens a recordset, does a .MoveFirst, picks out the value you want, and returns that. Then it cleans itself up. The reason that DLookup is slightly faster than doing those steps yourself is that DLookup code is compiled whereas any VBA you write is interpretive.

Frothy
Have a look at (or lookup) the various other speed tests I've done.
Most can be found here: http://www.mendipdatasystems.co.uk/speed-comparison-tests/4594424200

I have an antipathy to creating my own recordset code unless that offers a real advantage. For a password form, I don't believe that is the case.
DLookup works very fast for the user table size you're likely to be checking at login even though you have to run several DLookups for e.g. username/password/security level

BUT indexing is essential for fast searching

To be honest, the only time so far I've used an explicit recordset pull for a login was one application where i needed to regularly call about 8 different pieces of data. For that one, I created a UserInfo class, used a recordset to pull all 8 fields at the same time, and assigned the results to the properties of the class. Other than that, I've only used DLookups for logins to-date.

Okay, on my lunch break, out of sheer curiosity, I ran an Isladogs-style test. Due to only having a half hour to play with, I only did 1000 event runs rather than his usual 10,000 event ones, but i can rerun them again when I get home.

The tests were run against a linked SQL Server database, using linked table with roughly 1.8 billion records. (I can check against a few hundred records later.)

I set up a form with three text boxes. Each text box contained one piece of data used for the test.

Step 1, I checked DLookup speed.
In this test, I used three DLookups to each pull a different piece of data and then assign that result to a variable. I ran them 1000 times, and timed the entire sequence. This was done ten times.

Step 2, I checked CurrentDB.Openrecordset, using a dynamically-generated SQL statement as the recordset. The SQL was generated with the values of the controls being parsed in, and then the recordset was opened. The values of the same three fields used for Step 1 were then each assigned to variables, and the recordset was closed and set to nothing. As with Step 1, ten tests of 1000 runs each were run.

Step 3 was identical to step 2, except that a saved query was used, with parameters written into the query to pull the values needed for the search directly from the form via Forms!Formname!ControlName.

The results I received were as follows:

DLookup Results
Run 1 Total Time: 33s, Avg Time: 0.033s
Run 2 Total Time: 21s, Avg Time: 0.021s
Run 3 Total Time: 28s, Avg Time: 0.028s
Run 4 Total Time: 17s, Avg Time: 0.017s
Run 5 Total Time: 24s, Avg Time: 0.024s
Run 6 Total Time: 16s, Avg Time: 0.016s
Run 7 Total Time: 14s, Avg Time: 0.014s
Run 8 Total Time: 16s, Avg Time: 0.016s
Run 9 Total Time: 16s, Avg Time: 0.016s
Run 10 Total Time: 16s, Avg Time: 0.016s
Overall Average Time: 0.0201s


SQL Results
Run 1 Total Time: 11s, Avg Time: 0.011s
Run 2 Total Time: 12s, Avg Time: 0.012s
Run 3 Total Time: 10s, Avg Time: 0.01s
Run 4 Total Time: 9s, Avg Time: 0.009s
Run 5 Total Time: 11s, Avg Time: 0.011s
Run 6 Total Time: 10s, Avg Time: 0.01s
Run 7 Total Time: 10s, Avg Time: 0.01s
Run 8 Total Time: 11s, Avg Time: 0.011s
Run 9 Total Time: 10s, Avg Time: 0.01s
Run 10 Total Time: 11s, Avg Time: 0.011s
Overall Average Time: 0.0105s


Query Results
Run 1 Total Time: 8s, Avg Time: 0.008s
Run 2 Total Time: 7s, Avg Time: 0.007s
Run 3 Total Time: 8s, Avg Time: 0.008s
Run 4 Total Time: 7s, Avg Time: 0.007s
Run 5 Total Time: 10s, Avg Time: 0.01s
Run 6 Total Time: 7s, Avg Time: 0.007s
Run 7 Total Time: 7s, Avg Time: 0.007s
Run 8 Total Time: 9s, Avg Time: 0.009s
Run 9 Total Time: 9s, Avg Time: 0.009s
Run 10 Total Time: 7s, Avg Time: 0.007s
Overall Average Time: 0.0079s

TBH that's pretty much what I would have expected although the difference between the saved query and SQL was larger than i would have predicted.
See my reasonably similar tests on sql vs query here http://www.mendipdatasystems.co.uk/speed-comparison-tests-6/4594478795

What really does surprise me is how fast each of your three sets of tests were.
Must be a fairly powerful test machine

If you do a smaller scale version at home, perhaps you could upload it for others to try as well.

I was definitely thinking of doing that.

The test machine is an HP EliteBook (I don't know the specific type) running Windows 7 on an i5-4300U CPU at 1.90 GHz with 8 GB RAM. The data is saved in a dedicated SQL Server 2014 database with concurrent user counts ranging from 0 to around 20 open connections/active queries at any given time. (It's a 100-ish person department.) When I ran the test, there were 5 active connections running things, including one very large bulk insert. None were from my machine, though.

My home PC is a few years old, and actually not quite as powerful as the work laptop appears to be.

Cronk said:

Once the DLookup syntax is fixed, the next issue is with the If test. The And condition should be replaced with an Or

Otherwise, the logon test will pass if either the LogonID or Password match.

Click to expand...

I tried using OR and the login test passed either way that's why i changed it to AND

I apologize for not catching this before, but the nesting on this expression is ALL jacked up. There is NO WAY that the batch of right parentheses near the end of that is right. This expression SHOULD look something like this.

Cronk said:

Once the DLookup syntax is fixed, the next issue is with the If test. The And condition should be replaced with an Or

Otherwise, the logon test will pass if either the LogonID or Password match.

Click to expand...

I started with or and that's what happens. That's why I used the And statement.