Exporting Report to Word based on Search results....any tips?

[CarysW]

I'm trying to export labels as a report based on the results of a search (I need to export the labels to Word on request from the end-user).

My search consists of a search form with all fields and a 'Search' button, clicking the Search button brings up a Search box with a combo box and a text box, this is where the search is performed - the results are then shown on the search form, with next and previous buttons.

I have a button on the Form to create labels which ideally will export the predesigned report to Word. But I can't get it to do this - I have first tried just opening the report in Access using this code:

  DoCmd.OpenReport "rptSearchLbl", acViewPreview, , GCriteria

But even this just brings up all entries in the query.

And as I said, ideally it will export the report but as far as I can see there is no 'Where' in the OutputTo command

What am I doing wrong?

 

[DCrake]

What you need to do is to code the following

    DoCmd.OutputTo acReport, "[I]Your ReportNameHere[/I]", "RichTextFormat(*.rtf)", "[I]PathAndFileName.[B]Doc[/B][/I]", [COLOR="Red"]True[/COLOR], "", 0

This will output your report to Word. you can use this on the click of a button.

However you need to apply the filter to the report BEFORE you run the above code.

If your variable gCriteria is a public variable then design the report and go tot the OnLoad event of the report and enter the following

Me.Filter = gCriteria
Me.FilterOn = True

The True argument tells Access if you want to open the report in Word after it has been published.


Save the report and test.

David

 

[CarysW]

What you need to do is to code the following

    DoCmd.OutputTo acReport, "[I]Your ReportNameHere[/I]", "RichTextFormat(*.rtf)", "[I]PathAndFileName.[B]Doc[/B][/I]", [COLOR=Red]True[/COLOR], "", 0

This will output your report to Word. you can use this on the click of a button.

However you need to apply the filter to the report BEFORE you run the above code.

If your variable gCriteria is a public variable then design the report and go tot the OnLoad event of the report and enter the following

Me.Filter = gCriteria
Me.FilterOn = True

The True argument tells Access if you want to open the report in Word after it has been published.


Save the report and test.

David

Hmmm...have done all of this and it is still exporting all entries, I must be doing something wrong somewhere else! ARGH! Just when I thought I'd got the hang of it!

 

[CarysW]

For reference(if it helps) the code for my search is:

  'Generate search criteria
    If (cboSearchField) = "Name" Then
        gCriteria = ""
gCriteria = gCriteria & " [Business name] LIKE '*" & txtSearchString & "*'"
gCriteria = gCriteria & " OR [Trading As] LIKE '*" & txtSearchString & "*'"
        Else
   
        gCriteria = cboSearchField.Value & " LIKE '*" & txtSearchString & "*'"
        End If
         'Filter frmSearchA based on search criteria
        Form_frmSearchA.RecordSource = "select * from qrySearchF where " & gCriteria
        Form_frmSearchA.Caption = "qrySearchF (" & cboSearchField.Value & " contains '*" & txtSearchString & "*')"

And I have made the record source of my report the query(qrySearchF). Am I doing something wrong?

 

[CarysW]

I still haven't solved this problem.

 

[DCrake]

If you do a Debug.Print on gCriteria what does it return?
And has it been declared as a public variable in a standard module?

David

 

[CarysW]

If you do a Debug.Print on gCriteria what does it return?
And has it been declared as a public variable in a standard module?

David

From the fact that I haven't a clue what you're talking about I'm guessing that it hasn't been declared as a public variable. How and where do I do this?

And how and where do I do a Debug.Print on gCriteria?

 

[CarysW]

Right, I've declared it as a public variable. I've declared it as a Global variable.....I don't know what I'm doing wrong! HELP!

 

[CarysW]

OK. Imagine you yourself were going to create a form with a search box and a label button. You've made the 2 forms(frmSearch and frmSearchBox), you've made the query(qrySearch) it works for the search. You've made your report(rptSearchLbl).

Your code for the search is as follows:

 If (cboSearchField) = "Name" Then
        GCriteria = ""
GCriteria = GCriteria & " [Business name] LIKE '*" & txtSearchString & "*'"
GCriteria = GCriteria & " OR [Trading As] LIKE '*" & txtSearchString & "*'"
        Else
       

       'Generate search criteria
        GCriteria = cboSearchField.Value & " LIKE '*" & txtSearchString & "*'"
        End If
         'Filter frmSearchA based on search criteria
        Form_frmSearchA.RecordSource = "select * from qrySearchF where " & GCriteria
        Form_frmSearchA.Caption = "qrySearchF (" & cboSearchField.Value & " contains '*" & txtSearchString & "*')"

What would you do next(code wise) to make the labels export my search results?

 

[DCrake]

Can you send a pre 2007 snippet of your mdb to look at please.

David

 

[CarysW]

Crake;[/LEFT]885249]Can you send a p

re ​

2007 snippet of your m

db ​

to look at please.

David

It's just so big now I don't think I would be able to TBH. I've got a few design things to do to finish off my DB. The labels are the last bit I need to do so I think I might leave it for a while and see if I get a bolt of inspiration....

 

[DCrake]

Ok in the mean time take a look at this sample mdb
this discusses using public variable for queries and reports. This may be of interest to you.

David

 

[CarysW]

Ok in the mean time take a look at this sample mdb
this discusses using public variable for queries and reports. This may be of interest to you.

David

Thanks David. I've downloaded the files. My head is completely wrecked with this ATM but will have a look in the near future.