Yes it's been working since before I posted the first comment... I only wanted to find out what this rounding thing is all about and why does opening and closing the ComboBox solve the problem.
Like I said, it seems like a very specific quirk that only manifests itself If I don't open and close the ComboBox, so the question becomes what is it about opening or closing the ComboBox (before doing anything else) that stops it from rounding itself after selecting a record and writing its indexed value to the ComboBox's variable?
If I'm reading your question right, the Text Box named AC is where you input the character for the [AAC] field.
Looking at the above screenshot, you reminded me of another record selection method I incorporated into this form that I don't use too often... Probably because the [AIN] number is indeed arbitrary so it's not necessary to remember any specific record for any length of time.
That "unbound" text box at the upper right is a means to directly enter the [AIN] value of a record. It's governed by the VB code all the way at the bottom:
Code:
'Direct Entry of AIN number - Enter a number in the text box and go directly to that record.
'at the end, write that value to the [NavMenu] variable.
Private Sub AINDirectEntry_AfterUpdate()
If (AINDirectEntry & vbNullString) = vbNullString Then Exit Sub
Dim rs As DAO.Recordset
Set rs = Me.RecordsetClone
rs.FindFirst "AIN=" & AINDirectEntry
If rs.NoMatch Then
MsgBox "Sorry, no such record '" & [AINDirectEntry] & "' was found.", _
vbOKOnly + vbInformation
Else
Me.Recordset.Bookmark = rs.Bookmark
End If
rs.Close
Me.Disc_Title.SetFocus
NavMenu = AINDirectEntry
AINDirectEntry = Null
End Sub
RIght now, the code to open and close the NavMenu ComboBox is commented out- so upon opening the database, if you click a tab button then open the NavMenu, the record displayed in the menu will have an AIN value that's rounded to the most significant digit.
But if you close and open the database and type the AIN number of that same tab button into the unbound text box and hit enter and now open the NavMenu ComboBox, the highlighted record will have the same [AIN] value as the selected record in the form (no rounding issue).
If I close and re-open the database, then click the Q tab/button, it displays [AIN] = 1432 [Disc Title] "Queen's Blade - The Exiled Virgin - A Single Step" in the form.
Then, when I click the NavMenu icon to open the ComboBox, it displays [AIN] = 1000 [Disc Title] "Ikki Tousen - Dragon Destiny - Sousou's Ambition" because 1432 rounded to the most significant digit equals 1000:
But if I close the re-open the database and instead enter the number 1432 (the same [AIN] value as the G Tab/button) in the text box at the upper left:
... and hit Enter, that's the record that gets displayed...
... and it's also the correct record highlighted in the NavMenu's drop menu:
I noticed the "rs.Close" and "Me.Disc_Title.SetFocus" lines in the AINDirectEntry code that isn't in the AlphaTab code so I added those lines to the AlphaTab section to see if it made a difference (thanks for the advice regarding repeated code btw), but to no avail- clicking tabs still result in rounded [AIN] values appearing in the drop menu (unless I open/close the menu on loading).
and I don't understand what you mean "your method." I changed the code to what you described.
Oh and for some reason, now the rounding issue appears no matter what I do- upon first opening the database, the first time I open the ComboBox, the record highlighted has an AIN value that's rounded to the most significant digit for the first of any tab/button clicks (subsequent button clicks and the ComboBox opens to the correct record). I don't understand this- how could it be working before, but not working now?
The only time it doesn't get rounded is when I enter an AIN value directly in the unbound text box at the upper left.
I have amended ALL the buttons now.
I am in the process of trying to create a class for them, but not getting very far, so that code is commented out ATM.
Thank you. I do appreciate your efforts, but I have more questions than answers now.
1) My question still isn't answered. What causes the rounding issue?
2) I know what this does because I either wrote it or found it on the web and used it because it did whatever I wanted it to do:
I have no idea what this does because you wrote it:
I see this line repeated in both sections:
Code:
Private Const cstrAlphabet As String = "#ABCDEFGHIJKLMNOPQRSTUVWXYZ"
... but I have no idea what it does in either section.... or if it's doing anything.
That is me trying to create a class to replace all the code for those buttons.
That would be a more enhanced version of the common function I created.
With that function, one still has to add code to 27 buttons click event. With a class, all those 27 click events would disappear.
I am just using your uploaded db as a test db as it suits the class I am trying (badly) to create.
So that is nothing (at present) to do with your issue.
All the buttons now use LocateRecord for the moment. No rounding issue occurs. I have no idea why it did in the first place?
Perhaps any experts might know.?
I was finally able to download your file to take a look. Unfortunately, I may have misunderstood the problem as I don't seem to experience what you were saying about "rounding" and mismatches in the combobox. I did, however, add a single line of code for something else.
Would you please take a look at the file I uploaded (it's the file you uploaded with a small change) and verify that it is showing the problem you were having? If it does, would you mind giving me a step-by-step guide on how to duplicate the issue? Thanks!
@theDBguy
PMFJI
First you have to comment out the code he mentioned.
Then click one of the alpha buttons. The ain will be displayed for the start of that letter.
However, if you then click on that minute list box, top left, second control in, it will not show the same record.
If you try my version, it shows the correct record.
To replicate each time, just close the form or switch to design mode and back.
@theDBguy
PMFJI
First you have to comment out the code he mentioned.
Then click one of the alpha buttons. The ain will be displayed for the start of that letter.
However, if you then click on that minute list box, top left, second control in, it will not show the same record.
If you try my version, it shows the correct record.
To replicate each time, just close the form or switch to design mode and back.
I'm on my phone right now, but I didn't know I had to comment out a code to reproduce the problem. Which code was that and why comment it out if leaving it in doesn't cause any problems? I'll check it again when I get back in front of a computer.
@SpareHead3
It was pointed out to me when trying your DB and my class, that the db does not compile with Option Explicit, which you do not have.
That should be at the top of every module/Form/Report.
If you put that in then you will need to fix your non declared variables.
To have it for any new modules, go into the VBA editor and choose Tools/Options and tick the Require Variable Declaration
Access will then insert it for any new modules. Lucky for you, you only have one form to fix.
Okay, thanks. I see it now. The only thing I could come up with is that control is probably corrupted. As a quick test, I added my own dropdown in red, right next to it, and the new dropdown seems to work fine (I think). Could you please verify? Thanks!
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)?
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)?
I just gave it a try, and it always gives me the record with AIN one more than the selected one. For example, if I select AIN=1367, the dropdown shows 1368. I was also going to check what happens if the AINs weren't sequential.
