asked on

Combo Problem

I have a simple combo that searches the form's underlying table for contact name and moves to the record selected. For some strange reason occasionally the combo will not show all records. It will leave out "John Smith" but show "Jane Smith" and "Jonny Smith" any reason for this? it's very disconcerting!

peter57r

We need to see the sql of the rowsource for the query.
If the rowsource is a named/saved query we need the sql for that.

Derek Brown

ASKER

SELECT Project.AccountCode, Project.CustomerName, Project.ProjectRef, Project.Ref, Project.OrderNumber, Project.CustomerOrderNumber, Project.DateCreatedProject AS DateCreated, Project.AccountCode FROM Project ORDER BY Project.CustomerName, Project.AccountCode;

The code that runs for th combo box is:
Please note that the OnError part is in the wrong place could this be the problem? I also just noticed that I am re-querying the very combobox that I am clicking on. I do this so that if a new record is created it immediately appears in the combo. It is an early DB I did 10 years ago. That's my excuse.
Private Sub Combo29_AfterUpdate()
If Me.NewRecord = True Then
DoCmd.Echo False
Combo29.Requery
Combo32.Requery
Combo34.Requery
Combo36.Requery
Combo38.Requery
DoCmd.Echo True
End If
On Error GoTo Err_Combo29_Click
Dim rs As Object
Set rs = Me.Recordset.Clone
rs.FindFirst "[AccountCode] = " & Str(Nz(Me![Combo29], 0))
If Not rs.EOF Then Me.Bookmark = rs.Bookmark

Exit_Combo29_Click:
Exit Sub
Err_Combo29_Click:
MsgBox "The selected Project has been Deleted. Click OK and select another Project"
Resume Exit_Combo29_Click
End Sub

Dale Fye

To answer your first question, how many records do you have in this recordset you are using as the rowsource for Combo29? If you have too many records, Access might still be trying to return the entire recordset.

I'm not certain why you would requery the combos immediately after selecting a record in the combo. This could significantly slow down your application.

If you requery combo29 before doing the "findFirst", then there will be no value for the combo and the find first will not be able to locate the record. Try disabling or removing the requery code.

Private Sub Combo29_AfterUpdate()

'If Me.NewRecord = True Then
' DoCmd.Echo False
' Combo29.Requery
' Combo32.Requery
' Combo34.Requery
' Combo36.Requery
' Combo38.Requery
' DoCmd.Echo True
'End If
On Error GoTo Err_Combo29_Click
Dim rs As Object
Set rs = Me.Recordset.Clone
rs.FindFirst "[AccountCode] = " & Str(Nz(Me![Combo29], 0))
If Not rs.EOF Then Me.Bookmark = rs.Bookmark

Exit_Combo29_Click:
Exit Sub
Err_Combo29_Click:
MsgBox "The selected Project has been Deleted. Click OK and select another Project"
Resume Exit_Combo29_Click
End Sub

Derek Brown

ASKER

Thank you

There is about 400 records in the underlying table/query.

I will remove the re-query code. The difficulty here is that this problem is sporadic and not easy to repeat. So I won't know if it has worked until it comes up again. Closing the database and reopening it sometimes clears the problem.

I asked the following question years ago but had a mixed response. What is the best way to ensure that combo boxes are up to date or updated immediately after a new record is entered without forcing a user to do something like press an F Key or click a save button. Or should this be a separate post?

ASKER CERTIFIED SOLUTION

Helen Feddema

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Derek Brown

ASKER

That's great, thank you

When you say that the bookmark is no longer needed does that apply to with Access 2002?

Can you answer the last part of my last post if possible.

Many thanks

Derek

Derek Brown

ASKER

Thank you.