Urgent Help Needed with Lookup code...Please!!!
Your code is incomplete as I questioned in the previous thread. Also, since
you use A2K (which by default uses ADO Library, not DAO Library needed for
your code), you get the Compile error on the Dim db as Database as Database
is a DAO object, not ADO object. For more info., see the 'Articles'
section at
http://www11.ewebcity.com/brenreyn
However, if you only need to retrieve the *first* Title in the Table
[Resource] that starts with the user's input in the TextBox all you need is
the DLookUp function.
If you want all the Titles from the Table [Resource] that start with the
user's input, then you have to decide what to do with all the matched
Titles.
Your code looks like you want to retrieve Records using Recordset rather
than just the Titles. The code sets up the Recordset then abruptly ends
without doing anything with the Recordset. Basically, the code does nothing
for you except giving you Compile error above and potentially locking up the
PC memory.
Perhaps, you need to expand / explain what you want to do with the Title(s)
/ Records retrieved before respondents can help further.
HTH
Van T. Dinh
Quote:
> Can someone help me with the code below, or suggest a better way to do
what
> I am attempting to do? What I want to do is retrieve the titles that
match
> the one the user enters in txtTitleSearch when cmdTitleSearch is clicked.
I
> am not an Access developer I just happen to have to give support for a
> product created by someone else...so chances are the code I have here is
> incomplete. Here is what I have so far but I am getting syntax errors in
the
> SQL look-a-like piece of code...any help I can get on this would be
greatly
> appreciated. Thanks in advance.
> Private Sub cmdTitleSearch_Click()
> On Error GoTo Err_cmdTitleSearch_Click
> Dim db As Database
> Dim rs As Recordset
> Set db = CurrentDb()
> Set rs = db.OpenRecordset("SELECT OrderDetails.OrderID,
> OrderDetails.StandardNumber,
> OrderDetails.InvoiceNumber ,
> resource.Title, resource.Author,
> resource.PublicationDate ,
> resource.Edition
> FROM OrderDetails INNER JOIN
> Resource ON (Resource.ResourceID =
> OrderDetails.ResourceID)
> WHERE((Resource.Title) Like
'"
> & txtTitleSearch & "*');")
> Exit_cmdTitleSearch_Click:
> Exit Sub
> Err_cmdTitleSearch_Click:
> MsgBox Err.Description
> Resume Exit_cmdTitleSearch_Click
> End Sub
> J. Wilson
