Mail Merge True/False Issue

[coleman01]

Hi, I wondered if anyone could help me.

I have an access database as the data store of my application. Amongst other things there is a form where a user ticks a series of tick boxes to say which levels a student has met. These are stored in the database table under the relevant fields as TRUE/FALSE fields (data type: yes/no).

When I mail merge (which I have no issues with) I want the data to appear in a table which if true is returned a tick appears and if false is returned then the cell in the table is blank (in other words reflecting the initial form in a word document) as part of a report.

Can anyone tell me how to check a value of a field when mail merging a record from a table and if it says TRUE - replace the value with a tick symbol and if it says FALSE assign a null (or blank) value.

See pictures for a bit more detail.

Picture details...
1. Grading Form - what the user inputs within the application
2. What the mail merge looks like on initial merging.
3. What the table looks like in access
4. The ideal end result

Thanks folks. Any help you can provide would be great.

 

[dellman]

Help to be provided.

Can you provide me with what application you are doing your mail/merge in? Is is word? and can you send a copy of your access file (with dummy data) and I will do my very level best to help you out. If you cannot provide these, let me know as much detail as you can about how you see the end user using your system and exactly what they click on to view the end result and how you envision it working.

My own immediate ideas are that you need to create a macro in Word that reads your access database (not difficult) and then produces the output of what you see as your end result again, not difficult, but most certainly would require that you record a few sample macros to see how word would program a few things then with that in hand create the final macro.

Cheers.

 

[coleman01]

Response

Hi,
Thanks for posting a reply and offering to help. I am really grateful.

The merge process is performed in VB.NET and uses Microsoft Word 2000 for the merge process (I am limited to that software). I have attached a copy of the database (with dummy data) for your review. The tables which affect this merge process are:

1. StudentData (To display the students detail in the merged letter)
2. Grading (To display their grades for a given subject)
3. Subject (To resolve the subject name for the grading table)
4. SubjectTopic (To resolve the subject topic for the grading)
5. KeyStageGrades (this is the table with all the ticks, for a specific student, for various skills relating to one specific grading - within the grading table).

What I was thinking is, as it reads each record to be merged, when it comes accross a TRUE statement, it could replace it with the ASCII code for a tick and in contrast replace the FALSE statement with the ASCII code for a cross (or just an X). Not sure if that makes sense? I am just not sure how I would do that.

Thank you very much for your help. I really appreciate it.

P.S. - How I merge elsewhere in code...

I merge through code in the following way.. (code attached)

1. The data needed to merge is placed in a dataset.
2. Using StreamWriter the data is written to a text file.
3. The template document to merge to is then opened, followed by the hearders document.
4. The document is then merged based on the text file and headers doc and shown on screen.


Many thanks,
Chris

 

[dellman]

Many solutions, this is a possible one.

Here is a solution that you can use. If your anti-virus isn't too Vindictive against Word Macros and your security environ allows for macros to be embedded into word Documents you might consider the following code as part of a word macro to solve your problem.

Dim db As DAO.Database
db = DBEngine.OpenDatabase("name.mdb")
Dim rs As DAO.Recordset
Dim SQL As String
SQL = "SELECT subject.SubjectTitle, studentData.StudentForename...."
rs = db.OpenRecordset(SQL, dbOpenDynaset)
'process your recordset here.. and inside your loop to create the checkmark use the following code
Selection.InsertSymbol Font:="Wingdings 2", CharacterNumber:=-4016, Unicode:=True
'don't forget to set your rs and db objects to nothing or you get memory leakage.

In order for you to be able to successfully declare DAO as an object in a Word Macro you will need to go and turn on a reference to it. This is accomplished by clicking Tools --> References in the Visual Basic Editor as started from Word and finding and adding a checkmark to Microsoft DAO 3.6. If you are using access 2000 or less you may wish to use DAO 3.5 as I am not 100% certain anymore of its compatibility matrix. I know that 3.5 is NOT forwards compatible but I cannot remember of 3.6 is backwards compatible or not. Best way to find out is try it. Of course the above snippet is not complete but I think it communicates the general ghist of where I am going with it. If you have any questions on specific points, don't hesitate to post your question I will try to answer as best I can.

Cheers.

 

[coleman01]

Thanks.

Hi,
Thanks for that. I see exactly what you mean. My only issue with it, is that I have to fully automate it, using the existing system in place. Within the program a class selection box appears, so the teacher can select the class. They then press "Report" which is where this merge process should kick in and create a report for each user based on that data (which is where I need the ticks). Is there anyway to do what you say, within VB.NET or do it without the user knowing? For example, execute code within VB to do that to everything to the merged document (that is, create the ticks).

I have attached what i've got so far (based on other parts of the program where I mail merge without problems).

Sorry to be a pain. I'm new to this and its rather confusing.

Many thanks for your help and support.

Chris Coleman

 

[dellman]

Another possibility

Hmm.. I am certain there is a way to do that, however you can automate the above solution as well. Create a new table in your access database that will contain the reported data. That way your application can still store all the data that it needs to but when you want to see just a subset design your program to select the required data first and store it to the reporting table (I assume by mentioning this structure that the users will want the ability to see just part of the data they have stored rather than all of it). Once that is complete, create the macro as listed above in your word document and place the macro in the auto-run and give it full permissions to run. In this way your primary .NET program still retains full control over what the word macro can access which relegates the word macro to a simple reporting mechanism.

There are other ways to do this as well, I am certain that Crystal could do it but I have never tried. Since the character I am using for displaying the checkmark is a wingdings 2 character, I am thinking that a simple if then else statement in a crystal report would do what you want as well. In fact you have already solved that side of it. Once you have your data that you want to report on already packaged up into a dataset, you just need to build a report based on the dataset.

After doing some checking, I think the following scenario might work, but I don't have all the time required to do a full chase-down, I leave that to you since you already have the dataset created.

Here are the steps I followed
1. Created a sample database (I uses SQL Server since that is my main db)
- sample db had some boolean fields in it such as the type that access uses
2. Created a sample report based on that database and constructed the following formula.

IF {myTest.a1}=True then "P"

- you will want to create your report based on your access database file and then create your formula fields from there. You will use your formula fields as your display the fields rather than the fields that are provided by the datasource. That way you ensure that your formula gets run for each field.

3. Placed the Formula box on my report and changed its font to WingDings 2.<--this step is very important

Once you have completed the report, you should be able to use the following code to display your report in .NET.

lets say you have a data set called MyDataSet

 Dim MyNewReport As New MyNewReportWithCheckMarks
  MyNewReport.SetDataSource(MyDataSet)
  CrystalReportViewerControl.ReportSource = MyNewReport

4. If the formula works as I think it should when you apply your dataset to this report it will analyse a1 for a value of true and display a capital P in the box if it is. The Capital P in WingDings 2 is a checkmark character.

Then all you gotta do once you have finished your proof of concept is to build your table in Crystal Reports which should be reasonably easy to do.

Cheers.

 

[coleman01]

In response..

Thanks very much for that. That sounds quite possible. (he says).

I'll give it a go over the weekend and hope it will work.

I'll let you know. Thanks once again for all your time. I really appreciate it.

Regards,
Chris