use variable as reference in vba select statement

[barboza]

I am trying to use the variable strTest as a variable in the select statement below.

The fields in the custody database contain F8 thru F34.

Private Sub txtMethod_BeforeUpdate(Cancel As Integer)
strA = 8
Do While strA < 35
strTest = "F" + strA
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("Select strTest from Custody where LABID = '" & Me.txtLabID & "' AND strTest = '" & Me.txtMethod & "';")
strA = strA + 1
Loop

If rst.RecordCount = 0 Then
MsgBox Me.txtMethod + " is not required for Lab ID: " + Me.txtLabID
End
End If

End Sub




If I substitute this line of code:

Set rst = dbs.OpenRecordset("Select F8 from Custody where LABID = '" & Me.txtLabID & "' AND F8 = '" & Me.txtMethod & "';")


the line works just fine.

Obviously I would like to avoid repeating the line 26 times changing F8 to F9 to F10......


I also tried to use an if then statement in a loop with a counter.

The bottom line: I have not been able to reference the variable to see the contents of fields F8 - F34

As always, any help is greatly appreciated.

Thanks,

B

 

[pbaldy]

strTest needs to be outside the quotes (like the form references), so its value can be interpreted.

 

[barboza]

If I take the existing select line and put it in a MsgBox, the only part that is evaluated showing quotes are the form references.
Just for kicks, I edited the line so that the variable was treated the same way as the from references - didn't help. The form references show the values in the form that I type into the form in those fields.
The variable shows F8, not the value of F8.

To distill this down as much as possible -

How could I write a MsgBox line to show the contents of table Custody Field F8 using the variable strTest when

strTest = F8

or

how can I get strTest to reference the contents of F* when setting the equivalency:

strTest = F8.Value doesn't do it.

Thanks again,

B

 

[pbaldy]

Why don't you post what you actually tried?

 

[Libre]

Here's the syntax I use when I have string variables and numeric variables in an SQL statement:
Dim mySQL as String
mySQL = "UPDATE tblItemMaster SET tblItemMaster.CustName = '" & sName & "'"
mySQL = mySQL + " WHERE (((tblItemMaster.CustomerID) = " & iCustID & "))"
DoCmd.RunSQL mySQL

The variable sName is a string and the variable iCustID is an integer.
The string variable must ultimately be enclosed by single quotes and the numeric variable must not.

Another cool thing you can do is comment out the DoCmd.RunSql mySQL and substitue:
Debug.Print mySQL
That will send the statement as interpreted to the immediate window where you can study it. Handier than a message box which is modal and ties up the system while it's on the screen.

 

[barboza]

Hi PB,

Here is the line of code after editing.

Set rst = dbs.OpenRecordset("Select '" & strTest & "' from Custody where LABID = '" & Me.txtLabID & "' AND '" & strTest & "' = '" & Me.txtMethod & "';")

 

[pbaldy]

When the variable is a field name rather than a value, you don't want the single quotes around it.

 

[barboza]

No Single quotes - Doesn't work

Here is the code as edited to remove single quotes:

Set rst = dbs.OpenRecordset("Select " & strTest & " from Custody where LABID = '" & Me.txtLabID & "' AND " & strTest & " = '" & Me.txtMethod & "';")


If I knew how to make the value appear in a MsgBox, I would be good to go.

Any ideas how I can just make the MsgBox display the contents of the F8 field?

B

 

[pbaldy]

Try

MsgBox rst(0)