Search for a record using ComboBox 1

[valtersp]

Hi all!

I've got a very simple problem, but still can not solve it at all.
There is a ComboBox bounded to a field of a table and contains all records the table currently has (all values are unique).
When user choose a value from ComboBox, it is necessary to find out, which record have been chosen (record number e.g.) to make other form controls to point to the record and display corresponding values. (The ComboBox is being used as a record browser).

 

[pgk]

Hi,
Why don't you just open the table and retrieve the record and assign it to controls in the form?

Hope it helps. Let me know what happens.
With regards,
PGK

 

[valtersp]

The problem is the form contains another controls, such as textboxes, combo's which are used to edit records. The combo box described above should be used as a pointer to allow user jump and edit the record he (or she) wants - whether it'll be the first, last or tenth.
Currently I've made another form, which is bound to query, created on users choice (selected value of combo box). Looks a little bit strange, but works.

 

[evalesthy]

This code in AfterUpdate of control will move form to 'found' record.

Dim lngCId As Long
If Not IsNull(cmbFind) Then
lngCId = Me.cmbFind
Me.RecordsetClone.FindFirst "[ClientId] = " & lngCId
Me.Bookmark = Me.RecordsetClone.Bookmark
Me.cmbFind.Value = Null
Else
Me.cmbFind.Value = Null
Exit Sub
End If

I would put code under the NotInList also to account for entry with no matching record. This should get you started. This code assumes a number(long) as the search criteria. Code would change slightly for a string.

 

[valtersp]

Thanks evalesthy!

As I have a string instead of number, single quotations must be included. So, it'll look like this:

Dim strCId
If Not IsNull(cmbXquick) Then
strCId = "'" & Me.cmbXquick & "'"
Me.RecordsetClone.findfirst "[Name_Surname] = " & strCId
Me.Bookmark = Me.RecordsetClone.Bookmark
Me.cmbXquick.Value = Null
Else
Me.cmbXquick.Value = Null
Exit Sub
End If

Thanks everyone who responded my question!

Have a nice day!