Dynamic SQL

[waka]

Hi,

I'm new to databases and SQL and I need some help writing code. I have two tables, one is a list of plants (tlu_Plants) that has a favorites column (Yes/No) and the other is a favorites list (tlu_Plants_Favorites) by Park name. They are linked by a Plant_ID number.

When a user enters the database, they select a park from a list box. I want to have the database query tlu_Plants_Favorites based on the park they picked and update the tlu_Plants table.

Essentially:

"UPDATE tlu_Plants LEFT JOIN tlu_Plants_Favorites ON tlu_Plants.Plant_ID = tlu_Plants_Favorites.Plant_ID
SET tlu_Plants.Favorite = IIF([tlu_Plants_Favorites.[Park]]='" & list_Parks.Column(0) & "', TRUE, FALSE)"

The problem is I don't know how to create a variable for a field name, which in this statement is Park.

Please let me know if this isn't clear. Thanks!!

 

[pbaldy]

You're saying you have fields in the table for each park? If so, that's almost certainly a design mistake. In any case, you'd concatenate the field name from wherever the user has chosen it the same way you concatenate the listbox selection.

...tlu_Plants_Favorites.[" & FormOrVariable & "]...