Member Avatar for firoz.raj

sir i want if user click on submit all the records should come form production table if it match dtpicker1 and dtpicker2 value i have
written a code but it is not working kindly let me know.

Private Sub Command1_Click()
 Set rs = New adodb.Recordset
[B]rs.Open "select * from production  where issue_date =  dtpicker1.Value  and dtpicker2.value, con, adOpenDynamic, adLockOptimistic[/B]"
If Not rs.EOF Then
With DataReport3.Sections("section1").Controls
  .Item("text1").DataField = rs.Fields("productname").Name
    End With
    Set DataReport3.DataSource = rs
    DataReport3.Orientation = rptOrientLandscape
    DataReport3.Show
   End If
   End Sub
  • 2 Contributors
  • 4 Replies
  • 119 Views
  • 9 Hours Discussion Span
  • Latest Post Latest Post by firoz.raj

Dani AI

Generated

Brief summary and diagnosis: wants to show production rows between two datepicker values. The runtime error 3709 indicates the Recordset was closed when you tried to use it — commonly caused by a malformed SQL string (control names left inside the SQL literal), a closed connection, or an unsupported cursor type for the DataReport. pointed out the bad SQL but did not show a working pattern.

What to change (short checklist):

  • Do not put control names directly into the SQL text. Pass actual date values.
  • Prefer an ADODB.Command with parameters to avoid date-format issues.
  • For DataReport binding set rs.CursorLocation = adUseClient and use adOpenStatic/adLockReadOnly so the report can consume the RS.
  • Always verify con.State is open and check rs.State (or Not rs.EOF) before accessing fields.
  • When assigning a control DataField use the field name string, e.g. Controls.Item("text1").DataField = "productname".

Example pattern (VB6 + ADO) — parameterized and client-cursor before open:

Dim cmd As ADODB.Command
Dim rs  As ADODB.Recordset

Set cmd = New ADODB.Command
Set cmd.ActiveConnection = con   ' ensure con is open first
cmd.CommandText = "SELECT * FROM production WHERE issue_date BETWEEN ? AND ?"
cmd.CommandType = adCmdText
cmd.Parameters.Append cmd.CreateParameter("pStart", adDate, adParamInput, , CDate(dtpStart.Value))
cmd.Parameters.Append cmd.CreateParameter("pEnd",   adDate, adParamInput, , CDate(dtpEnd.Value))

Set rs = New ADODB.Recordset
rs.CursorLocation = adUseClient
rs.Open cmd, , adOpenStatic, adLockReadOnly

If rs.State = adStateOpen And Not rs.EOF Then
  DataReport3.DataSource = rs
  DataReport3.Sections("section1").Controls.Item("text1").DataField = "productname"
  DataReport3.Show
End If

Extra cautions: if the DB is Access and you build SQL strings instead of parameters, wrap dates with # (US format) or convert to an unambiguous format. If issue_date stores time, make the end bound include the whole day (or use DateValue). Check connection and add basic error handling so you can see why an OPEN failed instead of getting a closed RS error.

Member Avatar for ryan311

lolz this

rs.Open "select * from production where issue_date = dtpicker1.Value and dtpicker2.value, con, adOpenDynamic, adLockOptimistic"

Member Avatar for firoz.raj

i want to see those records on a report which match
with dtpicker1.value as well as dtpicker2.value
that is why i have written.
rs.Open "select * from production where issue_date = dtpicker1.Value and dtpicker2.value, con, adOpenDynamic, adLockOptimistic"

Member Avatar for ryan311

ahihihi even i help you i'm sure you will not mark as solved this. . well thanks. .

Member Avatar for firoz.raj

how i mark as solve i am still getting runtime error 3709
how should i mark as solved you tell my friend i have written
but it is not working
Private Sub Command1_Click()
Set rs = New adodb.Recordset
'rs.Open "select * from production where issue_date = dtpicker1.Value and dtpicker2.value, con, adOpenDynamic, adLockOptimistic"
rs.Open "select * from production where issue_date = dtpicker1.Value and dtpicker2.value, con, adOpenDynamic, adLockOptimistic"
If Not rs.EOF Then
With DataReport3.Sections("section1").Controls
.Item("text1").DataField = rs.Fields("productname").Name
End With
Set DataReport3.DataSource = rs
DataReport3.Orientation = rptOrientLandscape
DataReport3.Show
End If
End Sub

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.