Seek (ADO) finds same absoluteposition for two different indexes

M

Mac

The function below takes a serial number entered in the "Serach" control and
then seeks that value in the data table called "PCM Interfaces (Main Table)".
If seek does not return EOF then the absolute position is found and then the
form is set to show that record. This function is just a form record locator.

The problem is that the same AbsolutePosition (1) is found for records 1 and
2, when their respective serial numbers are searched. So the symptoms are
that when serial number 1 is searched it locates record 1. When serial number
2 is searched it locates record 1. When serial number 3 is searched it
locates record 2. And searching from that point on finds record (n-1) for the
serial number serached.

I believe this function worked at one time during development when I had
"dummy" test data in the data table. Once I deleted that data and started
entering valid data it no longer works. Any help would be appreciated.

Private Sub Find_Button_Click()

Dim lngDesiredRecord As Long
Dim intAnswer As Integer



If StrLen(Me![Search].Value) > 0 Then

'Open "PCM Interfaces (Main Table) recordset.
Dim rsMainData As New ADODB.Recordset
With rsMainData
.Open "PCM Interfaces (Main Table)", CurrentProject.Connection, _
adOpenKeyset, adLockOptimistic, adCmdTableDirect
.Index = "PrimaryKey"
.MoveFirst
End With

'Seek to find the record with the entered ("Search" Textbox Control)
Serial Number in the RecordSet.
rsMainData.Seek Me![Search].Value, adSeekFirstEQ

'Determine if the Serial Number was found using Seek
If rsMainData.EOF = False Then

'Get the position of the found record
lngDesiredRecord = rsMainData.AbsolutePosition

'Go to the found record on the form
DoCmd.GoToRecord acDataForm, "Main Data Entry", acGoTo,
lngDesiredRecord

'Close the recordset
rsMainData.Close
Set rsMainData = Nothing

Else

intAnswer = MsgBox("The desired Serial Number was not found in
the database", vbCritical + vbOKOnly, "No Record Found Warning")

Search.Value = Null

End If

Else

intAnswer = MsgBox("The Search Serial Number field is blank, enter a value
and try again.", vbExclamation + vbOKOnly, "Blank Field Warning!")

End If



End Sub
 
S

strive4peace

FindRecord
---

Hi Mac,

here is an alternate way to do a search -- if you need help
implementing, let us know

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...

=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 "IDfield = " & 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

'~~~~~~~~~~~~~~~~~~~~

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


The function below takes a serial number entered in the "Serach" control and
then seeks that value in the data table called "PCM Interfaces (Main Table)".
If seek does not return EOF then the absolute position is found and then the
form is set to show that record. This function is just a form record locator.

The problem is that the same AbsolutePosition (1) is found for records 1 and
2, when their respective serial numbers are searched. So the symptoms are
that when serial number 1 is searched it locates record 1. When serial number
2 is searched it locates record 1. When serial number 3 is searched it
locates record 2. And searching from that point on finds record (n-1) for the
serial number serached.

I believe this function worked at one time during development when I had
"dummy" test data in the data table. Once I deleted that data and started
entering valid data it no longer works. Any help would be appreciated.

Private Sub Find_Button_Click()

Dim lngDesiredRecord As Long
Dim intAnswer As Integer



If StrLen(Me![Search].Value) > 0 Then

'Open "PCM Interfaces (Main Table) recordset.
Dim rsMainData As New ADODB.Recordset
With rsMainData
.Open "PCM Interfaces (Main Table)", CurrentProject.Connection, _
adOpenKeyset, adLockOptimistic, adCmdTableDirect
.Index = "PrimaryKey"
.MoveFirst
End With

'Seek to find the record with the entered ("Search" Textbox Control)
Serial Number in the RecordSet.
rsMainData.Seek Me![Search].Value, adSeekFirstEQ

'Determine if the Serial Number was found using Seek
If rsMainData.EOF = False Then

'Get the position of the found record
lngDesiredRecord = rsMainData.AbsolutePosition

'Go to the found record on the form
DoCmd.GoToRecord acDataForm, "Main Data Entry", acGoTo,
lngDesiredRecord

'Close the recordset
rsMainData.Close
Set rsMainData = Nothing

Else

intAnswer = MsgBox("The desired Serial Number was not found in
the database", vbCritical + vbOKOnly, "No Record Found Warning")

Search.Value = Null

End If

Else

intAnswer = MsgBox("The Search Serial Number field is blank, enter a value
and try again.", vbExclamation + vbOKOnly, "Blank Field Warning!")

End If



End Sub
Click to expand...
 
M

Mac

Is there a way to do it so I can keep my string control? Do you have any idea
why after a seek the absoluteposition would be the same (1) for the first two
records and then one off (less) for all the rest throughout the recordset?

Any suggestions on how to open a recordset so it supports seek and
absolutposition (ADO)?
--
Regards, Michael


strive4peace said:
FindRecord
---

Hi Mac,

here is an alternate way to do a search -- if you need help
implementing, let us know

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...

=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 "IDfield = " & 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

'~~~~~~~~~~~~~~~~~~~~

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


The function below takes a serial number entered in the "Serach" control and
then seeks that value in the data table called "PCM Interfaces (Main Table)".
If seek does not return EOF then the absolute position is found and then the
form is set to show that record. This function is just a form record locator.

The problem is that the same AbsolutePosition (1) is found for records 1 and
2, when their respective serial numbers are searched. So the symptoms are
that when serial number 1 is searched it locates record 1. When serial number
2 is searched it locates record 1. When serial number 3 is searched it
locates record 2. And searching from that point on finds record (n-1) for the
serial number serached.

I believe this function worked at one time during development when I had
"dummy" test data in the data table. Once I deleted that data and started
entering valid data it no longer works. Any help would be appreciated.

Private Sub Find_Button_Click()

Dim lngDesiredRecord As Long
Dim intAnswer As Integer



If StrLen(Me![Search].Value) > 0 Then

'Open "PCM Interfaces (Main Table) recordset.
Dim rsMainData As New ADODB.Recordset
With rsMainData
.Open "PCM Interfaces (Main Table)", CurrentProject.Connection, _
adOpenKeyset, adLockOptimistic, adCmdTableDirect
.Index = "PrimaryKey"
.MoveFirst
End With

'Seek to find the record with the entered ("Search" Textbox Control)
Serial Number in the RecordSet.
rsMainData.Seek Me![Search].Value, adSeekFirstEQ

'Determine if the Serial Number was found using Seek
If rsMainData.EOF = False Then

'Get the position of the found record
lngDesiredRecord = rsMainData.AbsolutePosition

'Go to the found record on the form
DoCmd.GoToRecord acDataForm, "Main Data Entry", acGoTo,
lngDesiredRecord

'Close the recordset
rsMainData.Close
Set rsMainData = Nothing

Else

intAnswer = MsgBox("The desired Serial Number was not found in
the database", vbCritical + vbOKOnly, "No Record Found Warning")

Search.Value = Null

End If

Else

intAnswer = MsgBox("The Search Serial Number field is blank, enter a value
and try again.", vbExclamation + vbOKOnly, "Blank Field Warning!")

End If



End Sub
Click to expand...
Click to expand...
 
S

strive4peace

Hi Mac,

Is there a way to do it so I can keep my string control?

yes, you can change the code as follows:

'~~~~~~~~~~~~~~~~~~~~
Private Function FindRecordString()

'if nothing is picked in the active control, exit
If IsNull(Me.ActiveControl) Then
MsgBox "The Search Serial Number field is blank," _
& " enter a value and try again." _
, vbExclamation + vbOKOnly _
, "Blank Field Warning!")
Exit Function
end if

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

'declare a variable to hold the value to look up
Dim mStr as string

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

'clear the choice to find
Me.ActiveControl = Null

'find the first value that matches
Me.RecordsetClone.FindFirst "stringfield = '" & mStr & "'"

'if a matching record was found, then move to it
If Not Me.RecordsetClone.NoMatch Then
Me.Bookmark = Me.RecordsetClone.Bookmark
Else
MsgBox "The desired Serial Number was not found" _
& vbcrlf & vbcrlf & "--> " & mStr
, vbCritical + vbOKOnly, "No Record Found Warning"
End If

End Function

'~~~~~~~~~~~~~~~~~~~~

because this method works so well, I never use any other...

instead of just telling them the number was not found, you could ask if
they wish to add it

if MsgBox("Message" _
,vbYesNo + vbDefaultButton2 _
, "Add Record) = vbNo then exit function


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

Mac

It worked! I still have other code that uses absoluteposition and now I'm
going to use bookmark instead. How do I set a form to show a bookmark once
the record is found and the bookmark is recorded in a variable?
 
S

strive4peace

Hi Mac,

glad it worked for you. There is no reason to show or record the
bookmark -- it will not stay constant anyway. If you want to find the
record again, simply record the value of whatever field you used to find
it to begin with.

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

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

How to Sort a recorset and then Seek in that recordset (ADO)? 0
Seek in Recordset not working. 2
Help with Search from Main Switchboard 1
Seek Doesn't Work (sometimes) 2
Searching Across Two Tables For A Record 3
Newbie - Case Select Code? 4
Use a variable to update another record 1
My use of "FindFirst" is not working 13

Top