A report based on a Form

[raindrop3]

Hello,

Maybe a stupid question, but I have BIG troulbe with a report. I want a report, based on a form. I filter the form to find specific data and from the filtered form I want to make a report. I can't make it working! Does anybody know how to make a report based on a form?

Thanks!

Albert

 

[Rich]

Use the same record source as the Form, just apply the filter to the report. Or use a filtered query as the reports record source.
HTH

 

[raindrop3]

Hello,

I think i had to explain it more in details.
The form displays a number and a character and more information. Number and character are important.

One number can have one ore more characters. Eg: 100 b, 100 c, 100 d. But if I make a selection by year or something, it can be that only 100 b is selected. But 100 has more related records.

I short: I want to make a report with the same records as are in the recordset of the form. (SELECT * FROM [SELECTION_FORM)

If you need more details, please mail me.

Thanks!

Albert

 

[raindrop3]

Problem solved!

I wrote some code that writes all the records from the form to a temporary table and on that table I have a querie. The report is based on this querie.

Rich, thanks for the reply.

Albert

 

[BukHix]

I realize you have found a solution for your post and I may of misunderstood the question but if you want to open a report based on information from the form you could use this. By the way in my example I am using an input box but you could do the same thing with a combo, text or list box.

Dim strInput As String

    strInput = InputBox("Prompt User for Criteria", "Title for prompt.")
    If strInput = "" Then Exit Sub
     
    DoCmd.OpenReport "YourReportName", acViewPreview, , _
        "[FieldName] = '" & strInput & "'"

[FieldName] equals the criteria that you are looking for on your form. So say that you have a field in your table named [Months] and you want to open a report based on September. When you are prompted with the input box you would enter September and all records on your form with Months that equal September will be shown and the rest will be ignored.

 

[Hayley Baxter]

I want to create reports based on when a contract is due to expire. At the moment I have a datediff function which displays a msgbox on click of a combobox. Basically all I want to do is draw up say reports with 3mths to run or perhaps 2mths to run. What is the best way to go about this? I know it can be done by creating queries and in the criteria put say = jan but is this my best option or does anyone know of a better way?

Thanks in advance
Hayley

 

[BukHix]

Hayley,

You can use something similar to what I posted above. You should be able to modify this to work for you. Say you have two text boxes on a form, one named txtBeginDate and txtEndDate any you want to open a form with data between 1/1/02 and 1/30/02. You would put the two dates in the correct text boxes and then click the command button with code similar to this.

DoCmd.OpenReport "EmpContactLog", acPreview, , _
    "[date]>=#" & Me![txtBeginDate] & "# And [date]<= #" & _
    Me![txtEndDate] & "#"

Records from January 1st to the 30th will be shown on the report. Hope that helps!

 

[Hayley Baxter]

Bukhix thx for that I have altered the names in the code to match my own but vb doesn't like what I have at the moment. I'm not good with vb so If I paste what I have can you help me out? I know it's probably commas and things but I don't really use a great deal of code I'm still learning this.

Private Sub CmdOpenRep_Click()
stDocName = "RepContractDates"
DoCmd.OpenReport stDocName, acPreview
[Contract_Start_date]>=#" & Me![txtFromDate] & "# And [Contract_End_date]<= #" & _ Me![txtToDate] & "#"
End Sub

Many thanks



[This message has been edited by Hayley Baxter (edited 01-10-2002).]

 

[BukHix]

Try this:

Dim stdocname As String
stdocname = "RepContractDates"

DoCmd.OpenReport stdocname, acPreview, , _
"[Contract_Start_date]>=#" & Me![txtFromDate] & _
"# And [Contract_End_date]<= #" & Me![txtToDate] & "#"


[This message has been edited by BukHix (edited 01-10-2002).]

 

[Hayley Baxter]

I tried that but I get the following runtime error(3075)

Syntax error in date in query expression.

Should I have any criteria in my query that I'm drawing the information from or does the coding mean that I do not need to do this?
As you can tell I am still quite new to access.

Thanks for your help!

 

[BukHix]

If I understand your question right you shouldn't need the query, at least not for pulling the records that match the criteria because that is what the code is for.