0

<inlinecode>
Good morning,

I am using VB6 to create a form to collect data. I have a button(cmdSubmit) that will insert and update my MS Access database. </inlinecode>
<code>
Private Sub cmdSubmit_Click()
Dim conn As New ADODB.Connection
Set conn = New ADODB.Connection
conn.Open "Mon_Mac"
Dim rs0 As New ADODB.Recordset
Set rs0 = New ADODB.Recordset
Dim checksql As String
Dim machID As String
checksql = "SELECT MachineNo FROM tblStatus WHERE MachineNo='" + txtMachNo.Text + "'"
rs0.Open checksql, conn, adOpenDynamic, adLockOptimistic
Do Until rs0.EOF
machID = rs0("MachineNo")
rs0.MoveNext
Loop

If machID <> txtMachNo.Text Then
MsgBox "This Machine Number " + txtMachNo.Text + " is not existing. Please check machine number.", vbOKOnly
Set rs0 = Nothing
Exit Sub
Else
Dim rs4 As New ADODB.Recordset
Set rs4 = New ADODB.Recordset
Dim checksql2 As String
Dim machstatus1 As String
Dim repairdate As Date
checksql2 = "SELECT MachStatus FROM tblStatus WHERE MachineNo='" + txtMachNo.Text + "'"
rs4.Open checksql2, conn, adOpenDynamic, adLockOptimistic
machstatus1 = rs4("MachStatus")
If machstatus1 <> "OK" Then
Dim xxx As String
xxx = "Machine " + txtMachNo.Text + " is under " + machstatus1 + " "
xxx = xxx + " Please Recheck the machine number if correct. If correct, then the maintenance was not able to update previous repairs conducted on machine " + txtMachNo.Text + "."
MsgBox (xxx), vbOKOnly
Set rs4 = Nothing
Exit Sub

Else
Dim rs As New ADODB.Recordset
Set rs = New ADODB.Recordset
Dim strsql As String
strsql = "INSERT INTO tblReport(MachineNo,MachStatus,ReportBy,RepairDate) VALUES('" + txtMachNo.Text + "','" + Combo1.Text + "','" + txtReportBy.Text + "',NOW())"
rs.Open strsql, conn, adOpenDynamic, adLockOptimistic
Set rs = Nothing

Dim rs1 As New ADODB.Recordset
Set rs1 = New ADODB.Recordset
Dim strsql1 As String
strsql1 = "Update tblStatus SET OldStatus='" + Combo1.Text + "',MachStatus='" + Combo1.Text + "',RepairDate=now() WHERE MachineNo='" + txtMachNo.Text + "'"
rs1.Open strsql1, conn, adOpenDynamic, adLockOptimistic
Set rs1 = Nothing

Dim rs2 As New ADODB.Recordset
Set rs2 = New ADODB.Recordset
Dim strsql2 As String
strsql2 = "INSERT INTO tblHistory(MachineNo,MachStatus,StartRepair,Category) VALUES('" + txtMachNo + "','" + Combo1.Text + "',now(),'A')"
rs2.Open strsql2, conn, adOpenDynamic, adLockOptimistic
Set rs2 = Nothing
End If
End If
Set conn2 = Nothing
MsgBox "Update Successful", vbOKOnly
Call Refresh1
WebBrowser1.Refresh
End Sub
</code>
<inlinecode>
I use the WebBrowser tool to show my query when I click a button.
</inlinecode>
<code>
Private Sub cmdSummary_Click()
WebBrowser1.Navigate "http://xx.xxx.xx.xx/foldername/summary.asp"
End Sub
</code>
<inlinecode>
I use the same WebBrowser to show another query when I click another button.
</inlinecode>
<code>
Private Sub cmdDetail_Click()
WebBrowser1.Navigate "http://xx.xxx.xx.xx/foldername/detail.asp"
End Sub
</code>
<inlinecode>
My problem is when I click on the cmdSummary and cmdDetail button, the queiried result does not show the latest record. I have to do refresh manually using right click--> Refresh. Can somebody help me fix my code? Is my problem related to internet explorer? Is yes, is there a fix? Below is my asp query.
<inlinecode>
<inlinecode>summary.asp<inlinecode>
<code>
<html>
<h2>Summary of Machines</h2>
<%
set connlist=Server.CreateObject("ADODB.Connection")
connlist.Open "Mac_Mon"
set rslist=Server.CreateObject("ADODB.Recordset")
sqllist="SELECT DownMachines FROM qryStatus"
rslist.Open sqllist,connlist

z1=rslist("DownMachines")
z2=400-z1
z3=(z2-z1)/z2*100
%>
<table border=1>
<tr>
<td>Number of Operational Machines</td>
<td><%Response.write(""&z2&"")%></td>
</tr>
<tr>
<td>Number of Down Machines</td>
<td><%Response.write(""&z1&"")%></td>
</tr>
<tr>
<td>Operational Machine Percentage</td>
<td><%Response.write formatnumber(""&z3&"",2)&"%"%></td>
</tr>
<%
rslist.close
connlist.close
set rslist=nothing
set connlist=nothing
%>
</table>
</html>
</code>
<inlinecode>detail.asp<inlinecode>

<code>

<%
set connlist=Server.CreateObject("ADODB.Connection")
connlist.Open "Mac_Mon"
set rslist=Server.CreateObject("ADODB.Recordset")
sqllist="SELECT * FROM qryDowntime WHERE MachStatus<>'OK'"
rslist.Open sqllist,connlist
%>
<html>
<table align="center" border=1>
<tr>
<th>Machine No</th>
<th>Machine Status</th>
<th>Down Since</th>
<th>Downtime (hrs)</th>
</tr>
<%
do until rslist.eof
y1=rslist("MachineNo")
y2=rslist("OldStatus")
y3=rslist("RepairDate")
y4=rslist("Downtime")
%>
<tr>
<td><%response.write(""&y1&"")%></td>
<td><%response.write(""&y2&"")%></td>
<td><%response.write(y3)%></td>
<td><%response.write formatnumber(rslist("Downtime"),1)%></td>
</tr>
<%
rslist.movenext
loop
%>
</table>
<%
rslist.close
connlist.close
set rslist=nothing
set connlist=nothing
%>
</html>
</code>
<inlinecode>
Hoping for a response

Omengski
<inlinecode>

1
Contributor
1
Reply
3
Views
10 Years
Discussion Span
Last Post by omengski
0

Good morning,

I am using VB6 to create a form to collect data. I have a button(cmdSubmit) that will insert and update my MS Access database.

Private Sub cmdSubmit_Click()
Dim conn As New ADODB.Connection
Set conn = New ADODB.Connection
conn.Open "Mon_Mac"
Dim rs0 As New ADODB.Recordset
Set rs0 = New ADODB.Recordset
Dim checksql As String
Dim machID As String
checksql = "SELECT MachineNo FROM tblStatus WHERE MachineNo='" + txtMachNo.Text + "'"
rs0.Open checksql, conn, adOpenDynamic, adLockOptimistic
Do Until rs0.EOF
machID = rs0("MachineNo")
rs0.MoveNext
Loop

If machID <> txtMachNo.Text Then
MsgBox "This Machine Number " + txtMachNo.Text + " is not existing. Please check machine number.", vbOKOnly
Set rs0 = Nothing
Exit Sub
Else
Dim rs4 As New ADODB.Recordset
Set rs4 = New ADODB.Recordset
Dim checksql2 As String
Dim machstatus1 As String
Dim repairdate As Date
checksql2 = "SELECT MachStatus FROM tblStatus WHERE MachineNo='" + txtMachNo.Text + "'"
rs4.Open checksql2, conn, adOpenDynamic, adLockOptimistic
machstatus1 = rs4("MachStatus")
If machstatus1 <> "OK" Then
Dim xxx As String
xxx = "Machine " + txtMachNo.Text + " is under " + machstatus1 + " "
xxx = xxx + " Please Recheck the machine number if correct. If correct, then the maintenance was not able to update previous repairs conducted on machine " + txtMachNo.Text + "."
MsgBox (xxx), vbOKOnly
Set rs4 = Nothing
Exit Sub

Else
Dim rs As New ADODB.Recordset
Set rs = New ADODB.Recordset
Dim strsql As String
strsql = "INSERT INTO tblReport(MachineNo,MachStatus,ReportBy,RepairDate) VALUES('" + txtMachNo.Text + "','" + Combo1.Text + "','" + txtReportBy.Text + "',NOW())"
rs.Open strsql, conn, adOpenDynamic, adLockOptimistic
Set rs = Nothing

Dim rs1 As New ADODB.Recordset
Set rs1 = New ADODB.Recordset
Dim strsql1 As String
strsql1 = "Update tblStatus SET OldStatus='" + Combo1.Text + "',MachStatus='" + Combo1.Text + "',RepairDate=now() WHERE MachineNo='" + txtMachNo.Text + "'"
rs1.Open strsql1, conn, adOpenDynamic, adLockOptimistic
Set rs1 = Nothing

Dim rs2 As New ADODB.Recordset
Set rs2 = New ADODB.Recordset
Dim strsql2 As String
strsql2 = "INSERT INTO tblHistory(MachineNo,MachStatus,StartRepair,Category) VALUES('" + txtMachNo + "','" + Combo1.Text + "',now(),'A')"
rs2.Open strsql2, conn, adOpenDynamic, adLockOptimistic
Set rs2 = Nothing
End If
End If
Set conn2 = Nothing
MsgBox "Update Successful", vbOKOnly
Call Refresh1
WebBrowser1.Refresh
End Sub

I use the WebBrowser tool to show my query when I click a button.

Private Sub cmdSummary_Click()
WebBrowser1.Navigate "http://xx.xxx.xx.xx/foldername/summary.asp"
End Sub

I use the same WebBrowser to show another query when I click another button.

Private Sub cmdDetail_Click()
WebBrowser1.Navigate "http://xx.xxx.xx.xx/foldername/detail.asp"
End Sub

My problem is when I click on the cmdSummary and cmdDetail button, the queiried result does not show the latest record. I have to do refresh manually using right click--> Refresh. Can somebody help me fix my code? Is my problem related to internet explorer? Is yes, is there a fix? Below is my asp query.

summary.asp

<html>
<h2>Summary of Machines</h2>
<%
set connlist=Server.CreateObject("ADODB.Connection")
connlist.Open "Mac_Mon"
set rslist=Server.CreateObject("ADODB.Recordset")
sqllist="SELECT DownMachines FROM qryStatus"
rslist.Open sqllist,connlist

z1=rslist("DownMachines")
z2=400-z1
z3=(z2-z1)/z2*100
%>
<table border=1>
<tr>
<td>Number of Operational Machines</td>
<td><%Response.write(""&z2&"")%></td>
</tr>
<tr>
<td>Number of Down Machines</td>
<td><%Response.write(""&z1&"")%></td>
</tr>
<tr>
<td>Operational Machine Percentage</td>
<td><%Response.write formatnumber(""&z3&"",2)&"%"%></td>
</tr>
<%
rslist.close
connlist.close
set rslist=nothing
set connlist=nothing
%>
</table>
</html>

detail.asp

<%
set connlist=Server.CreateObject("ADODB.Connection")
connlist.Open "Mac_Mon"
set rslist=Server.CreateObject("ADODB.Recordset")
sqllist="SELECT * FROM qryDowntime WHERE MachStatus<>'OK'"
rslist.Open sqllist,connlist
%>
<html>
<table align="center" border=1>
<tr>
<th>Machine No</th>
<th>Machine Status</th>
<th>Down Since</th>
<th>Downtime (hrs)</th>
</tr>
<%
do until rslist.eof
y1=rslist("MachineNo")
y2=rslist("OldStatus")
y3=rslist("RepairDate")
y4=rslist("Downtime")
%>
<tr>
<td><%response.write(""&y1&"")%></td>
<td><%response.write(""&y2&"")%></td>
<td><%response.write(y3)%></td>
<td><%response.write formatnumber(rslist("Downtime"),1)%></td>
</tr>
<%
rslist.movenext
loop
%>
</table>
<%
rslist.close
connlist.close
set rslist=nothing
set connlist=nothing
%>
</html>

Hoping for a response

Omengski

Edited by __avd: fixed formatting.

This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.