Problems accessing records dynamically

[choward]

Hi Guys, can someone tell me what's wrong with this code?

em_name = DLookup("[MODE]", "[SPM Entry Mode Filter]", "[MODE ID] = " & em_no)
        
            If IsNull(("source![" & em_name & "]")) Then
                LOADING = 0
            Else
                LOADING = ("source![" & em_name & "]")
            End If

basically, the recordset associated to 'source' is a table - but as the table is generated in a previous step and can have different numbers of columns each time, i can't explicitely state which column to lookup.....

thanks!

Chris

 

[ajetrumpet]

what's going on with the code Choward?? getting an error message? if so, where is the error? If not, what is the problem??

Does it tell you that an object is required? If so, have you appended the previously created table to the tabledefs collection?? Have you declared and set the recordset variable??

I can only make a million assumptions here because the snippet of code you posted leaves out the best parts. But, it should also be noted that there are a few other possible syntax errors present, but it's impossible to know which ones are relevant without answering my questions...

 

[Guus2005]

If IsNull(("source![" & em_name & "]")) Then

This if statement gives you null if em_name is null. if it has a value e.g. "choward", it will never be null because the source!choward is never evaluated, it is a string "source!choward". IsNull("source!choward") is never true.
You have to evaluate "source!choward". You can probably do that by using the Eval function.

HTH

 

[namliam]

If IsNull(("source![" & em_name & "]")) Then

This if statement gives you null if em_name is null. if it has a value e.g. "choward", it will never be null because the source!choward is never evaluated, it is a string "source!choward". IsNull("source!choward") is never true.
You have to evaluate "source!choward". You can probably do that by using the Eval function.

HTH

You are right Guus, just want to add...

If source is a table
Source.fields(em_name)
Will return your value for you.... Simular syntax is possible for forms.

Using a DLookup should be your last resort really... Is there not a better way to do this?