By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,657 Members | 862 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,657 IT Pros & Developers. It's quick & easy.

Could someone point me in the right direction to use DAO to list all forms, their textboxes and two properties for each textbox?

P: n/a
MLH
I would like to populate a table with the following information:

tblPropertySettings
[strFormName]
[strTextboxName]
[strOnGFprop] - the GotFocus property setting string
[strOnLFprop] - the LostFocus property setting string

I'd like to document the above info for all Forms and all Textbox
controls on each Form.

I would write it to debug window if it weren't for the fact that
there are too many lines & the immediate window's FIFO buffer
would roll excessive lines off the top. Therefore, I'm using a table.

Here's what I snatched from HELP so far...
Sub ListFields()
Dim dbs As Database, tdf As TableDef, fld As Field

' Return Database object variable pointing to current
database.
Set dbs = CurrentDb
' Return TableDef object variable pointing to Employees table.
Set tdf = dbs.TableDefs!Employees
' Enumerate fields in Employees table.
For Each fld In tdf.Fields
Debug.Print fld.Name
Next fld
End Sub

I think what I need must go inside the for-next loop. Just don't know
what it is to be.
Jan 20 '06 #1
Share this Question
Share on Google+
2 Replies


P: n/a
Assuming Access 2000 or later, this first example shows how to get the names
of all forms in the database:

Function ShowAllForms()
Dim accobj As AccessObject
For Each accobj In CurrentProject.AllForms
Debug.Print accobj.Name
Next
End Function

This next example shows how to get at all the text boxes on the form, by
opening it in design view (hidden). Clearly you can call this funtion in the
loop above, so it is called for every form in your database:

Function ShowTextBoxes(strFormName As String)
Dim frm As Form
Dim ctl As Control

DoCmd.OpenForm strFormName, acDesign, _
WindowMode:=acHidden
Set frm = Forms(strFormName)

For Each ctl In frm.Controls
If ctl.ControlType = acTextBox Then
Debug.Print ctl.Name
End If
Next

Set ctl = Nothing
Set frm = Nothing
DoCmd.Close acForm, strFormName
End Function

The final piece of your puzzle is to write these names into a table. Do do
that, open a recordset, and add new:
Dim rs As DAO.Recordset
Set rs = dbEngine(0)(0).OpenRecordset("tblPropertySettings" ,
dbOpenDynaset, dbAppendOnly)

rs.AddNew
rs!strFormName = strFormName
rs!TextboxName = ctl.Name
rs!stOnGFprop = ctl.OnGotFocus
rs!strOnLFprop = ctl.OnLostFocus
rs.Update

rs.Close

In practice, you will use the first 3 lines at the top of the ShowAllForms
code, and the last line at the end of that function. You will pass the
recordset variable to the ShowTextBoxes() procedure, and so the middle 6
lines will replace:
Debug.Print ctl.Name

Hope that all makes sense, step by step, and when you put it together.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"MLH" <CR**@NorthState.net> wrote in message
news:t6********************************@4ax.com...
I would like to populate a table with the following information:

tblPropertySettings
[strFormName]
[strTextboxName]
[strOnGFprop] - the GotFocus property setting string
[strOnLFprop] - the LostFocus property setting string

I'd like to document the above info for all Forms and all Textbox
controls on each Form.

I would write it to debug window if it weren't for the fact that
there are too many lines & the immediate window's FIFO buffer
would roll excessive lines off the top. Therefore, I'm using a table.

Here's what I snatched from HELP so far...
Sub ListFields()
Dim dbs As Database, tdf As TableDef, fld As Field

' Return Database object variable pointing to current
database.
Set dbs = CurrentDb
' Return TableDef object variable pointing to Employees table.
Set tdf = dbs.TableDefs!Employees
' Enumerate fields in Employees table.
For Each fld In tdf.Fields
Debug.Print fld.Name
Next fld
End Sub

I think what I need must go inside the for-next loop. Just don't know
what it is to be.

Jan 20 '06 #2

P: n/a
On Fri, 20 Jan 2006 08:04:54 -0500, MLH <CR**@NorthState.net> wrote:

This code is for tables and fields. What you need is forms and
controls.
Pseudo code:
for each form in the Docuements collection
open form in design view
for each control in the form's Controls collection
if TypeOf control is TextBox then
inspect properties
write to table
end if
next
close form
next

-Tom.

I would like to populate a table with the following information:

tblPropertySettings
[strFormName]
[strTextboxName]
[strOnGFprop] - the GotFocus property setting string
[strOnLFprop] - the LostFocus property setting string

I'd like to document the above info for all Forms and all Textbox
controls on each Form.

I would write it to debug window if it weren't for the fact that
there are too many lines & the immediate window's FIFO buffer
would roll excessive lines off the top. Therefore, I'm using a table.

Here's what I snatched from HELP so far...
Sub ListFields()
Dim dbs As Database, tdf As TableDef, fld As Field

' Return Database object variable pointing to current
database.
Set dbs = CurrentDb
' Return TableDef object variable pointing to Employees table.
Set tdf = dbs.TableDefs!Employees
' Enumerate fields in Employees table.
For Each fld In tdf.Fields
Debug.Print fld.Name
Next fld
End Sub

I think what I need must go inside the for-next loop. Just don't know
what it is to be.


Jan 20 '06 #3

This discussion thread is closed

Replies have been disabled for this discussion.