Results 1 to 5 of 5
  1. #1
    Join Date
    Oct 2004
    Posts
    75

    Question Unanswered: multiple sources for report

    hi, I've come across this problem a few times now and I'd really love to know how to fix it!! =)
    I have a report based on a query, that requires one parameter which is the value of a control on a form. This form also has a button which opens the report. So, in the query I've set the criteria for the field as Forms!frmName!Control.Value...and all is fine with the world. But then I need to be able to open this report from another form...the field with the criteria hasn't changed, but what has changed is where the report (query) gets that value from, it's now coming from a control on this second form. I've tried a few different ways around this and none seem to be very good solutions, so I ask the experts....how would you do this?

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    A couple of suggestions (variations of which I have\ do use):

    1) Create a fairly generic function that gets hold of the query def object and alters the .SQL property. You can call this function from your form, alter the SQL to match the value in the text box and then run.

    2) Have all your criteria on a\ some pop up form(s). Have your report names and buttons on normal forms. When a user selects a report, the relevent pop up appears with the appropriate criteria boxes (combos\ list boxes\ check boxes etc). Have all your queries point at the pop up - the form you actually run your report from now becomes irrelevent.

    HTH
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Oct 2004
    Posts
    75
    thanks pootle, two great suggestions :-)

  4. #4
    Join Date
    Oct 2004
    Posts
    75
    would you be able to provide a bit of sample code for the first suggestion please? havin a bit of trouble....

  5. #5
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by Bane
    would you be able to provide a bit of sample code for the first suggestion please? havin a bit of trouble....
    Ok - this is a bit clumsy because I realised half way through that my use of this technique probably doesn't map too well to your requirements. I have knocked up the below though - see how you do:

    Code:
    Function AlterQDef(QName As String, ChangeThis As String, ToThat As String) As String
    On Error GoTo AlterQDef_Err
        Const NewQName As String = "NewQuery"
     
        Dim db As DAO.Database
        Dim QDef As DAO.QueryDef
        Dim NewQDef As DAO.QueryDef
     
        Set db = CurrentDb
     
        Set QDef = db.QueryDefs(QName)
        Set NewQDef = New DAO.QueryDef
     
        NewQDef.SQL = Replace(QDef.SQL, ChangeThis, ToThat)
        NewQDef.Name = NewQName 
     
        If QueryExists(NewQName) Then
     
            db.QueryDefs.Delete NewQName
     
        End If
     
        db.QueryDefs.Append NewQDef
     
        AlterQDef = NewQName
     
    AlterQDef_Exit:
    On Error Resume Next
    Set QDef = Nothing
    Set NewQDef = Nothing
    db.Close
    Set db = Nothing
     
        Exit Function
     
    AlterQDef_Err:
     
        MsgBox Err.Description
        AlterQDef = "error"
     
        Resume AlterQDef_Exit
     
    End Function
    Function QueryExists(QName As String) As Boolean
    On Error Resume Next
        Dim QDef As DAO.QueryDef
     
        Set QDef = Application.CurrentDb.QueryDefs(QName)
     
        If Err.Number = 0 Then
     
            QueryExists = True
     
        Else
     
            QueryExists = False
     
        End If
    End Function
    The queries SQL was:
    Code:
    SELECT T4.Value
    FROM T4
    WHERE T4.Value ="NumberHere"
    and I called it like:
    Code:
    Sub CallAlterSQL()
     
        Dim Result As String
     
        Result = AlterQDef("QDef_Alter", Chr(34) & "NumberHere" & Chr(34), "10")
     
        If Result <> "error" Then
     
            DoCmd.OpenQuery Result
     
        End If
     
    End Sub
    To enter several parameters you would need to call the function several times, using the new query name for the second and onwards calls or look at passing an array as an arg.

    A further illustration of using the query def object

    HTH
    Testimonial:
    pootle flump
    ur codings are working excelent.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •