List box category disappears after clicking in text box

[Pontus]

Hello! I just started working with Access 2013 trying to build a searchable database, but now I have a problem with a list box. I used the list box wizard to setup the list. I also imported all the categories for the list from a table.

When I view my form everyting looks good but as soon as I click in a text box which I am using as a searchfield some of the categories disappears from the list box and all of the others makes a jump to the left leaving the right side just gray.

I tried to google this problem but no results showed up. I would be super thankful I anyone could give me some advice!
Thanks in advance!


I found this code via a youtuber named "ibasskung":

Option Compare Database
Private Sub Form_Load()
txtSearch.SetFocus


End Sub
Private Sub Frame2_AfterUpdate()
txtSearch = ""
txtSearch.SetFocus
End Sub
Private Sub cmdClose_Click()
DoCmd.Close acForm, "Form1"
'Close Form1
End Sub
Private Sub txtSearch_Change()
If Frame2 = 1 Then 'Manufacturer
strRowsource = "SELECT [Manufacturer],[RatedCurrent],[RatedVoltage],[BIL],[TotalLength],[CatNO] " & "FROM BushingsT " & _
"WHERE [Manufacturer]Like '*" & Me.txtSearch.Text & "*' "
ElseIf Frame2 = 2 Then 'RatedCurrent
strRowsource = "SELECT [Manufacturer],[RatedCurrent],[RatedVoltage],[BIL],[TotalLength],[CatNO] " & "FROM BushingsT " & _
"WHERE [RatedCurrent]Like '*" & Me.txtSearch.Text & "*' "
ElseIf Frame2 = 3 Then 'RatedVoltage
strRowsource = "SELECT [Manufacturer],[RatedCurrent],[RatedVoltage],[BIL],[TotalLength],[CatNO] " & "FROM BushingsT " & _
"WHERE [RatedVoltage]Like '*" & Me.txtSearch.Text & "*' "
ElseIf Frame2 = 4 Then 'BIL
strRowsource = "SELECT [Manufacturer],[RatedCurrent],[RatedVoltage],[BIL],[TotalLength],[CatNO] " & "FROM BushingsT " & _
"WHERE [BIL]Like '*" & Me.txtSearch.Text & "*' "
Else 'TotalLength
strRowsource = "SELECT [Manufacturer],[RatedCurrent],[RatedVoltage],[BIL],[TotalLength],[CatNO] " & "FROM BushingsT " & _
"WHERE [TotalLength]Like '*" & Me.txtSearch.Text & "*' "
End If
List16.RowSource = strRowsource
End Sub
Private Sub txtSearch_Click()
strRowsource = "SELECT [Manufacturer],[RatedCurrent],[RatedVoltage],[BIL],[TotalLength], CatNO " & "FROM BushingsT"
List16.RowSource = strRowsource
End Sub

[CJ_London]

Your code would be a lot easier to read if you used the code tags (the # button) to preserve spacing and indentation

it is because you are only selecting these fields

SELECT [Manufacturer],[RatedCurrent],[RatedVoltage],[BIL],[TotalLength],[CatNO]

you can also significantly simplify your code

Private Sub txtSearch_Change()
    List16.RowSource = "SELECT [Manufacturer],[RatedCurrent],[RatedVoltage],[BIL],[TotalLength],[CatNO] " & "FROM BushingsT " & _
        "WHERE " & Choose(Frame2,"[Manufacturer]","[RatedCurrent]","[RatedVoltage]","[BIL]","[TotalLength]") & " Like '*" & Me.txtSearch.Text & "*'"
End Sub

or

Private Sub Frame2_AfterUpdate()
    txtSearch = ""
    txtSearch.SetFocus
    List16.RowSource = "SELECT [Manufacturer],[RatedCurrent],[RatedVoltage],[BIL],[TotalLength],[CatNO] " & "FROM BushingsT " & _
        "WHERE " & Choose(Frame2,"[Manufacturer]","[RatedCurrent]","[RatedVoltage]","[BIL]","[TotalLength]") & " Like '*" & Me.txtSearch.Text & "*'"
 End Sub

Private Sub txtSearch_Change()
    List16.requery
End Sub

[Pontus]

Your code would be a lot easier to read if you used the code tags (the # button) to preserve spacing and indentation

it is because you are only selecting these fields



you can also significantly simplify your code

Private Sub txtSearch_Change()
    List16.RowSource = "SELECT [Manufacturer],[RatedCurrent],[RatedVoltage],[BIL],[TotalLength],[CatNO] " & "FROM BushingsT " & _
        "WHERE " & Choose(Frame2,"[Manufacturer]","[RatedCurrent]","[RatedVoltage]","[BIL]","[TotalLength]") & " Like '*" & Me.txtSearch.Text & "*'"
End Sub

or

Private Sub Frame2_AfterUpdate()
    txtSearch = ""
    txtSearch.SetFocus
    List16.RowSource = "SELECT [Manufacturer],[RatedCurrent],[RatedVoltage],[BIL],[TotalLength],[CatNO] " & "FROM BushingsT " & _
        "WHERE " & Choose(Frame2,"[Manufacturer]","[RatedCurrent]","[RatedVoltage]","[BIL]","[TotalLength]") & " Like '*" & Me.txtSearch.Text & "*'"
 End Sub

Private Sub txtSearch_Change()
    List16.requery
End Sub

Thanks for the reply!

I don't quite understand what you mean. Am I only selecting one of the categories? Anyways I solved it in another way, by first showing the ID (AutoNumber) and when clicking in the text field the ID disappears.

[CJ_London]

I must have misunderstood your question

Good luck with your project