Lookup a record in another table

  • Thread starter Thread starter Lookup a record in another table
  • Start date Start date
L

Lookup a record in another table

In a code segment, I need to retrieve a record in another
table based on a field value and retrieve several field
values from that record. Can anyone please supply a sample
code segment? Thanks!

Howard
 
Dim qry As DAO.QueryDef
Dim rst As DAO.Recordset
Dim strVal1 As String, strVal2 As String, strVal3 As String

Set qry = CurrentDb.CreateQueryDef("", "SELECT Field1, " _
& "Field2, Field3 FROM tblName WHERE Field4 = " _
& <something>)
Set rst = qry.OpenRecordset

strVal1 = rst("Field1")
strVal2 = rst("Field2")
strVal3 = rst("Field3")

rst.Close
Set rst = Nothing

hth
 
On the statement ...

Dim qry As DAO.QueryDef

I get the error message ...

User defined type not defined

Any suggestions? Thanks!

- Howard
 
Sounds as though you don't have a reference set to DAO. (You must be using
Access 2000 or 2002, neither of which set the reference by default)

With any code module open, select Tools | References from the menu bar,
scroll through the list of available references until you find the one for
Microsoft DAO 3.6 Object Library, and select it.
 
You'll need to add a reference to DAO ("Microsoft DAO v3.x Object
Library").

Click Tools > References from any module in design mode, and put a
check mark next to the above named library.

Hope this helps,

Peter De Baets
Peter's Software - MS Access Tools for Developers
http://www.peterssoftware.com
 
Thanks, that got me by that error. Now I'm getting an
error that states it's expecting 2 parameters for the
statement ...

Set rst2 = qry.OpenRecordset

Your help is again appreciated. Thanks!

-Howard
 
Thanks, that got me by that error. Now I'm getting an
error that states it's expecting 2 parameters for the
statement ...

Set rst2 = qry.OpenRecordset

Your help is again appreciated. Thanks!

-Howard
 
Dim qry As DAO.QueryDef
Dim rst As DAO.Recordset
Dim prm As DAO.Parameter
Dim strVal1 As String, strVal2 As String, strVal3 As String

Set qry = CurrentDb.CreateQueryDef("", "SELECT Field1, " _
& "Field2, Field3 FROM tblName WHERE Field4 = " _
& <something>)

For Each prm in qry.Parameters
prm.Value = Eval(prm.Name)
Next prm

Set rst = qry.OpenRecordset

strVal1 = rst("Field1")
strVal2 = rst("Field2")
strVal3 = rst("Field3")

rst.Close
Set rst = Nothing

hth
 
Try:


dim rstRec as dao.RecordSet


set rstRec = currentdb.OpenRecordSet("select field1, field2, field3 from
tblCustomers where id = 123")

msgbox "field1 is " & rstRec!Field1

You don't need to bother with the querydef at all....
 
Worked Nicely ... Thanks!

- Howard
-----Original Message-----
Dim qry As DAO.QueryDef
Dim rst As DAO.Recordset
Dim prm As DAO.Parameter
Dim strVal1 As String, strVal2 As String, strVal3 As String

Set qry = CurrentDb.CreateQueryDef("", "SELECT Field1, " _
& "Field2, Field3 FROM tblName WHERE Field4 = " _
& <something>)

For Each prm in qry.Parameters
prm.Value = Eval(prm.Name)
Next prm

Set rst = qry.OpenRecordset

strVal1 = rst("Field1")
strVal2 = rst("Field2")
strVal3 = rst("Field3")

rst.Close
Set rst = Nothing

hth





.
Click to expand...
 
I have previously used Dlookup in form fields (text boxes and others) to
accomplish this. Is this method more efficient?

Steve
 
well, it depends on what you're doing. Howard's original post said he needed
*several* values from the specific record in the other table. it seemed
easier to me to pull the record once and have access to all the necessary
values, than to pull it separately for each value using a domain aggregate
function. but that was just my take on the situation. so was using a
QueryDef; actually Albert's solution is probably easier than creating a
QueryDef on-the-fly. using a QueryDef would probably make more sense if you
wanted to use an existing query object, not a SQL string.
 
I think the *several* is the tip here. I, too, have <several> values to
look up, and have been impressed by the slowness of dlookup. I think I have
a little re-writing to do!!! <g>
Thanks
Steve
 
Stephen Rockower said:
I have previously used Dlookup in form fields (text boxes and others) to
accomplish this. Is this method more efficient?

Steve
Click to expand...

Some have said they get a lot better performance with recordsets then the
dlookup.

I usually find them about the same. However, in my example, you can grab
MORE then one field.

If you JUST need one field..then likely dlookup, and my example will not
make much difference. However, if you need to grab 3, or 4 fields..then my
example is FAR better..since you only have ONE hit on the database:

dim rstRec as dao.RecordSet
set rstRec = currentdb.OpenRecordSet("select field1, field2, field3" & _
" from tblCustomers where id = 123")
msgbox "field1 is " & rstRec!Field1
msgbox "field2 is " & rstRec!Field2
msgbox "field3 is " & rstRec!Field3

So, the advantage of above is that I don't need 3 dlookups.
 
Back
Top