Results 1 to 6 of 6
  1. #1
    New Lounger
    Join Date
    Mar 2012
    Location
    Pennsylvania, USA
    Posts
    5
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Cool Jump to Record (Conditional)

    This is my first post. Here's hoping someone here can shed some light and thanks in advance for taking time out to read this and chime in!

    I'm a beginner to intermediate study with Access (mainly trying to apply old knowledge to Access 2007) and I'm trying to build a database to track CDs in our branch's collection.

    The primary key is the disc number (1 - 4001), which is constant and disabled in the entry form to prevent a user from mucking about with it.

    The form and database are built, but there are two pieces of functionality I need in order for it to be "complete" as an application.

    I'll just mention the first now so this can be easily found by someone else with a similar issue.

    When a new CD is received, we look for an unassigned disc number and go from there. I'd like to create a button which will jump to the first disc number that contains no information (I had thought of an "IF" statement where it searched for the first Disc # where Title is null, but I couldn't quite figure out the syntax of it).

    Any thoughts?

  2. #2
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,624
    Thanks
    3
    Thanked 60 Times in 60 Posts
    It isn't really necessary to resort to VBA to do what you want. A group-by query using First on the disk number will give you the lowest empty record if you put the criteria of Is Null on the appropriate information, and you can base your input form on that data set, although it probably won't be updateable. Give it a go and see what success you have. Others my jump in and suggest other alternatives. And welcome to the Windows Secrets Lounge.
    Wendell

  3. The Following User Says Thank You to WendellB For This Useful Post:

    ConanTheLibrarian (2012-03-22)

  4. #3
    New Lounger
    Join Date
    Mar 2012
    Location
    Pennsylvania, USA
    Posts
    5
    Thanks
    3
    Thanked 0 Times in 0 Posts
    Thanks for the welcome and the input, Wendell.

    I may be complicating this thing, but I would need the input form to have both the old records and the capacity for adding new ones (I use the term "add" loosely as the intention is to never actually "add" a new number, just populate the record). The db is an upgrade on an Excel spreadsheet the branch had been using which was never well maintained.

    So far, the intention was to add a button to the form that would just jump to the lowest disc number that was unpopulated. Like I said though, the coding side of it burnt my brain a little...

  5. #4
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts
    What Wendell suggested if i am correct is for a query along the lines of:

    SELECT Min([Disc Number]) as DiscNo
    FROM Table
    WHERE Title Is Null
    GROUP BY [Disc Number]

  6. #5
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts
    Try some code like this, behind a button

    Code:
    Me.RecordsetClone.FindFirst "isNull(Title)"
        Me.Bookmark = Me.RecordsetClone.Bookmark
    This assumes the current form has access to al the records.
    First, of course, depends on the order they are in. But I don't suppose it matters much which one is found, as long as you find one.
    Last edited by johnhutchison; 2012-03-24 at 05:40. Reason: correct small error
    Regards
    John



  7. The Following User Says Thank You to johnhutchison For This Useful Post:

    ConanTheLibrarian (2012-03-27)

  8. #6
    New Lounger
    Join Date
    Mar 2012
    Location
    Pennsylvania, USA
    Posts
    5
    Thanks
    3
    Thanked 0 Times in 0 Posts
    Thanks for the clarification, Patt. Unfortunately, the query approach doesn't do the form much good as I'm hoping to create an app for people who would go cross-eyed when confronted by an "uncovered" Access db. That's why I wanted an integrated function in the form.

    Speaking of which, John, you just made my day. The code was short, elegant, and did exactly what I needed it to! Thanks a million. Now, if you'll excuse me, I need to dig around so I understand exactly WHY it works. ;-)

    Oh, odd aside here: I was expecting some anomalies once that control (code is almost exactly what John posted) was used. I filled in some blank fields to see how the code would respond. Whether I am ahead of or behind the lowest blank entry, even with other blank entries between it and the "current," it always finds the lowest number. I've read that Access doesn't "sort" data unless it is told to and given specific parameters for doing so. Because my disc numbers are the primary key, is that being circumvented?

    Ultimately, I'm just trying to figure out if there's some way a user could "break" the function that I'm not picking up on.

    This ties into another question, but I'll leave it for a new post so people searching by topic can find it.

Posting Permissions

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