Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

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

Posted on 2007-11-28
4
218 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

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

I was working on a PowerPoint add-in the other day and a client asked me "can you implement a feature which processes a chart when it's pasted into a slide from another deck?". It got me wondering how to hook into built-in ribbon events in Office.
Calculating holidays and working days is a function that is often needed yet it is not one found within the Framework. This article presents one approach to building a working-day calculator for use in .NET.
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

792 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