VB Coding a Dynamic Report Title... Help! 1

[SilFaz]

Hello,

I am creating a database that has a Create Report function that creates a report based on the information that is retrieved by a search query. On the Form, there are 8 fields (eg. Name, Change Date, Date Submitted, Form Type... etc.) that the user can fill in and press the Create Report button to retrieve records. Any one or all of the fields can be filled in.

What I am trying to do is to have the name of the fields that the report is based on populate the title. So if the user searches by Name, then the report will be called "Report by: Name". If they search by more than one variable, I want all of the ones used to be shown in the title...

Is there any way to do this?

I'm not very good with VB, although I have done quite a bit of it for this database. ANY help would be greatly appreciated.

Silvia

 

[LonnieJohnson]

I have a query/report builder that I created inside of an application database that does what you are asking. The user of the database asked that the criteria of the report be put in the header.

If you want, I can strip the query/report builder and send it in a demo database.

Please state that you want the query builder demo.

Send an email to the address below.

ProDev, MS Access Applications
Visit me at ==>

http://www.prodev.us

Contact me at ==>lonniejohnson@prodev.us

May God bless you beyond your imagination!!!

 

[LonnieJohnson]

Also state if you want it in A97 or A2K.

ProDev, MS Access Applications
Visit me at ==>

http://www.prodev.us

Contact me at ==>lonniejohnson@prodev.us

May God bless you beyond your imagination!!!

 

[dhookom]

Folks,
Taking discussions off-line is somewhat in opposition with the objective of these fora. It is sometimes appropriate to suggest links to other web resources but beginning a thread with offers like this are frowned on. At this point in the discussion, communications should preferrably be kept in the public forum.

Silvia,
You didn't state how you are applying the filtering to your report. Are you doing this in the where clause of the DoCmd.Openreport method? Or, do you have references to the form controls in your report's record source?

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[SilFaz]

Hi Duane,

Here is my code for filtering the records and opening the report:

Private Sub CreateReportButton_Click()

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "MainReport"
stLinkCriteria = "[FormType]=" & "'" & Me![FormType] & "'"
DoCmd.OpenReport stDocName, acPreview

Exit_CreateReportButton_Click:
Exit Sub

End Sub

Hope that helps. Thank you!

Silvia

 

[LonnieJohnson]

I was just offering a demo of something that already exist. I didn't know of any other way to get it to her. It wasn't really a discussion just a copy of a file. Is there a place here in the forum I can upload these for her?

Thanks.

ProDev, MS Access Applications
Visit me at ==>

http://www.prodev.us

Contact me at ==>lonniejohnson@prodev.us

May God bless you beyond your imagination!!!

 

[dhookom]

Lonnie,
I don't think this site offers file storage or uploading. I was just trying to keep discussions and assistance within the forum as much as possible.
Silvia,
It seems you are missing something. Your initial post suggests "8 fields" for filtering yet your code has only one and you don't even use it in the OpenReport method. From what you have provided, the report will not be filtered. Again, how are the "8 fields" used to filter the report.


Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[SilFaz]

Hello Duane,

I'm actually not using a filter... I'm using a Query.

Here is the code that I'm using for the query:

SELECT [CM Table].[FormID], [CM Table].[FormType], [CM Table].[Submitted by], [CM Table].[System], [CM Table].[Change Date], [CM Table].[DateSubmitted], [CM Table].[Priority], [CM Table].[Technical Details]
FROM [CM Table]

WHERE (
(([CM Table].[FormID]=[Forms]![Frm_SearchAttempt_Main]![FormID])
Or ([Forms]![Frm_SearchAttempt_Main]![FormID] Is Null))

And (([CM Table].[FormType]=[Forms]![Frm_SearchAttempt_Main]![FormType])
Or ([Forms]![Frm_SearchAttempt_Main]![FormType] Is Null))

And (([CM Table].[Submitted by] Like [Forms]![Frm_SearchAttempt_Main]![SubmittedBy] & "*&quot
Or ([Forms]![Frm_SearchAttempt_Main]![SubmittedBy] Is Null))

And (([CM Table].[System] Like "*" & [Forms]![Frm_SearchAttempt_Main]![System] & "*&quot
Or ([Forms]![Frm_SearchAttempt_Main]![System] Is Null))

And (([CM Table].[Change Date]=[Forms]![Frm_SearchAttempt_Main]![cldChangeDate])
Or ([Forms]![Frm_SearchAttempt_Main]![cldChangeDate] Is Null))

And (([CM Table].[DateSubmitted]=[Forms]![Frm_SearchAttempt_Main]![cdDateSubmitted])
Or ([Forms]![Frm_SearchAttempt_Main]![cdDateSubmitted] Is Null))

And (([CM Table].[Priority]=[Forms]![Frm_SearchAttempt_Main]![Priority])
Or ([Forms]![Frm_SearchAttempt_Main]![Priority] Is Null))

And ((format([CM Table].[Change Date],'mm/yyyy')=[Forms]![Frm_SearchAttempt_Main]![DTMonth])
Or ([Forms]![Frm_SearchAttempt_Main]![DTMonth] Is Null))

And (([CM Table].[Technical Details]) Like "*" & [forms]![Frm_SearchAttempt_Main]![Technical Details] & "*&quot
Or ([Forms]![Frm_SearchAttempt_Main]![Technical Details] Is Null))

ORDER BY [CM Table].[FormID];

Then I am basing my report on this query... I'm pressing a Search button, and the records that meet the criteria of what is searched for is displayed in a subform. The Create Report button uses the code that I pasted ealier... (The Search VB code is exactly the same except it uses the DoCmd.OpenForm function).

Hope this helps...

Silvia

 

[SilFaz]

Anyone...?

Silvia

 

[dhookom]

You can add a text box in the report header with a control source like:
="Form ID:" & Nz([Forms]![Frm_SearchAttempt_Main]![FormID]) ,"All&quot
Use similar expressions in a single text box or add multiple text boxes.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[SilFaz]

Hi Duane,

I tried what you told me and it did not work... The control source would not accept the line that you told me to paste in....

Thanks,
Sil

 

[SilFaz]

Hello Duane and Lonnie,

Since I'm not very good with Access, and I couldn't quite figure out how to insert what you were telling me to into my database (I wasn't even too sure of where to put it...), I've decided to just put in a text box, the contents of which will appear in the report title. The users will need to type in which fields they used to retrieve records if they want that information to show in the report title.

So instead of having the user retrieve records (for example) submitted by Silvia, then press the Create reports button and have the report pop up with "Report by: Submitted by" automatically populating the title, they'll need to type field names (Submitted by, in this case) in the text box manually... Then the text in that box gets put into the title of the report...

I know it's messy, and doesn't look all that good, but unfortunately, due to time and knowledge constraints, I had to settle for this...

Thank you though!!!!!

Silvia

 

[dhookom]

I just noticed and extra ). The control source for the text box should be:
="Form ID:" & Nz([Forms]![Frm_SearchAttempt_Main]![FormID] ,"All&quot

Duane
MS Access MVP
Find out how to get great answers faq219-2884.