Combo Box, a mind of its own

[MrMcFestoe]

I have a combo box on a form, the combo box gets its information from a table and the rest of the form also from the same table.

When a value is selected in the combo box and tab is pressed the rest of the fields on the form are filled in with the correct information ( at this point it works) if you keep pressing tab to go through all the fields in the form it comes back to the combo box then tab again to the next field, this is where it goes wrong the value in the combo box is related to a customer and if you keep pressing tab the information below the combo box goes through all the customer one by one even if the value in the combo box does not apply to them.

below is code which is on the combo box afterupdate.

Private Sub URN_AfterUpdate()

On Error GoTo ErrorHandler

Dim strSearch As String

strSearch = "[URN] = " & Chr$(39) & _
Me![URN] & Chr$(39)

'Find the record that matches the control.
Me.Requery
Me.RecordsetClone.FindFirst strSearch
Me.Bookmark = Me.RecordsetClone.Bookmark

ErrorHandlerExit:
Exit Sub

ErrorHandler:
MsgBox "Error No: " & Err.Number & ": Description: " & _
Err.Description
Resume ErrorHandlerExit

End Sub

The value in the combo box can be made up of numbers and letters.

Any ideas what i have done wrong.

 

[tonyflavell]

Hi

The records you go through on the second pass of the form are showing you the remainder of the recordsetclone that you have created. The best way round this is to create a bookmark variable and a recordset variable and transfer the bookmark to the source table via the variable. Then delete the recordsetclone and delete the bookmark.

Private Sub URN_AfterUpdate()
On Error GoTo ErrorHandler

Dim strSearch As String
Dim MyBM as String, MyRst as Recordset

'Find the record that matches the control.
Me.Requery

strSearch = "[URN] = " & Chr$(39) & Me![URN] & Chr$(39)

Set MyRst = Me.RecordsetClone
MyRst.FindFirst strSearch

MyBM = MyRst.Bookmark
Me.Bookmark = MyBM

ErrorHandlerExit:
MyBM = Null
MyRst.Close
Set MyRst = Nothing
Exit Sub

ErrorHandler:
MsgBox "Error No: " & Err.Number & ": Description: " & _
Err.Description
Resume ErrorHandlerExit

End Sub

Try this, I hope it works for you. Make sure that "Limit to List" is on for the combo box and the forms "Cycle" is set to "Current Record"

Tony

 

[MrMcFestoe]

Tony,

Thanks for the reply, tried the code and iam now getting a Error Message

Error No94 Invalid use of Null

And the error box just sits there, any ideas, this combo box is driving me daft.

Colin

 

[tonyflavell]

Hi there

Sorry about destroying your code.

Remove the line which says "MyBM = Null" directly under ErrorHandlerExit:

ErrorHandlerExit:
' MyBM = Null
MyRst.Close
Set MyRst = Nothing
Exit Sub

I was a bit too eager to close everything. The Bookmark property is recalculated evry time the form is requeried or moved to a new record.

Tony

 

[MrMcFestoe]

Tony,

The combo box was limit to list, but it cant find the Cycle for the form anywhere is this whats throwing it out.

This is in Access2002

 

[tonyflavell]

Hi Colin

Rather than mess about, I have bastardised one of my databases so that just the search methods work using RecordsetClone. I hope it gives you ideas. Feel free to use any of the code in it.

Send me your email address to tony@accessdb.co.uk and I will zip and forward it to you!

Tony