Jump to Unused Record

[ConanTheLibrarian]

I actually have three separate issues I'm trying to resolve, so apologies, but I'm going to post three threads in case someone else can use the info I end up getting on them...

A bit of background: I'm working on a database to catalog CDs in my branch's collection. There are slots (numbered 1 - 4001) that will NOT change, of which roughly 1000 are not in use presently, so the need to expand is unlikely at best.

The trick is, because CDs are constantly being added and withdrawn, it's not as simple as "New Record" or "Jump to Last Record" when adding new entries. "Blank" disc numbers exist as placeholders throughout the range.

What I've been trying to figure out is a function (ideally triggered by a button on my form) which will search for the first Disc # (also my primary key) in which the Disc Title field is blank.

I've contemplated creating a query that listed only IsNull entries under Disc Title and having the function jump to the first entry in said query, but that struck me as unnecessarily complex.

Any and all input would be welcome.

 

[Beetle]

Shouldn't be too difficult. A bit of code behind a command button should do the trick.

Private Sub cmdYourButton_Click()

With Me.RecordsetClone
    .FindFirst Nz(![Disc Title], "") = ""
    If Not .NoMatch Then
        Me.Bookmark = .Bookmark
    Else
        MsgBox "No empty locations found"
    End If
End With

End Sub

 

[ConanTheLibrarian]

Sean, I may be asking a bit much here, but when I tried that code with modifications to point to the correct field in my database, it defaulted to the error window. Would it be possible to get a breakdown of what that code is doing on each line? An odd request, maybe, but I'd like to see if I can retrofit it to suit my purposes.

Also, is it likely that I'm running into code errors because the title field is not the primary key? I've encountered other scenarios not exactly like mine where issues arose because Access doesn't "order" information until it is told to. Would the Disc # need to be treated a certain way first before the code would return the desired result?

Oh, and so it is said loud and clear: THANK YOU! I could comb the internet for decades without getting quite what I need. This helps me narrow it down immensely! =)

 

[Beetle]

Here is the same code with comments;

Private Sub cmdYourButton_Click()

[COLOR="Red"]'Use a Clone of the current recordset to find a record.
'The Me keyword refers to the form this module is attached to.[/COLOR]
With Me.RecordsetClone
    [COLOR="Red"]'Find the first record where [Disc Title] is empty.
    'This uses the Nz function to convert any Null values
    'to an empty stirng ("") aka a zero length string (ZLS).[/COLOR]
    .FindFirst Nz(![Disc Title], "") = ""
   [COLOR="red"] 'If a match is found (which means it is NOT a .NoMatch)
    'then set the forms Bookmark (current record) equal
    'to the Clones Bookmark.[/COLOR]
    If Not .NoMatch Then
        Me.Bookmark = .Bookmark
    Else [COLOR="red"]'No record was found that matches the criteria.[/COLOR]
        MsgBox "No empty locations found"
    End If
End With

End Sub

However, what error message are you getting, and what line is highlighted when you debug?

 

[ConanTheLibrarian]

Sean, sorry for not being clear. It's not that the code returned an error in debug. When the button was activated, it always returned its "Else" error window. As I knew there were records that satisfied the conditions of the loop, I wasn't sure what to make of that.

For reference, I was able to track down a piece of code that did the job. I'm curious to know if it leaves anything out data-wise or is just another road to Rome as the saying goes.

Me.RecordsetClone.FindFirst "isNull(Title)"
Me.Bookmark = Me.RecordsetClone.Bookmark

In any event, Sean, thank you very much for the time and attention. I'm going to be spending some time dissecting the code so I understand WHY various things work the way they do. =)