Interesting puzzler

[SpareHead3]

Hi folks. I'm at a bit of a loss as to the reason why this is happening. I can circumvent it but the symptom is so oddly specific I can't believe it wasn't incorporated into the Access or Visual Basic with a specific purpose in mind.

This is also a long one so I'm splitting up my explanation into multiple posts.

Background

I use Access to document/catalog my Media collections (DVD/BR/4K and Mass Market Paperback). my largest collection is my Anime collection, with over 2100 titles spanning 2549 DVDs and 1733 Blu Ray discs.

When I started, I had a few hundred titles. To navigate the list, I used a ComboBox ([Combo51]) and a Logitech MX series mouse (which has a free-spinning scroll-wheel ability). One good spin and I could scroll from record 1 to record 800-1000 in less than 2 seconds. But now with over 2000 titles, scrolling from the first record to the last letters of the alphabet is a bit... tedious.

So I wanted to create a tabbed selection method similar to the old metal tabbed index address books. I wanted dynamic tabs- Tabs that would change based on the selected record. The easiest way to do this was to use transparent buttons on top of a dynamic ImageBox. An [ImageTab] field was added to the database for the appropriate filename to be associated with each record, and the ImageBox's Control Source was set to "=[CurrentProject].[Path] & "\Data\Index\" & [IndexTab]'.

A note about organization- I don't use strict alphabetization to organize my collection- Ex. The titles Brave Witches and World Witches Take Off! are sequel spinoffs within the Strike Witches franchise, but I keep them together in the list. When I first created the database, I used a field I've named [AIN] (Arbitrary Index Number); The number is arbitrary because it will change as titles are added and the displaced records are incremented by one to make room. Ex. Yesterday, Strike Witches - The Complete First Season had an [AIN] number of 1773. I added Hero Tales Part 1 and Part 2 to the list after the [Disc Title] "Hellsing Ultimate - Volumes IX-X" ([AIN] = 956) and so all titles after them had their [AIN] values increment by 2, which means the [AIN] number for "Strike Witches - The Complete First Season" is now 1775.

I just call it "Loose Alphabeticalization".

Because of this, there will be some titles where the first letter of the title won't coincide with where it sits "alphabetically"- The aforementioned Strike Witches titles begin with the letter S, so they belong "alphabetically" with other titles that begin with the letter S... but as Brave Witches and World Witches Take Off! are part of the Strike Witches series as a whole, I want them to be in the list where the Strike Witches titles are. To make sure the appropriate [ImageTab] is associated with those records, I created a new field similar to the [AIN] field call [AAC] or Arbitrary Alphabetical Character. Using this, I could just enter the appropriate character (# for punctuation and numbers, A-Z for everything else) and the [IndexTab] field could automatically be populated with a simple line [IndexTab] = "Tabs_" & [AAC] & ".png".

So, selecting a record for display in the form using the ComboBox is a fairly simple code:

Private Sub Combo51_AfterUpdate()
    Me.RecordsetClone.FindFirst "[AIN] = " & Me![Combo51]
    Me.Bookmark = Me.RecordsetClone.Bookmark
End Sub

To create the tabbed selection interface, there needs to be 27 individual transparent buttons each coded to a particular character/letter, and 27 images of tabs evenly spaced for the buttons to sit on top of- The code for the buttons is similar to the Combo51 code:

Private Sub AlphaTab1_Click()
    Me.RecordsetClone.FindFirst "[AAC] = '#'"
    Me.Bookmark = Me.RecordsetClone.Bookmark
End Sub

... and to update the [IndexTab] field, I make it so 1) the [AAC] field will only accept either the "#" character or the letters A-Z or a-z, then converts lowercase letters to uppercase... then generate the filename to populate the [ImageTab] field:

Private Sub AC_LostFocus()
    If [AAC] = LCase([AAC]) Then
        [AAC] = UCase([AAC])
    End If
    IndexTab = "Tabs_" & [AAC] & ".png"
End Sub

One of the features I thought was really cool when navigating the database via the ComboBox is that If I don't reset (Requery?) it, I can re-open/.Dropdown the menu and continue scrolling up or down from the record I previously selected... Very convenient if I'm 1500 records down the list and I only need to scroll up or down 50 or so records.

Well, I want that same convenience in the ComboBox when I click a tab. The whole idea of creating the tab interface is to be able to quickly jump directly to a specific letter, then continue scrolling from that point to the exact record I'm looking for or the exact location I want to insert a new record. I figure that should be easy enough to implement since, as I said, I don't clear the [Combo51] variable, so I should be able to set the variable within the button's code:

Private Sub AlphaTab12_Click()
    Me.RecordsetClone.FindFirst "[AAC] = 'K'"
    Me.Bookmark = Me.RecordsetClone.Bookmark
    [Combo51] = [AIN]
End Sub

And, after copying/pasting the the above code for each button... this actually works. I click the K tab/button, and the first record where the [AAC] field = 'K' gets displayed; [IndexTab] = "tabs_K.png", and the appropriate image the letter "K" highlighted appropriately appears along the left side of the form:

When I click the ComboBox icon, it opens to show the same record displayed in the form (allowing me to continue my search by scrolling up or down to find the exact record I need):

I do have to manually added letters to the [AAC] field and the appropriate filenames to the [ImageTab] field for all existing records, but that's about 20 minutes of copy/pasting data directly into the DVD and Blu Rays table; New records get their fields populated as expected.

So I'm done, right? We're done? Good. I'm done. Close the program and wait for a new title to come from Amazon or eBay.

(Or so I thought).

 

[SpareHead3]

The Puzzle.

As I said above, I purchased "Hero Tales Part 1” and “Hero Tales Part 2" (note the "Preordered" and "Arriving" buttons at the bottom of the form). I fire up Access and open the database... I click the H and see [AIN] = 926 and [Disc Title] = "Haibane Renmei - New Feathers":

I need to scroll down from here, so I click the ComboBox arrow and...

Okay that was unexpected- Why did the ComboBox display record #900 rather than #926? I close the ComboBox, click the "H" tab again, and re-open the ComboBox... and now it's displaying the correct record:

I chalk it up being to a minor glitch... I scroll down and insert two blank records (Between "Hellsing Ultimate - Volume IX - X" and "High School of the Dead"), adding "Hero Tales: Seven Stars Will Shatter The Empire" Parts 1 and 2 as [AIN] = 957 and 958, then close the database and Access and go back to watching cat videos on Youtube.

Later this afternoon Amazon will be delivering the Limited Edition version of "My Hero Academia - Season 6, Part 2". Obviously, this will be added to the database as a new record right after "Part 1", so I launch Access and open the database. I click the M tab...

[AIN] = 1152. Alphabetically, I'd probably be closer to the "My Hero Academia" titles/seasons if I clicked the "N" tab and scrolled backwards, but no matter... I Click the ComboBox drop arrow and:

Lolwut?

This is not right. I close Access and relaunch it. This time I'll click the "N" tab intending to scroll backwards, but...

Wait- [AIN] = 1000 still? I don't even bother closing the ComboBox- I click the "M" tab (causing the ComboBox to "Lose Focus" so it closes automatically) and then click the ComboBox to open it... and now it shows record #1152. I click the "N" tab again and open the ComboBox... now it shows record #1285.

I close and re-open the database... This time I click the R tab and the form displays [AIN] = 1445. I open the ComboBox and it displays [AIN] = 1000 again. I close and re-open the database again and try the S tab... [AIN] = 1585. This time when I open the ComboBox... [AIN] = #2000!?!

I tested a number of other tab/ComboBox pairs, closing and re-opening the database each time and this is what I found:

In Summary, After opening the database and selecting a record with a tab and writing/storing the [AIN] value to the [Combo51] variable, If opened/clicked the ComboBox named [Combo51] will have a record with an [AIN] value selected that's been rounded to the most (not least) significant digit rather than that which has been stored in the [Combo51] variable.

That's the puzzle- and I don't understand it.

It's like the whoever developed Microsoft Access decided to teach sloppy VB programmers a lesson by tossing in this annoying, undocumented quirk, like a deranged high school teacher 3 months out from retiring saying "If you don't do X (like, declare or define a variable properly), I'm gonna do this weird rounding thing the first time you use that variable, and you crayon-smoking psychopaths won't know why until you either pay attention in my class or I get my first state pension check."

 

[SpareHead3]

The Solution?

So bit of trial and error over a few sleepless nights led to an interesting discovery- Upon launching Access and opening the database, if I merely click the ComboBox to open it, then click it again (or click off of it for it to "Lose Focus") to close it, I can now click any tab then re-open the ComboBox and it will display the current (correct) record. I may not know the cause of the problem, but I think I've discovered how to circumvent it.

Now the question is how to open/close the ComboBox using VB upon opening the database... Well, since this is getting long (getting long? srsly?) so I'll just post what I came up with:

Private Sub Form_Load()
     Me.Painting = False
     With Me!Combo51
          .SetFocus
          .Dropdown
     End With
     With Me!Combo51
          .ListWidth = .ListWidth
     End With
     Me.Painting = True
End Sub

So simply, this 1) stops the Form from displaying animation (so it won't actually show the ComboBox opening and closing), 2) opens the ComboBox with the .Dropdown command, then 3) forces it to close by setting the .ListWidth to equal itself (method found at stackoverflow.com and nolongerset.com) then finally 4) resumes animation so future clicks of the ComboBox will show it opening/closing.

This works. When I open the database, I can now click any tab, and when I open the ComboBox, the selected record is the record that is highlighted at the top of the drop menu, and from here I can scroll up or down to whichever record I want to access.

So yea- I have questions... Is this really the solution (opening and closing the ComboBox), or is this just compensating for something I didn't program correctly? Why does it default to rounding the value stored in the [Combo51] variable if the ComboBox isn't opened at least once, rather than doing what it's expected to do (as it does after opening and closing the ComboBox once), and for what reason is it rounding to the most significant digit?

Thank you for putting up with my behavior,

Spare Head 3
My teacher said I would be no good at poetry thanks to my dyslexia. So far I've made two flower pots, a coffee cup and an ashtray.

 

[Gasman]

Start giving your controls meaningful names.

Combo51 is not going to mean anything to you or anyone else 6 months down the road, when you want to add new features or change the process.
If it was programmed correctly, you should go to your starting record for the letter.

You could compact, zip and upload the DB in a state where we can see the issue, and work from there.

 

[theDBguy]

Hi. Welcome to AWF!

In all your posts, and I admit I skipped reading some parts, I didn't see the rowsource of your combobox. Can you post that and any code you're using to update it after clicking on your buttons? Thanks.

 

[The_Doc_Man]

Layout-wise, that form looks nice and surely has lots of useful data and functionality - but your navigation method seems shaky. And the uncertainty in which a particular record is displayed in the combo reflects that uncertainty.

It is hard to recognize whether you understand an important point: The recordset of the form (Me.RecordSource) and the recordset that feeds the combo box (combo.RowSource) are two different recordsets, even if they point to the same table. It is possible to navigate the main form without navigating the combo box. In the absence of code in the the Form_Current event to adjust the combo's position, it is no surprise that they sometimes seem desynchronized. For you to open and close the combo suggests that a combo.Refresh might occur, because the screen doesn't always repaint if you don't do it manually OR do it through the actions that follow naturally from navigation.

That expository lump you wrote didn't seem to fully address the AIN and its uses. Your "mutable" index AIN seems like it is a relative record number, but it is not a primary key candidate (because of its mutability). This will make it much harder to keep the combo and main form aligned.

Access is a RELATIONAL database - but one of the foundations of such databases is the ability to exploit firm i.e. immutable relationships. It SEEMS from your discussion that AIN is one of your anchors... but that anchor is dragging in loose sand if AIN is mutable. Do you have formal relational links that somehow depend on this AIN value?

Ideally, you have an ABSOLUTE record number that is a potential primary key. If you have ANY relationships, they would be based on that permanent and unchanging primary key. You mentioned a query or some other facility that runs through the records when you insert "blank slots" to keep things alphabetic or near alphabetic. This is the road to nightmares as your database grows. If you want to keep records alphabetic based on some specialized indicator, index that field and build a query that does an ORDER BY of that field. Don't directly display the table. Display the sorted query. You are working too hard on behind-the-scenes mechanisms and not hard enough on a robust design that works WITH you rather than AGAINST (or IN SPITE OF) you.

 

[SpareHead3]

@Gasman - Zipping it up and including it here as an attachment might be a little problematic with over 2100 .png images of each DVD or Blu Ray disc/box cover and 2200 .png bar codes (file size of "Anime_List.zip" = 1.67Gb). I'll test how badly it errors out if I don't include them as soon as I can.

Also, upon your advice, I've renamed various items (Combo51, Combo162, Command104, Command 365, etc.) to names that are a bit more relevant to their function... [Combo51] is now called [NavMenu].

@theDBguy - According to the Property Sheet:

and the VB code has been modified (from the word salad above) to:

Private Sub NavMenu_AfterUpdate()
    Me.RecordsetClone.FindFirst "[AIN] = " & Me![NavMenu]
    Me.Bookmark = Me.RecordsetClone.Bookmark
End Sub

@The_Doc_Man - There is indeed something I didn't mention, and it's because I figured if I did the High Priests of the Database Gods would be gathering at my door with pitchforks and torches ready to have me drawn and quartered.

I decided to start documenting my Anime database by filling in information in an OpenOffice spreadsheet (I know OpenOffice has it's own database application but I didn't know anything about databases so I just made columns and entered info). When I decided "Oh- I think including images of the box covers might be cool", trying to include them in a spreadsheet... well, I suppose there were ways to do it, but I couldn't think of any that were good enough (for me).

By this point I had about 400 titles in the spreadsheet. I started at a new company and they provided us with the means to purchase Office 2016 Pro Plus so instead of using OpenOffice Base I went with Access 2016. I converted my "Anime List.ods" spreadsheet into an .xlsx file, then imported it into Access... The first column was the column I was using to count the titles I owned starting with #1 and since it had all unique numbers it became my indexed field.

Keep in mind that until this point I've never designed, maintained or modified any database before in my entire life. I bought an "Access 2013 for Dummies" because I couldn't afford the Access 2016 version, and just kept plugging away at what I thought was the best way to solve my problems as they appeared. Ex. like adding bar code images to the form thanks to modifying the HTML code shown on their web site (barcode.tec-it.com) for use in VB

In any case, you are correct- I am not maintaining an absolute index number for each title.

By the time I actually knew someone with database/SQL experience that I could actually ask questions directly, I had already spent multiple years updating the database with more features and titles- to the point where I feel that altering it to adhere to "industry standards" might alter it's entire design... as well as force me to change my entire workflow or understanding of what I want my results to be.

In the end, it's just a list- The reason I created it was because years ago when my younger brother was in college his CD collection was stolen- he had about 400 of them, but since he wrote their titles and artists he was able to submit them to my parents' homeowner's insurance and he as able to receive significant compensation ($10 per CD, for a total of $4,000... in 1990. in 2025 dollars, that's $9,817.75. I see Limited Editions (which I own) on eBay going for 10x what I bought them for (hence the reason for making sure I document their bar codes).

Yes, I know futzing with the indexed field is not proper database etiquette... but for an uneducated heathen who just wanted to document my collection in case (heaven forbid) I need a comprehensive list for insurance purposes, I don't think I did that bad for my first try.

 

[CJ_London]

Hope they don’t nick your computer

Without seeing the relevant code and properties of the relevant controls, difficult to suggest what the solution might be.

 

[Gasman]

I have not read much of your last post,but consider naming controls after after their type. That way pretty much anyone else will recognise what they are.

MS Access Naming Conventions

_________________________________________________________ Naming Conventions. This document is aimed at the user who is unfamiliar with any naming convention. It is based upon personal experience and the interaction I have had with others. It is not a hard and fast rule. Why use a...

www.access-programmers.co.uk

 

[Gasman]

@Gasman - Zipping it up and including it here as an attachment might be a little problematic with over 2100 .png images of each DVD or Blu Ray disc/box cover and 2200 .png bar codes (file size of "Anime_List.zip" = 1.67Gb). I'll test how badly it errors out if I don't include them as soon as I can.

Also, upon your advice, I've renamed various items (Combo51, Combo162, Command104, Command 365, etc.) to names that are a bit more relevant to their function... [Combo51] is now called [NavMenu].

@theDBguy - According to the Property Sheet:
View attachment 119934
and the VB code has been modified (from the word salad above) to:

Private Sub NavMenu_AfterUpdate()
    Me.RecordsetClone.FindFirst "[AIN] = " & Me![NavMenu]
    Me.Bookmark = Me.RecordsetClone.Bookmark
End Sub

@The_Doc_Man - There is indeed something I didn't mention, and it's because I figured if I did the High Priests of the Database Gods would be gathering at my door with pitchforks and torches ready to have me drawn and quartered.

I decided to start documenting my Anime database by filling in information in an OpenOffice spreadsheet (I know OpenOffice has it's own database application but I didn't know anything about databases so I just made columns and entered info). When I decided "Oh- I think including images of the box covers might be cool", trying to include them in a spreadsheet... well, I suppose there were ways to do it, but I couldn't think of any that were good enough (for me).

By this point I had about 400 titles in the spreadsheet. I started at a new company and they provided us with the means to purchase Office 2016 Pro Plus so instead of using OpenOffice Base I went with Access 2016. I converted my "Anime List.ods" spreadsheet into an .xlsx file, then imported it into Access... The first column was the column I was using to count the titles I owned starting with #1 and since it had all unique numbers it became my indexed field.

Keep in mind that until this point I've never designed, maintained or modified any database before in my entire life. I bought an "Access 2013 for Dummies" because I couldn't afford the Access 2016 version, and just kept plugging away at what I thought was the best way to solve my problems as they appeared. Ex. like adding bar code images to the form thanks to modifying the HTML code shown on their web site (barcode.tec-it.com) for use in VB

In any case, you are correct- I am not maintaining an absolute index number for each title.

By the time I actually knew someone with database/SQL experience that I could actually ask questions directly, I had already spent multiple years updating the database with more features and titles- to the point where I feel that altering it to adhere to "industry standards" might alter it's entire design... as well as force me to change my entire workflow or understanding of what I want my results to be.

In the end, it's just a list- The reason I created it was because years ago when my younger brother was in college his CD collection was stolen- he had about 400 of them, but since he wrote their titles and artists he was able to submit them to my parents' homeowner's insurance and he as able to receive significant compensation ($10 per CD, for a total of $4,000... in 1990. in 2025 dollars, that's $9,817.75. I see Limited Editions (which I own) on eBay going for 10x what I bought them for (hence the reason for making sure I document their bar codes).

Yes, I know futzing with the indexed field is not proper database etiquette... but for an uneducated heathen who just wanted to document my collection in case (heaven forbid) I need a comprehensive list for insurance purposes, I don't think I did that bad for my first try.

Always make a backup in that case.

 

[The_Doc_Man]

From your last post, I think I am not wrong to suggest that you look up some articles on database normalization. Since this IS a database forum, you can use the search button in the upper right of the page to search for normalization. On the other hand, if you choose to do a web search, you must qualify the search as database normalization, because by itself, normalization applies to databases, math, chemistry, biology, and diplomatic relations. (No, not kidding.) Take in a few of the articles from the .EDU domain first because that domain frequently will include tutorials. You can also look at .COM domains later. The only reason I would avoid .COM early is because they often have something to sell that could be a distraction.

 

[SpareHead3]

@Gasman @The_Doc_Man - I manually edited the DVD and Blu Rays table so 1) all titles use the same CoverImage File , 2) all titles use the same International EAN bar code (1234567890128) and EAN bar code file ("1 234567 890128.png") and 3) if the title is part of a boxed set, it uses a US UPC barcode number (123456789012) and UPC bar code file ("1 23456 78901 2.png").

Reduced the .zip file from 1.67Gb to 2.65Mb.

All Index tabs are present- Just Unzip Anime Library.accdb and Data folders into their own own directory and the DB should find it.

To see the rounding issue, delete/remark the section near the beginning of the VB code that begins with "Private Sub Form_Load()" up to, but not including the section starting with "Private Sub AC_LostFocust()":

 

[Gasman]

Not sure we can see the issue with what you have uploaded? I commented out your load event code.
Did you test it yourself?
I get your numbers in the left of the table in post #2 for your characters for the most part, a few are 1 or 2 more?, which I presume is newly added records?
A quick browse through the table shows the numbers match up?
Direct data entry for AIN works as it should I believe?

 

[Gasman]

Off topic.

Any time you find yourself copying code with minor changes, there generally is a better way?

Consider this. I changed the first two tabs. I added suitable characters to all the captions.
Now if for any reason the locate record needs some additional code later on down the road, it will be in one place, not 27.

Nice interface BTW

Private Sub AlphaTab1_Click()
'    Dim AAC As String
'    DoCmd.RunCommand acCmdSaveRecord
'    Me.RecordsetClone.FindFirst "[AAC] = '#'"
'    Me.Bookmark = Me.RecordsetClone.Bookmark
'    [NavMenu] = [AIN]
' Now use a local sub for common code of the tabs
    LocateRecord (Screen.ActiveControl.Caption)
End Sub
Private Sub AlphaTab2_Click()
'    Dim AAC As String
'    DoCmd.RunCommand acCmdSaveRecord
'    Me.RecordsetClone.FindFirst "[AAC] = 'A'"
'    Me.Bookmark = Me.RecordsetClone.Bookmark
'    [NavMenu] = [AIN]
' Now use a local sub for common code of the tabs
    LocateRecord (Screen.ActiveControl.Caption)
End Sub

and at the bottom of the form

Private Sub LocateRecord(strCaption As String)
    DoCmd.RunCommand acCmdSaveRecord
    Me.RecordsetClone.FindFirst "[AAC] = '" & strCaption & "'"
    Me.Bookmark = Me.RecordsetClone.Bookmark
    [NavMenu] = [AIN]
End Sub

 

[SpareHead3]

Ah I see....

etc, etc, etc...

But now clicking a button doesn't actually do anything.

How does the button know recognize which character (# or A-Z) it's supposed to act upon?

EDIT - Oh I see you added the appropriate character to this:

 

[SpareHead3]

So I commented out the code too...

Save it, then close the database and re-open it (From the Access Objects, it's the "Anime Input Form"). When I click a tab, say N...

The record with [AIN] = 1285 and [Disc Title] = "Nabari No Ou: Complete Series, Part 1" is displayed. But when I click the [NavMenu] ComboBox drop arrow...

The record highlighted is [AIN] = 1000 [Disc Title] = "Ikki Tousen: Dragon Destiny - Sousou's Ambition".

This doesn't happen upon subsequent tab clicks... so if you don't close/re-open the database, and just click the ComboBox arrow again (or click anywhere else to allow it to "Lose Focus" and let the menu close), click the N tab to display the [AIN] = 1285 and [Disc Title] = "Nabari No Ou: Complete Series, Part 1" record again then click the NavMenu ComboBox arrow, The correct record will now be shown at the top of the menu:

As I pointed out, this only happens with the first attempt to navigate the list after first opening the database - Click any tab then open the NavMenu ComboBox, and the record highlighted in the ComboBox will have an [AIN] value that's equal to the the AIN value of the Form's displayed record rounded to the most significant digit.

Close and re-open the database and click the N, O, P Q or R tabs, they'll display their corresponding first record with a Disc Title that starts with N, O, P, Q or R, but click the NavMenu ComboBox arrow to open it and it will default to displaying [AIN] = 1000 [Disc Title] = "Ikki Tousen: Dragon Destiny - Sousou's Ambition" for each one of those letters (to test each letter, you need to close and reopen the database each time).

Close and re-open the database and click any tab from S through Z, though... The first records that have Disc Titles that start with Letters S, T and U have AIN values greater than 1500 but under 2000 so after clicking either of them then opening the NavMenu ComboBox, you will now display [AIN] = 2000, [Disc Title] = Urusei Yatsura TV Series Vol. 20:

Close and re-open the database for each of the letters V, W, X, Y and Z tabs and you'll see their [AIN] values are higher than 2000... but open the NavMenu ComboBox and the only record highlighted each time will be the same [AIN] = 2000, [Disc Title] = Urusei Yatsura TV Series Vol. 20 that's displayed for letters S, T and U.

You're right- with that code in place, it works just fine and technically I could ignore it and go on with life.... But that's like buying a used car, and you find out that the windshield wipers do one sweep across the windshield immediately after you start the car. Rain or shine, they sweep left and right, just once, every time you turn the key. You ask the previous owner why and they say "Oh you just have to turn the wipers on then off while the car is turned off- then the wipers won't do that single cycle across the windshield when you start the car the next time." Incredulous as it sounds, you try it, and it works. You resign yourself to turning the wipers on then off immediately after parking the car so you don't have to think about doing it when you're ready to just get in and go, but really all you've done is circumvent the wiper issue without actually understanding the cause of it.

I just wanted to understand the mechanism around this anomaly- it seems oddly specific that without that code which merely opens and closes the NavMenu ComboBox upon opening the database, applying the indexed AIN value to the ComboBox results in the ComboBox displaying a record with an AIN value that's been rounded to the most significant digit compared to the record displayed in the Form... and this only happens the first time you click a button/tab (subsequent button clicks result in the correct record being highlighted in the ComboBox). Is there some variable declaration or initialization that I need to do after opening the database to circumvent that rounding function? What does opening and closing the ComboBox do that keeps the rounding quirk from manifesting?

 

[Gasman]

Ah I see what you mean now.
In fact you do not need to close the DB, merely close and reopen the form.

In trying to find out why, I am not sure your data is correct?

How do you manage to get the AIN to be in a non missing sequential order?

Well you have got me ATM, but I also spotted this?

is 1824 actually meant to be there?

Ok, I think I am getting somewhere. This is when having a common function comes in handy.
You are going to either change the tabs to the way I have indicated, or put this in every sub.

Opening the form and pressing any of my amended tabs it works. Try on non amended and I get your issue.
Mine are #,A,B,S,T,U, but check the code.

 

[SpareHead3]

[AIN] stands for "Arbitrary Index Number". It's an Indexed and Required field, but I don't make it increment automatically when a new record is created. When I want to create a new record, I select the record I want to create a new field after then click the "Insert After" button, which is associated with the [InsertRecord] VB code. I then use the VB code to apply an [AIN] value to the new record, but then I reassign the [AIN] as I see fit each time I insert a record (hence the reason why the number is "arbitrary").

I really don't need a "This is record #12345678" indexed record system when my queries for this database aren't that much more sophisticated than "How many titles do I have?"

In any case, this is what the VB code for the "Insert After" button is doing, in plain English:

Step 1 - Write the current record's [AIN] to the variable [CurrentRecord].
Step 2 - Jump to the last record (DoCmd.RunCommand acCmdRecordsGoToLast) and write the last record's [AIN] to [LastRecord].
Step 3 - Create a new record (DoCmd.GoToRecord , , acNewRec)
Step 4 - Give it a new [AIN] that's equal to [LastRecord] + 1.
Step 5 - Save the record.
Step 6 - using a While command,
a) Increment the [AIN] value of the Current Record (not the [CurrentRecord], but the record that's currently at the forefront) by 1
b) Save the record
c) Go To Previous Record (DoCmd.GoToRecord , , acPrevious)
d) Return to the beginning of the loop and do this until [AIN] = [CurrentRecord], then jump out of the loop
Step 7 - At this point, the [CurrentRecord]'s [AIN] and the "Next Record's" [AIN] has a difference of 2. Return to the Last Record (DoCmd.RunCommand acCmdRecordsGoToLast).
Step 8 - change it's [AIN] value so [AIN] = [CurrentRecord] + 1.
Step 9 - Give it a "blank.png" cover image (sorry - forgot to provide that image in the \Data\Covers folder) and a generic 'tabs.png" image (so the tabs aren't blank looking).
Step 10 - Save the record.

So let's take a hypothetical record insertion...

Let's say I've reset everything and am going to use this database as it is for a friend's Blu Ray collection. I clear out everything in the DVDs and Blu rays table and decide to import data into a new "DVDs and Blu Rays" table from an Excel spreadsheet which has all the appropriate columns fields set properly. The table has 21 of the 23 Infinity Saga films listed, with the Spider-Man films (Homecoming, and Far From Home) arriving in the afternoon. I ask how he wants them organized- He says he wants "Marvel Cinematic Universe" and "Phase #" in the title and he wants the movies listed in order of their release (It's his database, so I do what he wants).

So, I import the .xlsx file with the following info (just showing the [AIN], [AAN], [Release Date] and [Disc Title] fields for convenience):

AIN AAC Release Date Disc Title
1 M 2008 Marvel Cinematic Universe, Phase 1: Iron Man
2 M 2008 Marvel Cinematic Universe, Phase 1: The Incredible Hulk
3 M 2010 Marvel Cinematic Universe, Phase 1: Iron Man 2
4 M 2011 Marvel Cinematic Universe, Phase 1: Thor
5 M 2011 Marvel Cinematic Universe, Phase 1: Captain America: The First Avenger
6 M 2012 Marvel Cinematic Universe, Phase 1: The Avengers (Avengers Assemble)
7 M 2013 Marvel Cinematic Universe, Phase 2: Iron Man 3
8 M 2013 Marvel Cinematic Universe, Phase 2: Thor - The Dark World
9 M 2014 Marvel Cinematic Universe, Phase 2: Captain America - The Winter Soldier
10 M 2014 Marvel Cinematic Universe, Phase 2: Guardians of the Galaxy
11 M 2015 Marvel Cinematic Universe, Phase 2: Avengers - Age of Ultron
12 M 2015 Marvel Cinematic Universe, Phase 2: Ant-Man
13 M 2016 Marvel Cinematic Universe, Phase 3: Captain America - Civil War
14 M 2016 Marvel Cinematic Universe, Phase 3: Doctor Strange
15 M 2017 Marvel Cinematic Universe, Phase 3: Guardians of the Galaxy, Volume 2
16 M 2017 Marvel Cinematic Universe, Phase 3: Thor - Ragnarok
17 M 2018 Marvel Cinematic Universe, Phase 3: Black Panther
18 M 2018 Marvel Cinematic Universe, Phase 3: Avengers - Infinity War
19 M 2018 Marvel Cinematic Universe, Phase 3: Ant-Man and the Wasp
20 M 2019 Marvel Cinematic Universe, Phase 3: Captain Marvel
21 M 2019 Marvel Cinematic Universe, Phase 3: Avengers - Endgame
22 Z The End

the "title" named "The End" is important as I never got around to fixing the VB code that inserts records into recognizing what to do if it was trying to "insert" a record at the end of the table (easy enough to just say "create new record and do nothing else" but I hadn't got around to fixing it- just adding a record saying "don't use 'Insert after' on this record" is enough for me for now).

So "Spider-Man - Homecoming" and "Spider-Man - Far From Home" came out in 2017 (after "Guardians of the Galaxy, Volume 2") and 2019 (after "Avengers - Endgame"). so in accordance with the steps above...

1) We have to display the record where we're going to Insert the new record after- So since Spider-Man - Homecoming came after Guardians of the Galaxy, Volume 2, we need to navigate to that record first.
2) Click the "Insert After" button. This takes note of the [CurrentRecord] (Guardians of the Galaxy, Volume 2 has an [AIN] value of 15) and jumps to the Last Record and takes note of that too ("The End" has an [AIN] of 22.
3) A new record is created and the VB code gives it a new AIN value of [AIN] = [LastRecord] + 1 or 23, then saves that record.
4) the code then starts it's While loop- it first increments the new record's AIN by 1 (so now its [AIN] = 24, then it goes backwards in the records one at a time, incrementing each [AIN] value by +1 (so "The End" gets an ]AIN] of 23, "Avengers Endgame" gets an [AIN] of 22, Captain Marvel gets an [AIN] of 21, until it [AIN] = [CurrentRecord], at which point it stops (it does not increment the [CurrentRecord] by +1).
5) So now, we're back at Guardians of the Galaxy, Volume 2 with an AIN value of 15, but the "Next Record" (DoCmd.GoToRecord , , acNext), "Thor - Ragnarok" has been incremented by +1 to [AIN] = 17. We can now return to the actual Last Record (DoCmd.RunCommand acCmdRecordsGoToLast, not the record whose [AIN] = [LastRecord]), which is our new record We know there's now a space after Guardians of the Galaxy, Volume 2 that's equal to [CurrentRecord] + 1 and so now we can change the [AIN] value of the Last Record (currently [AIN] = 24) to 16....

With the newly created blank record having an AIN value of 16, we can now add the information for "Spider-Man - Homecoming" to it, nestled neatly between Guardians of the Galaxy, Volume 2 with [AIN] number 15 and Thor - Ragnarok, now with [AIN] number 17.

All of this being completely blasphemous to the database gods with regards to maintaining the integrity of database by never touching the indexed field.

We do it again for Spider-Man - Far From Home...

1) Display Avengers - Endgame in the Form View and click the "Insert After" button.
2) Avengers - Endgame's [AIN] (now it's 22) is written to the variable [CurrentRecord]. It jumps to the Last Record, and that record's [AIN] (23) is written to [LastRecord].
3) An New Record is created and it's [AIN] value is set to [LastRecord] + 1, or 24.
4) The While loop starts- While the [AIN] <> [CurrentRecord], first increment the present record's [AIN] by 1, then save the record, then "GoToPrevious" record. This results in the newly created record with [AIN] of 24 to be changed to 25. Then when it does the GoToPrevious, it just selected the "The End" record which has an [AIN] value of 23, si it increments that to 24, saves it and does a GoToPrevious again. It is now on the Avengers - Engame record which is the same [AIN] value (22) as the [CurrentRecord] variable, and it can now jump out of the loop.
5) With Avengers - Endgame having an [AIN] of 22 which is the same as the [CurrentRecord] variable, "The End" with an [AIN] value of 24, and the New/Blank Record with an [AIN] value of 25, I can now jump back to the Last Record, (not [LastRecord], but GoToLast) and update it's [AIN] value to [CurrentRecord] + 1 which equals 23.
6) I can now start populating New Record with [AIN] = 23 with the information for Spider-Man - Far From Home.

I'm a heathen... but I just want to know why it rounds that value to the most significant digit if I don't open and close the ComboBox, that's all.

 

[SpareHead3]

What you're seeing is part of the reason for including the bar codes in each record... I have multiple versions of the same titles, sometimes by different publishers and sometimes because I bought two of the same title because I want one that's unopened and another to actually open and watch... and other times I bought two of the same thing because I'm an idiot.

A.D. Police Files is a spinoff of the Bubblegum Crisis OVA series...

Bubblegum Crisis (which has nothing to do with Bubblegum) is about an all female team (known as the Knight Sabers) who battle AI-powered robots (called Boomers) that go "insane". Even the military recognizes that the Knight Sabers' suits rival their own hardware (but they don't know the leader of the team is the daughter of the one who designed the military's hardware to begin with). The A.D. Police (Advanced Defense Police) are overwhelmed by the insane bots, but try to save face by labeling the Knight Sabers as a vigilante group.

Bubblegum Crisis was the first OVA series, Bubblegum Crash was the second. AD Police Files and Parasite Dolls were spinoff OVA titles, while AD Police: To Protect and Serve and Bubblegum Crisis 2040 were made for TV.

 

[Gasman]

I just want to know why it rounds that value to the most significant digit

TBH, I have no idea.

All that said, is it working now?