Syntax for referring to a Me.FormField w/ "FormField" code generat

[Leo]

Does anyone know the correct syntax for referring to a Me.FormField , when
"FormField" is generated by code?

I am building an audit form for our Quality Department with 10 cloned tabs
writing to 10 cloned tables. I saved the ADO code in a class module. However,
instead of calling the code 10 times, I decided to iterate through the form
field name 10 times programmatically.

Below is an example of what I am talking about. I have taken the code hidden
in the module and brought it out into the open.

Some additional factors:
1. The code runs perfectly and updates the recordset if I set the form
references manually instead of through code (i.e. Me.txtName1 instead of
Me.[strNameField]
2. When I turn off the error handling, the debug passes by the table name,
which is being assigned correctly.
3. In the immediate window, “? strNameField†does return “txtName1â€, which
is the control I am referring to.
4. Yet, when I run the code, I get an error message saying that field "|" is
an incorrect reference.

Any suggestions? I hate the thought of writing the Add code 10 times … and
the OnCurrent code to display the data 10 times … and the edit saved record
10 times …

Thanks!
Leo

Dim counter as Integer
Dim strFieldNumber as String
Dim strTableName as string
Dim steNameField as String
Dim rst as ADODB.Recordset
Set rst = New ADODB.Recordset

For counter = (1-10)

strFieldNumber = CString (counter)

strTableName = “tblAudit†& strFieldNumber
strNameField = “txtName†& strFieldNumber

With rst
.Connection = CurrentProject.Connection
.Open, “SELECT * from “ & TableName “;â€
.AddNew
!Name = Me.[strNameField]
.Update
.Close
End With
Set rst = Nothing
Exit Sub

 

[Leo]

I forgot the "Next Counter" in the code above ... but not in the real code.

For counter = (1-10)

strFieldNumber = CString (counter)

strTableName = “tblAudit†& strFieldNumber
strNameField = “txtName†& strFieldNumber

With rst
.Connection = CurrentProject.Connection
.Open, “SELECT * from “ & TableName “;â€
.AddNew
!Name = Me.[strNameField]
.Update
.Close
End With
Set rst = Nothing
Next Counter
Exit Sub

 

[Allen Browne]

Use:
Me(strNameField)

To loop the controls on the form:
Dim ctl As Control
For Each ctl in Me.Controls
Debug.Print ctl.Name
Next

To loop the fields in the form's source table/query:
Dim fld As DAO.Field
For Each fld in Me.RecordsetClone.Fields
Debug.Print fld.Name
Next

If you need to work from the controls and get to the field names, see the
code in this article:
http://allenbrowne.com/ser-56.html
It loops the controls, tests if they have a ControlSource property (e.g.
labels and lines don't), skips those bound to an expression (starts with
"=".) You can also eliminate fields that are calculated query expressions by
testing their SourceTable.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

I forgot the "Next Counter" in the code above ... but not in the real code.

For counter = (1-10)

strFieldNumber = CString (counter)

strTableName = “tblAudit†& strFieldNumber
strNameField = “txtName†& strFieldNumber

With rst
.Connection = CurrentProject.Connection
.Open, “SELECT * from “ & TableName “;â€
.AddNew
!Name = Me.[strNameField]
.Update
.Close
End With
Set rst = Nothing
Next Counter
Exit Sub

Does anyone know the correct syntax for referring to a Me.FormField ,
when
"FormField" is generated by code?

I am building an audit form for our Quality Department with 10 cloned
tabs
writing to 10 cloned tables. I saved the ADO code in a class module.
However,
instead of calling the code 10 times, I decided to iterate through the
form
field name 10 times programmatically.

Below is an example of what I am talking about. I have taken the code
hidden
in the module and brought it out into the open.

Some additional factors:
1. The code runs perfectly and updates the recordset if I set the form
references manually instead of through code (i.e. Me.txtName1 instead of
Me.[strNameField]
2. When I turn off the error handling, the debug passes by the table
name,
which is being assigned correctly.
3. In the immediate window, “? strNameField†does return “txtName1â€,
which
is the control I am referring to.
4. Yet, when I run the code, I get an error message saying that field "|"
is
an incorrect reference.

Any suggestions? I hate the thought of writing the Add code 10 times …
and
the OnCurrent code to display the data 10 times … and the edit saved
record
10 times …

Thanks!
Leo

Dim counter as Integer
Dim strFieldNumber as String
Dim strTableName as string
Dim steNameField as String
Dim rst as ADODB.Recordset
Set rst = New ADODB.Recordset

For counter = (1-10)

strFieldNumber = CString (counter)

strTableName = “tblAudit†& strFieldNumber
strNameField = “txtName†& strFieldNumber

With rst
.Connection = CurrentProject.Connection
.Open, “SELECT * from “ & TableName “;â€
.AddNew
!Name = Me.[strNameField]
.Update
.Close
End With
Set rst = Nothing
Exit Sub

 

[Leo]

Allen,

Perfect. Thanks for your help. I figured that I had a syntax problem, and
removing the "." was just what the doctor ordered!

Leo

PS. Love your Website. Wish I'd have found it earlier while I was learning
Access VBA. Would have helped immensly. Prior to last year, w/ the exception
of DHTML, the last line of code I wrote was in Pascal circa 1987!

Use:
Me(strNameField)

To loop the controls on the form:
Dim ctl As Control
For Each ctl in Me.Controls
Debug.Print ctl.Name
Next

To loop the fields in the form's source table/query:
Dim fld As DAO.Field
For Each fld in Me.RecordsetClone.Fields
Debug.Print fld.Name
Next

If you need to work from the controls and get to the field names, see the
code in this article:
http://allenbrowne.com/ser-56.html
It loops the controls, tests if they have a ControlSource property (e.g.
labels and lines don't), skips those bound to an expression (starts with
"=".) You can also eliminate fields that are calculated query expressions by
testing their SourceTable.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

I forgot the "Next Counter" in the code above ... but not in the real code.

For counter = (1-10)

strFieldNumber = CString (counter)

strTableName = “tblAudit†& strFieldNumber
strNameField = “txtName†& strFieldNumber

With rst
.Connection = CurrentProject.Connection
.Open, “SELECT * from “ & TableName “;â€
.AddNew
!Name = Me.[strNameField]
.Update
.Close
End With
Set rst = Nothing
Next Counter
Exit Sub

Does anyone know the correct syntax for referring to a Me.FormField ,
when
"FormField" is generated by code?

I am building an audit form for our Quality Department with 10 cloned
tabs
writing to 10 cloned tables. I saved the ADO code in a class module.
However,
instead of calling the code 10 times, I decided to iterate through the
form
field name 10 times programmatically.

Below is an example of what I am talking about. I have taken the code
hidden
in the module and brought it out into the open.

Some additional factors:
1. The code runs perfectly and updates the recordset if I set the form
references manually instead of through code (i.e. Me.txtName1 instead of
Me.[strNameField]
2. When I turn off the error handling, the debug passes by the table
name,
which is being assigned correctly.
3. In the immediate window, “? strNameField†does return “txtName1â€,
which
is the control I am referring to.
4. Yet, when I run the code, I get an error message saying that field "|"
is
an incorrect reference.

Any suggestions? I hate the thought of writing the Add code 10 times …
and
the OnCurrent code to display the data 10 times … and the edit saved
record
10 times …

Thanks!
Leo

Dim counter as Integer
Dim strFieldNumber as String
Dim strTableName as string
Dim steNameField as String
Dim rst as ADODB.Recordset
Set rst = New ADODB.Recordset

For counter = (1-10)

strFieldNumber = CString (counter)

strTableName = “tblAudit†& strFieldNumber
strNameField = “txtName†& strFieldNumber

With rst
.Connection = CurrentProject.Connection
.Open, “SELECT * from “ & TableName “;â€
.AddNew
!Name = Me.[strNameField]
.Update
.Close
End With
Set rst = Nothing
Exit Sub