Jump to Record (Conditional)

[ConanTheLibrarian]

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?

[WendellB]

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.

[ConanTheLibrarian]

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...

[patt]

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]

[johnhutchison]

Try some code like this, behind a button

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.

[ConanTheLibrarian]

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.