Results 1 to 8 of 8
  1. #1
    Pawtang's Avatar
    Pawtang is offline Advanced Beginner
    Windows 10 Office 365
    Join Date
    Mar 2021
    Posts
    91

    Cool Best method for: Form PK Default Value = Increment PK from highest existing PK

    I am building a database for item return management. The primary key for the returns list is an RMA number, which effectively is just a unique ID for that return case. They are not automatically incremented, as the database currently accepts input for RMAs out of order as we change over from the old system. They just need to be unique.



    I'm looking for the best way to make the default value in this form be equal to the highest existing value for the RMA PK field in the RMA table plus one. This way, we can still enter RMAs as they are received out of order, but if we want to issue a new RMA from the database it will have a unique number.

    To further clarify the reason for out-of-order input: currently, RMAs aren't entered into the system until they are received back from customers. They are generated in order in an older, rigid database, but aren't entering the new system until they come back, which tends to be in a random order.

  2. #2
    Bob Fitz's Avatar
    Bob Fitz is offline Access Developer
    Windows 10 Access 2016
    Join Date
    May 2011
    Location
    Essex UK
    Posts
    3,530
    Perhaps you could use the DMax() function to return the highest value and add one to that value for the new value.
    I've used that before in a forms BeforeUpdate event.
    If this helped, please click the star at the bottom left of this posting and add to my reputation . Many thanks.
    Bob Fitzpatrick

  3. #3
    Pawtang's Avatar
    Pawtang is offline Advanced Beginner
    Windows 10 Office 365
    Join Date
    Mar 2021
    Posts
    91
    So far I've tried a couple methods including setting default value to "=AssignRMA()" Where that function is:

    Code:
    Public Function AssignRMA()
        AssignRMA = CurrentDb.OpenRecordset("qryMaxRMA").Fields("Max")
    End Function
    ^ This method only returns a #Name? error in that field.

    I also tried creating a query that successfully returns a single value equal to the Maximum existing RMA number plus one, and setting Default Value for field on form to =DLookUp("Max","qryMaxRMA").

    I will mention, as I am typing this, I set it back to =DLookUp("Max","qryMaxRMA") method so I could properly copy the expression into this thread and... it worked this time. Likely I didn't use quotations on the field the first time I tried.

  4. #4
    Bob Fitz's Avatar
    Bob Fitz is offline Access Developer
    Windows 10 Access 2016
    Join Date
    May 2011
    Location
    Essex UK
    Posts
    3,530
    "Max" is a "reserved" word in Access and should not be used as the name of a field or any other object.
    If this helped, please click the star at the bottom left of this posting and add to my reputation . Many thanks.
    Bob Fitzpatrick

  5. #5
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,861
    I'd concur with Bob and would use the DMax() function?
    I see no point creating a query for it, especially when you are not addressing it correctly?
    Please use # icon on toolbar when posting code snippets.
    Cross Posting: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?sear...bug+access+vba

  6. #6
    Pawtang's Avatar
    Pawtang is offline Advanced Beginner
    Windows 10 Office 365
    Join Date
    Mar 2021
    Posts
    91
    Bob, your method worked great, I think I had syntax issues first time I tried it. Thanks.

  7. #7
    Bob Fitz's Avatar
    Bob Fitz is offline Access Developer
    Windows 10 Access 2016
    Join Date
    May 2011
    Location
    Essex UK
    Posts
    3,530
    Quote Originally Posted by Pawtang View Post
    Bob, your method worked great, I think I had syntax issues first time I tried it. Thanks.
    Always glad to help if I can.
    BTW I hope you changed the name of that field called "Max"
    If this helped, please click the star at the bottom left of this posting and add to my reputation . Many thanks.
    Bob Fitzpatrick

  8. #8
    Pawtang's Avatar
    Pawtang is offline Advanced Beginner
    Windows 10 Office 365
    Join Date
    Mar 2021
    Posts
    91
    Quote Originally Posted by Bob Fitz View Post
    Always glad to help if I can.
    BTW I hope you changed the name of that field called "Max"
    Field no longer exists, as I deleted the query! It was a little stopgap solution.

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

Similar Threads

  1. Change/add argument's value of an existing method
    By Amirhossein14 in forum Programming
    Replies: 2
    Last Post: 01-17-2020, 03:06 PM
  2. Replies: 5
    Last Post: 08-22-2016, 08:08 AM
  3. Replies: 2
    Last Post: 04-27-2016, 03:34 AM
  4. Replies: 15
    Last Post: 05-20-2014, 02:10 PM
  5. Auto increment on existing number column
    By lcsgeek in forum Programming
    Replies: 1
    Last Post: 04-15-2013, 11:27 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