Stop DB entry if value is exceeded 1

[itsuit]

I have an Access 2000 database that stores employees' time off requests which are submitted via an ASP page. The database consists of two tables ("tblUsers" and "tblRequests&quot and a query ("qryTimeRemaining&quot. The query is used to show users (on a different ASP page) time they've already entered, along with how many hours they have left (total hours available - all hours already requested).

I'd like to be able to prevent the user from adding a record to the database when it exceeds their available hours; however, I don't know how to combine the Requests table with the TimeRemaining query to accomplish this.

My current code for adding to the Requests table looks like this:


Set objConn = Server.CreateObject("ADODB.Connection&quot
ConnectionString="DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=" & Server.MapPath("pto.mdb&quot
objConn.Open ConnectionString

sqlQuery = "SELECT * FROM requests"
Set objRS = Server.CreateObject("ADODB.Recordset&quot
objRS.Open sqlQuery, objConn, 1, 2

objRS.AddNew
objRS("Name&quot=strSender
objRS("StartDate&quot=strStart
objRS("EndDate&quot=strEnd
objRS("Hours&quot=strHours
objRS("Manager&quot=strManager
objRS("SubmitDate&quot=strDate
If strDetails="" Then
objRS("Details&quot="None"
Else
objRS("Details&quot=strDetails
End If

objRS.Update
objRS.Close
Set objRS = Nothing

I know I'd need another IF statement prior to the Update, but I'm a relative newbie to this and could really use some help getting "unstuck".

Thanks for anything you folks can offer.

Kyle

 

[itsuit]

Sorry - I forgot to mention that on failure I just want to redirect them to a page that basically says "You've exceeded your available PTO".

 

[jhall01]

Could you not create another recordset that pulls the employees time off left from the database, put that to a variable, and subtract the requested time. And if the time is < 0 then redirect back to the input page?

ie:

sqlQuery2 = &quot;SELECT timeremainCOL FROM qrytimeremaining&quot;
Set rsTR = Server.CreateObject(&quot;ADODB.Recordset&quot
rsTR.Open sqlQuery2, objConn, 1, 2

usrTimeRemain = rs.fields(&quot;timeremainCOL&quot

testvar1 = usrTimeRemain - strHours

If testvar1 < 0 Then
redirect code here (you can use meta-redirect or java for real time error popup)
else
add to table code here
End IF

Not exact code for you but should help you out. I am not sure if this is perfect for what you need.

 

[jhall01]

sorry....that rs.fields(&quot;timeremainCOL&quot should read:

rsTR.fields(&quot;timeremainCOL&quot

 

[itsuit]

I tried inserting the second SQL statement this way:

Set objConn = Server.CreateObject(&quot;ADODB.Connection&quot
ConnectionString=&quot;DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=&quot; & Server.MapPath(&quot;pto.mdb&quot
objConn.Open ConnectionString

sqlQuery = &quot;SELECT * FROM tblrequests&quot;
Set objRS = Server.CreateObject(&quot;ADODB.Recordset&quot
objRS.Open sqlQuery, objConn, 1, 2

sqlQuery2 = &quot;SELECT Remain FROM qryTimeRemaining&quot;
Set rsTR = Server.CreateObject(&quot;ADODB.Recordset&quot
rsTR.Open sqlQuery2, objConn, 1, 2

usrTimeRemain = rsTR.fields(&quot;Remain&quot

If usrTimeRemain < strHours Then
Response.Redirect (&quot;delay.asp?DELAY_TIME=3&DELAY_MESSAGE=&quot; & strERRMESSAGE & &quot;&REDIRECT_URL=time_off_req.asp&quot

else

objRS.AddNew
objRS(&quot;Name&quot=strSender
objRS(&quot;StartDate&quot=strStart

...etc.

But it still allows the entry. Any ideas what I'm doing wrong?

Thanks for the quick responses.

 

[itsuit]

I meant to mention that I had also tried it with your original variable (testvar1), but that didn't work either.

 

[jhall01]

you will need to limit down your query2 with where statements to get it down to a single instance ie

sqlQuery2 = &quot;Select Remain FROM qryTimeRemaining WHERE Name = '&quot; & frmName & &quot;'&quot;

of course you will have to set frmName to a value. If you are using an ASP form just use the frmName = Request.Form(&quot;textboxname&quot

Plus you haven't set your strSender Variable. Again if this is coming from a FORM on a website use the Request object to set the variable and do the comparison.

ie

frmHours = Request.Form(&quot;hourtextboxname&quot

If usrTimeRemain < frmHours Then
Response.Write &quot;You don't have that much time Silly!!!&quot;
Else
ADD record code
End IF

 

[jhall01]

Just one more consideration here.

If you have a form.....set variables for every form Item using the REQUEST object of VBS.

then instead of using the strQuery and the ADD property.

Create an INSERT INTO query.

So you will have

sqlCheckHours = &quot;Select Remain FROM qryTimeRemaining WHERE Name = '&quot; & frmName & &quot;'&quot; (make sure you have a single instance)

Then do the insert SQL:

sqlInsertRequest = &quot;INSERT INTO tblRequest VALUES( '&quot; & frmvariable & &quot;', '&quot; & frmVariable2 & &quot;' )&quot;

You would execute the sqlInsertRequest if the If statement is false.

 

[itsuit]

OK, here's everything I have so far (below the form), but it still isn't working; all entries are still being submitted to the Access DB:

  strName=Request.form("name")
	strFrom=Request.form("From")
	strSender=Request.form("sender")
	strStart=Request.form("StartDate")
	strEnd=Request.form("EndDate")
	strHours=Request.form("hours")
	strManager=Request.form("Manager")
	stremail=Request.form("mailtoperson")
	strDate=Now
	strDetails=Request.form("Details")
		
	Set objConn = Server.CreateObject("ADODB.Connection")
	ConnectionString="DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=" & Server.MapPath("pto.mdb")
	objConn.Open ConnectionString

	sqlQuery = "SELECT * FROM requests"
	Set objRS = Server.CreateObject("ADODB.Recordset")
	objRS.Open sqlQuery, objConn, 1, 2
		
	sqlQuery2 = "SELECT Remain FROM qryTimeRemaining WHERE Email = '" & strName & "'" [RED]//This is the correct mapping - the field "name" on the form is actually the submitter's e-mail address//[/RED]
	Set rsTR = Server.CreateObject("ADODB.Recordset")
	rsTR.Open sqlQuery2, objConn, 1, 2
	
	usrTimeRemain = rsTR.Fields("Remain")
	
	If usrTimeRemain < strHours Then
	 Response.Write &quot;You've exceeded your available PTO. Please try again.&quot;
		
	else
	
	objRS.AddNew
	objRS(&quot;Name&quot;)=strSender
	objRS(&quot;StartDate&quot;)=strStart
	objRS(&quot;EndDate&quot;)=strEnd
	objRS(&quot;Hours&quot;)=strHours
	objRS(&quot;Manager&quot;)=strManager
	objRS(&quot;SubmitDate&quot;)=strDate
		If strDetails=&quot;&quot; Then 
		objRS(&quot;Details&quot;)=&quot;None&quot; 
		Else
		objRS(&quot;Details&quot;)=strDetails
		End If
		
	objRS.Update
	objRS.Close
	Set objRS = Nothing
	
	End If

Thanks again for all the help.

 

[jhall01]

I did notice you are not closing your recordset rsTR and setting it to nothing in this code. Don't know if that is someithing you looked over or just did not include in the code you pasted here.

Are you sure usrTimeRemain and strHours are not empty null?

You might comment out your if statement and do a
response.write &quot;Remaining Time: &quot; & usrTimeRemain
response.write &quot;Requested Time: &quot; & strHours


I have a feeling that one of these two are not being set to anything. Using these outputs you might be able to do some debugging of the database pull.

Also you might try containing the open and close of &quot;sqlQuery&quot; all within the if statement instead of opening it in the beginning and calling the add function later.

 

[itsuit]

Thank you!

You got me on the right track when I realized that my linked fields in the TimeRemaining query weren't right.

It works perfectly. Thanks again for the help.