MS Access Loop Through Forms and write Captions to Table

Hi,
I need to list the captions of  all forms to tblMyTable.ObjectCaption.
The name of the respective form should be written to tblMyTable.ObjectName.
Your assistance would be greatly appreciated.
Kind Regards,
Mohamed
Mohamed SinghAsked:
Who is Participating?
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.

PatHartmanCommented:
    Dim db As DAO.Database
    Dim td As DAO.TableDef
    Dim rs As DAO.Recordset
    Dim D As Document
    Dim C As Container
    Dim i As Integer

    Set db = CurrentDb()
    Set td = db.TableDefs!tblMyTable
    Set rs = td.OpenRecordset

            Set C = db.Containers("Forms")
            For Each D In C.Documents
                rs.AddNew
                    rs!ObjectCaption = D.Caption
                    rs!ObjectName = D.Name
                rs.Update
            Next D

    Set db = Nothing
    Set C = Nothing

Open in new window

0
Mohamed SinghAuthor Commented:
Hi,
Thank You PatHartman for that exceptionally speedy reply.
I ran the code and get Compile error: "Method or data member not found" at:
rs.ObjectCaption = D.caption
Intellisense lists D.name but not D.caption.
Your feedback would be appreciated.
Kind Regards,
Mohamed
0
GozrehCommented:
rs!ObjectCaption = D.caption
Make sure to use ! mark for fields inside a recordset.
0
GozrehCommented:
   Dim db As DAO.Database, td As DAO.TableDef, rs As DAO.Recordset
   Set db = CurrentDb()
   Set td = db.TableDefs!tblMyTable
   Set rs = td.OpenRecordset
         
   Dim frm As AccessObject, dbs As CurrentProject
   Set dbs = Application.CurrentProject
   For Each frm In dbs.AllForms
      DoCmd.OpenForm frm.Name, acDesign, , , , acHidden
      rs.AddNew
         rs!ObjectCaption = Forms(frm.Name).Caption
         rs!ObjectName = frm.Name
      rs.Update
      Debug.Print Forms(frm.Name).Caption
      DoCmd.Close acForm, frm.Name
   Next frm

   Set db = Nothing

Open in new window

0

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
Mohamed SinghAuthor Commented:
Hi Gozreh,
Many Thanks for your solution.
Kind Regard,
Mohamed
0
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.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.