report with query

[bw2601]

I have been racking my brain for the past two days and i can't figure out how to do this.

I have a report that is tied to a query, so when you open the report it asks you for an ID number so that it will open a particular "Call record". Now I also have a form with the same data in it, that is not tied to the query, so I can view all "call records". I want to be able to email the report, and I can do that, but the problem I am having is this. The ONCLICK event that I have that will send the Report still asks for the ID number when i try to send it. What I would like it to do, is take the ID number from the Form, and automatically enter the number parameter into the input box of the report, so that when i try to email from the current form record, it uses the same ID on the Report that i am sending as an attachment.

now that i have probably confused every one, does anyone have any suggestions on how i might be able to do this?

i am really sumped.

any help you can give would be greately appreciated.

thank you.

 

[addy]

Well, a very simplistic way would be use two reports, both pretty much the same, but one will be linked to your query and the other linked to the recordsource of your form (may be a table or query - you don't specify) and then filter the report so that it only includes records where the ID Number equals the value of ID Numebr text box on your form.

It means you have two reports doing pretty much the same thing but it will work.

Just copy and paste your current report, give it a new name, change the record source, add the filter, job done.

 

[bw2601]

OK, I kind of see where you are going with this. How do you Get the report to Link with the ID field of the form? For whatever reason i thought reports were only based off of tables or queries.

 

[lupins46]

You can't use the same query for all purposes.
Either the query asks the user for an input OR it looks at the form for the value.

Set the criteria to Forms!formname!controlname in the query to make it look at the form.


As another option, the Openreport command can specify the criteria and the query has no parameter and so selects all records.
docmd.OpenReport \"myreport",,,"myfield = '" & me.controlname & "'"

(This assumes the criteria is text)

 

[bw2601]

I got it, in the form i just altered where it asked for the ID number and made the ID number of the form Equil the ID nubmer of the Report. Thanks for all of your help

 

[Remou]

Hi bw2601
Here is another (rough) idea:

Private Sub Report_Open(Cancel As Integer)
If IsLoaded("TheForm") Then
    Me.RecordSource = _
        "SELECT TheTable.ID, TheTable.OtherFields " _
    & " FROM TheTable WHERE TheTable.ID=" & [Forms]![TheForm].[ID] 
Else
    intID = InputBox("Enter ID: ")
    Me.RecordSource = _
        "SELECT TheTable.ID, TheTable.OtherFields " _
    & " FROM TheTable WHERE TheTable.ID=" & intID 
    
End If
End Sub

Function IsLoaded(ByVal strFormName As String) As Boolean
 ' Returns True if the specified form is open in Form view or Datasheet view.
    
    Const conObjStateClosed = 0
    Const conDesignView = 0
    
    If SysCmd(acSysCmdGetObjectState, acForm, strFormName) <> conObjStateClosed Then
        If Forms(strFormName).CurrentView <> conDesignView Then
            IsLoaded = True
        End If
    End If
    
End Function