You can use the Seek method if you open the table from th database it is
defned in.
So you could use something like the following code to get a reference to the
database and teh source table name.
Function ConnectDB(ByRef TableName As String) As DAO.Database
' Note TableName is used to return the SourceTablename
' and it's therefore necessary to use a variable in the
' calling procedure in order to capture this
Dim currDb As DAO.Database
Dim loTab As DAO.TableDef
Dim strConnect As String
Const DATABASE_TAG = "DATABASE="
Set currDb = CurrentDb
Set loTab = currDb.TableDefs(TableName)
strConnect = loTab.Connect
If Len(strConnect) > 0 Then
strConnect = Trim(Mid(strConnect, InStr(strConnect, DATABASE_TAG) +
Len(DATABASE_TAG)))
Set ConnectDB = OpenDatabase(strConnect)
TableName = loTab.SourceTableName
Else
Set ConnectDB = currDb
End If
End Function
and a sample call as follows
Function TestConnectDB()
Dim strTabName As String
Dim db As DAO.Database
Dim rs As DAO.Recordset
strTabName = "CS3Clicks"
Set db = ConnectDB(strTabName)
Set rs = db.OpenRecordset(strTabName)
' At this point strTabName is actually "_CS3Clicks"
' which is the name of th source tale.
With rs
.Index = "PrimaryKey"
.Seek "=", 10
MsgBox .Fields("Description")
.Close
End With
Set rs = Nothing
db.Close
Set db = Nothing
End Function
--
Terry Kreft
"jbsfe" <jb***@earthlink.net> wrote in message
news:11**********************@g47g2000cwa.googlegr oups.com...
I have "Spilt" my database and the "lookup" and "seek" methods that
previously worked, no longer do. I have learnd from reviewing the
posts that the "lookup" and "Seek" methods cannot be used on linked
tables and that you cannot set an "index" for a linked table.
What I don't know how to do, is revise my code to complete the same
tasks as it it did before I split the database. I'm sure the problem
lies is this block of code:
'Define the index to search, then seek the LookFor value.
TBLProducts.Index = "PrimaryKey"
TBLProducts.Seek "=", LookFor
I've included the entire "AfterUpdat" event for reference.
Following is the code I used prior to the split.
Define DAO object variables
Set ThisDB = CurrentDb()
Set TBLProducts = ThisDB.OpenRecordset("TBLProducts")
'if the item number field is blank, Beep and move focus the description
field
If IsNull([ItemNumber]) Then
DoCmd.Beep
DoCmd.GoToControl "Description"
Else
'Isolate the first five characters in the ItemNumber field.
LookFor = Left([ItemNumber], 5)
End If
'If not found, beep and move focus to the Description control on this
form.
If TBLProducts.NoMatch Then
DoCmd.Beep
DoCmd.GoToControl "Description"
Else
'if a matching Item Number is found, fill Description, PDCCost and
RetailCost
'fields then move focus to the Quantity control.
[Description] = TBLProducts!Description
[PDCCost] = TBLProducts!PDCCost
[RetailCost] = TBLProducts!RETAIL
DoCmd.GoToControl "Quantity"
End If
End Sub
Any help would be greatly appreciated.
Jimmy
