Hello! Not sure how to do this so here is a description of the process.

Have an Access table that contains Username and Week1, Week2, ... up to Week17 column name. Would like a form page that has a select option drop down box where a value is picked to update a specific column on the
table which is determined by the week number which is contained in a session variable.

The week number that is put in session variable svWeekNum is calculated on
the login_redirect.asp page. Based on a range of dates from Monday thru the
following Sunday a week number is determined and saved with a value of Week5 for example which is also the name of the table column.

Therefore, "Week5" is put into svWeekNum on the login_redirect page and it will
be called on the update form input page. Now the Update Record behavior
needs to have a specific table column name updated by the form input field. The
current processing has select updates column "Week5" Text in the Form Elements
section of the Update Record dialog box now.

Would like to somehow pass the svWeekNum session variable as the name of the
column. It does not look like the Update Record behavior will allow this as you can
only choose column names on the table. Is there a way to do this with the current or another behavior type? If not then could the Update Record behavior code be modified to pass the svWeekNum value to the
query that is built by the behavior on the page? Looks like the following code could be modified.

' *** Update Record: set variables

If (CStr(Request("MM_update") <> "" And CStr(Request("MM_recordId") <> ""
Then

MM_editConnection = MM_FPoolconn_STRING
MM_editTable = "Winners_Table"
MM_editColumn = "WinnerID"
MM_recordId = "" + Request.Form("MM_recordId" + ""
MM_editRedirectUrl = "win_change_weeklypick_confirm.asp"
MM_fieldsStr = "select|value"
MM_columnsStr = "Week5|',none,''"

' create the MM_fields and MM_columns arrays
MM_fields = Split(MM_fieldsStr, "|"
MM_columns = Split(MM_columnsStr, "|"

' set the form values
For i = LBound(MM_fields) To UBound(MM_fields) Step 2
MM_fields(i+1) = CStr(Request.Form(MM_fields(i)))
Next .....

Specifically, the MM_columnsStr statement above that holds the column.
Could the svWeekNum session variable be passed instead of the hardcoded
Week5 column name and if so what is the syntax of this statement now? Maybe
like this MM_columnsStr = "'svWeekNum'"|',none,''" or similar code here.

If this is not feasible then another but long way to do this would be to
redirect to a different page upon login based on the svWeekNum variable. These pages
would have links to specific update form pages that have the week number hardcoded
by the Update Record behavior function. This would entail 17 different form pages which looks like wasting time to build these.

Has anybody run across the above or have any recommendations for another method that
can be used here. Will appreciate any help. Thanks!

Hello! Have not figured out how to change the SQL in the Update behavior dynamically and wanted to bump this up once more.

Have read tutorials for building SQL dynamically by adding variables to your SQL that would build the Where clause conditionally but have not read anything specifically related to the Update behavior code that is produced.

Not sure where to place the dynamic Where or the syntax to use here. Would appreciate any recommendations or help. Thanks!

have you thought about normalizing your table to two tables. One containing the user information and the other table containing the week information with a secondary key linking the row to the primary key in the user table. You would then have a one to many relationship of user table to weektable. Also in the week table place begining and ending week values that could be calculated based on the week of the year. This way you can look and compare the last cookie variable and compare it to any row in the table that corrisponds to the user

Wm. Kent Steelman

Hey! Have two tables already with one containing basic user information on the User table. The second table is the one mentioned in the post that would like to update here.

This contains the Username which is determined at logon and put in a session variable. Logon also determines the Week of the year determined by a function which checks date ranges and values the Week number also in a session variable. This Week number in the session variable is the same as a column name on the second or Winners table.

Can understand what you are saying about linking the two tables but not sure how that would work when updating the second table.

The Week column to be updated on the Winners table is determined at logon. Was hoping for a simple update to that column on this table. Not sure how adding a date range on the second table would help here.

Would that be two columns only, for beginning and end date which would have to be updated weekly as the week changes or a beginning and end date column set for each Week column. The Week columns contain the name of a winning team picked in a pool so that information is displayed on a page as the weeks move along during the year.

Currently, depending on the week number and the week column to be updated, there is a separate update page for each week which works but it is a waste of space and not very efficient so wanted to get the Week number contained in the session variable into the Update behavior somehow on one Update page.

Hope this is a little clearer. Thanks!