getting a record

[Jean-Paul]

Hi,

Until now, to get a specific record I wrote folliwing code:

Dim db As Database
Dim TB As Recordset
Dim SQL As String
Set db = CurrentDb()
SQL = "SELECT Setup.omschrijving, Setup.detail FROM Setup WHERE
Setup.omschrijving='gebruiker';"
Set TB = db.OpenRecordset(SQL)
MP_Setup.gebruiker = TB!detail

This works perfectly but I'm sure there must be a simpler way....

Can somebody explain a better way?
Thanks

 

[Allen Browne]

That's fine.

If you just wanted one value, you could use DLookup().
For help with the syntax, see:
http://allenbrowne.com/casu-07.html

 

[Albert D. Kallal]

Hi,

Until now, to get a specific record I wrote folliwing code:

Dim db As Database
Dim TB As Recordset
Dim SQL As String
Set db = CurrentDb()
SQL = "SELECT Setup.omschrijving, Setup.detail FROM Setup WHERE
Setup.omschrijving='gebruiker';"
Set TB = db.OpenRecordset(SQL)
MP_Setup.gebruiker = TB!detail

This works perfectly but I'm sure there must be a simpler way....

Can somebody explain a better way?
Thanks

you can use:

MP_Setup.gebruiker("detail","SetUp","omschriving = 'gebruiker'")

and, you could use in code

dim TB as recordSet

set tb = currentdb.OpenRecordSet("SELECT omschrijving,detail FROM
Setup WHERE Setup.omschrijving='gebruiker'")
MP_Setup.gebruiker = TB!detail

So, you really don't have to define so many variblaes here........

I suppose for ease of reading, I would dim a sql var, but that give us:

dim TB as recordSet
dim SQL as string

SQL = "SELECT detail FROM Setup WHERE omschrijving='gebruiker'"
set tb = currentdb.OpenRecordSet(SQL)
MP_Setup.gebruiker = TB!detail


So, you can pair down the amout of code you writing, but dlookup is only one
line....

= klookup("fieldName","TableName","where")