Results 1 to 13 of 13
  1. #1
    QuietPenguin is offline Novice
    Windows 10 Access 2016
    Join Date
    May 2019
    Posts
    12

    Can you use DLookup function in the 'Default Value' field?

    I'm trying to have a flexible default value for certain fields in my table, can DLookup be used for this purpose? I am using Access 2016 and whenever I do I get the error 'Unknown function 'DLookup' in validation expression of default value on 'TABLE.FIELD'.

  2. #2
    moke123's Avatar
    moke123 is online now Me.Dirty=True
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2012
    Location
    Ma.
    Posts
    1,643
    Yes you can.

    Perhaps you can show us your code and where/how you are using it.

  3. #3
    QuietPenguin is offline Novice
    Windows 10 Access 2016
    Join Date
    May 2019
    Posts
    12
    I've currently created a table to hold my default values ('DefaultAuctionSettings') and then here is the code I'm trying to use. I've tried different iterations (names are all so long as I was original using smaller ones like 'default' and figured they may be words already used by Access) and keep getting the same error.

    Click image for larger version. 

Name:	screenshot2.JPG 
Views:	26 
Size:	18.9 KB 
ID:	42656
    Click image for larger version. 

Name:	screenshot1.JPG 
Views:	27 
Size:	29.8 KB 
ID:	42657

  4. #4
    moke123's Avatar
    moke123 is online now Me.Dirty=True
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2012
    Location
    Ma.
    Posts
    1,643
    I dont know if you can use it in a table, probably not.

    You can use it on your form however.

  5. #5
    CarlettoFed is offline Competent Performer
    Windows 7 64bit Access 2013 32bit
    Join Date
    Dec 2019
    Posts
    255
    Of course you can use it but you must omit the "=" (equal) sign and the "," (comma) must be replaced by the ";" (semicolon).

  6. #6
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    DLookUp("Importance","Calendar","CalID=1")
    You have successfully done this?
    AFAIK, a calculated table field cannot use DLookup. If you know differently, you might be on to something.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  7. #7
    moke123's Avatar
    moke123 is online now Me.Dirty=True
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2012
    Location
    Ma.
    Posts
    1,643
    Quote Originally Posted by Micron View Post
    You have successfully done this?
    AFAIK, a calculated table field cannot use DLookup. If you know differently, you might be on to something.
    I tried it also and got the same error as the OP.

  8. #8
    QuietPenguin is offline Novice
    Windows 10 Access 2016
    Join Date
    May 2019
    Posts
    12
    Quote Originally Posted by CarlettoFed View Post
    Of course you can use it but you must omit the "=" (equal) sign and the "," (comma) must be replaced by the ";" (semicolon).
    I tried your suggestion but received an error:

    "The expression you entered contains invalid syntax. You omitted an operand or operator, you entered an invalid character or comma, or you entered text without surrounding it in quotation marks."

    Were you able to use DLookup successfully in this manner? Or is there another similar expression I can use?

  9. #9
    moke123's Avatar
    moke123 is online now Me.Dirty=True
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2012
    Location
    Ma.
    Posts
    1,643
    I dont believe you can use it to set the default value in a table. You can use it to set the default value of the control in your form. So do it in your form.

  10. #10
    QuietPenguin is offline Novice
    Windows 10 Access 2016
    Join Date
    May 2019
    Posts
    12
    Quote Originally Posted by moke123 View Post
    I dont believe you can use it to set the default value in a table. You can use it to set the default value of the control in your form. So do it in your form.
    Oh! That should be a sufficient workaround for me. Do you have an example of that please? Thank you for your help!

  11. #11
    moke123's Avatar
    moke123 is online now Me.Dirty=True
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2012
    Location
    Ma.
    Posts
    1,643
    See if this gives you ideas
    ForQP.zip

  12. #12
    QuietPenguin is offline Novice
    Windows 10 Access 2016
    Join Date
    May 2019
    Posts
    12
    I was able to adapt that to suit my needs. Thank you so much!

  13. #13
    moke123's Avatar
    moke123 is online now Me.Dirty=True
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2012
    Location
    Ma.
    Posts
    1,643
    Your welcome. Good luck with your project.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 5
    Last Post: 03-11-2016, 01:03 PM
  2. Setting default with an IIF Function
    By cptdave1996 in forum Access
    Replies: 14
    Last Post: 12-14-2015, 01:57 PM
  3. Replies: 2
    Last Post: 04-09-2013, 02:37 AM
  4. dlookup as default value
    By ShostyFan in forum Forms
    Replies: 7
    Last Post: 01-28-2013, 01:18 AM
  5. Default value a DLookup?
    By Lockrin in forum Access
    Replies: 1
    Last Post: 07-01-2010, 08:03 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