Page 1 of 3 123 LastLast
Results 1 to 15 of 36
  1. #1
    Beanie_d83 is offline Advanced Beginner
    Windows 8 Access 2013 64bit
    Join Date
    May 2016
    Posts
    73

    Help needed with DLookup

    Hi all,



    I could use a little help using DLookup function where it places a numeric result of 2 queries (though I think I can get the same result with just using 1) in an unbound text box on a continuous form.

    By placing

    Code:
    =DLookUp(" MaxOfPart_Number","qryIncrementPart_Number")
    in the control source for the text box I get the correct the result, but it is placing the same number in every record. What I am trying to achieve is an increment of 1 in each records text box.

    Any pointers?

  2. #2
    Minty is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    3,001
    On a continuous form any unbound control will repeat for every record.
    You would be better to move this into the underlying query for the form, and use a join to the query to get your result. This will be more efficient as well.
    DLookup Syntax and others http://access.mvps.org/access/general/gen0018.htm
    Please use the star below the post to say thanks if we have helped !
    ↓↓ It's down here ↓↓

  3. #3
    Beanie_d83 is offline Advanced Beginner
    Windows 8 Access 2013 64bit
    Join Date
    May 2016
    Posts
    73
    Hi Minty,

    At present I have 2 queries, but I may have misunderstood how to get the result I wanted and could just have the 1 query?!

    I have attached images of both queries below in the design view. Would I need to put the aforementioned DLookup code in the Part_Number field of qryCountOfComponent_Type?

    Click image for larger version. 

Name:	qryCountOfComponent_Type.PNG 
Views:	33 
Size:	26.5 KB 
ID:	33962Click image for larger version. 

Name:	qryIncrementPart_Number.PNG 
Views:	33 
Size:	20.8 KB 
ID:	33963

  4. #4
    Minty is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    3,001
    Can you take a step back and explain the purpose of this - is it to get the next number in a sequence that is there for each part ?
    Maybe a little demo data - what you have and what your desired output is ?

    I'm struggling to see the end result without some extra knowledge of the process.
    DLookup Syntax and others http://access.mvps.org/access/general/gen0018.htm
    Please use the star below the post to say thanks if we have helped !
    ↓↓ It's down here ↓↓

  5. #5
    Beanie_d83 is offline Advanced Beginner
    Windows 8 Access 2013 64bit
    Join Date
    May 2016
    Posts
    73
    I am wanting to produce a database to help keep track of the parts we create to manufacture our products.

    On frm_component there is a combo box where the users can select which type of component they wish to create. The continuous sub form then populates with information of all the parts in that category.

    The idea behind the query is when a component type is selected it finds the highest value in this group so that when the next record is created in the sub form, the part number increments when the Created By combo is used.

    I also need to ensure that none of the existing records can be edited in the sub form. This can only be done by users with administrator security privileges.

    Hopefully the screenshots below will help to make better sense of it all!


    Click image for larger version. 

Name:	Main Form.PNG 
Views:	30 
Size:	26.4 KB 
ID:	33965

    Click image for larger version. 

Name:	tbl_parts.PNG 
Views:	30 
Size:	52.3 KB 
ID:	33966

  6. #6
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Disclaimer - I have not absorbed the entire thread because:
    If you don't provide criteria in a DLookup, you get what's in the first record of that domain. So it matters not if the expression would be the same result in every iteration of an unbound control on a continuous form. The foregoing dictates that you will get the same result anyway. Then there is your use of the DMax function against said domain. Not understanding how you'd get any value other than the Max of a field in that domain, which is a single number. Perhaps I'm not understanding the whole picture and you need to just disregard the musings of senility.

  7. #7
    Beanie_d83 is offline Advanced Beginner
    Windows 8 Access 2013 64bit
    Join Date
    May 2016
    Posts
    73
    Perhaps I'm not understanding the whole picture and you need to just disregard the musings of senility.
    I can assure you it is I who does not understand!! I have only got this far mostly thanks to help of the good people on this forum!

  8. #8
    Minty is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    3,001
    Okay - I'm still not understanding the purpose of this.
    You have a unique ID for each part, why would you want to try and have another non unique number that isn't obviously relevant ? If its just to keep tabs on how many of them of that type you have you can always just count them, and if you delete any or make a mistake it's still accurate.
    DLookup Syntax and others http://access.mvps.org/access/general/gen0018.htm
    Please use the star below the post to say thanks if we have helped !
    ↓↓ It's down here ↓↓

  9. #9
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    OK, I looked deeper. While what I wrote you may be able to make use of another time, it doesn't really apply here. I think your main problem is that you can't bind a control so that it presents different data in every record, as well as have it perform a calculation. The solution that comes to mind is that you could hide this calculation in the form header (pointless to have it repeat for every record) and via code, insert the DMax + 1 (which is what I think you need) into the bound field. Not sure what event you want for this - perhaps the after update of the component combo; perhaps after the name selection IF the component has been selected.
    I also need to ensure that none of the existing records can be edited in the sub form.
    By anyone? This statement conflicts with
    This can only be done by users with administrator security privileges.
    You need to have user permission groups defined in a user table, and base these control decisions on their group membership. There are several ways to accomplish that, the simplest probably being to store the group value of a user in a hidden form and reference it when needed.

    You could always upload a copy of your db if you're really stuck. Sensitive info can be replaced with cartoon names; unnecessary forms/tables/reports can be removed from the copy. Just make sure you can open the required forms/queries if you trim tables/queries.

  10. #10
    Gicu's Avatar
    Gicu is offline VIP
    Windows 8 Access 2013
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Hi there,
    Sorry to jump in so late in the game. I see you already have a field in the tblParts table called Part_Number. Are you planning to save the unbound value in the textbox with the dlookup in that field for a new record (to increment the part number)? If yes you need to bind the textbox to the PArt_Number field and add code to the Current event of the form to check if new record and if yes make the control = (dmax("Part_Number","tbl_Parts","[Component_Type_ID_FK] = " & [Component_Type_ID])+1) (note the one in red is the name of the control on your form that is bound to the Component_Type_ID field.

    Cheers,
    Vlad

  11. #11
    Beanie_d83 is offline Advanced Beginner
    Windows 8 Access 2013 64bit
    Join Date
    May 2016
    Posts
    73
    You have a unique ID for each part, why would you want to try and have another non unique number that isn't obviously relevant ?
    It's because I needed to continue the numbering system already in place at my company. Effectively the current part numbering system is just a count of the parts, with the prefix defining the group. For example, SP5001, SP5002....etc, is the numbering system for one group and PR5001, PR5002....etc for another group, so on and so fourth.

  12. #12
    Gicu's Avatar
    Gicu is offline VIP
    Windows 8 Access 2013
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    But why then the screenshot shows 0 (text) for the part number field tbl_Part? Are you going to populate that?

  13. #13
    Beanie_d83 is offline Advanced Beginner
    Windows 8 Access 2013 64bit
    Join Date
    May 2016
    Posts
    73

    I also need to ensure that none of the existing records can be edited in the sub form.


    By anyone? This statement conflicts with
    This can only be done by users with administrator security privileges.


    @Micron,

    I also need to ensure that none of the existing records can be edited in the sub form



    By anyone? This statement conflicts with

    This can only be done by users with administrator security privileges.


    I'm imagining that I will be the administrator, perhaps one other user also. The idea is that all of the design engineers can enter parts they create via the sub form but any of the previously entered parts above cannot be changed without the administrator/s permission.

    I have already created a users table and a security level table, I just haven't researched on how to accomplish this stage yet! Learning Access again has been somewhat of a slow process for me!!

    @Gicu,

    But why then the screenshot shows 0 (text) for the part number field tbl_Part? Are you going to populate that?
    Yes, the idea is that when each part is entered, the number automatically increments from the last in that group of components. Eventually, all the parts that have been created in the old manual system will be entered in this database so the numbers can pick up from there.

  14. #14
    Gicu's Avatar
    Gicu is offline VIP
    Windows 8 Access 2013
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    So I think what I said in post #10 will work, you need to bind the textbox to the field in the tbl_Parts table and increment it only for the new records.

    Cheers,
    Vlad

  15. #15
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    how to accomplish this stage yet
    When a 'restricted' form opens, you retrieve the user's rights and based on that data, either open the form in Allow Additions mode, or Edit. You'll need to research the interactions that these settings have with each other, as some of them will over-ride other settings. As noted, the simplest may be to lookup and store the permissions value on a hidden form. Then there's the Environ variable. The most complex but most powerful (at least I think so) is a user defined object, such as dbUser, to which you can add any property you can think of. From it you could retrieve FName, LName, emlAddress, Level (as in user rights level), EmplNo, etc. Obviously these are my own property names that I've used in the past.

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

Similar Threads

  1. DLookup Help Needed
    By krakalackin in forum Programming
    Replies: 4
    Last Post: 11-07-2014, 04:07 PM
  2. Help needed : Library catalogue in access format is needed
    By dealers in forum Sample Databases
    Replies: 3
    Last Post: 01-16-2014, 02:03 PM
  3. DLookUP Help Needed
    By Kirsti in forum Programming
    Replies: 2
    Last Post: 11-06-2012, 01:19 PM
  4. *URGENT HELP NEEDED* DLookUp Formula
    By iProRyan in forum Forms
    Replies: 1
    Last Post: 03-28-2012, 11:55 AM
  5. Dlookup help needed
    By mkb_cma in forum Access
    Replies: 12
    Last Post: 11-28-2011, 11:37 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