Results 1 to 13 of 13
  1. #1
    Alocin is offline Novice
    Windows 7 64bit Access 2016
    Join Date
    Jun 2020
    Posts
    15

    Button to add new highest sub-number based on the related number

    Hi everyone, many of you helped me on my previous thread adding a new highest number. Thank you!

    Here is what I am trying to do:
    Table "Requests" displayed in "TblRequests" form.
    Two table fields are tracking the entries, both number types, "Request_Number" and "Request_Sub_Number".

    You all have helped me create a button that will increase the "Request_Number" to the next highest number. This works using a macro with the action "SetValue" see picture of the macro
    Click image for larger version. 

Name:	example of functioning button request number macro.PNG 
Views:	41 
Size:	2.6 KB 
ID:	42400


    I am now trying to add a button that will increase the "Request_Sub_Number" by 1 based on previous sub-number related to its "Request_Number".
    Example of the form:



    Click image for larger version. 

Name:	example of request and sub-request data entries.PNG 
Views:	40 
Size:	9.3 KB 
ID:	42401
    I was trying to use the same macro but that will add "1" to the highest value on the sub-number list.
    Any help would so much appreciate. Thank you so much in advance!

    Alocin!

  2. #2
    kd2017 is offline Well, I tried at least.
    Windows 10 Access 2016
    Join Date
    Jul 2017
    Posts
    1,142
    The DMax function has an optional third parameter you can use to filter with. Try something like this
    Code:
    =Nz(Dmax("Request_Sub_Number", "Requests", "Request_Number=" & [Request_Number]), -1) + 1
    https://www.techonthenet.com/access/...omain/dmax.php
    https://docs.microsoft.com/en-us/off...plication.dmax

    (edit)
    You may want to filter out the current requests record so you don't needlessly increment a value and create gaps in your sequence.
    Code:
    =Nz(Dmax("Request_Sub_Number", "Requests", "Request_Number=" & [Request_Number] & " AND Requests_ID_Field_Name_here <>" & Requests_ID_Field_Name_here), -1)  + 1
    (edit x2)
    You can use Nz() function to catch null values in the event there aren't any preceeding records found with the DMax function. I edited the code above to use it.
    https://www.techonthenet.com/access/...dvanced/nz.php

  3. #3
    Alocin is offline Novice
    Windows 7 64bit Access 2016
    Join Date
    Jun 2020
    Posts
    15
    Thank you Kd2017!
    It worked great. I just had to add a refresh action to the macro, otherwise, I could only see it after reopening the form.
    Click image for larger version. 

Name:	example of functioning button request sub-number macro.PNG 
Views:	37 
Size:	4.6 KB 
ID:	42404

    Also, do you think you could explain a little more what you mean by "You may want to filter out the current requests record so you don't needlessly increment a value and create gaps in your sequence"? Sorry I am not sure I understand what you mean. Thank you so much

    Alocin!

  4. #4
    kd2017 is offline Well, I tried at least.
    Windows 10 Access 2016
    Join Date
    Jul 2017
    Posts
    1,142
    I don't know how you're implementing your code so it may not matter. Once a user clicks the +1 button is there anything to prevent them from clicking it again? Can a click the button if a request_sub_number is already assigned?

    You may already realize this, but the reason I mentioned is for the following scenario. Let's say you have the following data:

    Code:
    ID | Request # | Request sub #
    ---+-----------+---------------
     1 | 370       | 0 
    ---+-----------+---------------
     2 | 370       | 1 
    ---+-----------+---------------
     3 | 370       | 2 
    ---+-----------+---------------
     4 | 370       | 3
    And let's say you're on record ID#4 and you press your +1 button again, what will happen? The DMax function will collect all the *existing* records with Request # 370, find the max request sub #, 3, and add 1 to *itself*. The request sub # sequence would become 0, 1, 2, 4 and skip 3.
    Let's say instead you were in ID#2 and you press the +1 button, what will happen? The DMax function would return 3 and add one turning the sequence into 0, 4, 2, 3.

    *IF* this is behavior you need to prevent than you're going to need to take some further steps. The easiest off the top of my head would be to just disable the +1 button anytime the current record moves to a record where the request sub # is not null. Moreover, I would probably find a way to automate the +1 all together so the user never has to click the button.

  5. #5
    Alocin is offline Novice
    Windows 7 64bit Access 2016
    Join Date
    Jun 2020
    Posts
    15
    Oh no! It is doing just what you said. Bummer!
    Unfortunately, if it is a new request, a new request number needs to generated. So I do need to have it created.
    I will try your suggested code and see how if it prevents it from happenining.
    Code:
    =Nz(Dmax("Request_Sub_Number", "Requests", "Request_Number=" & [Request_Number] & " AND Requests_ID_Field_Name_here <>" & Requests_ID_Field_Name_here), -1) + 1
    Thank you so much for the heads up.

    kindly,
    Alocin!

  6. #6
    Alocin is offline Novice
    Windows 7 64bit Access 2016
    Join Date
    Jun 2020
    Posts
    15
    Hi Kd2017, ok I guess what I need is a bit more convoluted than I thought.
    1) So, when I need a new request_number, I would need the code to generate a new request_number (example here would be 370) with a default "0" request_sub-number.
    2) Then if a new request_sub_number is needed to an existing request_number (example 370) then in a new entry line it should re-create the existing request_number (370) and generate a new request_sub_number which in this case would now be "1".

    Any help really appreciated.

    Thank you.
    Alocin

  7. #7
    kd2017 is offline Well, I tried at least.
    Windows 10 Access 2016
    Join Date
    Jul 2017
    Posts
    1,142
    If I'm fully understanding you correctly then the *psuedocode* you posted in #5 should have been about what you needed. What happened when you tried that?

    You can zip your database and post it here for review if you like.

  8. #8
    Alocin is offline Novice
    Windows 7 64bit Access 2016
    Join Date
    Jun 2020
    Posts
    15
    Hi kd2017 and all, I was wondering if you could help out on the creation of "next highest" new request number and/or sub-request number related to the associated request number. I am including a mock up db so you can see that what I have seems like it is working but as kd2017 explained on #4. I tried the suggested code on #5 as pointed out by kd2017
    Code:
    =Nz(Dmax("Request_Sub_Number", "Requests", "Request_Number=" & [Request_Number] & " AND Requests_ID_Field_Name_here <>" & Requests_ID_Field_Name_here), -1) + 1
    Unfoturnately, it still generates a wrong sub-request number if I am located on the wrong ID number. I am attaching my current db and hopefully someone can help me solve it. Very much appreciate any help. I cannot tell if db was attached correctly.

    Truly kind gesture in this group
    Alocin!
    Attached Files Attached Files

  9. #9
    kd2017 is offline Well, I tried at least.
    Windows 10 Access 2016
    Join Date
    Jul 2017
    Posts
    1,142
    Looking at what you've done so far I think you may want to rethink the table design.

    Looks like you need a separate table for Employees, Companies, Products, ActiveIngredients??, and finally Requests. What is a request exactly? What differentiates a sub-request from a request?

    Can you describe in plain english the purpose of this database?

  10. #10
    Alocin is offline Novice
    Windows 7 64bit Access 2016
    Join Date
    Jun 2020
    Posts
    15
    Yes kd2017, I can describe!

    1) What is a request? A new or an existing customer (CustomerA) orders from us (requests from us) a specific product, example 50 boxes of bacitracin (antibiotic cream with the active ingredient Bacitracin), we then contact the manufacturer and place the order. If this Request is a NEW order, a new request number is given, example "Request" number "410" and for any new orders the "Sub-request" number is always "0".

    2) What is a sub-request? If CustomerA re-orders the same product then the EXISTING request number "410" is used again and the sub-request number is increased by 1, "Sub-request" "1". See example on #4

    3) What is the purpose of this database? To keep track of all our customers' orders, new or re-orders. Due to the database size I couldn't upload the it with other tables such as customers info, manufacturers' info, vendors, etc.

    I really do appreciate your questions in trying to help me. You are very kind!

    Thank you in advance.
    Alocin

  11. #11
    Join Date
    Apr 2017
    Posts
    1,673
    I assume you have a table like tblRequests: RequestNumber, ...
    And you have a table like tblSubRequests: SubRequestID, RequestNumber, RequestSubnumber, ...

    When this is so, the form you are asking about must be requests subrequests form. Otherwise you have some problems with your database design!

    When the form is requests subrequests form, there is an easy way to get you want without having a separate subrequests table:
    In your form, for RequestSubnumber create a combobox control with RowSource like
    Code:
    "SELECT MAX(sr.RequestSubnumber) + 1 FROM tblSubrequests sr WHERE sr.RequestNumber = " & Me.txtRequestNumber
    Now, whenever you want to apply a number to new subrequest, simply select it from combo - you have a single number available there.
    NB! You must restrict editing of this control at least in form's AfterUpdate event (when RequestSubnumber Is Not Null, then user can't change it - otherwise there will be possibility the user can rise subnumber value for same row infinitely.

  12. #12
    Alocin is offline Novice
    Windows 7 64bit Access 2016
    Join Date
    Jun 2020
    Posts
    15
    Thanks for your reply.
    Correct! I do have tblRequests that has RequestNumber...
    No! Current db does not have a table for subrequests. Do you suggest I have a tbleSubRequests table?
    Also, are you saying that I should create a single form called "requests subrequests form"?

    Finally, in your code does "sr" stand for subrequest?

    Thank you very much.
    Alison!

  13. #13
    Join Date
    Apr 2017
    Posts
    1,673
    Quote Originally Posted by Alocin View Post
    No! Current db does not have a table for subrequests. Do you suggest I have a tbleSubRequests table?
    I suggested, that you can do without separate subrequests table!
    Also, are you saying that I should create a single form called "requests subrequests form"?
    Easiest way is, you have a single requests form, and in it a continuous subform where all subrequests of active request are displayed (subforms LinkChildFields and LinkMasterFields properties are essential to set this up). A bonus is, that any subrequest inserted into subform will be automatically linked to active request.
    Finally, in your code does "sr" stand for subrequest?
    This is table alias. It is your choice what it will be - you can have e.g. a, b and c as aliases for 3 different tables. This allows to read queries better - removing long file names which must be repeated for every field, and which are cluttering the SQL string.

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

Similar Threads

  1. Replies: 2
    Last Post: 08-05-2019, 01:24 PM
  2. Replies: 19
    Last Post: 05-16-2017, 07:34 AM
  3. Replies: 8
    Last Post: 02-08-2017, 07:58 PM
  4. Replies: 15
    Last Post: 12-22-2016, 12:16 PM
  5. Replies: 16
    Last Post: 06-20-2013, 09:25 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