Update query as SQl problem

[CBRAY]

Hi
I am trying to use a form to update a field in a table by selecting all the values on the form and passing them into a SQL statement.

I select the table name [Points] on my form from a list box 'lsttables' which uses MSysObjects as row source to show all the tables in the database. The On Click event for lsttables sets the row source of a combo box 'cmbid' to lsttables.value, the row source type is field list.

I then use ‘cmbid’ to dropdown the field names for the table selected [ID].

I then use unbound text box to create the value for [pretext].

What I would like to do is change [ID] in [Points] to [Pretext]&[ID]

I can achieve this in an update query SQL below. But then I am fixed to the table name.

UPDATE Points SET Points.ID = Forms!PDTScript_ACAD!pretext & [ID];

I have tried to write the SQL but I am having problems.

Is this possible?

Many Thanks

 

[workmad3]

"UPDATE " & lsttables & " SET " & lsttables & ".ID = " & Forms!PDTScript_ACAD!pretext & [ID]

perhaps that will work?

 

[CBRAY]

Workmad3
Thanks for the reply.
I am taking the value for ID from combo box cmbid on the form and therefore substituted for this for ID in your code. Pretext is resident on the current form so I also removed Forms!PDTScript_ACAD!

UPDATE & lsttables & " SET " & lsttables & ".cmbid= " & pretext & cmbid;"

Now I get a compile error expected end of statement

Thanks in advance for your time.

 

[CBRAY]

wrong code, this is what I am trying to use:

"UPDATE " & lsttables & " SET " & lsttables & "." & cmbunique & " = " & pretext & cmbunique;"