Combobox selecting beyond displayed list..

[martinpratt]

Hi All,

I have a working combo box on a form that displays issue numbers for part number typed into a text box.
This all works fine when the list of issue numbers is below the displayed list (16), but when the list is longer than 16 you cannot seem to select anything after item 16.

When the combo box has, say 25 items a scroll bar appears allowing the user to scroll down to item 25, but when you click the mouse the list just reverts back to the first 16 items?

The only way to select an item after 16 is to select item 16 which then closes the combo box, then when you reopen it the list now shows items 17 to 25 which you can then select item 25.

This is the same in reverse, when you have item 25 selected, you cannot select item 1. When you click the mouse the list reverts back to items 17 - 25.

The combo box is populated in code using the lost focus event on the text box for the part number.

There is probably a simple solution, so if anyone knows what's going on, please help :banghead:

Thank you

MP

 

[Daz]

Try going to the combo box properties and select the format. There is a "List Row" change that to the appropriate number and see what happens. Let me know.

 

[martinpratt]

Try going to the combo box properties and select the format. There is a "List Row" change that to the appropriate number and see what happens. Let me know.

Hi Daz,

Thanks for your reply. I had already tried this, I can make the list display 25 items, but again when the list is longer than this the same problem occurs.

I would prefer to keep the list at around 16 ish. The list changes per part number entered. The one I am using at the moment is 31 items.

I would really like this to work correctly.

Thanks

MP

 

[Daz]

Wish I could be of more help. I have some code on a form I have with a combo box that is similar to what you maybe talking about. Seems to work ok there are some requeries :

Option Compare Database
Private Sub qfacility_AfterUpdate()
Dim MyFacility As DAO.Recordset
Set MyFacility = Me.RecordsetClone
MyFacility.FindFirst "[Facility] = '" & Me.qfacility & "'"
If MyFacility.NoMatch Then
MsgBox "Facility Not found"
Else
Me.Bookmark = MyFacility.Bookmark
End If

End Sub
Private Sub qfacility_LostFocus()
Me.qfacility.Width = 1440
End Sub
Private Sub qregion_AfterUpdate()
Me.qfacility.Requery
End Sub
Private Sub qregion_GotFocus()
Me.qregion.Dropdown
End Sub

 

[Daz]

DataPig.com has some good stuff for free on Access that might help, I use it a lot. Old Access Videos. Might be of some help, that is how I developed the combo boxes that code is based on.

Daz

 

[martinpratt]

Wish I could be of more help. I have some code on a form I have with a combo box that is similar to what you maybe talking about. Seems to work ok there are some requeries :

Option Compare Database
Private Sub qfacility_AfterUpdate()
Dim MyFacility As DAO.Recordset
Set MyFacility = Me.RecordsetClone
MyFacility.FindFirst "[Facility] = '" & Me.qfacility & "'"
If MyFacility.NoMatch Then
MsgBox "Facility Not found"
Else
Me.Bookmark = MyFacility.Bookmark
End If

End Sub
Private Sub qfacility_LostFocus()
Me.qfacility.Width = 1440
End Sub
Private Sub qregion_AfterUpdate()
Me.qfacility.Requery
End Sub
Private Sub qregion_GotFocus()
Me.qregion.Dropdown
End Sub

Hi Daz,

Thanks anyway, my code is a simple 'RowSouce' code which updates the combo box on lost focus event of text box

issue_no.RowSource = "Select Q_Issues.[new iss] " & _
        "From Q_Issues " & _
        "Where Q_Issues.doc_ref = '" & assy_no.Value & "'"

MP

 

[martinpratt]

DataPig.com has some good stuff for free on Access that might help, I use it a lot. Old Access Videos. Might be of some help, that is how I developed the combo boxes that code is based on.

Daz

Er, not sure about that website, I got some security warning about running code on my PC, think I'll avoid that one

Thanks

 

[martinpratt]

Hi All,

Just in case anyone is interested I have now fixed this issue.

I was doing a TRIM on the mouse down event of the combo box. This seemed to be causing the problem.

I have now amended the RowSource code to...

issue_no.RowSource = [COLOR=red]Trim([/COLOR]"Select Q_BoM_Issues.[new iss] " & _
        "From Q_BoM_Issues " & _
        "Where Q_BoM_Issues.doc_ref = '" & assy_no.Value & "'"[COLOR=red])[/COLOR]

All working fine now

Thanks for your help

MP