MS Access 2013: change form page caption programmatically

MS Access: I have two tables (Table1 and Table2) and a form (Form1) which is bound to the contents of Table1. Form1 has some pages which are labeled. I want to be able to rename each of the page label captions based on values stored in Table2 using SQL (for example, user1 might see one set of captions for pages 1, 2, and 2, where user2 might see another set of captions for the same pages -- the on load event for the form would know the user ID and be able to reference Table2 for the caption values). The page will always have a pre-existing caption to change, so no null values.

I'm guessing DAO is the way to go to get the values but I'm having trouble with the syntax, and also not sure how to marry the value up with the caption-change code.
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

crystal (strive4peace) - Microsoft MVP, AccessRemote Training and ProgrammingCommented:
assuming you have tables something like this:

CapSets (Caption Sets)
- CapSetID, AutoNumber, PK (Primary Key)
- CapSetName, text -- language or whatever

- FormID, AutoNumber, PK (Primary Key)
- FrmName, text, Description = Form Name

- CtrlID, AutoNumber, PK (Primary Key)
- FormID, Long Integer, FK (Foreign Key to Formz)
- CtrlName, text, description = control name

- UsrID, AutoNumber, PK (Primary Key)
- UsrName, text
- CapSetID, Long Integer, FK (Foreign Key to CapSets) -- what caption set this user uses

- CapID, AutoNumber, PK (Primary Key)
- CapSetID, Long Integer, FK (Foreign Key to CapSets)
- CtrlID, Long Integer, FK (Foreign Key to Controlz)
- Captn, text -- caption to show for this caption set

Then, when the form is opened, you would know the CapSetID because you would know which user is opening it.  You can then loop through the controls and redefine the caption for them.  Personally, I would also set StatusBarText for bound controls too -- but will just show changing captions for labels and other controls that have them and are defined in the Controlz and Captionz tables.

   On Error GoTo Proc_Err
'151113, strive4peace

   dim sSQL as string _
     , nFormID as long _
     , nCapSetID as long

   dim ctl as control _
      , db as dao.database _
      , rs as dao.recordset

   nFormID = 1 'or whatever for this form
   nCapSetID = ... '-- figure this out from user settings

   set db = currentdb

   for each ctl in me.controls

      sSQL = "SELECT Captn FROM Captionz INNER JOIN Controlz ON Controlz.CtrlID = Captionz.CtrlID " _
          & "  WHERE Controlz.FormID = " & nFormID _
          & " AND Captionz.CapSetID = " & nCapSetID _
          & " AND Controlz.CtrlName = """ & & """""

       set rs = db.openrecordset(sSQL, dbopensnapshot)

      with rs
         if not .eof then
           ctl.caption = !Captn 
         end if
      end with

   next ctl

   set rs = nothing
   set db = nothing

   On Error Resume Next
   'release object variables 
   set ctl = nothing
   if Not rs is Nothing then
      set rs = Nothing
   end if
   Set db = Nothing
   Exit sub
   MsgBox Err.Description, , _
        "ERROR " & Err.Number _
        & "   ProcedureName "  'replace with YOUR Procedure Name 

   Resume Proc_Exit

Open in new window

There are other ways to do this but I didn't want it to get any more complicated.

you can populate information in Formz and Controlz using the free Analyzer for Microsoft Access here:

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
alfamikefoxtrotAuthor Commented:
Hi Crystal -- thanks for the direction. Let me give this a shot, but it looks very promising.
alfamikefoxtrotAuthor Commented:
Thanks for the completeness of the solution.....!
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and ProgrammingCommented:
you're welcome ;) ~ happy to help
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.