Can I import a field name into a query? 2

[Hanss]

I have a query that looks like this:

SELECT [Forms]![form1]![fieldname] AS field
FROM Table1;


In form1 I have a field named fieldname where I have entered "field1"

What I want the query to do is display the data of field1 in table1. The problem is that only "field1" appears when I run the query instead of the data from field1 in table1. Does anyone have any idea how I can solve this problem?

Hanss
Zurich, Switzerland

 

[KenReay]

Hi

You do not say if this a saved query or a quiry you are building in code, if you are building the SQL string in code then try

strSQL = "SELECT " & [Forms]![form1]![fieldname] & " AS field FROM Table1;"


Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[Eupher]

Hanss,

I think KenReay is on the right track. I don't know how to do this directly within a saved query. My approach would be to create a combo on my form, make its Row Source Type a "Field List" and its Row Source the name of the source table. Then in the combo's AfterUpdate event you could change the SQL of your query to reflect the chosen field. Something like this:

Private Sub Combo2_AfterUpdate()
Dim CurDB As DAO.Database
Dim qryDef As DAO.QueryDef
Dim SQLStmt As String

If Not IsNull(Me![Combo2]) Then
    Set CurDB = CurrentDb()
    Set qryDef = CurDB.QueryDefs("qryMyQuery")
    SQLStmt = "SELECT [" & Me![Combo2] & "] FROM tblMyTable;"
    qryDef.SQL = SQLStmt
    Set qryDef = Nothing
    Set CurDB = Nothing
End If

End Sub

You may want to set a default value for the combo and put some code in the form's Open event to reset the SQL of the query to the default value - else the query's SQL will be whatever it was the last time the combo was updated.

HTH,

Ken S.

 

[Hanss]

Thank you both for your reply! Sorry for not getting back to you sooner... You got me on the right track in that the problem could only be solved with code. I ended up putting the an unbound box in the form and then used case in the report to change the record source as needed:

Case "1"
Me.RecordSource = ...


Case "2"
Me.RecordSource = ...

Kind regards,
Hanss