How to seek and then reference the found record to a form record?

[Guest]

I am having trouble using seek to find a record with a unique Serial Number
and then display that record on a form. The Serial Number input is a string
(textbox). My seek code works Ok but after seek, if not EOF, I try to find
absolute position and then use "goto" that record on the form but the
absoluteposition is not always correct. The record that gets shown is always
offset of one or two records from what I need.

Any help is appreciated.

 

[strive4peace]

FindRecord
---


Hi Michael,

try this:

Make one or more unbound combos on your form. Let the first column be
invisible and be the primary key ID of the recordsource of your form and
then, on its AfterUpdate event, launch the code to Find the record

so, if you want to display Serial Number but your (autonumber) primary
key is called SomeID...

RowSource -->
SELECT SomeID, [Serial Number]
FROM tablename
ORDER BY [Serial Number]

ColumnCount --> 2
ColumnWidths --> 0;2
ListWidth --> 2

AfterUpdate -->
=FindRecord()

this code goes behind the form:

'~~~~~~~~~~~~~~~~~~~~
Private Function FindRecord()

'if nothing is picked in the active control, exit
If IsNull(Me.ActiveControl) Then Exit Function

'save current record if changes were made
If me.dirty then me.dirty = false

'declare a variable to hold the primary key value to look up
Dim mRecordID As Long

'set value to look up by what is selected
mRecordID = Me.ActiveControl

'clear the choice to find
Me.ActiveControl = Null

'find the first value that matches
Me.RecordsetClone.FindFirst "SomeID = " & mRecordID

'if a matching record was found, then move to it
If Not Me.RecordsetClone.NoMatch Then
Me.Bookmark = Me.RecordsetClone.Bookmark
End If

End Function

'~~~~~~~~~~~~~~~~~~~~
where
SomeID is the Name of the primary key field, which is in the
RecordSource of the form -- I am assuming your primary key is a Long
Integer data type (autonumbers are long integers)

Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*