Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 252
  • Last Modified:

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

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now