Using strings in reference to form objects

[Chad Peacey]

Hello, I need some help with this one! I'm trying to get
a text box on a form to display a message related to a
field on the form. The message is in a different table.
My question is why I can't use strings to identify the
form control. I've included the code for this, but it
won't accept it. Access generates an error when the form
is run. Please help!

Sub Description_Refresh(TableName As String, FieldName1 As
String, FieldName2 As String, FormField As String,
RefNumber As String)
On Error GoTo Err_Description_Refresh

Dim strSQL As String
Dim rst As ADODB.Recordset
Set rst = New ADODB.Recordset

' Populate Appropriate Statement field in form Assessment
Plan Form
strSQL = "SELECT " & TableName & "." & FieldName2 & _
" FROM " & TableName & _
" WHERE (((" & TableName & "." & FieldName1 & ")='" &
RefNumber & "'));"
rst.Open strSQL, CurrentProject.Connection

******** THIS IS THE PROBLEM LINE *******
Forms![Assessment Plan Form].FormField = rst!FieldName2
*****************************************

rst.Close


Exit_Description_Refresh:
Exit Sub

Err_Description_Refresh:
MsgBox Err.Description & " " & Err.Number
Resume Exit_Description_Refresh

End Sub

Thank you for your help!

Chad Peacey

 

[Rick Brandt]

Hello, I need some help with this one! I'm trying to get
a text box on a form to display a message related to a
field on the form. The message is in a different table.
My question is why I can't use strings to identify the
form control. I've included the code for this, but it
won't accept it. Access generates an error when the form
is run. Please help! [snip]

******** THIS IS THE PROBLEM LINE *******
Forms![Assessment Plan Form].FormField = rst!FieldName2
*****************************************

Forms = rst!FieldName2

 

[MDW]

What is the error you're getting? Not quite sure what
specific problem your'e having, but looking at your code I
can suggest this change -

Replace this

Forms![Assessment Plan Form].FormField = rst!FieldName2

With this

FormField.SetFocus
FormField.Text = rst("FieldName2")

If the form this code is running on is [Assessment Plan
Form], you shouldn't have to qualify the name of the text
box.

-----Original Message-----
Hello, I need some help with this one! I'm trying to get
a text box on a form to display a message related to a
field on the form. The message is in a different table.
My question is why I can't use strings to identify the
form control. I've included the code for this, but it
won't accept it. Access generates an error when the form
is run. Please help!

Sub Description_Refresh(TableName As String, FieldName1 As
String, FieldName2 As String, FormField As String,
RefNumber As String)
On Error GoTo Err_Description_Refresh

Dim strSQL As String
Dim rst As ADODB.Recordset
Set rst = New ADODB.Recordset

' Populate Appropriate Statement field in form Assessment
Plan Form
strSQL = "SELECT " & TableName & "." & FieldName2 & _
" FROM " & TableName & _
" WHERE (((" & TableName & "." & FieldName1 & ")='" &
RefNumber & "'));"
rst.Open strSQL, CurrentProject.Connection

******** THIS IS THE PROBLEM LINE *******
Forms![Assessment Plan Form].FormField = rst!FieldName2
*****************************************

rst.Close


Exit_Description_Refresh:
Exit Sub

Err_Description_Refresh:
MsgBox Err.Description & " " & Err.Number
Resume Exit_Description_Refresh

End Sub

Thank you for your help!

Chad Peacey
.

 

[Rick Brandt]

Hello, I need some help with this one! I'm trying to get
a text box on a form to display a message related to a
field on the form. The message is in a different table.
My question is why I can't use strings to identify the
form control. I've included the code for this, but it
won't accept it. Access generates an error when the form
is run. Please help! [snip]

******** THIS IS THE PROBLEM LINE *******
Forms![Assessment Plan Form].FormField = rst!FieldName2
*****************************************

Forms = rst!FieldName2

Should be...

Forms = rst(FieldName2)