472,804 Members | 1,148 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 472,804 software developers and data experts.

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

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

Jan 5 '06 #1
3 2368
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


Jan 5 '06 #2
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

Jan 5 '06 #3
Tom, Terry, Thaks for the help, it's much appreciated.
Jimmy

*** Sent via Developersdex http://www.developersdex.com ***
Jan 8 '06 #4

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

1
by: s o | last post by:
Hi, I'm creating a xml schema for my existing database. I've about a dozen lookup tables, such as States, Unit of Measures, etc. I figured the most logical way to represent them is to create...
2
by: hedrew3 | last post by:
I have a query in of the form "select * from mytable where myfield is like '*ABC*' or like '*DEF*' or like "*GHI*'". The problem is that the list of items is growing, and I would like to place...
4
by: John Phelan | last post by:
I have created a front-end application and back-end database. I have code that automatically links the front-end application to the back-end database for whenever I provide a patch or upgrade....
11
by: Paul H | last post by:
Suppose I have a table called tblPeople and I want a field to illustrate whether each person prefers cats or dogs. I could do it one of three ways. 1. A plain text field Create a text field in...
2
by: JHNielson | last post by:
I am trying to see is there is a way to lookup and update a field based on matching part of the contents of a field to a lookup table. Example. I have an "Error Description" field. And it adds...
2
by: dave | last post by:
Hi, I have searched for the answer for this error message without success. I have seen the question many times though:) I create an ASP.NET project (VS 2005, C#), and use a very simple .mdf...
4
by: TinaF | last post by:
I want to set a default value in a table called "Quotes" for a field called "Source." The "Source" field uses a lookup table called "Source" which looks like this: Sourceabbrev Sourcename...
12
blazedaces
by: blazedaces | last post by:
Hello again. I'm trying to take as an input an ArrayList<String> and utilize String's .spit(delimiter) method to turn that into a String. I'm getting some kind of error though (I'll post the code...
11
by: gautamga | last post by:
Hi All i have created the script which calls method from other script lib and while executing i get and error Can't call method "prepare" on an undefined value at...
0
by: erikbower65 | last post by:
Using CodiumAI's pr-agent is simple and powerful. Follow these steps: 1. Install CodiumAI CLI: Ensure Node.js is installed, then run 'npm install -g codiumai' in the terminal. 2. Connect to...
0
by: erikbower65 | last post by:
Here's a concise step-by-step guide for manually installing IntelliJ IDEA: 1. Download: Visit the official JetBrains website and download the IntelliJ IDEA Community or Ultimate edition based on...
0
by: kcodez | last post by:
As a H5 game development enthusiast, I recently wrote a very interesting little game - Toy Claw ((http://claw.kjeek.com/))。Here I will summarize and share the development experience here, and hope it...
2
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Sept 2023 starting at 18:00 UK time (6PM UTC+1) and finishing at about 19:15 (7.15PM) The start time is equivalent to 19:00 (7PM) in Central...
0
by: Taofi | last post by:
I try to insert a new record but the error message says the number of query names and destination fields are not the same This are my field names ID, Budgeted, Actual, Status and Differences ...
0
by: Rina0 | last post by:
I am looking for a Python code to find the longest common subsequence of two strings. I found this blog post that describes the length of longest common subsequence problem and provides a solution in...
0
by: lllomh | last post by:
Define the method first this.state = { buttonBackgroundColor: 'green', isBlinking: false, // A new status is added to identify whether the button is blinking or not } autoStart=()=>{
0
by: Mushico | last post by:
How to calculate date of retirement from date of birth
2
by: DJRhino | last post by:
Was curious if anyone else was having this same issue or not.... I was just Up/Down graded to windows 11 and now my access combo boxes are not acting right. With win 10 I could start typing...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.