Combo Box response time

[Tomm]

Hi.

I have a form that pulls back info from an sql database -a picture, name, etc.

I setup a combobox that lists all the surnames and the user can find the user in that list and clikc and it loads.

However, it takes ages to load the record and often hangs the program.

But if I use the 'next record' button, it loads the record straight away so it isn't a connection issue.

Here is the code behind the combo/dropdownlist:

 Private Sub Searchbox_AfterUpdate()
     ' Find the record that matches the control.
     Dim rs As Object
 
     Set rs = Me.Recordset.Clone
     
     rs.FindFirst "[Surname] = " & Chr(34) & Me![Searchbox] & Chr(34)
     
     If Not rs.EOF Then Me.Bookmark = rs.Bookmark
 End Sub

Any thoughts?

edit - oh, the sql VIew/query on the database returns about 2000 records. Not ideal I suppose for a combobox but what alternative is there for the end-user just to start typing 'Smith' and it gives them all the Smiths?

edit 2 - also, the combobox generates the data in the list straight away, it's when I click a record that the slowness kicks in.

 

[Tomm]

I have tried the suggestions from the document in this thread: http://www.access-programmers.co.uk/forums/showthread.php?t=161713

But the issue remains - combobox generates the list straightaway but clicking in the list makes Access hang/freeze for approx. 1 minute and then displays the requested data.

But if I use the prev/next record navigation buttons, it loads the data immediately.

Thanks

 

[Tomm]

Morning all, this is just a shameless bump as I'm still having problems.

Thanks