Interesting puzzler

[SpareHead3]

Thank you both - I really appreciate for your efforts (and your patience with me).

I commented out the Private Sub Form_Load() section in the original database, and replaced all [NavMenu] = [AIN] lines with Me.NavMenu = Me.NavMenu.ItemData(AIN), and it worked- no rounding of the selected/highlighted Drop Menu record

So how does replacing that one line modify that function?

And, (just because I never asked anyone ever), what is the "Me." thing doing? I've used it in other sections, but it was mostly just copy/pasting coce I'd found from various web sources and since it worked I didn't think much of it.

I also went to the Editor Options and checked off "Require Variable Declaration" as you recommended.

 

[theDBguy]

Thank you both - I really appreciate for your efforts (and your patience with me).

So how does replacing

NavMenu = AIN

'or

[NavMenu] = [AIN]

with

Me.NavMenu = Me.NavMenu.ItemData(AIN)

cause it to not do the weird rounding thing?

And, (just because I never asked anyone ever), what does the "Me." part actually mean or do?

Hi. Did you try the last file I posted? If so, you'll see I didn't use that code. And as I also said in my last post, I don't think that code approach may not always work in some cases. For example, I deleted some records, and the code didn't work as expected anymore.

 

[SpareHead3]

That's the one thing I built into the database that makes the database gods' teeth itch- the means to keep the indexed AIN field from going out of sequence. The "Insert After" button (top left, next to the NavMenu ComboBox icon) does it... select a record where you want to insert a new record after and click the Insert After button... a new record is created with a new AIN... a kind of Bubble Sort is performed afterwards, incrementing all AIN values from the new record backwards through the records up to the record I selected to insert a record after... Now there's a gap between the current record and the next record, which means I can now re-write the AIN value of the new record to fill the gap.

That procedure was described in post #18 (I understand why a noob like me wouldn't generally be allowed to post links, but you'd think the forum would at least be able to recognize it's own domain, instead of suggesting a URL link to post #18 is a spam site).

There's a "Delete Record" button at the top right corner which does the exact opposite- after giving the record I want to delete an AIN value that's +1 more past the Last Record's AIN value, I then start subtracting 1 from each AIN value of every record from the gap I created to that "(LastRecord + 1)" which I can now delete with confidence that I haven't interrupted the AIN field's incremental sequence.

That being said, I see your code (removing the commented sections for space and privacy purposes)...

Private Sub cboAIN_AfterUpdate()
Me.Recordset.FindFirst "[AIN] = " & Me.cboAIN
End Sub

Private Sub Form_Current()
Me.NavMenu = Me.AIN
Me.cboAIN = Me.AIN
End Sub

So if I'm to guess what's happening here... you created cboAIN to pretty much do the same thing as NavMenu... Form_Current() functions immediately after loading a new record into the form regardless of what action or function loaded the record? Which means upon the loading a record, the AIN value of that record will get written to the NavMenu and cboAIN variables which govern those ComboBoxes.

I noticed that you used "Me.RecordSet.FindFirst" rather than "Me.RecordSetClone.FindFirst"... Should I be using RecordSet rather than RecordSetClone in other sections of my code? What's the difference?

 

[Gasman]

replaced all [NavMenu] = [AIN] lines with Me.NavMenu = Me.NavMenu.ItemData(AIN)

And you had to do that 27 times

The same thing will happen if you need to modify that again.
I would explore @theDBguy's solution as well, as it took me quite a while to find a solution that would work.
As I mentioned, I had no idea as to why it did that, corruption is a possibility though.

 

[Gasman]

Thanks for checking. Does that version of the code still work if after you get to a different record by selecting either the buttons on the left or an item from the dropdown, you would then hit the Next button at the top (a few times)?

Yes, it appears to. It was the buttons on the left that were the issue.
That is all I concentrated on. That anomaly, that on first open and first click, you got a rounded value in the combo box. The next click, it works as it should.

 

[SpareHead3]

I used your LocateRecord change too:

Private Sub AlphaTab23_Click()
    LocateRecord (Screen.ActiveControl.Caption)
End Sub
Private Sub AlphaTab24_Click()
    LocateRecord (Screen.ActiveControl.Caption)
End Sub
Private Sub AlphaTab25_Click()
    LocateRecord (Screen.ActiveControl.Caption)
End Sub
Private Sub AlphaTab26_Click()
    LocateRecord (Screen.ActiveControl.Caption)
End Sub
Private Sub AlphaTab27_Click()
    LocateRecord (Screen.ActiveControl.Caption)
End Sub

Private Sub LocateRecord(strCaption As String)
    DoCmd.RunCommand acCmdSaveRecord
    Dim rs As DAO.Recordset
    Set rs = Me.RecordsetClone
    rs.FindFirst "AAC = '" & strCaption & "'"
    Me.Bookmark = rs.Bookmark
    rs.Close
    Me.Disc_Title.SetFocus
    Me.NavMenu = Me.NavMenu.ItemData(AIN) 'used this for all other "NavMenu = AIN" references
End Sub

 

[Gasman]

Well, if I ever get the class working, I will let you know, but from experience at the moment, I would probably leave your DB as is.

Edit: Forget the class, that is not going to happen.

 

[Gasman]

What are you going to do when you get a new publisher?

        'Populate Publisher field based on Manufaturer's glyph
        If [PublishGlyph] = "04400" Then
            Publisher = "Funimation Entertainment / Crunchyroll, LLC"
        End If
        If [PublishGlyph] = "02727" Then
            Publisher = "Section 23 / ADV Films"
        End If

I would put these into a Publisher table and use a DlookUp().
Then when you get a new publisher, you just add a new record with their details.

 

[SpareHead3]

As it is, since I'm the only one that plays with this database and its code, I don't have any qualms about copying/pasting new If/End If lines to add a new Publisher.

But that is a good idea- I'm trying to adapt my Anime/Media database to a Mass Market Paperback database, and there are many more book publishers than there are home video publishers/producers (still a work in progress- not much of a "Runtime" on a Paperback is there, hehe):

Seeing as this is "access-programmers.co.uk" I'll shamelessly add that I do have a signed hardcover copy of Douglas Adams' "Mostly Harmless", "The fifth book in the increasingly inaccurately named Hitchhiker's Trilogy".

 

[Gasman]

I am the only one using my diabetes db, but I would still do something like that, (and I do).
In my case it is a new food, which generally is cropping up weekly.

Plus every time you copy and paste, there is an avenue for errors? I bet you have had a few in the past?, missing changing something, or even not know and amend the incorrect field/variable. The program will still compile (although your does not at present with Option explicit), so you should really fix all those errors.

 

[SpareHead3]

So in my main database I combined both your techniques:

Added

Private Sub Form_Current()
     Me.NavMenu = Me.NavMenu.ItemData(AIN)
End Sub

and

Private Sub AlphaTab1_Click()
    LocateRecord (Screen.ActiveControl.Caption)
End Sub
Private Sub AlphaTab2_Click()
    LocateRecord (Screen.ActiveControl.Caption)
End Sub

for all tabs, through...

Private Sub AlphaTab26_Click()
    LocateRecord (Screen.ActiveControl.Caption)
End Sub
Private Sub AlphaTab27_Click()
    LocateRecord (Screen.ActiveControl.Caption)
End Sub

Private Sub LocateRecord(strCaption As String)
    DoCmd.RunCommand acCmdSaveRecord
    Dim rs As DAO.Recordset
    Set rs = Me.RecordsetClone
    rs.FindFirst "AAC = '" & strCaption & "'"
    Me.Bookmark = rs.Bookmark
    rs.Close
    Me.Disc_Title.SetFocus
    Me.NavMenu = Me.NavMenu.ItemData(AIN)
End Sub

Also:

Me.NavMenu = Me.NavMenu.ItemData(AIN)  'replaces all instances of NavMenu = AIN and [NavMenu] = [AIN]

So far, it works just fine (ComboBox opens to the correct expected title).

Haven't set it as Option Explicit yet, but I looked up what it does- I might do that later

I also intend on setting an immutable index number for each record but still setting a sequential AIN number... but that's for the future.

 

[Gasman]

Yes, you are relying on that AIN number (with my code at least) as being sequential to the data.
BTW, if you compact and repair your DB, after taking a backup, it will got from 26MB to 5MB.