Page 1 of 2 12 LastLast
Results 1 to 15 of 22
  1. #1
    Join Date
    Feb 2020
    Posts
    18

    DLookup loading form problem

    Hello all,



    I am very knew to Access (I have about a solid week on it) and was asked to make a database to track inventory. I eventually will not be with the company (temporary internship) and so I want to make this database bulletproof enough so that when I'm gone that they won't need to call me for anything. Here's the problem I'm having:

    I am using DLookup to return a variety of things (I have pictures attached). The first time or two that I use it it works fine but when I want it to return a string (based on the user id matching the security id) it says that it's expecting an end of expression. Now, depending on what project someone is working on depends on what is shown in the inventory so I use a "WhereCondition" and I want it to return something like, "Project1 = True" so that all of the inventory that Project1 is authorized to see will appear in the database.

    I have looked for hours but to no avail. I'm sure it is something simple and I'm just missing something. Thank you so much in advance for your help!

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    First names are usually text , so you must put quotes around text strings.
    docmd.openform "formname",,,"[uFirstName]='" & me.cboLoginID & "'"

    if your field is numeric, you dont need the quotes:
    docmd.openform "formname",,,"[uFirstName]=" & me.cboLoginID

  3. #3
    Join Date
    Feb 2020
    Posts
    18
    DoCmd.OpenForm "InventoryFinder", WhereCondition:="("DLookup("[uFirstName]", "tblUsers", "[uUserID]=" & me.cboLoginID)") = True"

    Like so? Because it still throws the error.

    Also, what makes the difference between Me![cboLoginID] and me.cboLoginID?

  4. #4
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Assuming your form's record source has a field called uFirstName then you would use:

    DoCmd.OpenForm "InventoryFinder", WhereCondition:="[uFirstName]= '" & DLookup("uFirstName", "tblUsers", "[uUserID]=" & me.cboLogin) & "'"

    Cheers,
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  5. #5
    Join Date
    Feb 2020
    Posts
    18
    Attachment 40949
    So this is the error that it gives me now. The "Me.cboLogin" is what the user types in for the login information correct? How do I correct this error?

  6. #6
    Join Date
    Feb 2020
    Posts
    18
    Update: I changed the line of code to this

    DoCmd.OpenForm "InventoryFinder", WhereCondition:="[uFirstName]= '" & DLookup("uFirstName", "tblUsers", "[uUserID]=" & Me![cboLogin]) & "'"

    It didn't give me an error message anymore but it also didn't open up the form that I wanted it to look up. What am I doing wrong?

  7. #7
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    The message means that either a method or an object can't be found as referenced. You have cboLoginID beforehand, yet cboLogin where the message is raised. So are we to assume you have different controls with almost the same name, or you have misspelled one of them? I'd suspect the latter because of your use of Me!SomecontrolName instead of Me.SomeControlName.

    Do you have Option Explicit at the top of every module (or at least in this one)? If no, does your code even compile?
    Why oh why do you have On Error Resume next like that? You are telling Access to overlook errors, which is OK for certain cases, but certainly not for the whole procedure!

    If you could post code within code tags instead of pictures it would help a lot.

    Do not use ! when referring to controls on forms or reports.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  8. #8
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Probably my fault, I see now that I misnamed the control in my first post, can you try:

    DoCmd.OpenForm "InventoryFinder", WhereCondition:="[uFirstName]= '" & DLookup("uFirstName", "tblUsers", "[uUserID]=" & me.cboLoginID) & "'"

    Sorry about that!

    Cheers,
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  9. #9
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Vlad, I was referring to post 5, which I thought was the OP's code (picture). I didn't realize that was yours!
    Still, I hope there's some agreement on the other comments I made.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  10. #10
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    For sure Micron, I agree with what you sugested, all goòd points, I think as I was back-spacing to clear the = True at the end of the original dLookup in post #3 I went too far and cleared the ID part....
    Cheers,

  11. #11
    Join Date
    Feb 2020
    Posts
    18
    Oh my heavens, it's close. And I should have paid more attention to the line of code you wrote so that's also my bad for not double-checking.

    DoCmd.OpenForm "InventoryFinder", WhereCondition:="[uFirstName]= '" & DLookup("uFirstName", "tblUsers", "[uUserID]=" & Me.cboLoginID) & "'" = True

    So this is what I have and the form opens but for whatever reason it doesn't include the parts that I have check-marked for it. I included a picture of the table that I have where I check-mark the project for each given part for reference. I thought it was a parenthesis problem but that didn't solve anything. Any ideas? The screenshot doesn't include the whole table cause it's too long just fyi.

    Also, VBA automatically capitalizes the m of me.cboLoginID. I don't think that matters but tell me if I'm wrong. Cause I tried to change it and it wouldn't let me.


    Attachment 40990

  12. #12
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Hi there,

    I think I'm starting to understand what you have and I'm afraid it is not the best way to do it. Looks like you have in the inventory table a series of Yes/No fields for each first names i your users tables and you "assign" to them by checking that corresponding field. So if you get a new user you will need to add a new field and potentially delete the old one. I would instead add a short text field names "AssignedTo" in which you enter (easiest by using a combo-box on a form) the first name or user id of the corresponding user. For this scenario the code will be:

    DoCmd.OpenForm "InventoryFinder", WhereCondition:="[AssignedTo]= '" & DLookup("uFirstName", "tblUsers", "[uUserID]=" & Me.cboLoginID) & "'"

    For your current setup try this:
    DoCmd.OpenForm "InventoryFinder", WhereCondition:="[" & DLookup("uFirstName", "tblUsers", "[uUserID]=" & Me.cboLoginID) & "]" = True

    Cheers,
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  13. #13
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    @cassandragubler,
    Maybe you would explain what you mean by "make a database to track inventory". No jargon, just what you expect to accomplish with the dB.

    Did you design the dB using pencil/paper or whiteboard before jumping into Access?

    "Old Programmer's Rule" is this: If you can't do it on paper, then you can't do it in Access. Until you can draw out your tables AND rules for how they change for each function you want to perform, you are going nowhere fast.

    I would suggest posting your dB, even without records or just a few records. It would be much easier to test code/designs.

  14. #14
    Join Date
    Feb 2020
    Posts
    18
    Mr. Cucinschi,

    I am making this database for a group of engineers and this is how they've told me they have wanted it. I actually had a much "simpler" VBA code portion before but if they added a project then they would have to go directly into the VBA and change some things and I didn't want them to have to do that. They add and delete projects every few months so I don't think it's a big problem. They do know how to go and create a new field and checkmark all the parts and what not.

    As for the line of code, it still didn't bring anything up. It would open up the form but none of the parts that were related to that specific project. All keep looking at it but do you have any other ideas?

    I do have a question, why do you put a "&" before the DLookup? I actually didn't think it was necessary so I tried deleting it and it threw an error so it must be important. I just don't understand why.

    Thank you so much.

  15. #15
    Join Date
    Feb 2020
    Posts
    18
    Mr. Steve,

    I didn't not sketch anything out before creating this database. The first time I actually ever even started working with Access was this project. As far as what I want to be able to accomplish with this database is the following: The company I work for has several projects going on simultaneously. Some of the inventory that they need is at a different location but as of right now, it is only the memory of the engineers that can help them. If they can't remember, then they can't ship the part out. The purpose of the database is have a record of all the inventory at that site so we know where it is. Different projects have different levels of clearance so they should only be able to see certain inventory pieces. These projects come and go every few months so I didn't want to have to make a different table for every project. I have one table containing all the inventory (not right now cause I couldn't take anything classified off the site so it's just junk information right now) and then depending on what project you are working on, upon logging in, a form will appear with all the inventory that you are allowed to see. This last line of code for the login is the last thing that I have and then the database will be ready to be used.

    If you have any further questions then I can answer them. I appreciate the help. But I actually don't know how to upload the file. All I see is a sport for a url link.

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

Similar Threads

  1. Loading A Form
    By Robert2150 in forum Access
    Replies: 5
    Last Post: 06-24-2015, 01:38 PM
  2. Replies: 3
    Last Post: 07-11-2014, 08:13 AM
  3. loading empty form
    By Suzie2012 in forum Forms
    Replies: 5
    Last Post: 10-10-2012, 07:03 PM
  4. Possible If statement for loading of a form
    By computer_man20037 in forum Programming
    Replies: 2
    Last Post: 05-16-2012, 09:53 AM
  5. Form title dlookup problem
    By zoinnk in forum Forms
    Replies: 3
    Last Post: 10-03-2011, 08:40 AM

Tags for this Thread

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