Is there a way to retain a selected value from a number field in Access as an absolute # in the current handling file, even though that value may be changed later in that field?
As an example:
I have a number field in each player's record which I call "ladder_spot". When I record the results of a challenge game I select that field to find the current positions of winner and loser:
quote:strSql = "SELECT M_NAME, LADDER_SPOT FROM FORUM_MEMBERS WHERE M_NAME = '" & totalGame("VICTOR") & "'" set intVictor = my_Conn.Execute(strSql) VictorSpot = intVictor("ladder_spot")
strSql = "SELECT M_NAME, LADDER_SPOT FROM FORUM_MEMBERS WHERE M_NAME = '" & totalGame("DEFENDER") & "'" set intDefender = my_Conn.Execute(strSql) LoserSpot = intDefender("ladder_spot")
Then I need to assign the higher position to the winner:
quote: '## adjust the two duellers' positions ############################ strSQL = "UPDATE FORUM_MEMBERS SET LADDER_SPOT = '" & intDefender("LADDER_SPOT") & "' WHERE M_NAME = '" & totalGame("CHALLENGER") & "'" my_Conn.execute(strSql)
strSQL = "UPDATE FORUM_MEMBERS SET LADDER_SPOT = LADDER_SPOT + 1 WHERE M_NAME = '" & totalGame("DEFENDER") & "'" my_Conn.execute(strSql)
To adjust the numbers for the insertion of the winner into the higher position I also have to add +1 to each player who was in positions between the winner and loser:
quote:'## adjust other positions between the duellers ############################ strSQL = "UPDATE FORUM_MEMBERS SET LADDER_SPOT = LADDER_SPOT + 1 WHERE LADDER_SPOT>= " & LOSERSPOT & " AND LADDER_SPOT< " & VICTORSPOT & "" my_Conn.execute(strSql)
The numbers never come out right though, and I suspect the trouble to be that the original position of the winner has changed by now and is selected as the new value, which means I don't know who was between the two players.
Is there a way to store the original value in the working file for use down the line?
I know this may be a bit confusing, and I hope I made the task clear enough!
Thanks for the response, laser! While waiting for help I was working some more on the code, and suddenly it all seemed to work out. Similar to your thinking I shifted the two combatants' positions AFTER adjusting the ones between them. I had tried that same thing earlier with no success, which may have been due to some other logical error. Anyway, it seems to work now with this:
quote:'## adjust Ladder_Spot ##################################################### if totalGame("TYPE") = "Ladder Duel" then
strSql = "SELECT * FROM FORUM_MEMBERS where LADDER_SPOT<>0 ORDER BY LADDER_SPOT ASC" set mySpots = my_Conn.Execute(strSql)
strSql = "SELECT M_NAME, LADDER_SPOT FROM FORUM_MEMBERS WHERE M_NAME = '" & totalGame("VICTOR") & "'" set intVictor = my_Conn.Execute(strSql) VictorSpot = intVictor("ladder_spot")
strSql = "SELECT M_NAME, LADDER_SPOT FROM FORUM_MEMBERS WHERE M_NAME = '" & totalGame("DEFENDER") & "'" set intDefender = my_Conn.Execute(strSql) LoserSpot = intDefender("ladder_spot")
if totalGame("VICTOR")=totalGame("CHALLENGER") then
'## adjust other positions between the duellers ############################ strSQL = "UPDATE FORUM_MEMBERS SET LADDER_SPOT = LADDER_SPOT + 1 WHERE LADDER_SPOT > " & LOSERSPOT & " AND LADDER_SPOT < " & VICTORSPOT & "" my_Conn.execute(strSql)
'## adjust the two duellers' positions ############################ strSQL = "UPDATE FORUM_MEMBERS SET LADDER_SPOT = '" & intDefender("LADDER_SPOT") & "' WHERE M_NAME = '" & totalGame("CHALLENGER") & "'" my_Conn.execute(strSql)
strSQL = "UPDATE FORUM_MEMBERS SET LADDER_SPOT = LADDER_SPOT + 1 WHERE M_NAME = '" & totalGame("DEFENDER") & "'" my_Conn.execute(strSql)
strSQL = "UPDATE GAMES SET VICTOR = 'suspended' WHERE ID = " & request.form("ID") & "" my_Conn.execute(strSql) end if end if '## end Ladder_Spot and end all ifs #################
Topic Locked
Posted - 22 August 2003 : 11:12:54
