access vba - Global Variable Lookup -

hopefully, i'll make question precise , understandable possible - you'll tell me if don't ! in advance.

firstly, little background , i've found work, on small change cannot work. rather use whole code, i've used snippets should give enough information understand have initiated things correctly first.

i use menu system (a turbo-charged version of original ms one) has additional fields store information needed make changes depending upon wording user wants use, may name field product, whereas user may want call goods or items or stuff or whatever desires! store user preferences in separate table (we'll call tblwords). when menu populated (remember operates in similar fashion standard switchboard) data fields: itemtext, command & argument menu displays text itemtext, use. but, have added new field called caption in switchboard table because vba code not allow formatting labels want them. so, when vba code reads itemtext label, recordset, , encounters | (pipe), added vba code looks field caption string. hopefully, enough background info!!??

[switchboard].[caption] contained following: (everything included)
"" & dlookup("fldproduct","tblwords") & ""

this worked perfectly!! but...

instead of performing lookup every time need word, decided create global variables, have global variable of glproduct, obtains word tblwords table correctly , retains fine. available throughout session anywhere.

i have substituted string above read global variable instead of performing lookup each time, to: (again, included)
"" & glproduct & ""

so, code follows:

while (not (rs.eof))     me("option" & rs![itemnumber]).visible = true 'my pipe deviation     if left(trim(rs![itemtext] & ""), 1) = "|" 'this line works fine 'displays correctly (rs!caption = "" & dlookup("fldproduct","tblwords") & "")         sztemp = dlookup(rs![caption], "tblwords") 'as (but, i'm explicitly naming variable in code! entered line show variable working!)         sztemp = "" & glbproduct & "" 'this (just byref function testing)         sztemp = fngetvalue(dlookup(rs![caption], "tblwords"))         me("option" & rs![itemnumber]).caption = sztemp     else         if rs![itemnumber] = 0             me("optionlabel" & rs![itemnumber]).caption = vba.trim(rs![itemtext] & " (" & rs![switchboardid] & ")")             me("optionlabel" & rs![itemnumber]).visible = true             me("option" & rs![itemnumber]).visible = false         else             me("option" & rs![itemnumber]).caption = vba.trim(rs![itemtext] & "")         end if     end if     rs.movenext wend 

revised to:

while (not (rs.eof))     me("option" & rs![itemnumber]).visible = true 'my pipe deviation     if left(trim(rs![itemtext] & ""), 1) = "|" 'this works (but, i'm explicitly naming variable in code! entered line show variable working!)         sztemp = "" & glbproduct & "" 'this not (fyi: rs!caption = "" & glbproduct & "")         sztemp = rs!caption 'nor (just byref function testing)         sztemp = fngetvalue(rs!caption)         me("option" & rs![itemnumber]).caption = sztemp     else         if rs![itemnumber] = 0             me("optionlabel" & rs![itemnumber]).caption = vba.trim(rs![itemtext] & " (" & rs![switchboardid] & ")")             me("optionlabel" & rs![itemnumber]).visible = true             me("option" & rs![itemnumber]).visible = false         else             me("option" & rs![itemnumber]).caption = vba.trim(rs![itemtext] & "")         end if     end if     rs.movenext wend 

what displayed literal string entered ("" _glproduct "") not product prior revision!

so... first problem you're confusing you're doing , it's happening - did rs![caption] come from???

the second problem can't access global variables in form or control properties.

you need set caption via code. i'm going make big guesses you're using , each element is, let's start here:

first
replace caption property else - "caption" example.

second

add code after loop - think work - hard know because don't show code , when runs

[switchboard].caption = "" & glproduct & ""