We help IT Professionals succeed at work.

Data Environment SQL update at run time

etstiles
etstiles asked
on
Can a sql statement in a data environment object be updated at runtime?

What I want is to print a report of a specific record.  The user will decide which record to print.  When they decide I want to update the data environment's sql statement.  Then the information is passed to a data report that will be printed (I have completed this).

Also, is there a better(faster or more aesthetic) way to print out record information.  The information is stored in an Access DB, but I am not fond of setting up a report in the DB.
Comment
Watch Question

BRONZE EXPERT
Top Expert 2012

Commented:
You don't even need to use the Data Environment.

Just set your recordset (rs) to the one record and do something like:

Set DataReport1.DataSource = rs
DataReport1.Refresh   'This may be required.

Let me know if you need more details.
Anthony

Author

Commented:
Can you provide a little more detail.  I understand what your providing, but I can't get it to work just yet.

Thanks,
BRONZE EXPERT
Top Expert 2012

Commented:
I will have to get back to you this evening.

Anthony
BRONZE EXPERT
Top Expert 2012
Commented:
I appologize taking so long to get back to you.  It totally slipped my mind.

In any case here is a more detailed solution:
I am assuming that you already have a Data Report fully designed using a Data Environment.  

For this example I used the NorthWind database
1. Start a New Project
2. Add a List Box (List1)
3. Add a Command Button (Command1)
4. Add a Data Environment with a connection to the NorthWind database and a SQL Statement = Select * from Customers
5. Add a DataReport (DataReport1) and design a report
using the Data Environment created at step 1.
6. (Optional) Remove the Data Environment as it is no longer needed for the report
7. Remove the DataMember and DataSource properties of the DataReport.
8. Add the following code to the form:
Option Explicit
Private cn As ADODB.Connection
Private rs As ADODB.Recordset

Private Sub Form_Load()

Set cn = New ADODB.Connection
With cn
   .ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=NWIND.MDB"
   .Open
End With

Set rs = New ADODB.Recordset
With rs
   .Source = "Select * From Customers"
   .ActiveConnection = cn
   .CursorType = adOpenForwardOnly   'If you need to add or update change to adOpenDynamic or something appropriate
   .LockType = adLockReadOnly 'If you need to add or update change to adLockOptimistic  or something appropriate
   .Open Options:=adCmdText
   Do While Not .EOF
      List1.AddItem !CustomerID
      .MoveNext
   Loop
End With

End Sub

Private Sub Command1_Click()

With DataReport1
   Set .DataSource = rs
   .Show vbModal
End With

End Sub

Private Sub List1_Click()

rs.Filter = "CustomerID='" & List1.Text & "'"
With DataReport1
   Set .DataSource = rs
   .Show vbModal
End With
rs.Filter = vbNullString

End Sub

Private Sub Form_QueryUnload(Cancel As Integer, UnloadMode As Integer)

If rs.State = adStateOpen Then
   rs.Close
End If
If Not rs Is Nothing Then
   Set rs = Nothing
End If

If cn.State = adStateOpen Then
   cn.Close
End If
If Not cn Is Nothing Then
   Set cn = Nothing
End If

End Sub

You will notice I am using the Filter property for the recordset.  I also could have created a new recordset with the one record.

Hope this helps,
Anthony

Author

Commented:
I haven't tried it, but it looks good.  I actually found something on the microsoft site that loop through each object on the data report and set values to accordingly.  The one problem with their method is that it is based on the order to which they were added to the page so you need to keep that in mind when writing the code.

Thank you for this option.

Explore More ContentExplore courses, solutions, and other research materials related to this topic.