Index

[Guest]

I would like to use the Seek function to find the details in a table and then
write it into a new record.

Private Sub ItemID_AfterUpdate()

Dim mydb As Database
Dim myset As Recordset
Dim ItemId As Integer
Dim CurrentPrice As Currency

Set mydb = CurrentDb()

'gets the value of the technicians name being sought
ItemId = InputBox("Enter the ItemID")

With rstItems
'Set the index."
myset.Index = "ItemID"
End With

'searches the recordset for the technician name inputted above
myset.Seek "=", [ItemId]

'if there is no match (the technician name is not found) then put up a
'messagebox
If myset.NoMatch Then
MsgBox ("ItemId not found")
Else

'open the form, go to the technician name field and find the record
specified 'above
[CurrentPrice] = myset!Price
Quantity.SetFocus

End If
End Sub

The problem comes up at setting the index. Any help would be appreciated.
Thankyou.

 

[Allen Browne]

Open the table in design view.
Open the Indexes box (View menu).
What is the Name of the index?
It may be different from the name of the field.
For example, the index might be named "PrimaryKey".

Please note that this code works only with local tables, not attached
tables. When you OpenRecordset() on a local table, it defaults to
dbOpenTable, whereas an attached table uses dbOpenDynaset. Since most
databases end up being split, it might be better to just open the recordset
with the record you desire:
strSql = "SELECT Price FROM Table1 WHERE ItemId = " & ItemId & ";"
Set myset = mydb.OpenRecordset(strSql)

Of course, for that simple example you could just use DLookup(), but that's
the idea.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

"(e-mail address removed)" <[email protected]>
wrote in message
news:[email protected]...