473,327 Members | 1,919 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,327 software developers and data experts.

Timeclock scenario bugging out on dlookup

13
Edit #1 - I'm Using access 2010 with an access desktop database (.accdb). hopefully i'm referring to that correctly.
I'm working on a makeshift timeclock via VBA and access and I was never the best with access when I took the class in high school. its been about 10 years since I've done much with it. Same goes for visual basic though never VBA. Anyway I have it figured out to the point where i must use a dlookup command to check the passwords against each other. I'm working with 3 tables and my clock form. All names I am giving in this post are exactly what they are in the database itself.

Form: TimeClock
I have a drop down box for the username and a text box for the password entry.
There will be 4 seperate buttons under the password text box. CmdClockIn, CmdLunchOut, CmdLunchIn, and CmdClockOut.When you click on each button it will record the timestamp and when we generate our report it will do the math to get the total hours worked.
Also the recordsource for this form is set to TblTimeSlip.

Table 1: EmployeeInfo
This table contains the username and password for the employee along with other irrelevant information.
Fields affected: Username, EmpPassword

Table 2: TblTimeSlip
This table is where the timestamps will be filled.
Fields Affected: TimeIn, LunchOut, LunchIn, TimeOut

By looking at other posts and examples I've found I got the form to check and make sure there is something entered but it errors out when trying to lookup the password. The current Code I have is:

Expand|Select|Wrap|Line Numbers
  1. Option Compare Database
  2.  
  3. Private Sub CmdClockIn_Click()
  4. Dim time As String
  5. If IsNull(Me.UserNameSelect) Or Me.UserNameSelect = "" Then
  6.     MsgBox "You must select the name of a current Employee.", vbOKOnly, "Required Data"
  7.         Me.UserNameSelect.SetFocus
  8.     Exit Sub
  9. End If
  10.  
  11. If IsNull(Me.PasswordEntry) Or Me.PasswordEntry = "" Then
  12.     MsgBox "You must provide your password.", vbOKOnly, "Required Data"
  13.         Me.PasswordEntry.SetFocus
  14.     Exit Sub
  15. End If
  16.  
  17. Me.[EmployeeInfo]!EmpPassword.Value = DLookup("[EmpPassword]", "EmployeeInfo", "[EmpID] ='" & Me.UserNameSelect & "'")
  18. time = Now()
  19. Me!TimeIn = time
  20.  
  21.  
  22.  
  23. End Sub
I have also noticed that when I'm the program gets to the point up inputting the timein it just puts it on the first record of TblTimeSlip.

I'm sure this seems elementary to a lot of people but for my first time through I'm dying for some help. If I could even get the correct syntax for some of these commands that would help tremendously. Even better would be an example with the correct syntax included. When i say correct syntax i mean some breakdowns of the commands like:

dlookup(table, record) or something in that general format.

I apologize if this is all jumbled but i forgot how to add a source code box on a forum. If there are any questions I will be checking this actively. Thank you so much for any and all help given. And I apologize for my lack of experience and knowledge on VBA and access.

Dave Martin
Aug 21 '13 #1

✓ answered by zmbd

What is the EXACT error?

You've stumbled upon one of my pet peeves by building the criteria string within the command - and it's not your fault because that's how a majority of examples show how to use the command.

Instead I suggest that you build the string first and then use the string in the command. Why you might ask, because you can then check how the string is actually resolving; thus, making troubleshooting the code so much easier as most of the time the issue is with something missing or not resolving properly/as expected within your string.
So to use your code from line 17 :
Expand|Select|Wrap|Line Numbers
  1. (...)
  2. DIM strSQL as string
  3. 'omitting some stuff - you can omit the comments :)
  4. '
  5. strSQL = "[EmpID] ='" & Me.UserNameSelect & "'"
  6. '
  7. 'now you can insert a debug print here for troubleshooting
  8. ' - press <ctrl><g> to open the immediate window
  9. ' - you can now cut and paste this information for review!
  10. '
  11. debug.print "Your criteria = " & strSQL
  12. '
  13. 'now use the string in your code:
  14. Me.[EmployeeInfo]!EmpPassword.Value = _
  15.     DLookup("[EmpPassword]", "EmployeeInfo", strSQL ) 
  16. '
  17. '(...)
If you will make these little changes and post back the resolved string we can help you tweak the code.


I have also noticed that when I'm the program gets to the point up inputting the timein it just puts it on the first record of TblTimeSlip.
This is a second question and really should be in a new thread; however, it's an easy answer... you didn't create a new record so you're only changing the current record of the form.
You will need to create a new thread to work on this question.

6 1300
zmbd
5,501 Expert Mod 4TB
What is the EXACT error?

You've stumbled upon one of my pet peeves by building the criteria string within the command - and it's not your fault because that's how a majority of examples show how to use the command.

Instead I suggest that you build the string first and then use the string in the command. Why you might ask, because you can then check how the string is actually resolving; thus, making troubleshooting the code so much easier as most of the time the issue is with something missing or not resolving properly/as expected within your string.
So to use your code from line 17 :
Expand|Select|Wrap|Line Numbers
  1. (...)
  2. DIM strSQL as string
  3. 'omitting some stuff - you can omit the comments :)
  4. '
  5. strSQL = "[EmpID] ='" & Me.UserNameSelect & "'"
  6. '
  7. 'now you can insert a debug print here for troubleshooting
  8. ' - press <ctrl><g> to open the immediate window
  9. ' - you can now cut and paste this information for review!
  10. '
  11. debug.print "Your criteria = " & strSQL
  12. '
  13. 'now use the string in your code:
  14. Me.[EmployeeInfo]!EmpPassword.Value = _
  15.     DLookup("[EmpPassword]", "EmployeeInfo", strSQL ) 
  16. '
  17. '(...)
If you will make these little changes and post back the resolved string we can help you tweak the code.


I have also noticed that when I'm the program gets to the point up inputting the timein it just puts it on the first record of TblTimeSlip.
This is a second question and really should be in a new thread; however, it's an easy answer... you didn't create a new record so you're only changing the current record of the form.
You will need to create a new thread to work on this question.
Aug 21 '13 #2
Rinis
13
Gave your idea a shot. I'm so happy you told me about the trick for the debug.print. getting a different error code now thanks to your help. In the comments at the bottom I wrote out my steps of manipulation.

Expand|Select|Wrap|Line Numbers
  1. Option Compare Database
  2.  
  3. Private Sub CmdClockIn_Click()
  4. Dim time As String
  5. Dim strSQL As String
  6. If IsNull(Me.UserNameSelect) Or Me.UserNameSelect = "" Then
  7.     MsgBox "You must select the name of a current Employee.", vbOKOnly, "Required Data"
  8.         Me.UserNameSelect.SetFocus
  9.     Exit Sub
  10. End If
  11.  
  12. If IsNull(Me.PasswordEntry) Or Me.PasswordEntry = "" Then
  13.     MsgBox "You must provide your password.", vbOKOnly, "Required Data"
  14.         Me.PasswordEntry.SetFocus
  15.     Exit Sub
  16. End If
  17.  
  18. 'omitting some stuff - you can omit the comments :)
  19. '
  20. strSQL = "[EmpID] ='" & Me.UserNameSelect & "'"
  21. '
  22. 'now you can insert a debug print here for troubleshooting
  23. ' - press <ctrl><g> to open the immediate window
  24. ' - you can now cut and paste this information for review!
  25. '
  26. Debug.Print "Your criteria = " & strSQL
  27. 'Your criteria = [EmpID] ='3'
  28. 'now use the string in your code:
  29. Me.[EmployeeInfo]!EmpPassword.Value = DLookup("[EmpPassword]", "EmployeeInfo", strSQL)
  30.  
  31. '^In the above line of code I'm getting a data type mismatch error: 3464
  32. 'I figured I would try changing the datatype of empid to text.
  33. 'I'm no longer getting a data type mismatch error.
  34. 'Now theres an error coming through saying it cannot find the field I'm referring to '|1'
  35. 'error code for this is 2465
  36.  
  37. End Sub
  38.  
Essentially what I'm saying in the comments is
I initially got a syntax error because of the break between the lines of the dlookup function. I got rid of the break and I started to get the first error listed.
This error was a data mismatch error 3464. My thinking in this situation was that the only field that is not text is the [EmpID]field so I tried changing this to Text. This solved the problem of the data mismatch error, but in turn created the second error.
This error says "Microsoft access cannot find the field '|1' referred to in your expression. " The error number is 2465. I have seen this error a couple of times before but it always stumps me. I have generally found another way around.

Either way I am extremely happy that you replied to my post. This has gotten me further than I've gotten from any other source. Any more insight is greatly appreciated.
Aug 22 '13 #3
zmbd
5,501 Expert Mod 4TB
This error says "Microsoft access cannot find the field '|1' referred to in your expression. " The error number is 2465
Almost always a malformed string or a typo in the code. I have yet to have this happen to me for any other reason than the ole fumble-finger.

If you will take a look at the resolved string (using that debug.print thing again :) ) make sure that the returned value is actually found in your tables. Ensure that the braces are paired etc...

As for changing the [EmpID] from a numeric to a text type, that is one fix. Better fix would have been to modified the code to match the data. You needed to only remove the single quotes " ' " from around the value you were looking up for numerics. Use " ' " around strings, Use " # " around dates (remember, Access ALWAYS uses mm/dd/yyyy in the SQL despite your region settings), and for numeric values no demarkation is required.
Aug 22 '13 #4
zmbd
5,501 Expert Mod 4TB
Just occured to me:
In the code block posted in Post#3
Take line 29:
Me.[EmployeeInfo]!EmpPassword.Value = DLookup("[EmpPassword]", "EmployeeInfo", strSQL)

Let's split this:

Expand|Select|Wrap|Line Numbers
  1. 'Add the following   
  2.    Dim str_FoundPW AS String
  3. '(...)
  4. '(...replace 29 with the following...)
  5.    str_FoundPW = _
  6.        DLookup("[EmpPassword]", "EmployeeInfo", strSQL)
  7.    Me.[EmployeeInfo]!EmpPassword.Value = str_FoundPW
  8. '
  9. '(...)
  10.  
Lets see if it is your Lookup that is causeing the issue, or if it is the refernce to the control/field that is causing the error...

Me.[EmployeeInfo]!EmpPassword.Value just doesn't look like a proper reference.


Normally I'd have you start a new thread for this; however, it's tangentially related and we can split the thread if needed :)
Aug 22 '13 #5
Rinis
13
You were definitely correct the problem is the reference to the value. str_foundPW is doing the lookup and returning the [EmpID] value. I'm not sure if that's what it's suppose to do though. I'm still getting the same error:2465 so it has to be the reference. Here's how it looks as of now:

Expand|Select|Wrap|Line Numbers
  1. Option Compare Database
  2.  
  3. Private Sub CmdClockIn_Click()
  4. Dim time As String
  5. Dim strSQL As String
  6. Dim str_foundPW As String
  7.  
  8. If IsNull(Me.UserNameSelect) Or Me.UserNameSelect = "" Then
  9.     MsgBox "You must select the name of a current Employee.", vbOKOnly, "Required Data"
  10.         Me.UserNameSelect.SetFocus
  11.     Exit Sub
  12. End If
  13.  
  14. If IsNull(Me.PasswordEntry) Or Me.PasswordEntry = "" Then
  15.     MsgBox "You must provide your password.", vbOKOnly, "Required Data"
  16.         Me.PasswordEntry.SetFocus
  17.     Exit Sub
  18. End If
  19.  
  20. 'omitting some stuff - you can omit the comments :)
  21. '
  22. strSQL = "[EmpID] ='" & Me.UserNameSelect & "'"
  23. '
  24. 'now you can insert a debug print here for troubleshooting
  25. ' - press <ctrl><g> to open the immediate window
  26. ' - you can now cut and paste this information for review!
  27. '
  28. Debug.Print "Your criteria = " & strSQL
  29. 'Your criteria = [EmpID] ='3'
  30. 'now use the string in your code:
  31. str_foundPW = DLookup("[EmpPassword]", "EmployeeInfo", strSQL)
  32. Debug.Print "The value of str_foundPW = " & str_foundPW
  33.     Me.[EmployeeInfo]!EmpPassword.Value = str_foundPW
  34. 'The value of str_foundPW = [EmpID] ='password'
  35.  
  36. 'Now theres an error coming through saying it cannot find the field I'm referring to '|1'
  37. 'error code for this is 2465
  38.  
  39. End Sub
My problem in this situation is I know nothing of these me statements. and I believe I made that particular statement myself so I'm not surprised its not the correct way of formatting it, just another thing i pulled off an example and tried to adapt to my needs.
Aug 22 '13 #6
Rinis
13
Update: Got this working now by getting rid of the me statement there i was using it completely wrong. i changed it to
Expand|Select|Wrap|Line Numbers
  1. Private Sub CmdClockIn_Click()
  2. Dim time As String
  3. Dim strSQL As String
  4. Dim str_foundPW As String
  5.  
  6. If IsNull(Me.UserNameSelect) Or Me.UserNameSelect = "" Then
  7.     MsgBox "You must select the name of a current Employee.", vbOKOnly, "Required Data"
  8.         Me.UserNameSelect.SetFocus
  9.     Exit Sub
  10. End If
  11.  
  12. If IsNull(Me.PasswordEntry) Or Me.PasswordEntry = "" Then
  13.     MsgBox "You must provide your password.", vbOKOnly, "Required Data"
  14.         Me.PasswordEntry.SetFocus
  15.     Exit Sub
  16. End If
  17.  
  18. 'omitting some stuff - you can omit the comments :)
  19. '
  20. strSQL = "[EmpID] ='" & Me.UserNameSelect & "'"
  21. '
  22. 'now you can insert a debug print here for troubleshooting
  23. ' - press <ctrl><g> to open the immediate window
  24. ' - you can now cut and paste this information for review!
  25. '
  26. Debug.Print "Your criteria = " & strSQL
  27. 'Your criteria = [EmpID] ='3'
  28. 'now use the string in your code:
  29. str_foundPW = DLookup("[EmpPassword]", "EmployeeInfo", strSQL)
  30. Debug.Print "The value of str_foundPW = " & str_foundPW
  31.  
  32. If Me.PasswordEntry = str_foundPW Then
  33. time = Now()
  34. Else
  35. MsgBox "incorrect Password.", vbOKOnly, "Required Data"
  36.     Exit Sub
  37. End If
  38.  
  39. End Sub
Now I need to figure out how to make VBA select the correct record/create a new one if needed. Should be relatively easy I'd think. I'm not completely done here but i definitely found my new favorite forum to ask for help in. Hopefully i can get this stuff down well enough to answer some questions eventually :)

Thank you so much ZMBD you will be hearing from me again for additional thanks. :D

also if you could post the basic syntax for VBA to create a new record or input data to a particular record and field that would be fantastic. If not no big deal seeing as how it would be something that would be better placed in a seperate thread.
Aug 22 '13 #7

Sign in to post your reply or Sign up for a free account.

Similar topics

4
by: MLH | last post by:
I have tried using DLookUp in this manner... If DLookUp("","tblClients","='2021234567'") Then MsgBox "Found it!" End If I am wondering if that is a misuse of the DLookUp command? Type...
2
by: ctyrrell | last post by:
I have read with interest the many discussions of the 3048 Error: Cannot open any more databases. I understand how the number of open Table ID's causes the problem. My question has to do with the...
8
by: Christine Henderson | last post by:
I have a problem using the above function in the following simplified circumstance: In the lookup table called "Klms Travelled" I have 3 fields, eg: Receiver Name Receiver Suburb ...
11
by: MLH | last post by:
DLookup("", "tblPreliminaryVINs", "=Forms!frmVINODO!SerialNum") is giving me a Type Mismatch error. That's confusing to me and I don't know how to circumvent it. The field in...
2
by: Don | last post by:
Can someone help me fix my DLookup problem. I'm far from proficiency with Access. I've been creating databases for several years for work with the help of many of you and trial and error. I have...
4
by: ilikebirds | last post by:
I was wondering what method would be more appropriate for the following scenario. I have a form that allows a user to enter 1001 into a text box. Once enter is hit, would it be better to use a...
9
by: | last post by:
In my database I have a 'control table' in which basic info is stored about the application, for instance the application's path and the name of the company that is using it. In all of the...
15
by: rleepac | last post by:
This is a little complicated but I'll do my best to explain. In my db I have a table called L_AgeCorrection which has the following fields: Age, Sex, Frequency, AgeValue This is a table used to...
8
by: JDaly | last post by:
This bombs on the StPL= line. I want it to look up a string value from a table with a criterion of matching another string. In immediate mode the form evaluates correctly with ? Form!! as TEST but...
4
by: James Grant | last post by:
Hi Everyone, I've created a database (MS Access 2003) to help monitor staffing at my work. I now need to transcribe data from one table (tblPositions) into another (tblTeams) based on a common...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.