help Creating reports

[irishvber]

I am a beginner so bare with me please...

I am having trouble creating reports based on user entered data.
I have a database table made up of alot of fields one in particular is a
unique sheet number.
I want to be able to allow the user to retrieve
a report from the database (which is an access database) by entering
a valid sheet number in a text box.
I am using the data report designer. I have the report designed and also
a connection ( called conRequest) established with the database. To source
the data returned from the database I want to use an sql statement. (one of the 2 option given
when sourcing data in report designer).
Currently if I hardcode ( that is enter a valid sheet number (well one that
is in the databasae) the sql statement
e.g select * from request where sheetnumber = 4454
it retrieves the relevant information I want.
My problem is that I want the user to be able to enter a sheet number on a
form ( a form called say frmReport) in a text box ( say called txtSheetNumber)
then click a command buttonthat returns a report. I am trying to get the sql syntax correct
and am having difficulty. I am trying sql like
select * from request where sheetnumber = frmreport.txtsheetnumber.text

but I am getting an error stating "NO value given for one or more required
parametres" when I enter a number in the textbox specified in the sql statement
and click the retrieve report button.

Can anyone help?
It would be greatly appreciated.

 

[pgk]

Hi,

Did you use the Val function to convert the user entered value into a numeric value?

Like "Select * from reports where sheetNumber=" & Val(frmReports.txtSheetNumber) Hope it helps. Let me know what happens.
With regards,
PGK

 

[johnwm]

Try this:

mySQL = "SELECT * from request where sheetnumber = " & frmreport.txtsheetnumber.text

.open mySQL

Let me know if this helps
________________________________________________________________
If you are worried about how to post, please check out FAQ222-2244 first

'There are 10 kinds of people in the world: those who understand binary, and those who don't.'

 

[irishvber]

Pgk that did not work
I am lost!
What are the steps i should take.
What I do is add an data enviroment from the menu. Then
I look at the properties establish a connection. Test t. The on the properties interface i choose the SQL option button and write in the sql above.
Should i have the sql here?

Pardon my ignorance ..........I am not very knowelegable when it come to vb

 

[johnwm]

Show us the code you are using then we can comment! Let me know if this helps
________________________________________________________________
If you are worried about how to post, please check out FAQ222-2244 first

'There are 10 kinds of people in the world: those who understand binary, and those who don't.'

 

[irishvber]

I was using the data environment but recently found out you cany use variables in the sql there.

All I am looking for is a basic example using DAO to retrieve a report based on a sql statement with variables in it
i.e open a recordset then open a report on this recordset
i just can work out how to do that

 

[irishvber]

can anyone provide an example as specified above?

 

[irishvber]

Private Sub cmdShowReport_Click()

Dim cn_ForReport As ADODB.Connection
Dim rs_ForReport As ADODB.Recordset
Dim strSQL As String

Set cn_ForReport = New ADODB.Connection

cn_ForReport.ConnectionString = "Provider=Microsoft.Jet.OLEDB.3.51;Persist Security Info=False;Data Source=H:\projectsss\System.mdb"
cn_ForReport.Open

strSQL = "Select * from request where location = '" & cboWhere.Text & "'"
can anyone see whats wrong with aboeve code
Set rs_ForReport = cn_ForReport.Execute(strSQL)

Set DataReport1.DataSource = rs_ForReport

DataReport1.Show

End Sub

 

[irlbinky]

Private Sub DataReport_Initialize()
Set objConn = New ADODB.Connection
objConn.Open "provider = Microsoft.jet.oledb.3.51;data source = c:\database.mdb"

Dim objRec As New ADODB.Recordset
Dim SQL As String

SQLaction = "select * from table where value = '" & formName.txtBox &"'"
objRec.Open SQLaction, objConn

If Not objRec.EOF Then
Load DataReport2
With DataReport2
Set .DataSource = objRec
.Show 'to preview or .PrintReport to print directly
End With
Else
MsgBox "No data to display", vbInformation
End If
End Sub


Make sure your report has textboxes within the Detail section that bind to "column 1" or "column 2" - in the .DataField property.
Leave the DataReport .DataSource blank in the design, and leave all .DataMember properties blank (unless using a hierarchical recordset)

Any problems let me know.

rgds
Cathal

 

[irishvber]

how do i bind the textboxes on the report to the using the datafield?
when i go to the list of properties for the textbox and click on the datafield property there is nothing there to select.
what do i do?

 

[aklein]

>strSQL = "Select * from request where location = '" & cboWhere.Text & "'"
>can anyone see whats wrong with aboeve code

Yes. location is most likely a numeric field, but you're sending it text. Change the line to:

strSQL = "Select * from request where location = " & Val(cboWhere.Text)

as pgk suggested.

 

[irlbinky]

>how do i bind the textboxes on the report to the using the datafield?

After you insert the text box into the report, goto the properties of that box. In the Datafield box type in the name of the column from the database that you want te textbox to store.

Anymore problems let me know.