Lookup tbl value based on variable set on form

[cjLiteStep]

by way of a form, i obtain a string(strField) value equal to the field name (i.e. [MemNoBook]) in a lookup table ([tblCourseFees]) ... i need to find the value of this field based on the [recordID] in the table. the table is in the open database, but is not open itself.
i don't understand the structure to access this table information
appreciating any suggestions

cj

 

[ghubbell]

Hi cj,
What you should probably use is the "Dlookup" Function:

= DLookup("[MemNoBook]", "tblCourseFees", "[RecordID] = Forms![NameOfForm]![YourFieldRecordID]&quot

Poor example however hit F1 and search Accesshelp for lots more! Gord
ghubbell@total.net

 

[cjLiteStep]

that looks like the ticket alright
thanks gord

 

[tigi1]

Gord
Hope you saw the posting on the reports forum.

I saw your 'Dlookup' solution here and it just reminded me of a problem I had faced.
I have to get multiple fields out of a table to update labels in a form. I do the 'dlookup' business about sixtimes in every record for six fields.
Is there a better and faster method such as getting the whole record out and then updating the fields in the form ?
Thanks
Tigi

 

[ghubbell]

Hi Tigi, you thru this in just to stall me on a solution for that other devil right?! If you're updating labels on a form for a language change or other, or if its text fields, I usually make some sort of VB function that can handle the chore. This is assuming all the fields are from the same table: Yes, pull the whole record. Let me know if you need an example... If you have to go and poke at 6 different tables, I don't believe you would be better off with anything other than "Domain Aggregate Functions"... Gord
ghubbell@total.net

 

[tigi1]

Hi Gord
No,no, any case you've given me a fast solution to the top value problem.
As for this, its not 6 tables, rather 6 fields in a table. I pull them out one by one using dlookup 6 times.
Its like this.
I have a table 't1' with daily data. I open up a form which has a different table 't2' as source. t2 is to be updated on some fields from 't1'. So i look around for a particurlar item in 't1' using the dlookup. when found , its current status and 5 other fields are to be taken out for updating.
problem is
The table to 'dlookup' is huge(50,000+). Is there a faster way to get the 6 values out in one go than 'dlookup' sixtimes?
sure this will not require helmets and aspirins for you (me , maybe yes) !
I'd very much like to have your example.
Thanks
Tigi
tigilal@hotmail.com