dlookup

  • Thread starter Thread starter iccsi
  • Start date Start date
I

iccsi

I use dlookup to find a specific data which is very useful.

I just wonder that are there any way to retrieve 2 or more fields in
the same table.
I use 2 or more dlookup which seems not efficient.

Your information is great appreciated,
 
You can open a recordset to retrieve them:

Dim rsLookup As DAO.Database
Dim strSQL As String

strSQL = "SELECT Field1, Field2, Field3 " & _
"FROM SomeTable " & _
"WHERE ID = " & Me.SomeControl
Set rsLookup = CurrentDb.OpenRecordset(strSQL)
If rsLookup.EOF = False Then
Me.Text1 = rsLookup!Field1
Me.Text2 = rsLookup!Field2
Me.Text3 = rsLookup!Field3
End If
rsLookup.Close
Set rsLookup = Nothing

Alternatively, you can take advantage of the fact that you can return
multiple values with a single DLookup. You just have to cheat a little...

For example,

strValue = DLookup("[Field1] & ';' & [FIeld2] & ';' & [Field3]",
"SomeTable", "ID = " & Me.SomeControl)

will put the 3 values (separated by semi-colons) into strValue. You could
then use the Split function to get at the individual values.
 
You can open a recordset to retrieve them:

Dim rsLookup As DAO.Database
Dim strSQL As String

  strSQL = "SELECT Field1, Field2, Field3 " & _
    "FROM SomeTable " & _
    "WHERE ID = " & Me.SomeControl
  Set rsLookup = CurrentDb.OpenRecordset(strSQL)
  If rsLookup.EOF = False Then
    Me.Text1 = rsLookup!Field1
    Me.Text2 = rsLookup!Field2
    Me.Text3 = rsLookup!Field3
  End If
  rsLookup.Close
  Set rsLookup = Nothing

Alternatively, you can take advantage of the fact that you can return
multiple values with a single DLookup. You just have to cheat a little...

For example,

  strValue = DLookup("[Field1] & ';' & [FIeld2] & ';' & [Field3]",
"SomeTable", "ID = " & Me.SomeControl)

will put the 3 values (separated by semi-colons) into strValue. You could
then use the Split function to get at the individual values.

--
Doug Steele, Microsoft Access MVPhttp://I.Am/DougSteele
(no private e-mails, please)




I use dlookup to find a specific data which is very useful.
Click to expand...
I just wonder that are there any way to retrieve 2 or more fields in
the same table.
I use 2 or more dlookup which seems not efficient.
Click to expand...
Your information is great appreciated,- Hide quoted text -
Click to expand...

- Show quoted text -
Click to expand...

Thanks millions,
 
Back
Top