Solved

MS Access Loop Through Forms and write Captions to Table

Posted on 2014-11-17
5
243 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 36

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

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

The first two articles in this short series — Using a Criteria Form to Filter Records (http://www.experts-exchange.com/A_6069.html) and Building a Custom Filter (http://www.experts-exchange.com/A_6070.html) — discuss in some detail how a form can be…
Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

828 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