refresh datagrid with combobox and datepicker vb6

[eppitis1]

hello, i have this code

Option Explicit
Dim dbFilename As String
Dim nodeid As String
Dim rs As New ADODB.Recordset
Dim cn As ADODB.Connection

Private Sub DTPicker_start_date_change()
    If Me.DTPicker_end_date.Value < Me.DTPicker_start_date.Value Then
    Me.DTPicker_end_date.Value = Me.DTPicker_start_date.Value + TimeSerial(24, 0, 0)
    End If
    refreshRs
End Sub

Private Sub DTPicker_end_date_change()
    If Me.DTPicker_end_date.Value < Me.DTPicker_start_date.Value Then
    Me.DTPicker_start_date.Value = Me.DTPicker_end_date.Value - TimeSerial(24, 0, 0)
    End If
    refreshRs
End Sub

Private Sub DTPicker_start_hour_change()
    refreshRs
End Sub

Private Sub DTPicker_end_hour_change()
    refreshRs
End Sub
Private Sub Form_Load()
    Me.pump.AddItem "P1--01"
    Me.pump.AddItem "P2--02"
    Me.pump.AddItem "P3--03"
    Me.pump.AddItem "P4--04"
    Me.pump.AddItem "P5--05"
    Me.DataGrid.Visible = True
End Sub

Public Sub pump_Click()
   refreshRs
End Sub


Public Sub refreshRs()
    Dim sqlstr As String
    Set cn = New ADODB.Connection
    dbFilename = "Report_" & Format(Now, "MM_YY") & ".mdb"
    cn.ConnectionString = BuildConnectionString(dbFilename)
    cn.Open
    sqlstr = "SELECT * From Report WHERE(datevalue(RecDate)<= #" & Format(DTPicker_end_date.Value, "mm/dd/yy") & _
            "# and timevalue(RecDate)<= #" & Format(Me.DTPicker_end_hour, "hh:mm:ss") & _
            "# and (datevalue(RecDate)>= #" & Format$(Me.DTPicker_start_date, "mm/dd/yy") & "# and timevalue(RecDate)>= #" & _
            Format(Me.DTPicker_start_hour, "hh:mm:ss") & "# and Report.NodeID = '" & Me.pump.Text & "'));"
    
    Set rs = cn.Execute(sqlstr)
    Set DataGrid.DataSource = rs
    DataGrid.Refresh
    Me.Caption = "number of records: " & rs.RecordCount
    cn.Close
    Debug.Print sqlstr
End Sub

in the form_load i have one combobox,4datepickers(2 for selecting dates and 2 for selecting hours) and one datagrid where the results of a database(.mdb) must be shown.i want when i click from the combobox a pump or when i change a date/hour from the datepicker the refresh sub to select the exact values from the database.when i run the sqlstr in the immediate window it shows me that works fine but in the datagrid no results are entered.why is that happenning?also the cn connection which refers to the database seems to work fine.any ideas ?please help.thx

[akhileshbc]

Why don't you write it in the Click() event...???
Like this:

Private Sub DTPicker_start_hour_click()
    refreshRs
End Sub

[eppitis1]

Why don't you write it in the Click() event...???
Like this:

Private Sub DTPicker_start_hour_click()
    refreshRs
End Sub

i have tried nothing happend.the correct event is the change
event:)

[akhileshbc]

i have tried nothing happend.the correct event is the change
event

Ok... Can you upload your sample code...????

[eppitis1]

Ok... Can you upload your sample code...????

this is my code .in my first post...

[AndreRet]

I might be wrong here, but I seem to find a problem with the following -

dbFilename = "Report_" & Format(Now, "MM_YY") & ".mdb"

dbFileName in this case should be a set name. To try and format the name by using format will result in an incorrect connection, hence the empty grid.... (I presume that your database has already been named BEFORE the connection? By using "NOW" the name changes...)

I would also rather put all my dtPicker values into a textbox BEFORE the connection call. You are making use of time strings "Timevalue()" which changes by the time you are connecting to your database. Should the values be read from a set date and time in a textbox, no changes occur, thus less errors....

These are just some ideas to try. Hope it helps. I wiil be "playing with your code for a while to see if I can re-create your circumstances and then post a possible solution.

[eppitis1]

be more specific with code please ...because i am desperate and i dont understand ..thanks

[AndreRet]

I will personally do the following:

I will add four textboxes to my form, one for every dtPicker that you are going to use. I will then add the dtPicker value to that textbox -

Text1.Text = dtPicker1.Value
Text1.Text = Format(Text1.Text, "mm/dd/yy")

Once all the values are set, I will then run my sql statement -

sqlstr = "SELECT * From Report WHERE(datevalue<=" & "'" & Text1.Text & "'" & " AND timevalue<=" & "'" & Text2.Text & "'" 'and so forth.

You must still keep in mind that when you connect to your database to use the name of the database as saved on your system -

'Your code
dbFilename = "Report_" & Format(Now, "MM_YY") & ".mdb"

'Should be
dbFilename = "Report_AndThe RestOfYourDatabaseName.mdb"

If you use the "NOW" statement as in -

dbFilename = "Report_" & Format(Now, "MM_YY") & ".mdb"

, you will not be able to connect to your database, because the returned value is incorrect. In other words, you have a saved database with the name Report_11_2009.
Once you select now, the return value might not work because you will get "Report_12_2009" when you are in December etc.

I hope this clarifies some of your problems...

[rovelyn]

oh my god, i can't focus with my thesis.....

[rovelyn]

can u help me guys with my problem?....

can u give me a sample codes for searching date picker using visual basic 6.0..

can anybody help me.. this is my thesis.