Combo Box Freezes after first use

[gilave]

I have placed an unbound combo box in the form header for searching records.

The combo box properties are:
Name: cboID
Row Source: SELECT ID, Desc FROM Table1
Column Count: 2
Bound Column: 1
Limit to list: Yes

I have set the AfterUpdate event procedure:

with Me.RecordsetClone
.MoveFirst
.FindFirst "ID = " & Me!cboID
Me.Bookmark = .BookMark
End With

The form's record source is Table1. The combo box worked fine until now. No changes made to the form. I can only use the combo box once and after that the list drops down fine but an item on the list cannot be selected - the combo box locks up.

Please help.

I am using Access 2000.

 

[TheAceMan1]

How are ya gilave . . . .
[ol][li]In . . .

[blue] SELECT ID, [purple][b]Desc[/b][/purple] FROM Table1[/blue]

. . . [purple]Desc[/purple] is a [blue]KeyWord[/blue] in [blue]Jet SQL[/blue] and shouldn't be used as a fieldname. Its use is for sorting a query in [purple]Desc[/purple]ending order. This may well be the culprit.[/li]
[li]If you select a [blue]nonexisting ID[/blue] from cboID, [blue]findFirst will fail[/blue], and the subsequent [purple]erroneous bookMark assignment[/purple] may be the other culprit.[/li][/ol]

See Ya! . . . . . .

 

[gilave]

Thanks for responding TheAceMan1.

Desc is not actually the field name I am using but thanks for the reminder about the KeyWord issue.

To avoid the problem you mentioned in 2, the combo boxes row source derives its data from the same table that is used as the record source of the form and I have also set the Limit to list property to Yes.

 

[TheAceMan1]

Roger That gilave . . . . .

Although you don't need [blue].MoveFirst[/blue] (.FindFirst starts at the beginning of the recordset anyway), removing it won't/shouldn't change anything.

To be sure code is working replace with the following:

[blue]   With Me.RecordsetClone
      .FindFirst "ID = " & Me!cboID
      
      If .NoMatch then
         MsgBox "ID '" & Me!cboID & "' Not Found"
      Else 
         Me.Bookmark = .BookMark
      End If

   End With[/blue]

[purple]Let me know the results . . . . .[/purple]

See Ya! . . . . . .

 

[PHV]

And what about this ?
Dim rs As DAO.Recordset
Set rs = Me.RecordsetClone
With rs
.FindFirst "ID = " & Me!cboID
If Not .NoMatch Then Me.Bookmark = .BookMark
End With
rs.Close
Set rs = Nothing

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886