crazy stuff (1 Viewer)

[John Sh]

I have a combobox that takes multiple entries and puts a comma between each.
The entries are in the form of abbreviations with a maximum of 5 entries possible and no duplicate entries.
There is a requirement that some entries might need to be removed in the future so I have written code to remove entries from the end of the string with "backspace" or "delete" and governed by the position of the last comma.
Where it gets crazy is this. If I step through the code using the 'Backspace" key everything works as expected. The string is truncated to the character before the last comma. This works right back to a null string.
If I run the code without the step through the result is nothing like expected. The string is still truncated but there seems to be no order to what is being removed.
I have created a simple form with just the combo box and the necessary tables to make it run. There are no modules or calls to external subs / functions.
The result is the same.
In the test setup I have a table with one field and one record called "Main" and a second table called "Phenology" with two fields and five records, This table holds the data for the query.
The form and it's associated files, queries etc run to 15mb. I have no idea how to reduce this size.
My code is below. Any suggestions will be gratefully received.

Option Compare Database
Option Explicit
Private Phen As String
Private cPos As Integer


Private Sub cboPhenology_Change()
    If Phen = Me.cboPhenology Then
        GoTo fixit
    End If
    If InStr(1, Phen, Me.cboPhenology + ",") = 0 Then
        If Right(Phen, 1) <> "," Then
            Phen = Phen + ","
        End If
        Me.cboPhenology = Phen & Me.cboPhenology & ","
        Phen = Me.cboPhenology
    Else
        Me.cboPhenology = Phen
    End If
'    Me.Dirty = False
'    Me.cboPhenology.SetFocus
fixit:
    Call fixit
End Sub

Private Sub cboPhenology_GotFocus()
    If Not IsNull(cboPhenology) Then
        Phen = cboPhenology
        Call fixit
    Else
        Phen = ""
    End If
'    Call newPosition("A")
    Me.cboPhenology.BackColor = RGB(218, 255, 94)
    Me.cboPhenology.SelStart = cPos
End Sub

Private Sub cboPhenology_KeyDown(KeyCode As Integer, Shift As Integer)
    Dim num As Integer
    On Error GoTo Error
    Phen = Me.cboPhenology
    num = InStrRev(Phen, ",")
    If KeyCode = vbKeyDelete Then
        Phen = ""
    ElseIf num = 0 Then
        Phen = Left(Phen, Len(Phen) - 1)
    ElseIf KeyCode = vbKeyBack Then
        Phen = Left(Phen, IIf(num > 2, num - 1, num))
    End If
    Me.cboPhenology = Phen
    Call fixit
Error:
    Exit Sub
End Sub

Private Sub cboPhenology_lostFocus()
    Me.cboPhenology.BackColor = RGB(240, 240, 240)
'    Call noMessage
End Sub


Private Sub fixit()
    If Right(Me.cboPhenology, 1) = "," Then
        Me.cboPhenology = Left(Me.cboPhenology, Len(Me.cboPhenology) - 1)
    End If
    If Left(Me.cboPhenology, 1) = "," Then
        Me.cboPhenology = Right(Me.cboPhenology, Len(Me.cboPhenology) - 1)
    End If
    Phen = Me.cboPhenology
    cPos = Len(Phen)
    Me.cboPhenology.SelStart = cPos
End Sub

Private Sub CloseBtn_Click()
    DoCmd.Close acForm, Me.Name
End Sub

and the lone query

SELECT Phenology.Phenology, Phenology.Descrription
FROM Phenology
ORDER BY Phenology.Phenology;

 

[theDBguy]

What is "fixit?" It would probably be easier if you could post a sample db. It doesn't have to be your real db. Just a similar, but smaller copy.

 

[John Sh]

What is "fixit?" It would probably be easier if you could post a sample db. It doesn't have to be your real db. Just a similar, but smaller copy.

fixit simply removes any comma's before, or after the combobox string has been truncated.
Here is the full test setup.
It's too big to send, how do I send a sample?

 

[oleronesoftwares]

It's too big to send, how do I send a sample?

You can zip it, and click on attach files

 

[John Sh]

You can zip it, and click on attach files

Thank you. Too easy. It opens with 7zip

 

[CJ_London]

unable to open it even though I have 7zip - suggest zip using the windows option (right click on file>send to>compressed zip folder)

 

[John Sh]

unable to open it even though I have 7zip - suggest zip using the windows option (right click on file>send to>compressed zip folder)

Interesting. I've opened it twice with 7zip. Possibly a version difference.
Here it is zipped with Windows.

 

[arnelgp]

can you test this

 

[John Sh]

can you test this

Some major changes and it seems to work as required.
Thank you.
Can you offer an explanation of the original behaviour, why did it work correctly in debug mode?
John