Results 1 to 6 of 6
  1. #1
    Join Date
    Jan 2016
    Posts
    4
    Provided Answers: 1

    Question Answered: Finding the next highest index number

    Greetings,

    I'm running Access 2013 on Win 7. I would categorize my skill level with Access as "Beginner". The last time I did any programming in Access was in 1997/1998. I've forgotten a lot since then and obviously Access itself is very different now.

    I'm looking for VBA code that will search for the next highest Primary Key number to use in a custom "Next Record" button on a form containing a subform.

    Image of table relationships below:

    Click image for larger version. 

Name:	Relationships.JPG 
Views:	3 
Size:	42.3 KB 
ID:	16744

    Image of query relationships below:

    Click image for larger version. 

Name:	Query.JPG 
Views:	1 
Size:	26.5 KB 
ID:	16745

    The only criteria for the query is "Is Null" in the DateClosed field of the IssueDescription table.

    My form is supposed to show only open issues and the associated actions in the subform.

    The problem I have is that if an issue has multiple actions associated with it, the issue appears multiple times (equal to the number of actions) in the query datasheet. Using the standard built in Next Record / Previous Record buttons results in displaying the same record multiple times.

    Image of datasheet below:

    Click image for larger version. 

Name:	Datasheet.JPG 
Views:	2 
Size:	23.2 KB 
ID:	16746

    I'm looking for some VBA code that will find the next highest / lowest Primary Key number to use as Next Record / Previous Record controls. I have some experience with VBA in Excel but it's a little different in Access. I've tried various pieces of code I found on the internet, most of which don't do exactly what I need and use the DMax / DMin functions. I've tried modifying them with no success.

    Thanks in advance for any and all assistance.

    ~ Phil

  2. Best Answer
    Posted by PKW57

    "
    Quote Originally Posted by ranman256 View Post
    iLargest = Dmax("[id]","table")

    iNext = Dmax("[id]","table","[id]<" & iLargest)
    Ranman,

    Thank you very much for your response. I tried

    Code:
    Private Sub NextRecord_Click()
    iLargest = DMax("IssueID", "OpenIssuesQuery")
    iNext = DMax("IssueID", "OpenIssuesQuery", "IssueID <" & iLargest)
    End Sub
    and although I don't get any error messages, it doesn't move to the next record. Have I got the syntax wrong? If so I would have expected an error message."


  3. #2
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    495
    Provided Answers: 24
    iLargest = Dmax("[id]","table")

    iNext = Dmax("[id]","table","[id]<" & iLargest)

  4. #3
    Join Date
    Jan 2016
    Posts
    4
    Provided Answers: 1
    Quote Originally Posted by ranman256 View Post
    iLargest = Dmax("[id]","table")

    iNext = Dmax("[id]","table","[id]<" & iLargest)
    Ranman,

    Thank you very much for your response. I tried

    Code:
    Private Sub NextRecord_Click()
    iLargest = DMax("IssueID", "OpenIssuesQuery")
    iNext = DMax("IssueID", "OpenIssuesQuery", "IssueID <" & iLargest)
    End Sub
    and although I don't get any error messages, it doesn't move to the next record. Have I got the syntax wrong? If so I would have expected an error message.

  5. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    so what happens if you put a watch / breakpoint on the code and then step through it?

    why is that code not working?

    what values do you have for
    iLargest?
    iNext?

    what do you do with iNext?
    I'd rather be riding on the Tiger 800 or the Norton

  6. #5
    Join Date
    Jan 2016
    Posts
    4
    Provided Answers: 1
    Quote Originally Posted by healdem View Post
    so what happens if you put a watch / breakpoint on the code and then step through it?

    why is that code not working?

    what values do you have for
    iLargest?
    iNext?

    what do you do with iNext?
    Healdem,

    I wish I was VBA literate enough to answer your questions intelligently but sadly, that's not the case. I have not set any values for either iLargest or iNext

    I don't think this code is running at all. When I set break points and try to step through, nothing happens and the Immediate Window is blank.

  7. #6
    Join Date
    Jan 2016
    Posts
    4
    Provided Answers: 1
    Quote Originally Posted by PKW57 View Post
    Healdem,

    I wish I was VBA literate enough to answer your questions intelligently but sadly, that's not the case. I have not set any values for either iLargest or iNext

    I don't think this code is running at all. When I set break points and try to step through, nothing happens and the Immediate Window is blank.
    All,

    Thank you for your time, patience, and willingness to help.

    I have figured out a different way to accomplish my goal. I used a subform query with the Unique Values property set to Yes.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •