Solved

MS Access Loop Through Forms and write Captions to Table

Posted on 2014-11-17
5
242 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 35

Expert Comment

by:PatHartman
ID: 40448119
    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
ID: 40448263
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
ID: 40448372
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
ID: 40448397
   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
ID: 40448966
Hi Gozreh,
Many Thanks for your solution.
Kind Regard,
Mohamed
0

Featured Post

Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
This is Part 3 in a 3-part series on Experts Exchange to discuss error handling in VBA code written for Excel. Part 1 of this series discussed basic error handling code using VBA. http://www.experts-exchange.com/videos/1478/Excel-Error-Handlin…

777 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