Results 1 to 12 of 12
  1. #1
    Evad111 is offline Novice
    Windows 10 Office 365
    Join Date
    Dec 2021
    Posts
    6

    DLookup gives me a #Name? Error after closing and re-opening the DB file.

    Hello,

    I am very new to Access but have battled my way through many function challenges creating my own database. Kind of an emotional roller coaster of successes and failures.

    The trouble I am having is with a Form textbox that uses DLookup to find the current product quantity on hand for the selected product chosen on the form.
    After building it, and working my way through the DLookup function control source expression, it worked fine and had no issues until I closed the DB and reopened it later.
    Now the form opens with the value #Name? in it and does not work. After hours of trying to figure out what I did wrong I stumbled on the fact that if I simply put the form in Design View and add an unbound text box to the form and switch back to Form View it works as designed and desired. However, when I close the whole DB and reopen it the #Name? problem exists again so I put the form in Design View and delete the text box I created earlier then go back to Form View it work as desired.

    I attached a screen video of the work around I describe above for example.
    Attachment 46816
    Below is the Control Source expression I am using on the field that the #Name? error shows up in.

    =DLookUp("[Qty In Stock]","[Products_T]","[GG Product No]= " & [Forms]![MFG_Product_F]![GG_Product_No])

    Just to note, I am using Microsoft 365 version of Access. I have opened and closed this DB on my work Laptop that has Access 2013 installed and this problem does not exist in that version.
    Attachment 46816
    I can send the entire DB or if more information is needed just let me know.

    Thanks in advance,


    Dave

  2. #2
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Would help if you could zip and upload the db, please make sure no sensitive data is included.
    Try requerying the unbound control with the dLookup in the AfterUpdate event of the combo box.
    And maybe try this updated version to deal with empty combos:

    =DLookUp("[Qty In Stock]","[Products_T]","[GG Product No]= " & Nz([Forms]![MFG_Product_F]![GG_Product_No]))
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  3. #3
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    You shouldn't be looking up such stored values anyway, so that might be where you ought to step back to.

    http://allenbrowne.com/AppInventory.html
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  4. #4
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    is MFG_Product_F the name of the form that you are opening/closing?

    If not then perhaps that form is not open. If it is the same form then all you should need is

    =nz(DLookUp("[Qty In Stock]","[Products_T]","[GG Product No]= " & Nz([GG_Product_No],0)),0)

    and I presume GG_Product_No is a numeric field and not a text field since you say it works

  5. #5
    Evad111 is offline Novice
    Windows 10 Office 365
    Join Date
    Dec 2021
    Posts
    6
    Thank you all for our responses.


    I have zipped up the DB file but it does not contain the picture files within it. I store the pictures in separate folders. The Products_T & the Parts_T are where the path is located in the Image field respectively. There is not any sensitive information in this DB.

    Attachment 46828


    There is another DLookup field on this Form as well using the same expression, but it looks up an image file path in the same Products_T instead of the Quantity on Hand field the other one does. I don’t see any error information on it but it does not work as well when the #Name? error exists in the Textbox so I am assuming it is affected in the same way.


    To give you some perspective on what this form does and where I am at with the whole DB, below are some fact points.

    · I am starting up a business with a line of electronic products I have designed and manufacture. Some of the products I manufacture are made up of many parts I need to keep in stock. Once a product is manufactured, I need to adjust the number products I have on hand by how many were built and also deplete the part quantities used to build that number of product.


    The MFG_Products_F is the form in question. The MFG Update button on the Home Menu will open it for you.
    o The Main Form fields are set up for Data Entry only and the data entered in those is stored in the MFG_Product_T. I use a Query for that subform so I can Desend the sort order in that view.
    o The MFG_Product_Parts_Q SubF shows all the parts used for the product that is selected in the Combo. The Qty field is the number of each part needed for that product and the Qty In Stock shows how many of that part is on hand.
    o The Adjust Inventories button launches an On Click Event Macro that uses the MFG Update queries for both the Parts and the Products.
    o The Macro also contains a refresh which does update MFG_Product_Q_SubF data but does not set the Main Form to be ready for a new entry so I put in a New Record Button. (The records Refresh button on the ribbon refreshes both the SubF data and sets the form for a new record but the Macro refresh doesn’t.)

    The following tables exist but are not used yet as well as any associated queries and forms for those tables.
    o Customer_T
    o Expenses_T
    o Orders_T
    o Order_Details_T


    Just to reiterate, I did not have this problem when I opened this DB in Access 2013, it worked just fine.

  6. #6
    Evad111 is offline Novice
    Windows 10 Office 365
    Join Date
    Dec 2021
    Posts
    6
    Quote Originally Posted by Micron View Post
    You shouldn't be looking up such stored values anyway, so that might be where you ought to step back to.

    http://allenbrowne.com/AppInventory.html

    Micron,
    Thank you for the best practice insight. I will definitely keep this in mind moving forward as I do understand and agree with the concept being shown. I love working on this DB but still have a day job and working to get this business off the ground so unfortionately it cuts into my DB development time.

  7. #7
    Evad111 is offline Novice
    Windows 10 Office 365
    Join Date
    Dec 2021
    Posts
    6
    Gicu,

    I tried the updated version you provided and it did not resolve the #Name? error but I believe it might resolve the occasional #Error I would see in the textbox sometimes when the Combobox was blank. However, I could not generate the #Error in a non-updated DLookup version to know for sure. I just know I have seen that error when the form as in a working state and it went away as soon as I selected something from the dropdown list.

    "Try requerying the unbound control with the dLookup in the AfterUpdate event of the combo box."
    I wasn't sure what I needed to do for sure with this instruction.

    Dave

  8. #8
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    My 2 cents. First is to remove the control source property from this and any other combo that's used to filter data. As is, you're not just altering subform records based on the selection, you're altering the value associated with the related data - e.g. if a date is related to that combo value and you choose a different value, that date now becomes related to the new value as you're changing it. Search combos should not be bound. Perhaps your data has been compromised as a result.

    Then I'd remove the calculation from the textbox to the after update event of the combo and remove the embedded macro. In that event, I'd also deal with the possibility that some calculations return Null, so wrap the DLookup in the Nz function as in
    Me.Text43 = Nz(DLookup("[Qty In Stock]", "[Products_T]", "[GG Product No]= " & Me.GG_Product_No), 0)
    You don't need to use Forms! reference when the field is on the same form.

    Above all, I'd try to have the calculation performed by the form query, except for the fact that your table is the form recordsource. I almost never do this because there's little flexibility, or control over what happens to the table record. If you can do that, you won't need to DLookup at all. I find that sometimes the calc takes a long time and the textbox disappears. Perhaps there are other embedded macros or events that play into that.

    Lastly (about the form or its properties), you should adopt a naming practice. Text43 is a lousy name for a control. I realize it's the way Access works, but you don't have to settle for it.
    http://access.mvps.org/access/general/gen0012.htm
    https://www.access-programmers.co.uk...d.php?t=225837

    What not to use in names
    - http://allenbrowne.com/AppIssueBadWord.html

    Perhaps most important, have Option Explicit at the top of every module. In fact, turn it on going forward (vb editor, Tools, Options & find "require variable declaration")
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  9. #9
    Evad111 is offline Novice
    Windows 10 Office 365
    Join Date
    Dec 2021
    Posts
    6
    Micron,

    I have focused on the "MFG Update" form following your advise and everything is working very well with this form now. I attached the DB below if you wouldn't mind taking a look at the "MFG Update" form to see if I am on the right track now. I moved the DLookup function of the combo box to be a VBA code function but left the image field function using a DLookup data control source. I was having trouble getting it to work as a DLookup VBA code function. Need to research that more.

    Attachment 46842
    Attachment 46842

    I have been building this database and learning both at the same time and not always clear on a lot of the things I was doing but finding ways to accomplish them to keep me going. Obviously, not using the best practices. I will go back through all of the other forms I have built already and re-design them to perform more efficiently and safe guarding the data better.

    I believe this exercise has really helped fill in some knowledge blanks for me.

    Thanks,
    Dave

  10. #10
    ssanfu is offline Master of Nothing
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    The worst thing I see in the dB is that there are SPACES in field names. Never use spaces in field names. If you must separate the words in the field name, use the underscore. Instead of "Qty Need To Order" I would use Qty_Need_To_Order" or better yet, "QtyNeedToOrder".
    For "GG Product No", I would use "GG_ProductNum".

    I always use a suffix ("_PK") for the Primary Key fields and ("_FK") for the foreign key fields. All FK fields in a table are immediately below the PK field.

    Also you should not use Look-UP FIELDS at the table level. (It appears that you only have 2).


    You should look into splitting your dB - a BE (just the tables) and a FE (all other objects).


    In the table "Parts_T", do the prices change over time? Do you need to keep a history of the changes? I would have the prices in their own table. You are limited to those 6 quantities. What happens if you need a price for a quantity of 200? Right now you would need to modify tables, queries, forms, reports and maybe the VBA code.


    And what's up with a field names like "GG Product No"?? Always makes me wonder if there is a field "GG Product Yes". Letters are cheap - one more letter and you could use Num ("GG Product Num").



    OK, I'm done ranting........


    Good luck with your project.........

  11. #11
    Evad111 is offline Novice
    Windows 10 Office 365
    Join Date
    Dec 2021
    Posts
    6
    ssanfu,

    Thank you for your input.

    """The worst thing I see in the dB is that there are SPACES in field names. Never use spaces in field names. If you must separate the words in the field name, use the underscore. Instead of "Qty Need To Order" I would use Qty_Need_To_Order" or better yet, "QtyNeedToOrder".
    For "GG Product No", I would use "GG_ProductNum"."""


    I do understand that spaces in field names can cause problems with migrating the database into another platform. When I first started this project I thought that only applied to Table, Query & Form names and I was far enough in when I realized it also applied to field names too. Not being very savvy with what I was doing, things would break when I tried to correct them. I am able to dog paddle a little better now and I think I can correct the name issues as I move forward. Besides, I will be upgrading a lot of the forms and queries I have and that would be a good time to address that issue.

    """I always use a suffix ("_PK") for the Primary Key fields and ("_FK") for the foreign key fields. All FK fields in a table are immediately below the PK field."""

    I do like this _PK and _FK suffix idea. At first I thought those had to be the same exact field names for the relationships to work but I do understand now that as long as you make the relationships between them the database will know. It definitely distinguishes the function of that field relative to the table.

    """Also you should not use Look-UP FIELDS at the table level. (It appears that you only have 2)."""


    This was an issue I addressed in the "MFG_Product_F" per Micron's recommendations. You can see the difference with that form between the 2 database files I have uploaded to this thread. I plan to rework other forms that are still doing that using more of the practices I changed in the latest version of this form as an example.

    """You should look into splitting your dB - a BE (just the tables) and a FE (all other objects)."""

    I haven't thought about doing this. I am not up to speed on what all the benefits and challenges of this are but I can think of a few possibilities off the top of my head. Ex: Maybe sensitive data, Customer Info type, would be better kept in a separate DB that the main one calls from. I will go down this rabbit hole and research it more.

    """In the table "Parts_T", do the prices change over time? Do you need to keep a history of the changes? I would have the prices in their own table. You are limited to those 6 quantities. What happens if you need a price for a quantity of 200? Right now you would need to modify tables, queries, forms, reports and maybe the VBA code."""


    For heavens sake YES they change. I use a lot of little very inexpensive parts. Many parts cost a fraction of one cent each. Updating cost prices is not a pleasant task but I try to do it for each suppliers parts within a quarterly basis. My main use of the parts cost prices is to establish the total cost of the parts needed to manufacture a product and maintain a minimum profit margin for the retail price setting. As I grow and I am able to purchase in larger quantities I will have more flexibility with retail pricing so I came up with these price cost tiers. If a part cost has breaks at 100, 250 & 1000 I use the 100 cost for 100 and the 250 cost for the 500 quantity. It does serve the purpose for what it is intended but I agree with your reasoning for keeping it separated.

    """And what's up with a field names like "GG Product No"?? Always makes me wonder if there is a field "GG Product Yes". Letters are cheap - one more letter and you could use Num ("GG Product Num")."""

    LOL To each their own right? In this world of acronyms we live in today it drives me crazy too. I work for an ISP (Internet Service Provider) company in a department titled ISP (Inside Plant).
    I definitely could rename the "GG Product No" and "GG Part No" fields to something like GGProductID and GGPartID using the extensions _PK & _FK relative to where applicable. The "Product No" is unique in the DB to the Gate Goalie products but the term "Part No" needs to be differentiated form GG Part No, Manufacturer Part No & Supplier Part No. I need to make some logical naming changes for sure and will work on a plan for that while removing the spaces.

    Thank you Steve and everyone else for taking the time to review and leave feedback. It is greatly appreciated.

    Dave

  12. #12
    ssanfu is offline Master of Nothing
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Dave,
    Quote Originally Posted by Evad111 View Post
    """You should look into splitting your dB - a BE (just the tables) and a FE (all other objects)."""

    I haven't thought about doing this. I am not up to speed on what all the benefits and challenges of this are but I can think of a few possibilities off the top of my head. Ex: Maybe sensitive data, Customer Info type, would be better kept in a separate DB that the main one calls from. I will go down this rabbit hole and research it more.
    The main benefit with a split dB (FE and BE) is when the FE dB gets corrupted, you can replace the FE and be up and running without losing the data (that is in the BE). You can be using the FE and modifying a copy of the FE. When you are happy with the changes, you just re-link the New FE to the BE and don't have to worry about copying/moving the data.

    Of course, you are still doing back ups of the FE and BE.
    You should ALWAYS follow the 3 rules of computing......



    Rule #1: Back up.
    .
    .
    .
    .
    .
    .
    .
    .
    Rule #2:Back up.
    .
    .
    .
    .
    .
    .
    .
    .
    And Rule #3:Back up!!!




    There are lots of sites that explain split dBs - here is one by FMS Inc.-> Microsoft Access Split Database

    This one is about Primary Key Tips

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

Similar Threads

  1. Error message when opening file
    By Alan H in forum Access
    Replies: 1
    Last Post: 04-13-2016, 10:36 AM
  2. Error While Opening .mdb file
    By ravininave in forum Access
    Replies: 8
    Last Post: 03-15-2015, 01:36 AM
  3. Error on opening file
    By Sheba in forum Forms
    Replies: 5
    Last Post: 09-05-2014, 12:42 PM
  4. Replies: 7
    Last Post: 03-08-2013, 02:13 AM
  5. Problem opening file, Error 3197
    By mmad57 in forum Security
    Replies: 11
    Last Post: 01-24-2013, 08:06 AM

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