#1
  1. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    May 2003
    Posts
    5
    Rep Power
    0

    Changing SQL statement used in a report


    Right well bit of background info so ppl who wanna help know what i am doing/trying to do.

    Well ive got a program for a Exam Board (AS level computing project) and I need to beable to print off Reports specific to a centre number. So i have created a report in VB and using the Data Environment used an SQL statement to get from database.

    On the reports menu form i have a text box which the user inputs the 'Centre Number' and then the report loads showing Centre Number as what user input.

    You get the idea. anyway. Ive semi got it to work so when you input the data in textbox it gets that from the database and does report. But only works once round and program needs to be closed and re-run to beable use different Centre Number.

    Private Sub cmdCentre_Click()
    DE.Commands(7).CommandText = "SELECT * FROM qryCentreRemarks WHERE (CentreNumber = '" & txtCentreReport.Text & "')"
    CentreRpt.Show
    End Sub
    That is what i used for changing the CommandText of SQL statement and it changes it, but the report somehow wont use the new input Centre Number.

    thanks for any help in advanced and if anyone could be assed to read all this

    -noginn
  2. #2
  3. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    May 2003
    Location
    Namibia eh!
    Posts
    2
    Rep Power
    0
    Hey, my first post to this forum (it looks a hellavuh lot like CodeGuru so I thought I'd give it a go - enjoy helping where I can)

    So, noginn

    I don't know if you're still after this, but try destroying the recordset that is created by DE.Commands(7).

    E.g. if Commands(7).name is cmdCommand then the recordset will be rscmdCommand.
    i.e. set rscmdCommand = nothing

    HTH

IMN logo majestic logo threadwatch logo seochat tools logo