Filter number on combo box dropdown list

[sophannaly]

Hi,

I have one problem that I need to solve. In the last post, according to you all solution, I can filter on number base on user input. And the even that I use for

dropdown control is :

Private Sub eco_code_Change()
    Dim varKey As Variant
     
    On Error GoTo ErrorHandler
    
    varKey = Me.eco_code.Text
    strsql = "select code,description FROM expenditure_eco_classification WHERE Cstr(expenditure_eco_classification.code) LIKE '" & varKey & "*' ORDER BY 

expenditure_eco_classification.code"
    ' To let cursor on other component to refresh rowsource everytime there is something change in eco_code combo box
    Me.txtHidden.SetFocus
    Me.eco_code.RowSource = strsql
    Me.eco_code.Requery
    Me.eco_code.SetFocus
    Me.eco_code.Dropdown
    Exit Sub
     
ErrorHandler:
        ' Display error information.
        Select Case Err.Number
     
        Case Is = 2110
                Resume Next
        Case Is = 2118
                Resume Next
        Case Else
                MsgBox "Error number " & Err.Number & ": " & Err.Description
                ' Resume with statement following occurrence of error.
                Resume Next
        End Select
End Sub

Private Sub eco_code_GotFocus()
    'Automatic dropdown list when the field is being selected
    Me.eco_code.Dropdown
End Sub

Private Sub eco_code_LostFocus()
    ' This code purpose is after Change() function, then in the next record rowsource will be limit to Select statement with condition.
    ' So we need to define default rowsource with full list to this control back
    strsql = "select code,description FROM expenditure_eco_classification ORDER BY expenditure_eco_classification.code"
    Me.eco_code.RowSource = strsql
End Sub

In form, for eco_code dropdown list, column count is 2. eco_code control bound column is 1, and column count=2 will give eco_description control.

When user input on eco_code, the other eco_description will disappear for a while, until user click to select from list, then the other eco_description text will

show up again.

For user input, this is not very convenient cuz could make feel confuse.

Could you tell how can I change this?

Best regards,
Sophanna

[NeoPa]

A ComboBox shows column data only when dropped down. That is the design of the ComboBox. If this is not what you want then you are either using the wrong control or using it the wrong way.

It is possible to reflect the column data of the selected item once it's been updated, but that would take code and one or more controls in which to reflect this data.

That should be enough to point you on your way but if more is required then just let us know.

[sophannaly]

Hi Neopa,

Thanks for your reply. Let me explain my problem again.
In my continuous form, there is a combo box and it works fine. My combo box is eco_code, its data source is

SELECT code, description FROM expenditure_eco_classification ORDER BY expenditure_eco_classification.code;

Column count is 2 and Bound column is 1. The control next to this combo box is eco_description textbox. And its datasource is =[eco_code].[column](1).

For first row everything show fine. But for second row input and so on, when user type on eco_code combo box, then the upper row which its eco_code if different from current filter eco_code that user is working on, eco_description text will disappear. After user click to select from eco_code drop down list, then all upper rows eco_description will show up again. I don't how to solve this.

Best regards,
Sophanna

[NeoPa]

Hi Sophanna.

Sorry for the delay responding.

I read your last post, but wasn't able to understand what it is you're trying to explain. Could you possibly try again.

[sophannaly]

Hi NeoPa,

I'm sorry for late to reply too cuz I didn't see notification in message box. The problem is when user still work on eco_code, then the upper record on eco_description which is data source as eco_code.column (1) will disappear. this eco_description will appears again when focus is leaving from eco_code.

I don't know how to upload screenshot here. If I can upload it then you will understand problem.

Best regards,
Sophanna

[NeoPa]

To upload a screenshot :

1. From the 'Post your reply' section at the bottom select advanced.
2. Enter the main part of your post.
3. Scroll down to Additional Options.
4. Click on Manage Attachments.
5. Click on top Browse button.
6. Select screenshot file.
7. Click on Upload button.
8. Close the pop-up window.
9. When you've submitted the post copy the link to the attached file to the clipboard.
10. Edit the post and create an :
    [IMGNOTHUMB]copied link[/IMGNOTHUMB] link.

[sophannaly]

Hi NeoPa,

Please take a look at screen shot. As you could see that second column which base on first combox column(1) will disappear when i'm about selecting new item from dropdown list in first column, and it will appear again when I already selected from dropdown list and move my cursor to click on other field.
[IMGNOTHUMB]http://bytes.com/attachments/attachment/7670d1401848781/1.png[/IMGNOTHUMB][IMGNOTHUMB]http://bytes.com/attachments/attachment/7671d1401848781/2.png[/IMGNOTHUMB]

[NeoPa]

Hi Sophanna.

Sorry for the long delay. I've been very busy with work commitments to the extent that I haven't visited Bytes for a week or more. Very unusual for me.

As for this problem, it looks very strange to me. I can think of no reason why this might be happening except that there might be a corruption somewhere. Can you try the same Access project on some other PCs to see if you get the same behaviour on all of them.

[zmbd]

Open the properties for the list/combobox
{Format tab}

• [Column Count] =
  [Column Widths] =
  [List Rows] =
  [List Width] =
  

[sophannaly]

Hi zmbd,

Thanks for your reply. As NeoPa suggest, I tried it with other laptop and the problem still exist. Could you tell me what to do with the four properties that you suggest? These four properties already used when I defined it on form design.

Sophanna

[zmbd]

Please fill out the information for the properties listed (^_^)

[sophannaly]

Hi,

All these properties already set for combo box when I put it on the form. Do I need to define it again from VBA code?

Sophanna

[Rabbit]

When you change the drop down row source, the values that were previously available for the other rows in the dataset are no longer available. That is why your description disappears, the description no longer exists in the drop down row source.

After you leave the field, the row source is reset and all the descriptions are available again so they repopulate.

One way around this is to use a DLookup instead of relying on the ever changing row source. It's slower but would stop the description from disappearing whenever you change the row source of the drop down.

[NeoPa]

I'm afraid I can think of nothing that would explain this.

Except, perhaps, if the text is wrapping and showing only a blank line - which is only part of the whole value.

I'll continue to monitor in case I get any further ideas, but for now I'm out of them.

[Rabbit]

You must have been in the thread responding as I was doing the same. I believe my previous post correctly diagnoses the cause. I had a similar issue before in the past with continuous forms and changing the row source of a combo box.