Bob,
I apologize for the late response. The actual problem is that when the
checkbox is checked in the front-end and it is unchecked and saved, the
Request.Form("chk_Complete") value is not false, instead it is a null value,
I am attaching the sql string that is built here:
START CHECK COMPLETE
THE END
StartCheckBox
EndCheckbox
UPDATE tblGMISExpenditures_Quarter SET
tblGMISExpenditures_Quarter.CurrentOutlay= '$550.00' ,
tblGMISExpenditures_Quarter.LocalShare= '$100.50' ,
tblGMISExpenditures_Quarter.MBCCShare= '449.5' ,
tblGMISExpenditures_Quarter.UnpaidOblig= '$250.00' ,
tblGMISExpenditures_Quarter.LocalShareUnpaidOblig= '$35.35' ,
tblGMISExpenditures_Quarter.ProjectIncome= '$100.00' ,
tblGMISExpenditures_Quarter.ForfeitureIncome= '$200.00' ,
tblGMISExpenditures_Quarter.OtherExpense= '$30.00' ,
tblGMISExpenditures_Quarter.ForfeitureExpense= '$40.00' ,
tblGMISExpenditures_Quarter.InterestReceived= '$20.00' ,
tblGMISExpenditures_Quarter.TotalPeriodOutlay= '4568.39' ,
tblGMISExpenditures_Quarter.Remarks= 'Testing GWIS' ,
tblGMISExpenditures_Quarter.Name= 'Jack Jones' ,
tblGMISExpenditures_Quarter.Title= 'Manager' ,
tblGMISExpenditures_Quarter.AreaCode= '111' ,
tblGMISExpenditures_Quarter.Phone1= '111' ,
tblGMISExpenditures_Quarter.Phone2= '8762' ,
tblGMISExpenditures_Quarter.Date= '1/11/2005' ,
tblGMISExpenditures_Quarter.IsChecked= where
tblGMISExpenditures_Quarter.SubgrantIntID = 4836;
The CODE for database entry is as follows:
<%@ Language=VBScript %>
<%
'The following line is to prevent this page coming from history.
'We need a new page from the server each time so that all the
'session vairables are reset
Response.CacheControl = "no-cache"
Response.AddHeader "Pragma", "no-cache"
%>
<HTML>
<HEAD>
<META NAME="GENERATOR" Content="Microsoft Visual Studio 6.0">
</HEAD>
<BODY>
<P> </P>
<%
'myDSN="DRIVER={Microsoft Access Driver (*.mdb)}; "
'myDSN=myDSN & "DBQ=C:\_______GWISBRANDNEWREADY5\GMISDATA.mdb"
myDSN="Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\_______GWISBRANDNEWREADY5\GMISDATA.mdb"
set CN=server.createobject("ADODB.Connection")
CN.Open myDSN
'ecRS stands for recordset corresponding to the current expense
set ecRS=server.createobject("ADODB.Recordset")
'recRS stands for recordset corresponding to the recordcount of the current
expense
set recRS = server.CreateObject ("ADODB.Recordset")
'detecRS stands for the detailed recordset corresponding to the current
expense
set detecRS = server.CreateObject("ADODB.Recordset")
recRS.ActiveConnection = CN
ecRS.ActiveConnection = CN
detecRs.ActiveConnection = CN
'GET GRANT ID FROM SESSION ID
GrantID = Session("sess_GrantID")
SQL = "SELECT COUNT(*) AS reccount FROM tblGMISExpenditures_Quarter WHERE
SubgrantIntID = " & GrantID & ";"
recRS.Open SQL
'If there is no record in the expense table corresponding to the current
subgrantid number then
'insert the subgrantid number in the tblGMISExpenditures_Quarter table.
if recRS("reccount") < 1 then
recRS.Close
strSQL = "INSERT INTO tblGMISExpenditures_Quarter(SubgrantIntID)
VALUES ("& GrantID &")"
ecRS.Open strSQL
'else
end if
' In the event there is already the subgrantintid in the above table, we
just need to update
' the table with the input values of the main form.
'Define local variables to store the values obtained from input form(either
text or calculated fields)
Dim l_CurrentOutlay
Dim l_CurrentLocalShareOutlay
Dim l_c_MBCCShareOfOutlay 'c represents calculated
Dim l_CurrentUnpaidObligations
Dim l_CurrentLocalShareUnpaidObligations
Dim l_CurrentOtherProjectIncomeReceived
Dim l_CurrentForfeitureIncomeReceived
Dim l_CurrentOtherProjectIncomeExpenditures
Dim l_CurrentForfeitureIncomeExpenditures
Dim l_CurrentInterestIncomeReceivedOnMBCCFunds
Dim l_p_c_TotalOutlay ' c represents calculated
Dim l_f_remarks ' Note that f stands for final remark to distinguish between
variable in the main form
'Similary, the following would be the variables for other contact fields
Dim l_f_name
Dim l_f_personstitle
Dim l_f_PhoneAreaCode
Dim l_f_Phone1
Dim l_f_Phone2
Dim l_f_Date
'The following variable is being added in order to add the value of checkbox
in the database
Dim l_f_IsChecked
'Now store the values collected from the main form to the local variables
l_CurrentOutlay = Request.Form("txt_CurrentOutlay")
l_CurrentLocalShareOutlay = Request.Form("txt_CurrentLocalShareOutlay")
'THIS IS THE CODE INTRODUCED HERE TO FORCE THE SESSION VALUE TO 0
If (Session("l_cu_c_MBCCShareOfOutlays"))= "" Then
Session("l_cu_c_MBCCShareOfOutlays") = 0
End If
'END OF THE CODE INTRODUCED WITH SESSION VALUE SETTING
l_c_MBCCShareOfOutlay = Session("l_cu_c_MBCCShareOfOutlays")
l_CurrentUnpaidObligations = Request.Form("txt_CurrentUnpaidObligations")
l_CurrentLocalShareUnpaidObligations =
Request.Form("txt_CurrentLocalShareUnpaidObligations")
l_CurrentOtherProjectIncomeReceived =
Request.Form("txt_CurrentOtherProjectIncomeReceived")
l_CurrentForfeitureIncomeReceived =
Request.Form("txt_CurrentForfeitureIncomeReceived")
l_CurrentOtherProjectIncomeExpenditures =
Request.Form("txt_CurrentOtherProjectIncomeExpenditures")
l_CurrentForfeitureIncomeExpenditures =
Request.Form("txt_CurrentForfeitureIncomeExpenditures")
l_CurrentInterestIncomeReceivedOnMBCCFunds =
Request.Form("txt_CurrentInterestIncomeReceivedOnMBCCFunds")
If Session("l_p_TotalOutlay")= "" Then
Session("l_p_TotalOutlay") = 0
End If
l_p_c_TotalOutlay = Session("l_p_TotalOutlay")
l_f_Remarks = trim(Request.Form("txt_Remarks"))
l_f_name = trim(Request.Form("txt_Name"))
l_f_personstitle = trim(Request.Form("txt_Title"))
l_f_PhoneAreaCode = trim(Request.Form("txt_PhoneAreaCode"))
l_f_Phone1 = trim(Request.Form("txt_Phone1"))
l_f_Phone2 = trim(Request.Form("txt_Phone2"))
l_f_Date = Request.Form("txt_Date")
'If (Request.Form("chk_Complete")) = "" then
' Request.Form("chk_Complete") = "false"
'End If
l_f_IsChecked = Request.Form("chk_Complete")
' Response.Write "Start_Check_l_f_IsChecked" & "<BR>"
' Response.Write l_f_IsChecked & "<br>"
' Response.Write "End_Check_l_f_IsChecked" & "<BR>"
Response.Write "START CHECK COMPLETE" & "<BR>"
Response.Write Request.Form("chk_Complete") & "<br>"
Response.Write "THE END" & "<BR>"
'---------START OF DEBUGGING TOOLS FOR EACH FIELD RETRIEVED FROM AND SAVED
IN DATABASE-------
'Response.Write "StartCurrentOutlay" & "<br>"
'Response.Write l_CurrentOutlay & "<br>"
'Response.Write "EndCurrentOutlay" & "<br>"'
'Response.Write "StartCurrentLocalShareOutlay" & "<br>"
'Response.Write l_CurrentLocalShareOutlay & "<br>"
'Response.Write "EndCurrentLocalShareOutlay" & "<br>"
'Response.Write "Testing MBCC Share Of Outlay" & "<br>"
'Response.Write l_c_MBCCShareOfOutlay & "<br>"
'Response.Write Session("l_cu_c_MBCCShareOfOutlays") & "<br>"
'Response.Write "EndCurrentMBCCShareOfOutlay" & "<br>"
'Response.Write "StartCurrentUnpaidObligations" & "<br>"
'Response.Write l_CurrentUnpaidObligations & "<br>"
'Response.Write "EndCurrentUnpaidObligations" & "<br>"
'Response.Write "StartCurrentLocalShareUnpaidObligations" & "<br>"
'Response.Write l_CurrentLocalShareUnpaidObligations & "<br>"
'Response.Write "EndCurrentLocalShareUnpaidObligations" & "<br>"
'Response.Write "StartCurrentOtherProjectIncomeReceived" & "<br>"
'Response.Write l_CurrentOtherProjectIncomeReceived & "<br>"
'Response.Write "EndCurrentOtherProjectIncomeReceived" & "<br>"
'Response.Write "StartCurrentForfeitureIncomeReceived" & "<br>"
'Response.Write l_CurrentForfeitureIncomeReceived & "<br>"
'Response.Write "EndCurrentForfeitureIncomeReceived" & "<br>"
'Response.Write "StartCurrentOtherProjectIncomeExpenditures" & "<br>"
'Response.Write l_CurrentOtherProjectIncomeExpenditures & "<br>"
'Response.Write "EndCurrentOtherProjectIncomeExpenditures" & "<br>"
'Response.Write "StartCurrentForfeitureIncomeExpenditures" & "<br>"
'Response.Write l_CurrentForfeitureIncomeExpenditures & "<br>"
'Response.Write "EndCurrentForfeitureIncomeExpenditures" & "<br>"
'Response.Write "StartPeiodicOutlay" & "<br>"
'Response.Write l_p_c_TotalOutlay & "<br>"
'Response.Write "EndPeriodicOutlay" & "<br>"
'Response.Write "StartRemarks" & "<br>"
'Response.Write l_f_Remarks & "<br>"
'Response.Write "EndRemarks" & "<br>"
'Response.Write "StartName" & "<br>"
'Response.Write l_f_Name & "<br>"
'Response.Write "EndName" & "<br>"
'Response.Write "StartPersonTitle" & "<br>"
'Response.Write l_f_personstitle & "<br>"
'Response.Write "EndPersonTitle" & "<br>"
'Response.Write "StartPhoneAreaCode" & "<br>"
'Response.Write l_f_PhoneAreaCode & "<br>"
'Response.Write "EndPhoneAreaCode" & "<br>"
'Response.Write "StartPhone1" & "<br>"
'Response.Write l_f_Phone1 & "<br>"
'Response.Write "EndPhone1" & "<br>"
'Response.Write "StartPhone2" & "<br>"
'Response.Write l_f_Phone2 & "<br>"
'Response.Write "EndPhone2" & "<br>"
'If l_f_IsChecked = "" Then
' l_f_Checked = False
'End If
Response.Write "StartCheckBox" & "<br>"
Response.Write l_f_IsChecked & "<br>"
Response.Write "EndCheckbox" & "<br>"
'---------END OF DEBUGGING TOOLS FOR EACH FIELD RETRIEVED FROM AND SAVED IN
DATABASE-------
'NOW START BUILDING THE SQL STATEMENT TO UPDATE THE CURRENTQUARTERVALUES
'Response.Write "<br>"
'Response.Write "Hello"
strSQL1 = "UPDATE tblGMISExpenditures_Quarter SET
tblGMISExpenditures_Quarter.CurrentOutlay= '" & l_CurrentOutlay & "' " & _
", tblGMISExpenditures_Quarter.LocalShare= '" &
l_CurrentLocalShareOutlay & "' " & _
", tblGMISExpenditures_Quarter.MBCCShare= '" &
l_c_MBCCShareOfOutlay & "' " & _
", tblGMISExpenditures_Quarter.UnpaidOblig= '" &
l_CurrentUnpaidObligations & "' " & _
", tblGMISExpenditures_Quarter.LocalShareUnpaidOblig= '" &
l_CurrentLocalShareUnpaidObligations & "' " & _
", tblGMISExpenditures_Quarter.ProjectIncome= '" &
l_CurrentOtherProjectIncomeReceived & "' " & _
", tblGMISExpenditures_Quarter.ForfeitureIncome= '" &
l_CurrentForfeitureIncomeReceived & "' " & _
", tblGMISExpenditures_Quarter.OtherExpense= '" &
l_CurrentOtherProjectIncomeExpenditures & "' " & _
", tblGMISExpenditures_Quarter.ForfeitureExpense= '" &
l_CurrentForfeitureIncomeExpenditures & "' " & _
", tblGMISExpenditures_Quarter.InterestReceived= '" &
l_CurrentInterestIncomeReceivedOnMBCCFunds & "' " & _
", tblGMISExpenditures_Quarter.TotalPeriodOutlay= '" &
l_p_c_TotalOutlay & "' " & _
", tblGMISExpenditures_Quarter.Remarks= '" & l_f_Remarks & "' " & _
", tblGMISExpenditures_Quarter.Name= '" & l_f_Name & "' " & _
", tblGMISExpenditures_Quarter.Title= '" & l_f_personstitle & "' "
& _
", tblGMISExpenditures_Quarter.AreaCode= '" & l_f_PhoneAreaCode &
"' " & _
", tblGMISExpenditures_Quarter.Phone1= '" & l_f_Phone1 & "' " & _
", tblGMISExpenditures_Quarter.Phone2= '" & l_f_Phone2 & "' " & _
", tblGMISExpenditures_Quarter.Date= '" & l_f_Date & "' " & _
", tblGMISExpenditures_Quarter.IsChecked= " & l_f_IsChecked & " "
& _
" where " & _
" tblGMISExpenditures_Quarter.SubgrantIntID = " & GrantID & ";"
Response.Write "<br>"
Response.Write strSQL1
Response.Write "<br>"
'Now open the recordset to update the current record'
detecRs.Open strSQL1
Hope this helps