Access 2007 combobox doesn't work

[aucamusc]

Pls. in spite I have been working many years with Access this is the first time I have this problem.

I have a form with a combobox and a text box, fields in the combobox: Id, surname, givenname. Surname y givenname ascending.

The first time I type surname it shows exactly what I need, surname, given name and shows Id number in the textbox as needed.

The file has 35,000 records of patients. I use a query with these three fields: Id number (patient number), surname and given name.

After I close the form that worked so well the first time, the second time don;t show the autocomplete and finally send me the following message "The specify text is not member of the list", so I cannot find any patient.

I have had the same result in three forms, I created the one I am explaining here so simple but the undesirable result is the same.

Thank you in advance for yuour kindness.

 

[MajP]

Did this use to work and then stopped or has this capability never worked?
It at one time it use to work. Make sure to compact and repair the database.
If that does not help then consider creating a new database and importing all the object into the new database. This is a good general practice to avoid corruption. Remember to readd any references.

If this never worked you may want to limit the rowsource 35k is a lot of records. May be the cause of the problem. One way is as you type say "M" the rowsource returns only rows starting with "M". There should be some FAQs on this.

 

[aucamusc]

Thanks a lot MajP for your attention.
I have compacted and repaired the databasde, I recreated the combobox, and it works find testing with many calls of patients, but as soon as I close the form and open again, it doesn't work. This is my first system under W7 platform.
Thank you again. I'll try a new database
aucamusc

 

[MajP]

That is a strange problem, usually closing and reopening fixes problems. Is there any code run on the close event of the form?

 

[aucamusc]

This is all the code it has:

Private Sub Ape_AfterUpdate()
Me.Ficha = Me.Ape.Column(2)
End Sub
**********************
Ape = Combobox name

Ficha = Textbox name

By the way, I recreated the whole database, except the linked tables, which I linked after the import of the objects.

Behaviour, exactly the same, first time I open the form works fantastic, fast and exact, but second opening "SPECIFY TEXT IS NOT MEMBER OF THE LIST", also it vanishes the autocomplete feature.
Besides that, it drops down the query from the first patient surname alphabetically ordered.

Thank you for your support,

aucamusc

 

[MajP]

I have an idea, but need to see the properties of the combo the first time and second time you open it. Add this code to your form.

Public Sub PrintProperties(cmbo As Access.ComboBox)
  With cmbo
    Debug.Print "Row Source: " & .RowSource
    Debug.Print "Control " & .ControlSource
    Debug.Print "Bound: " & .BoundColumn
    Debug.Print "Col Count: " & .ColumnCount
    Debug.Print "Widths: " & .ColumnWidths
   End With
End Sub

In your combo's enter event call the below code like

printproperties me.YourCombosName

example

Private Sub Customer_ID_Enter()
  PrintProperties Me.Customer_ID
End Sub

This should print out the important propties in the immediate window of vbe. Something like

Row Source: SELECT [ID], [Company] FROM [Customers Extended] ORDER BY [Company]; 
Control Customer ID
Bound: 1
Col Count: 2
Widths: 0;1440

Post the results from the immediate window. Especially check to see if any those properties change from the first time to the second.

 

[aucamusc]

This is what I got after recreate combobox (works find):

Row Source: SELECT [qryBuscaApep].[Apellidos], [qryBuscaApep].[Nombres], [qryBuscaApep].[Ficha] FROM qryBuscaApep ORDER BY [Apellidos], [Nombres];
Control
Bound: 3
Col Count: 3

This is what I got after closing and reopening the db.:

Row Source: SELECT [qryBuscaApep].[Apellidos], [qryBuscaApep].[Nombres], [qryBuscaApep].[Ficha] FROM qryBuscaApep ORDER BY [Apellidos], [Nombres];
Control
Bound: 3
Col Count: 3

but autocomplete don't work, and when I complete the surname and given name and press ENTER, it shows the message:

"The specify text is not member of the list".

the drop down works find alphabetically ordered. If I pick any of the names it finds with no problems, the problem is that if I want a surname with initial "P" it will take a lot of time to run the list until find the "P"s initials, worse with last letter of the alphabet.

I have use most of the Access tools, the only thing would be to reaload Access.

Thanking your support,

Aucamusc

 

[MajP]

Faq702-6304

 

[MajP]

Also see this

http://support.microsoft.com/kb/824189/en-us?spid=2509&sid=285

 

[aucamusc]

Dear MajP, your help have being invaluable, The problem was solved adding DISTINCT to the source quey, now it works find.

Congratulations for your outstanding support.

Thanks a lot,

aucamusc