Solved

Using a timer control to refresh a grid which pulls data from a database in VB 6

Posted on 2007-11-28
4
217 Views
Last Modified: 2013-12-25
Please help me to refresh a grid that receives data from a database.  I want to use a VB 6 timer control to refresh the grid every 30 seconds.  

The code is attached below.
Private Sub Toolbar1_ButtonClick(ByVal Button As MSComctlLib.Button)
 
MSHFlexGrid1.Visible = True
 
'===================================================================
If Button.Index = 3 Then
'===================================================================
 
Dim conn As adodb.Connection 'connection
Dim rs As adodb.Recordset 'record set
Dim rs3 As adodb.Recordset 'record set
Dim rs4 As adodb.Recordset 'record set
Dim rs5 As adodb.Recordset 'record set
Dim rs6 As adodb.Recordset 'record set
Dim strconn As String 'conn string
Dim strsql As String
Dim inti
Dim intj
 
'===================================================================
'Open connection to Database
'===================================================================
 
strconn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & App.Path & "\TechLog.mdb"
Set conn = New adodb.Connection
conn.Open strconn
 
'Open Rs
strsql = "Select * from tbl_Open"
Set rs = New adodb.Recordset
rs.Open strsql, conn, adOpenDynamic, adLockOptimistic
 
'===================================================================
'Grid setup
'===================================================================
 
'MSHFlexGrid1.Clear
'MSHFlexGrid1.Rows = 1
'MSHFlexGrid1.Cols = rs.Fields.Count - 4
 
With MSHFlexGrid1
 
.TextMatrix(0, 1) = "Case"
.TextMatrix(0, 2) = "Firm Name"
.TextMatrix(0, 3) = "Name"
.TextMatrix(0, 4) = "User ID"
.TextMatrix(0, 5) = "Application"
.TextMatrix(0, 6) = "Problem"
.TextMatrix(0, 7) = "Working Time"
.TextMatrix(0, 8) = "Time Opened"
.TextMatrix(0, 9) = "Opened By"
 
.Row = 0
.Col = 0
.RowSel = 0
.ColSel = 9
.CellAlignment = flexAlignCenterCenter
.CellFontBold = True
 
.ColWidth(0) = 300
.ColWidth(1) = 2000
.ColWidth(2) = 2300
.ColWidth(3) = 1300
.ColWidth(4) = 2000
.ColWidth(5) = 2300
.ColWidth(6) = 2000
.ColWidth(7) = 2300
.ColWidth(8) = 2000
.ColWidth(9) = 2000
 
End With
 
'===================================================================
 
intj = 1
 
Do Until rs.EOF = True 'do until all records in recordset
 
MSHFlexGrid1.Rows = MSHFlexGrid1.Rows + 1
 
MSHFlexGrid1.TextMatrix(intj, 1) = rs.Fields("CaseID").Value
MSHFlexGrid1.TextMatrix(intj, 4) = rs.Fields("UserID").Value
MSHFlexGrid1.TextMatrix(intj, 5) = rs.Fields("ApplicationID").Value
MSHFlexGrid1.TextMatrix(intj, 6) = rs.Fields("ProblemID").Value
MSHFlexGrid1.TextMatrix(intj, 7) = rs.Fields("Time_Working").Value
MSHFlexGrid1.TextMatrix(intj, 8) = rs.Fields("Time_Open").Value
MSHFlexGrid1.TextMatrix(intj, 9) = rs.Fields("QueueID").Value
 
'================================================================================
'Application ID Selection
'================================================================================
 
ApplicationID = MSHFlexGrid1.TextMatrix(intj, 5)
Set rs3 = New adodb.Recordset
AppIDTemp = "select Application from tbl_Applications where ApplicationID = " & ApplicationID
rs3.Open AppIDTemp, conn, adOpenDynamic, adLockOptimistic
If rs3.EOF = False And rs3.BOF = False Then
ApplicationName = rs3.Fields("Application")
End If
MSHFlexGrid1.TextMatrix(intj, 5) = ApplicationName
 
'================================================================================
'FirstLast Name Selection
'================================================================================
 
UserID = MSHFlexGrid1.TextMatrix(intj, 4)
Set rs4 = New adodb.Recordset
UserIDTemp = "select [First Name], [Last Name] from [User ID] where UserID = '" & UserID & "'"
rs4.Open UserIDTemp, conn, adOpenDynamic, adLockOptimistic
If rs4.EOF = False And rs4.BOF = False Then
FirstName = rs4.Fields("First Name")
LastName = rs4.Fields("Last Name")
End If
FirstLastName = FirstName & " " & LastName
MSHFlexGrid1.TextMatrix(intj, 3) = FirstLastName
 
'================================================================================
'Problem Name Selection
'================================================================================
 
ProblemID = MSHFlexGrid1.TextMatrix(intj, 6)
Set rs5 = New adodb.Recordset
ProblemIDTemp = "select Problem from tbl_Problems where ProblemID = " & ProblemID
rs5.Open ProblemIDTemp, conn, adOpenDynamic, adLockOptimistic
If rs5.EOF = False And rs5.BOF = False Then
ProblemName = rs5.Fields("Problem")
End If
MSHFlexGrid1.TextMatrix(intj, 6) = ProblemName
 
'================================================================================
'Queue Name Selection
'================================================================================
 
QueueID = MSHFlexGrid1.TextMatrix(intj, 9)
Set rs6 = New adodb.Recordset
QueueIDTemp = "select UserName from tbl_Users where UserID = " & QueueID
rs6.Open QueueIDTemp, conn, adOpenDynamic, adLockOptimistic
If rs6.EOF = False And rs6.BOF = False Then
UserName = rs6.Fields("UserName")
End If
MSHFlexGrid1.TextMatrix(intj, 9) = UserName
 
'================================================================================
'Firm Name Selection
'================================================================================
 
UserID = MSHFlexGrid1.TextMatrix(intj, 4)
Set rs7 = New adodb.Recordset
FirmIDTemp = "select FirmSub from [User ID] where UserID = '" & UserID & "'"
rs7.Open FirmIDTemp, conn, adOpenDynamic, adLockOptimistic
If rs7.EOF = False And rs7.BOF = False Then
FirmSub = rs7.Fields("FirmSub")
End If
 
Set rs8 = New adodb.Recordset
FirmNameTemp = "select [Firm Name] from Correspondents where FirmSub = '" & FirmSub & "'"
rs8.Open FirmNameTemp, conn, adOpenDynamic, adLockOptimistic
If rs8.EOF = False And rs8.BOF = False Then
FirmName = rs8.Fields("Firm Name")
End If
MSHFlexGrid1.TextMatrix(intj, 2) = FirmName
 
'================================================================================
 
intj = intj + 1
 
rs.MoveNext
 
Loop
 
'===================================================================
 
rs.Close
Set rs = Nothing
 
rs3.Close
Set rs3 = Nothing
 
rs4.Close
Set rs4 = Nothing
 
rs5.Close
Set rs5 = Nothing
 
rs6.Close
Set rs6 = Nothing
 
rs7.Close
Set rs7 = Nothing
 
rs8.Close
Set rs8 = Nothing
 
conn.Close
Set conn = Nothing
 
End If

Open in new window

0
Comment
Question by:Tony789
  • 2
4 Comments
 
LVL 15

Accepted Solution

by:
JackOfPH earned 250 total points
ID: 20370740
First, set the timer interval to 30000 which means means 30 seconds.

Then put this on timer1_interval code.

Private sub Timer1_Interval()

Toolbar1_ButtonClick(3)

end sub
0
 

Author Comment

by:Tony789
ID: 20375102
I don't want this to refresh only when the button is clicked.  It should refresh by itself.  
0
 
LVL 10

Expert Comment

by:3_S
ID: 20385331
the code of JackOfPH is correct.
If you want to make you code more understandeable then you should put you code in a new routine for example

Private sub GetData ()
   'put all the code you have in your code when button.index = 3 here (this is the code between If Button.Index = 3 Then.... end if)

end sub

Private sub Timer1_Interval()
 call getData()
end sub

adapt your existing code

Private Sub Toolbar1_ButtonClick(ByVal Button As MSComctlLib.Button)
MSHFlexGrid1.Visible = True
'===================================================================
 If Button.Index = 3 Then
   call getData()
 end if
end sub




0
 
LVL 15

Expert Comment

by:JackOfPH
ID: 20393067
The timer will automatically refresh it for you.

Did you enabled the timer?
Set it on timers property.
Timer1.eneabled = true

0

Featured Post

Courses: Start Training Online With Pros, Today

Brush up on the basics or master the advanced techniques required to earn essential industry certifications, with Courses. Enroll in a course and start learning today. Training topics range from Android App Dev to the Xen Virtualization Platform.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Gridview selected row 9 44
how to wrap a merged cells of datagridview column in vs2010 (vb.net) 8 33
VB.NET 1 21
Help with preventing downloading a zip file 10 35
The ECB site provides FX rates for major currencies since its inception in 1999 in the form of an XML feed. The files have the following format (reducted for brevity) (CODE) There are three files available HERE (http://www.ecb.europa.eu/stats/exch…
You can of course define an array to hold data that is of a particular type like an array of Strings to hold customer names or an array of Doubles to hold customer sales, but what do you do if you want to coordinate that data? This article describes…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…

786 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question