set table column name as a variable

[matrixindicator]

Hello,

Can you set the name of a table column to a variable ?
qryName is a variable from a function argument but match one name in the table, if yes, I doubt about the syntax ?

Set dbs = CurrentDb
    Set rst = dbs.OpenRecordset("T14", dbOpenDynaset)

    If Not rst.EOF And Not rst.BOF Then
        With rst
            .MoveLast
            .Edit
            !qryName = qryName
            .Update
        End With
    Else

 

[HarleyQuinn]

You can do it using TableDef. A search on this forum or the internet should provide lots of examples.

Hope this helps

HarleyQuinn
---------------------------------
The most overlooked advantage to owning a computer is that if they foul up there's no law against wacking them around a little. - Joe Martin

Get the most out of Tek-Tips, read FAQ222-2244 before posting.

 

[matrixindicator]

I think about it, if not with a recordset (I prefer), I can try to use an SQL string, I think there you can use variables for the column names.

 

[HarleyQuinn]

Out of interest, what SQL string were you thinking?

I'd still use TableDefs, but if you wanted to do it in SQL then I'd have a look at ALTER TABLE statements.

You'd need to ADD a column, UPDATE it with the details of the old column and then DROP the old column.

Hope this helps

HarleyQuinn
---------------------------------
The most overlooked advantage to owning a computer is that if they foul up there's no law against wacking them around a little. - Joe Martin

Get the most out of Tek-Tips, read FAQ222-2244 before posting.

 

[matrixindicator]

Is not changing the column, its only adding information to the corresponding column.

queryName "qry123" should be places in column with the Name "qry123".

To avoid replication of my code for each columnname I like to use a variable if this is possible.

 

[HarleyQuinn]

Ah right, it was

Can you set the name of a table column to a variable ?

that threw me.

Yes, you can do this with a very simple UPDATE statement in that case.

HarleyQuinn
---------------------------------
The most overlooked advantage to owning a computer is that if they foul up there's no law against wacking them around a little. - Joe Martin

Get the most out of Tek-Tips, read FAQ222-2244 before posting.

 

[matrixindicator]

This is ok, I just neet to find out that he is updating the last row. Can I move before the DoCmd.RunSql to the last record with a rst.movelast or should I include a where condition ?

DoCmd.RunSQL "UPDATE T14 SET T14 ." & qryName & "='" & qryName & "';"

 

[HarleyQuinn]

As long as you can reliably get to the last row in the WHERE clause I'd personally use that method.

Hope this helps

HarleyQuinn
---------------------------------
The most overlooked advantage to owning a computer is that if they foul up there's no law against wacking them around a little. - Joe Martin

Get the most out of Tek-Tips, read FAQ222-2244 before posting.

 

[matrixindicator]

Something is wrong with the concatenation location I think.

DoCmd.RunSQL "UPDATE T14 SET T14." & qryName & "='" & qryName & "';"

For some fields he accept this, for other not.

I try a messag box and the result is something like, see the ' in the text to fill in. Whats wrong with my syntaxis ?

UPDATE T14_PROCDOC SET T14_PROCDOC.QRY123='QRY123';

 

[HarleyQuinn]

Do you get an error message? Are all of your fields text?

HarleyQuinn
---------------------------------
The most overlooked advantage to owning a computer is that if they foul up there's no law against wacking them around a little. - Joe Martin

Get the most out of Tek-Tips, read FAQ222-2244 before posting.

 

[matrixindicator]

I think it is another problem. He update a first column, but for the second he said the field is not updatable. I verify a lot of thinks, I can't find something. I look it up after the weekend.

 

[PHV]

Something like this ?
If Not rst.EOF And Not rst.BOF Then
With rst
.MoveLast
.Edit
[!].Fields(qryName)[/!] = qryName
.Update
End With
Else

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886