VBA syntax Help

[Osiris]

Hello all,

I'm having a problem with a piece of VB code in a program that I am trying to create for work.

Basically I am trying to write a function that will write information out of a table to various fields on a form. I plan to call this function from the On_Change event handler of each field. The following piece of code is where I'm having difficulty.

With tbl2
.Find strWoNum
If .EOF = True Then
MsgBox "That is not a valid Work Order Number", vbCritical, "ERROR"
DoCmd.DeleteObject acTable, "WoInfo"
Else:
TimeCard!["strText1"] = tbl2("EQNUM")
TimeCard!["strText2"] = tbl2("TASKDESC")
tbl2.Close
End If
End With

The problem is with the bold code. I can not get access to recognize that "strText1" is a variable pointing to the name of a field on the form. It wants to take everything inside the brackets as the field name. Is there any way I can get around this??

If you can give me any help at all it will be GREATLY appriciated...

Thank you

 

[Travis]

The Syntax for Identifing a field on a form is:

Forms![FormName].[Field Name]

Forms!TimeCard.strText1


The Syntax for Identifing a Field in a recordset:

[RecordSetObject]![FieldName]

tbl2!EQNUM

or

[RecordSetObject].Fields("FieldName")

tbl2.Fields("EQNUM")

Try This:

Forms!TimeCard.strText1=tbl2!EQNUM

 

[Osiris]

The only problem is that strText1 is not the field on the form. strText1 is a string variable that is holding the name of the field on the form(txtFac1) that I am trying to currently write to. I need it that way so I can call this function from several different fields and store the actual field name within strText1 so the function will write to the correct field on the form.

I will post all of the code for this specific function that I am trying to do. I'm not real good at explaining it, but mabey this will give you a better idea of what I am tryin to do.

This is attached to the fields LostFocus event handler.

Private Sub txtWo_1_LostFocus()
strText1 = "txtFac1"
strText2 = "txtDesc1"
strLookup = Me.txtWo_1
strQuery = "WoInfo1"

Call WOInfo(strLookup, strQuery, strText1, strText2)

End Sub

This is the module where the problem lies..

Function WOInfo(strLookup, strQuery, strText1, strText2)

Set TimeCard = Forms![Time_Card]
Set tbl2 = New ADODB.Recordset
Set cnn2 = CurrentProject.Connection

DoCmd.OpenQuery strQuery, acNormal
tbl2.Open "WoInfo", cnn2, adOpenDynamic, adLockOptimistic
strWoNum = "WONUM = '" & strLookup & "'"

With tbl2
.Find strWoNum
If .EOF = True Then
MsgBox "That is not a valid Work Order Number", vbCritical, "ERROR"
tbl2.Close
Set tbl2 = Nothing
cnn2.Close
Set cnn2 = Nothing
DoCmd.DeleteObject acTable, "WoInfo"
Else:
TimeCard!["strText1"] = tbl2("EQNUM")
TimeCard!["strText2"] = tbl2("TASKDESC")
tbl2.Close
End If
End With

End Function

I hope you can make sense of this..I really appriciate your help,

Thank you very much

 

[WayneRyan]

Osiris,

If you are on the form:
Me.Controls(strFieldName)

If you are not on the form, but it is open:
Forms![YourForm].Controls(strFieldName)

hth,
Wayne

 

[Osiris]

Worked Perfectly. THANK YOU!

heh should have known it would be something rather easy.

Thank you Very Very much.

Osiris