Syntax problem

[GaelicFatboy]

I’m looking to write a common piece of code to get data from a table using the following code:

Private Sub MySub_DataFind(My_TblQry As String, My_Field As String, My_Control As String)

Dim My_RecordSet As DAO.Recordset, My_DataBase As DAO.Database
Dim My_test As String

Set My_DataBase = CurrentDb
et My_RecordSet = My_DataBase.OpenRecordset(My_TblQry, dbOpenForwardOnly) With My_RecordSet
Me.Controls(My_Control) = ![My_Field] 'this is where it doesn’t work
.Close
End With

End Sub


Normally I would type the field name between the square brackets “[ ]” but for the sub routine to work the field name will vary. I’m not getting the syntax right can anyone help?

Cheers

D

 

[pbaldy]

Try

My_RecordSet(My_Field)

 

[c_smithwick]

Try the following:

[SIZE=3]Private Sub MySub_DataFind(strMy_TblQry As String, strMy_Field  As String, strMy_Control As String)[/SIZE]

[SIZE=3]Dim rstMy_RecordSet As DAO.Recordset
Dim dbMy_DataBase As  DAO.Database[/SIZE]
[SIZE=3]Dim strMy_test As String[/SIZE]

[SIZE=3]Set dbMy_DataBase = CurrentDb[/SIZE]
[SIZE=3]Set rstMy_RecordSet = dbMy_DataBase.OpenRecordset(strMy_TblQry,  dbOpenForwardOnly)

With rstMy_RecordSet[/SIZE]
     [SIZE=3]Me.Controls(strMy_Control) = .Fields(strMy_Field)
[/SIZE][SIZE=3]     .Close[/SIZE]
[SIZE=3]End With[/SIZE]

[SIZE=3]End Sub[/SIZE]

 

[GaelicFatboy]

Cheers smithwick that worked a treat.

D