Data Report Print Single Record

[Lynus]

Hi folks, Heres my problem. How do I get a DataReport to print only one record? I have a database that has any number of records in it. I would like the user to select the record and have the datareport print only that record not the entire database. Any ideas on how this can be accomplished. Right now I have the Dataenvironment set and the report pointing to an Access Dbase which works fine if I want to print all the records in the database. But seeing as I only want to print one this isnt working. Thanks.

 

[johnwm]

I take it that your Dataenvironment command is set to Database object - Table and Object Name - YourTable.

If so just set the Command to SQL statement and put in the SQL statement to get the particular record you want. e.g Select * from YourTable Where fieldName = 'Smith'

To do it the easy way there is a SQL builder on the Command Properties dialog window


________________________________________________________________
If you want to get the best response to a question, please check out FAQ222-2244 first

'People who live in windowed environments shouldn't cast pointers.'

 

[Lynus]

Hi John, Ok so heres the code. I dont know what you mean by "Database object - Table and Object Name - YourTable". I thought the table was something that was specified in the Dataenvironment object command. Also where do I put the SQL statement?


Dim mypath As String
Dim searchstringtext As String
searchstringtext = bugno.Caption
mypath = App.Path
Dim newrec As ADODB.Recordset
Dim adoconnection As String
Dim sqlstring As String
sqlstring = "SELECT * FROM Bugs WHERE DBBugNo = ('" & searchstringtext & "')"
Set newrec = New ADODB.Recordset
adoconnection = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source= " + mypath + "\BugDB.mdb;Persist Security Info=False"
Debug.Print adoconnection
newrec.CursorType = adOpenKeyset
newrec.Open sqlstring, adoconnection
Debug.Print newrec.RecordCount

'open the dataenvironment
DataEnvironment1.Database.ConnectionString = adoconnection

CurrentBug.Show
GoTo endit

 

[Lynus]

OK so I figured some out. I foundout where to insert the SQL string now the question becomes how to I pass the string with the user-defined variable into the objects sql statement. Here is the revised code:

Dim mypath As String
Dim searchstringtext As String
searchstringtext = bugno.Caption
mypath = App.Path
Dim adoconnection As String
Dim sqlstring As String
sqlstring = "SELECT * FROM Bugs WHERE DBBugNo = ('" & searchstringtext & "')"
adoconnection = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source= " + mypath + "\BugDB.mdb;Persist Security Info=False"

'open the dataenvironment
DataEnvironment1.Database.ConnectionString = adoconnection
DataEnvironment1.TempBugs.CommandText = sqlstring
CurrentBug.Show
GoTo endit

I think the command text is supposed to hold the SQL string but I cant figure out how to set any of the properties on the dataenvironment object in code. It keeps telling me "Improper use of Qualifier" Are these properties read only at run time? If not how do I manipulate them?

 

[Lynus]

Ok so heres where I stand, after a couple of hours racking my brain I figured it out. Here is the resulting code:


If BugFrame.Visible = False Or Bugdescription.Text = "" Then GoTo norecord
Dim mypath As String
Dim searchstringtext As String
searchstringtext = bugno.Caption
mypath = App.Path
Dim adoconnection As String
Dim sqlstring As String
sqlstring = "SELECT * FROM Bugs WHERE DBBugNo = ('" & searchstringtext & "')"
adoconnection = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source= " + mypath + "\BugDB.mdb;Persist Security Info=False"

'open the dataenvironment
DataEnvironment1.Database.ConnectionString = adoconnection
DataEnvironment1.rsTempBugs.Open sqlstring
Debug.Print DataEnvironment1.rsTempBugs.RecordCount
CurrentBug.Show
GoTo endit

norecord:
intResult = MsgBox("No current record displayed. Please select a bug and then try to print it.", vbCritical + vbOKOnly + vbDefaultButton1 + vbApplicationModal, "Please select a record first&quot
GoTo endit
endit:
End Sub

Ok and when the data report starts up it shows my record awesome! Now, when you close the datareport(currentbug), Under the terminate option of the form I have these lines:

DataEnvironment1.rsTempBugs.Delete
DataEnvironment1.rsTempBugs.Close
DataEnvironment1.Database.Close

Ok so heres my newest problem:
While im still in the program I go to a different bug and then try to print it and I get an error on the following line:

DataEnvironment1.rsTempBugs.Open sqlstring

I cant seem to open it back up again. Error 3709
"The Connection cannot be used to perform this operation. IT is either closed or invalid in this context.

I closed the connection so I am failing to see why I am still having a problem here. Is there something I am forgetting to close?

Any ideas??????

P.S. I changed the Dataenvironment command to return a recordset. I set the max record property to 1.

 

[johnwm]

If you're going to use DataEnvironment then get the best out of it and use it's visual interface. You also need to close and re-open every time (see code below)

If you open the DataEnvironment window in design mode, right click on Command1 and select properties you will get the properties dialog box. On the General tab select SQL string then click SQL builder. From the Dataview window drag tables that you need for your query into the Design:Command1 dialog box. Drag the fields that you need into the Grid, and in the Criteria column of the required field type in "Like ?" without the quote marks. Close that dialog box and re-open Command1 properties dialog box (as above). Now select the parameters tab and in Parameter properties, give the parameter a name. OK it all, close the Dataview window and the dataenvironment window and return to your form.

In the appropriate click event use something like:[tt]

Private Sub Command1_Click()
Dim strParam1 As String
'next line adds wildcard to end of Text3.text
strParam1 = Text3.Text & "%"
With DataEnvironment1
If .Connection1.State <> 0 Then .Connection1.Close
DoEvents
.Connection1.Open
.Command1 strParam1
End With

dr1.Show
End Sub[/tt]

When you type the line .command1 and space then Intellisense shows the parameter names that you used in the Set Parameter above.

Good Luck!


________________________________________________________________
If you want to get the best response to a question, please check out FAQ222-2244 first

'People who live in windowed environments shouldn't cast pointers.'

 

[Lynus]

I got it to work, Thanks for the input John, You have been a great help.