Solved

MS Access Loop Through Forms and write Captions to Table

Posted on 2014-11-17
5
235 Views
Last Modified: 2014-11-17
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
0
Comment
Question by:Mohamed Singh
  • 2
  • 2
5 Comments
 
LVL 34

Expert Comment

by:PatHartman
Comment Utility
    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
 

Author Comment

by:Mohamed Singh
Comment Utility
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
 
LVL 10

Expert Comment

by:Gozreh
Comment Utility
rs!ObjectCaption = D.caption
Make sure to use ! mark for fields inside a recordset.
0
 
LVL 10

Accepted Solution

by:
Gozreh earned 500 total points
Comment Utility
   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
 

Author Closing Comment

by:Mohamed Singh
Comment Utility
Hi Gozreh,
Many Thanks for your solution.
Kind Regard,
Mohamed
0

Featured Post

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

This article is a continuation or rather an extension from Cascading Combos (http://www.experts-exchange.com/A_5949.html) and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
Most if not all databases provide tools to filter data; even simple mail-merge programs might offer basic filtering capabilities. This is so important that, although Access has many built-in features to help the user in this task, developers often n…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …

772 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now