By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,846 Members | 1,680 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,846 IT Pros & Developers. It's quick & easy.

Index Search using Seek

P: n/a
If anyone could help with this one, I am a bit puzzled. I understand
you can create an index using two fields of a database, then use the
seek method to find a record based on both fields. My code isn't
working. It finds the record but the record exists under a different
customer record. I want it only to find the record if both conditions
(customer, number) are met. Here is the code:

Dim dbs As Database, tdf As TableDef, idx As Index
Dim fldLastSixOfVin As Field, fldDlrNum As Field, rst As Recordset

Set dbcurrent = CurrentDB

' Return reference to current database.
Set dbcurrent = CurrentDB
' Return reference to Employees table.
Set tdf = dbcurrent.TableDefs!Inventory
' Return Index object that points to new index.
Set idx = tdf.CreateIndex("LastSix")

' Create and append index fields.
Set fldLastSixOfVin = idx.CreateField("LastSix", dbText)
Set fldDlrNum = idx.CreateField("DlrNumber", dbText)

idx.Fields.Append fldLastSixOfVin
idx.Fields.Append fldDlrNum

tdf.Indexes.Refresh

' Open table-type Recordset object.
Set rsExistingInv = dbcurrent.OpenRecordset("Inventory")
Set rsTempInv = dbcurrent.OpenRecordset("TempInventory")
' Set current index to new index.
rsExistingInv.MoveFirst
rsTempInv.MoveFirst
rsExistingInv.Index = idx.Name

Do Until rsTempInv.EOF
rsExistingInv.MoveFirst

' Specify record to find.
Dim varBookmark As Variant
varBookmark = rsExistingInv.Bookmark
' I want the lastsix to get a match if and only if it falls under
the same dealer
rsExistingInv.Seek "=", rsTempInv.Fields("LastSix")

If rsExistingInv.NoMatch Then

rsExistingInv.AddNew
rsExistingInv!DlrNum = rsTempInv!DlrNum
rsExistingInv!VIN = Mid(rsTempInv!VIN, 1, 11)
rsExistingInv!LastSix = rsTempInv!LastSix
rsExistingInv!Year = rsTempInv!Year
rsExistingInv!Make = rsTempInv!Make
rsExistingInv!Model = rsTempInv!Model
rsExistingInv!Mileage = rsTempInv!Mileage
rsExistingInv!FlooredAmount = CDbl(rsTempInv!FlooredAmount)
End if
Loop

' What is wrong with my code?

VBA2VBZTechie

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!
Nov 13 '05 #1
Share this Question
Share on Google+
1 Reply


P: n/a
Unless my memory fails me, the _default_ is to open a Recordset as a Dynaset
(and Seek does not work on Dynasets). You'll need to specify in your
OpenRecordset statement to open it as Table type.

Even better, though, unless you are going to do a lot of searching while the
Recordset is open, would be to open a Dynaset on a Query / SQL statement
that included the search values in its WHERE clause. (Typically faster, and
certainly returns fewer records: one if it exists, none if it doesn't.)

Larry Linson
Microsoft Access MVP

"Average Bear" <av************@yahoo.com> wrote in message
news:41**********************@news.newsgroups.ws.. .
If anyone could help with this one, I am a bit puzzled. I understand
you can create an index using two fields of a database, then use the
seek method to find a record based on both fields. My code isn't
working. It finds the record but the record exists under a different
customer record. I want it only to find the record if both conditions
(customer, number) are met. Here is the code:

Dim dbs As Database, tdf As TableDef, idx As Index
Dim fldLastSixOfVin As Field, fldDlrNum As Field, rst As Recordset

Set dbcurrent = CurrentDB

' Return reference to current database.
Set dbcurrent = CurrentDB
' Return reference to Employees table.
Set tdf = dbcurrent.TableDefs!Inventory
' Return Index object that points to new index.
Set idx = tdf.CreateIndex("LastSix")

' Create and append index fields.
Set fldLastSixOfVin = idx.CreateField("LastSix", dbText)
Set fldDlrNum = idx.CreateField("DlrNumber", dbText)

idx.Fields.Append fldLastSixOfVin
idx.Fields.Append fldDlrNum

tdf.Indexes.Refresh

' Open table-type Recordset object.
Set rsExistingInv = dbcurrent.OpenRecordset("Inventory")
Set rsTempInv = dbcurrent.OpenRecordset("TempInventory")
' Set current index to new index.
rsExistingInv.MoveFirst
rsTempInv.MoveFirst
rsExistingInv.Index = idx.Name

Do Until rsTempInv.EOF
rsExistingInv.MoveFirst

' Specify record to find.
Dim varBookmark As Variant
varBookmark = rsExistingInv.Bookmark
' I want the lastsix to get a match if and only if it falls under
the same dealer
rsExistingInv.Seek "=", rsTempInv.Fields("LastSix")

If rsExistingInv.NoMatch Then

rsExistingInv.AddNew
rsExistingInv!DlrNum = rsTempInv!DlrNum
rsExistingInv!VIN = Mid(rsTempInv!VIN, 1, 11)
rsExistingInv!LastSix = rsTempInv!LastSix
rsExistingInv!Year = rsTempInv!Year
rsExistingInv!Make = rsTempInv!Make
rsExistingInv!Model = rsTempInv!Model
rsExistingInv!Mileage = rsTempInv!Mileage
rsExistingInv!FlooredAmount = CDbl(rsTempInv!FlooredAmount)
End if
Loop

' What is wrong with my code?

VBA2VBZTechie

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Nov 13 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.