Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Can I dynamically navigate my recordset in VBScript?

Status
Not open for further replies.
Lokoono

Lokoono

Programmer
Jun 13, 2007
34
US
In the VBSCript code below (its a fully functional .hta code), I can't figure out how to use the arrow buttons on the form in the HTA to go from record to record without manually telling it. Right now, I have it figured for 3 records per each associate, but what if there are 20 records...I can't see it being very fundamental to write IF...Then 20 times over.

The VBScript is calling from a database which has the fields: Employee ID, EmployeeName, DateRequested, TimeRequested, RequestCompletedBy, CompletedDate, CompletedTime.


Could someone please help me figure out the rs.move parts of the code to move from one record to another? When it reaches the end of the recordset, the right button goes invisible and vice versa for the left button when its at the beginning of the recordset.

Thanks!


Code: 
<html>
<head>
<HTA:APPLICATION 
     APPLICATIONNAME="Window Without a Title Bar"
     SCROLL="no"
     SINGLEINSTANCE="yes"
     SysMenu="No"
     CONTEXTMENU="no"  
     BORDER="sizable" 
SELECTION="no"    
>

<SCRIPT language="VBScript" type="text/vbscript">
Sub StartApp
window.focus()
        window.resizeTo 1024,768
window.moveTo 0,0
End Sub

Sub LookForEmployee
window.focus
If document.getElementById("IDNum").value="" Then
exit sub
end if
Const ForReading = 1
Dim objFSO, tsInc, strInc, lngStory


Set conn = CreateObject("ADODB.Connection")
' Connect to the database
strConnect = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source='c:\Lists.mdb'"
conn.Open strConnect

StrSQL = "SELECT * from [tblLists] WHERE EmployeeID = " & document.getElementById("IDNum").value
Set rs = conn.Execute(StrSQL)


StrSQL2 = "SELECT Count(*) FROM [tblLists] WHERE EmployeeID = " & document.getElementById("IDNum").value
set nCount = conn.Execute(STRSQL2)
if nCount(0)=0 then
set rs=nothing
EmployeeNotFound
Exit Sub
end if
EmployeeFound
End Sub

Sub EmployeeFound
NoEmpFound.style.visibility="hidden"
NoEmpFound.style.display="none"
dim strhtml7
strhtml7="<div style='margin-top:40px;margin-left:70px;font-size:20px;font-family:arial;font-weight:bold;color:white'>Employee's Name: &nbsp;</div><div style='margin-top:-23px;margin-left:259px;color:yellow;font-size:20px;font-family:arial;font-weight:bold' id='employeename'></div>"
strhtml7=strhtml7 & "<div style='margin-top:2px;margin-left:70px;text-align:right;width:186px;font-size:20px;font-family:arial;font-weight:bold;color:white'>Employee's ID #: &nbsp;</div><div style='margin-top:-23px;margin-left:259px;color:yellow;font-size:20px;font-family:arial;font-weight:bold' id='employeeidnum'></div>"
strhtml7=strhtml7 & "<div style='margin-top:40px;margin-left:70px;border: 1px #ffffff solid;background-color:#006668;width:770px;height:220px;'>"
strhtml7=strhtml7 & "<div style='margin-top:11px;font-family:arial;font-size:18px;color:white;font-weight:bold;text-align:center;'><u>Previous Request Date(s) For The Employee</u></div>"
strhtml7=strhtml7 & "<div style='margin-top:11px;font-family:arial;font-size:13px;color:yellow;font-weight:bold;margin-left:80px;'>Below is a history of the employee's requests.<br>Once a request is entered, the boxes below are filled with the request date/time information."
strhtml7=strhtml7 & "<br>Use the arrow buttons below to cycle through the requests.</div>"
strhtml7=strhtml7 & "<div style='margin-left:80px;margin-top:15px;height:30px;width:100px;border: solid #ffff00 1px;'>"
strhtml7=strhtml7 & "<div style='margin-left:2px;margin-top:2px;height:24px;width:94px;background-color:#FFA9B9;border: solid #000000 1px;'>"
strhtml7=strhtml7 & "<div style='margin-top:2px;font-size:13px;font-family:arial;font-weight:bold;color:black;text-align:center;'>Request Date</div></div></div>"
strhtml7=strhtml7 & "<div style='margin-left:179px;margin-top:-30px;height:30px;width:105px;border: solid #ffff00 1px;'>"
strhtml7=strhtml7 & "<div style='margin-left:2px;margin-top:2px;height:24px;width:99px;background-color:#FFA9B9;border: solid #000000 1px;'>"
strhtml7=strhtml7 & "<div style='margin-top:2px;font-size:13px;font-family:arial;font-weight:bold;color:black;text-align:center;'>Request Time</div></div></div>"
strhtml7=strhtml7 & "<div style='margin-left:283px;margin-top:-30px;height:30px;width:165px;border: solid #ffff00 1px;'>"
strhtml7=strhtml7 & "<div style='margin-left:2px;margin-top:2px;height:24px;width:159px;background-color:#FFA9B9;border: solid #000000 1px;'>"
strhtml7=strhtml7 & "<div style='margin-top:2px;font-size:13px;font-family:arial;font-weight:bold;color:black;text-align:center;'>Request Completed By</div></div></div>"
strhtml7=strhtml7 & "<div style='margin-left:447px;margin-top:-30px;height:30px;width:120px;border: solid #ffff00 1px;'>"
strhtml7=strhtml7 & "<div style='margin-left:2px;margin-top:2px;height:24px;width:114px;background-color:#FFA9B9;border: solid #000000 1px;'>"
strhtml7=strhtml7 & "<div style='margin-top:2px;font-size:13px;font-family:arial;font-weight:bold;color:black;text-align:center;'>Completed Date</div></div></div>"
strhtml7=strhtml7 & "<div style='margin-left:566px;margin-top:-30px;height:30px;width:120px;border: solid #ffff00 1px;'>"
strhtml7=strhtml7 & "<div style='margin-left:2px;margin-top:2px;height:24px;width:114px;background-color:#FFA9B9;border: solid #000000 1px;'>"
strhtml7=strhtml7 & "<div style='margin-top:2px;font-size:13px;font-family:arial;font-weight:bold;color:black;text-align:center;'>Completed Time</div></div></div>"
strhtml7=strhtml7 & "<div style='margin-left:80px;margin-top:-1px;height:30px;width:100px;border: solid #ffff00 1px;'>"
strhtml7=strhtml7 & "<div style='margin-left:2px;margin-top:2px;height:24px;width:94px;background-color:#FFFFFF;border: solid #000000 1px;'>"
strhtml7=strhtml7 & "<div style='margin-top:2px;font-size:13px;font-family:arial;font-weight:bold;color:black;text-align:center;' id=dateofrequest></div></div></div>"
strhtml7=strhtml7 & "<div style='margin-left:179px;margin-top:-30px;height:30px;width:105px;border: solid #ffff00 1px;'>"
strhtml7=strhtml7 & "<div style='margin-left:2px;margin-top:2px;height:24px;width:99px;background-color:#FFFFFF;border: solid #000000 1px;'>"
strhtml7=strhtml7 & "<div style='margin-top:2px;font-size:13px;font-family:arial;font-weight:bold;color:black;text-align:center;' id=timeofrequest></div></div></div>"
strhtml7=strhtml7 & "<div style='margin-left:283px;margin-top:-30px;height:30px;width:165px;border: solid #ffff00 1px;'>"
strhtml7=strhtml7 & "<div style='margin-left:2px;margin-top:2px;height:24px;width:159px;background-color:#FFFFFF;border: solid #000000 1px;'>"
strhtml7=strhtml7 & "<div style='margin-top:2px;font-size:13px;font-family:arial;font-weight:bold;color:black;text-align:center;' id=requestcompby></div></div></div>"
strhtml7=strhtml7 & "<div style='margin-left:447px;margin-top:-30px;height:30px;width:120px;border: solid #ffff00 1px;'>"
strhtml7=strhtml7 & "<div style='margin-left:2px;margin-top:2px;height:24px;width:114px;background-color:#FFFFFF;border: solid #000000 1px;'>"
strhtml7=strhtml7 & "<div style='margin-top:2px;font-size:13px;font-family:arial;font-weight:bold;color:black;text-align:center;' id=compdate></div></div></div>"
strhtml7=strhtml7 & "<div style='margin-left:566px;margin-top:-30px;height:30px;width:120px;border: solid #ffff00 1px;'>"
strhtml7=strhtml7 & "<div style='margin-left:2px;margin-top:2px;height:24px;width:114px;background-color:#FFFFFF;border: solid #000000 1px;'>"
strhtml7=strhtml7 & "<div style='margin-top:2px;font-size:13px;font-family:arial;font-weight:bold;color:black;text-align:center;' id=comptime></div></div></div>"
strhtml7=strhtml7 & "<div style='margin-top:12px;font-family:arial;font-size:12px;color:white;font-weight:bold;text-align:center;'>Now viewing the latest request of <span id='countreq'></span> &nbsp;requests</div>"
strhtml7=strhtml7 & "<button onfocus='window.focus()' onclick='PrevReq' id='arrowleftPrevReq' style='margin-left:20px;margin-top:-58px;width:50px;text-align:center;font-family:Wingdings 3;font-size:17px;font-weight:bold;color:black'><div style='margin-left:-5px;'>tt</div></button>"
strhtml7=strhtml7 & "<button onfocus='window.focus()' onclick='NextReq' id='arrowrightNextReq' style='margin-left:625px;margin-top:-58px;width:50px;text-align:center;font-family:Wingdings 3;font-size:17px;font-weight:bold;color:black'><div style='margin-left:-2px;'>uu</div></button>"
EmpFound.style.visibility="visible"
EmpFound.style.display="block"
EmpFound.innerHTML=strhtml7
arrowleftPrevReq.style.visibility="hidden"
arrowleftPrevReq.style.display="none"
arrowrightNextReq.style.marginleft="700px"
Set conn = CreateObject("ADODB.Connection")
' Connect to the database
strConnect = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source='c:\Lists.mdb'"
conn.Open strConnect
StrSQL1 = "SELECT Count(*) FROM [tblLists] WHERE EmployeeID = " & document.getElementById("IDNum").value
set nCount = conn.Execute(STRSQL1)
countreq.InnerHTML=nCount(0)
document.getElementById("currentrecord").value=1
document.getElementById("totalrecords").value=nCount(0)
if nCount(0)>0 then
StrSQL2 = "SELECT * from [tblLists] WHERE EmployeeID = " & document.getElementById("IDNum").value & " ORDER BY tblLists.DateRequested DESC , tblLists.TimeRequested DESC"
End If
Set rs = conn.Execute(StrSQL2)
employeename.InnerHTML=rs.fields("EmployeeName").value
employeeidnum.InnerHTML=document.getElementById("IDNum").value
If month(rs.fields("DateRequested")) < 10 Then
dateofrequest.InnerHTML="0" & month(rs.fields("DateRequested")) & "/" & day(rs.fields("DateRequested")) & "/" & year(rs.fields("DateRequested"))
compdate.InnerHTML="0" & month(rs.fields("CompletedDate")) & "/" & day(rs.fields("CompletedDate")) & "/" & year(rs.fields("CompletedDate"))
else
dateofrequest.InnerHTML=month(rs.fields("DateRequested")) & "/" & day(rs.fields("DateRequested")) & "/" & year(rs.fields("DateRequested"))
compdate.InnerHTML=month(rs.fields("compdate")) & "/" & day(rs.fields("compdate")) & "/" & year(rs.fields("compdate"))
End if
timeofrequest.InnerHTML=rs.fields("TimeRequested")
requestcompby.InnerHTML=rs.fields("RequestCompletedBy")
comptime.InnerHTML=rs.fields("CompletedTime")
End Sub

Sub NextReq
Set conn = CreateObject("ADODB.Connection")
' Connect to the database
strConnect = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source='c:\Lists.mdb'"
conn.Open strConnect
StrSQL2 = "SELECT * from [tblLists] WHERE EmployeeID = " & document.getElementById("IDNum").value & " ORDER BY tblLists.DateRequested DESC , tblLists.TimeRequested DESC"
Set rs = conn.Execute(StrSQL2)
If document.getElementById("currentrecord").value=1 then
rs.Move(1)
If month(rs.fields("DateRequested")) < 10 Then
dateofrequest.InnerHTML="0" & month(rs.fields("DateRequested")) & "/" & day(rs.fields("DateRequested")) & "/" & year(rs.fields("DateRequested"))
compdate.InnerHTML="0" & month(rs.fields("CompletedDate")) & "/" & day(rs.fields("CompletedDate")) & "/" & year(rs.fields("CompletedDate"))
else
dateofrequest.InnerHTML=month(rs.fields("DateRequested")) & "/" & day(rs.fields("DateRequested")) & "/" & year(rs.fields("DateRequested"))
compdate.InnerHTML=month(rs.fields("compdate")) & "/" & day(rs.fields("compdate")) & "/" & year(rs.fields("compdate"))
End if
timeofrequest.InnerHTML=rs.fields("TimeRequested")
requestcompby.InnerHTML=rs.fields("RequestCompletedBy")
comptime.InnerHTML=rs.fields("CompletedTime")
arrowleftPrevReq.style.display="block"
arrowleftPrevReq.style.visibility="visible"
arrowleftPrevReq.style.marginleft="18px"
arrowleftPrevReq.style.margintop="-55px"
arrowrightNextReq.style.margintop="-30px"
document.getElementById("currentrecord").value=2
else
If document.getElementById("currentrecord").value=2 then
rs.Move(2)
If month(rs.fields("DateRequested")) < 10 Then
dateofrequest.InnerHTML="0" & month(rs.fields("DateRequested")) & "/" & day(rs.fields("DateRequested")) & "/" & year(rs.fields("DateRequested"))
compdate.InnerHTML="0" & month(rs.fields("CompletedDate")) & "/" & day(rs.fields("CompletedDate")) & "/" & year(rs.fields("CompletedDate"))
else
dateofrequest.InnerHTML=month(rs.fields("DateRequested")) & "/" & day(rs.fields("DateRequested")) & "/" & year(rs.fields("DateRequested"))
compdate.InnerHTML=month(rs.fields("compdate")) & "/" & day(rs.fields("compdate")) & "/" & year(rs.fields("compdate"))
End if
timeofrequest.InnerHTML=rs.fields("TimeRequested")
requestcompby.InnerHTML=rs.fields("RequestCompletedBy")
comptime.InnerHTML=rs.fields("CompletedTime")
arrowleftPrevReq.style.display="block"
arrowleftPrevReq.style.visibility="visible"
arrowleftPrevReq.style.marginleft="18px"
arrowleftPrevReq.style.margintop="-55px"
arrowrightNextReq.style.margintop="-30px"
document.getElementById("currentrecord").value=3
else
If document.getElementById("currentrecord").value=3 then
If document.getElementById("currentrecord").value=document.getElementById("totalrecords").value then
exit sub
end if
rs.Move(3)
If month(rs.fields("DateRequested")) < 10 Then
dateofrequest.InnerHTML="0" & month(rs.fields("DateRequested")) & "/" & day(rs.fields("DateRequested")) & "/" & year(rs.fields("DateRequested"))
compdate.InnerHTML="0" & month(rs.fields("CompletedDate")) & "/" & day(rs.fields("CompletedDate")) & "/" & year(rs.fields("CompletedDate"))
else
dateofrequest.InnerHTML=month(rs.fields("DateRequested")) & "/" & day(rs.fields("DateRequested")) & "/" & year(rs.fields("DateRequested"))
compdate.InnerHTML=month(rs.fields("compdate")) & "/" & day(rs.fields("compdate")) & "/" & year(rs.fields("compdate"))
End if
timeofrequest.InnerHTML=rs.fields("TimeRequested")
requestcompby.InnerHTML=rs.fields("RequestCompletedBy")
comptime.InnerHTML=rs.fields("CompletedTime")
arrowleftPrevReq.style.display="block"
arrowleftPrevReq.style.visibility="visible"
arrowleftPrevReq.style.marginleft="18px"
arrowleftPrevReq.style.margintop="-55px"
arrowrightNextReq.style.margintop="-30px"
document.getElementById("currentrecord").value=3
end if
end if
end if
End Sub

Sub PrevReq
arrowrightNextReq.style.display="block"
arrowrightNextReq.style.visibility="visible"
arrowrightNextReq.style.margintop="-55px"
arrowleftPrevReq.style.display="none"
arrowleftPrevReq.style.display="hidden"

End Sub

Sub EmployeeNotFound
EmpFound.style.visibility="hidden"
EmpFound.style.display="none"
Dim strhtml8
strhtml8="<div style='margin-top:12px;margin-left:140px;border: 1px #ffffff solid;background-color:#aaffff;border: 2px #000000 solid; width:688px;height:128px;'>"
strhtml8=strhtml8 & "<div style='margin-top:24px;margin-left:15px;font-family:arial;font-size:20px;color:black;font-weight:bold;'>No record exists for Employee ID:&nbsp; " & document.getElementById("IDNum").value & ".</div></div>"
NoEmpFound.style.visibility="visible"
NoEmpFound.style.display="block"
NoEmpFound.innerHTML=strhtml8
End Sub



Sub ExitApp

blahtry=msgbox(vbcrlf & "Are you sure you want to exit the form?" & vbcrlf, vbyesno, "Confirmation Needed")
if blahtry=vbyes then
window.close()
End If
End Sub



</script>
<body bgcolor="navy" onload="StartApp">
<input type="text" id="currentrecord" size="9" style="margin-left:0;margin-top:0;visibility:hidden;">
<input type="text" id="totalrecords" size="9" style="margin-left:0;margin-top:0;visibility:hidden;">
<input type="text" id="moveahead" size="9" style="margin-left:0;margin-top:0;visibility:hidden;">
<div style="margin-top:-6px;margin-left:28px;font-size:20px;color:yellow;font-family:verdana;font-weight:bold;">Employee Request Lookup Form</div>
<div style="margin-top:34px;margin-left:27px;width:80px;height:30px;background-color:#99ffcc;border-right: 2px solid red;border-left: 2px solid red;border-bottom: 3px solid red;border-top: 2px solid red;">
<div style="margin-top:3px;color:black;font-size:20px;font-weight:bold;font-family:arial;text-align:center;">
Search
</div></div>
<div style="margin-top:-42px;margin-left:125px;width:442px;height:52px;background-color:#6495ED;border: 1px solid black;">
<div style="margin-top:9px;margin-left:10px;width:142px;height:33px;background-color:peachpuff;border: 1px solid black;">
<div style="margin-top:4px;color:black;font-size:19px;font-weight:bold;font-family:arial;margin-left:7px;">
Employee ID:
</div>
</div>
</div>

<input type="text" id="IDNum" onKeyDown="KeyCheck1" maxlength="4" style="padding-top:1px;padding-left:4px;width:103px;margin-left:286px;margin-top:-60px;height:30px;font-size:19px;font-family:arial;font-weight:bold;">
<input type="button" value="Find Employee" onclick="LookForEmployee" onfocus="window.focus()" style="width:152px;margin-left:11px;margin-top:-63px;height:37px;font-size:20px;font-family:arial;font-weight:bold;">
</div>
<button onfocus="window.focus()" onclick="ExitApp" style="width:110px;margin-left:714px;margin-top:-104px;height:52px;font-size:18px;font-family:arial;font-weight:bold;color:maroon;">Exit The<br>Form</button>
<span id="EmpFound"></span>
<span id="NoEmpFound"></span>
</body>
</html>
 
Status
Not open for further replies.

Similar threads

bdwilcox
  • Question
Trying to adapt VBScript to search if machine is in an AD group 6
Replies
8
Views
851
SoftwareRT
SoftwareRT
K
  • Locked
  • Question
VBSCRIPT dont work windows 11 23h2 ???
Replies
2
Views
297
SoftwareRT
SoftwareRT
Alexvb6
  • Question
Deprecation of VBScript + Classic ASP: How to Ask Microsoft to cancel their planned craziness
Replies
0
Views
139
Alexvb6
Alexvb6
wchull
  • Locked
  • Question
How do I resolve this permissions error in my script?
Replies
0
Views
163
wchull
wchull
Katto
  • Locked
  • Question
Excel VBA functions in VBScript
Replies
4
Views
238
dhookom
dhookom

Part and Inventory Search

Sponsor

Back
Top