Change "lookUp Method" to code used in a linked table

[jbsfe]

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

[Tom van Stiphout]

On 4 Jan 2006 19:43:36 -0800, "jbsfe" <jb***@earthlink.net> wrote:

Too much code for me to read through right now, but if you want to use
the Seek method (highly recommended because faster than any other
recordset method), you'll need to open a database object on the
back-end:
set dbBE = dbengine.opendatabase("c:\backend.mdb") 'recommend to keep
this open for the duration of your session.
set rs = dbBE.OpenRecordset("sometable", dbOpenTable, dbReadonly)
'firehose cursor
rs.Index = "somekey"
rs.Seek "=", "somevalue"
if not rs.Nomatch then
'record found.

-Tom.

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

[Terry Kreft]

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

[Jimmy Bates]

Tom, Terry, Thaks for the help, it's much appreciated.
Jimmy

*** Sent via Developersdex http://www.developersdex.com ***