DoCmd.SearchForRecord odd behavior

[WCStarks]

Question 1)
I have a combo box for selecting a member to go to. It was originally created by the Wizard as a Macro, before I recently converted it to a Procedure. It contains this AfterUpdate Procedure:

DoCmd.SearchForRecord , "", acFirst, "[ID] = " & Str(Nz(Screen.ActiveControl, 0))

This odd behavior was the same when it was a Macro, as it is now as a Procedure.

Odd Behavior:
When I make a selection in this combo box, the screen will briefly flash a default member record in the form, before displaying the person selected. I am looking for a solution to avoid flashing the default record, before displaying the selected record.

Question 2)
The current default record is mine. Earlier, it was another person. I don't know how a record becomes a default record, or how it got changed. Any thoughts?

[davegri]

That converted macro code can be so lame..
Try the below. Adjust the red for your conditions. It looks like ID might be OK as is.

Private Sub Find_Combo_AfterUpdate()
	Dim strCriteria As String
	strCriteria = "[ID] =" & Me.Find_Combo
	Me.recordsetclone.Findfirst (strCriteria)
	If me.recordsetclone.NoMatch Then
		MsgBox “No entry found”
	Else
		Me.Bookmark = me.recordsetclone.Bookmark
	End If
End Sub

[WCStarks]

Thank you. That stopped the intermediate flash - Great! However, there is a problem with MsgBox "No entry found". I changed it to include the "=" but it still causes a compile syntax error. So I commented it out in the mean time.

[davegri]

The msgbox problem is the double quotes, caused by my paste from an external editor. Replace them with your double quotes and all is well.

[WCStarks]

Thanks. That worked. Issue resolved.

[WCStarks]

Do you have any feedback on my question 2)?

[davegri]

By default do you mean the record displayed on the form when it is opened, before any selection is made in the combobox?
If so, it's just the first record in the table or query that is the recordsource for the form when it is opened.

[WCStarks]

It used to show the first record alphabetically (record # 36), when the form opened. Now, it shows me, record #2 when it opens.

Concerning the other issue, which ever record was defaulted when it opened, is also the one that the combo box would flash to, when it went to the selected record.

[davegri]

SORTING
if the recordsource for your form is a table, change it to:
Select * from MyTable ORDER by Myfield1, Myfield2;

If the recordsource is a Named query you can do the same:
Select * from Myquery ORDER BY myfield1, myfield2;

If the recordsource is a query string, add the ORDER BY clause to the end of the query string.