Using an SQL statement in VBA to change Field Values on a form

  • Thread starter Thread starter rburna
  • Start date Start date
R

rburna

I have a form and I need to change several values depending on a drop
down, I do not want to have to use the column references to do this.

I have the following code in place

DIM strSQL As String

strSQL = "Select [TestColumn1], [TestColumn2],[TestColumn3] FROM TEST
WHERE RowNum = 1"

DoCmd.RunSQL = strSQL

Me.txtColumn1 = ??
Me.txtColumn2 = ??
Me.txtColumn3 = ??

I know there has to be a way to reference the recordset but I can't
seem to find it.....

Any help is appreciated....

Thanks,
Richard
 
You have not createed a recordset. You have only created an SQL string for a
Select query. It will not work. The RunSql method is only for Action
queries. Here is how you would create a recordset to do what you want. The
only thing I am not sure about is what Rownum is.

DIM strSQL As String
Dim rst as Recordset

Set rst = CurrentDb.OpenRecordset("Select [TestColumn1], [TestColumn2], _
[TestColumn3] FROM TEST WHERE RowNum = 1;")

Me.txtColumn1 = rst![TestColumn1]
Me.txtColumn2 = rst![TestColumn2]
Me.txtColumn3 = rst![TestColumn3]

rst.Close
Set rst = Nothing
 
Back
Top