Page 1 of 2 12 LastLast
Results 1 to 15 of 18
  1. #1
    dniezby is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Apr 2013
    Posts
    183

    WTF? DLookup not working...

    I can't for the life of me figure out why my simple DLookup isn't working.



    I have a table with literally 1 field in it called - Settings. This will be where my local user makes all of their, well, settings. One of them is their local time zone. So for now, I only have the one field.

    In the settings table, the one field looks up the time offset and the common name but only stores the offset.
    Click image for larger version. 

Name:	Screenshot (2).png 
Views:	26 
Size:	97.9 KB 
ID:	28007

    I can't for the life of me figure out why it's not returning the value of that field. It should return (in this case) -6

    Here is the code:
    Code:
    Private Sub Form_Load()
         PLocalTime = DLookup("MyTimeZone","Settings")
    End Sub
    I've even tried referencing a column since the Settings table uses a lookup to get the data into that table. I've tried [] ...

    What even more strange is sometime when I F5 the debug, it pops an error asking for a Macro? WTF? There is no Macro?

    So baffled by something that should be so simple.
    Last edited by dniezby; 03-26-2017 at 08:46 AM. Reason: Fixed typo with sample code to reflect actual code I'm having problem with.

  2. #2
    andy49's Avatar
    andy49 is offline VIP
    Windows 10 Access 2007
    Join Date
    Nov 2016
    Location
    London
    Posts
    1,051
    Assuming mylocaltime is a field name in the table settings, this would pull a random value when the form loads. Is that what you want?


    Sent from my iPhone using Tapatalk

  3. #3
    dniezby is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Apr 2013
    Posts
    183
    Quote Originally Posted by andy49 View Post
    Assuming mylocaltime is a field name in the table settings, this would pull a random value when the form loads. Is that what you want?


    Sent from my iPhone using Tapatalk
    Oops, I'm sorry, That's a typo in the post.

    I'm fixing the post now.

    It should say,

    Code:
    Private Sub Form_Load()
         PLocalTime = DLookup("MyTimeZone","Settings")
    End Sub

  4. #4
    andy49's Avatar
    andy49 is offline VIP
    Windows 10 Access 2007
    Join Date
    Nov 2016
    Location
    London
    Posts
    1,051
    So settings table has a field called mytimezone.


    Sent from my iPhone using Tapatalk

  5. #5
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716

  6. #6
    dniezby is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Apr 2013
    Posts
    183
    Quote Originally Posted by andy49 View Post
    So settings table has a field called mytimezone.


    Sent from my iPhone using Tapatalk
    Yes

    Does the screencap not come through on your TapTalk? BTW, love TapTalk for when I'm mobile. Great app.

  7. #7
    dniezby is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Apr 2013
    Posts
    183

    WTF? DLookup not working...

    Quote Originally Posted by orange View Post
    The info on Dlookup here may be useful.

    Already did this. That's how I got the code that I did use. That's why I'm trying to figure out why it's not working.
    Last edited by dniezby; 03-26-2017 at 12:32 PM.

  8. #8
    andy49's Avatar
    andy49 is offline VIP
    Windows 10 Access 2007
    Join Date
    Nov 2016
    Location
    London
    Posts
    1,051
    You haven't entered any criteria in the dlookup though?


    Sent from my iPhone using Tapatalk

  9. #9
    dniezby is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Apr 2013
    Posts
    183
    Quote Originally Posted by andy49 View Post
    You haven't entered any criteria in the dlookup though?


    Sent from my iPhone using Tapatalk
    I did though. The field has -6 selected. I just posted the open combo so it can show how it displayed to the user.



    Sent from my iPhone using Tapatalk

  10. #10
    dniezby is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Apr 2013
    Posts
    183
    I think the problem might be the way I'm storing my data in the table. I have to get better at table design.


    Sent from my iPhone using Tapatalk

  11. #11
    andy49's Avatar
    andy49 is offline VIP
    Windows 10 Access 2007
    Join Date
    Nov 2016
    Location
    London
    Posts
    1,051
    Is the mytimezone field a lookup field. If so can we check the settings in design view?


    Sent from my iPhone using Tapatalk

  12. #12
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Perhaps it relates to this
    Settings table uses a lookup to get the data into that table
    ??


    Also, from your first post you have spaces in your table names which can lead to other issues.

  13. #13
    dniezby is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Apr 2013
    Posts
    183
    Quote Originally Posted by andy49 View Post
    Is the mytimezone field a lookup field. If so can we check the settings in design view?


    Sent from my iPhone using Tapatalk
    It is a look up field. I just created a regular lookup from the complete time zone table.

    I'll have to post a cap of the design view when I get back to my machine.


    Sent from my iPhone using Tapatalk

  14. #14
    Micron is offline Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    You haven't entered any criteria in the dlookup though?
    I did though.
    I see no criteria in this expression: PLocalTime = DLookup("MyTimeZone","Settings")
    Without criteria, you get the field value from the first record in the table, so you are probably correct about the table design. I see no field that you can filter on, such as UserID, to get the time shift value according to the user.

    I think many here would agree with the notion that you should dispense with lookup fields in tables. Here's why http://access.mvps.org/access/lookupfields.htm
    Last edited by Micron; 03-26-2017 at 12:56 PM. Reason: added info
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  15. #15
    dniezby is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Apr 2013
    Posts
    183
    Quote Originally Posted by orange View Post
    Perhaps it relates to this
    ??


    Also, from your first post you have spaces in your table names which can lead to other issues.
    Yeah, learning that now. Also need to fix that in field names. If I change a fieldname, will it change its references automatically?


    Sent from my iPhone using Tapatalk

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. DLookup not working
    By Lou_Reed in forum Access
    Replies: 29
    Last Post: 03-13-2017, 01:25 PM
  2. DLookup not working,HELP!!
    By riocobre in forum Access
    Replies: 2
    Last Post: 03-12-2017, 11:36 AM
  3. Dlookup not working
    By shaunacol in forum Forms
    Replies: 8
    Last Post: 06-12-2015, 09:28 AM
  4. Three criteria in Dlookup not working?
    By dcdimon in forum Programming
    Replies: 5
    Last Post: 07-03-2014, 07:04 AM
  5. DLookup isn't working
    By cec in forum Access
    Replies: 7
    Last Post: 12-28-2012, 12:21 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums