School Report System - please help

[Andy248]

Hi,

I'm trying to get a table that has fields - Name, subject, Teacher, Comment (where each pupil could have 10 or more reports) into another table which has Name, subject1, comment1, teacher1, subject2, comment2 teacher2 etc so I can export this into a word template. Trouble is my vba code will do about 2/3rds of the reports i.e not all the reports in table one get imported into table 2. All the comment fields are memo's. Here's my code so far - yes you guessed I don't do VBA for a living! I though it was due to tabs and quotes in the memo fields but I think I've taken those out. Could some kind soul take a look at my code and see where I've gone wrong.

Many thanks in anticipation.

Andy

 

[gromit]

Hi Andy -

I think that I am still trying to sort out what this is supposed to do. It looks to me like you are trying to take all the individual records (Student, Subject, Teacher, Comment) and aggregate them by student (Student, Subject1, Teacher1, Comment1, Subject2, Teacher2, Comment2). Am I getting that right?

I tried to duplicate your code and ended up getting a single record written for each student record (ie, no aggregation going on). Is this similar to what you are experiencing or do you get multiple records (Comment1, Comment2, Comment3) on each line? Can you clarify what you mean by getting 2/3's of the records? (2/3's of each line? First 2/3's of the students, etc.)

- g

 

[Andy248]

Report System Fixed

Hi Gromit,

Many thanks for looking at this. I've fixed the problem today and, I hope, improved the code by getting rid of a lot of the sql lines. The main problem was that although the input table looked sorted it was in fact not. My original code would only work if I seached the output table to see if a name had already been processed. So I altered the myrs.Open table like this:-

table = "tbly12reviewsfeb04"
outtable = "tbly12consolidate"
mysql = "SELECT tbly12reviewsfeb04.Student, tbly12reviewsfeb04.Subject, tbly12reviewsfeb04.Comment,"
mysql = mysql + " tbly12reviewsfeb04.Teacher FROM tbly12reviewsfeb04 ORDER BY tbly12reviewsfeb04.Student"
myrs.Open mysql

and removed the Do.CMD lines but replaced them with another recordset. (new code attached). I'm sure my code is still very iffy but it seems to work. The only issue I've got to sort now is why, when I export the consolidate table to form the Word data source, Excel doesn't recognise the memo fields and truncates all the text! I've just used a text file for the moment but it would be nice to sort.

Many thanks for your help. Any other comments welcomed!

Andy

 

[gromit]

Good job in getting things sorted out.

When you import into Excel are you using delimited text or fixed width?

- g

 

[Andy248]

Report System cont

Hi Gromit,

What I did was attach some code to a button that runs the code then it does:-
DoCmd.RunMacro "ExportDataSource"
Call Merge(Frm)
The Macro just give the name of the output table and the name and location of the file. The type is Excel 97-2003. Module 3 just runs an instance of word:-
Dim MergeDoc, direc As String
If Left(Frm, 6) = "Remove" Then
direc = Left(Frm, 6)
Else
direc = Left(Frm, 7)
End If
MergeDoc = "N:\Reports\Templates\"
MergeDoc = MergeDoc + direc + "\" + Frm + " Template Autumn.doc"
Dim Wrd As New Word.Application
Set Wrd = CreateObject("Word.Application")

Wrd.Visible = True
Wrd.Documents.Open MergeDoc

All the fields have been preset into the Word template but the memo fields are all truncated. Is there another value I need to set to force a excel to accept all the data? Unless I'm wrong it looks like text files are exported minus the header info?

Many thanks again for your help.

Andy

 

[gromit]

Hi Andy -

Merging from Access to Word is not my strong point. I normally do my merging from Excel to Word.

Are the fields truncated in the Excel (export) from Access or are the just not making the import into Word?

Can you tell if the fields are truncated by length, or is it by character?

I've tried pasting some long strings into a memo field (~9600 characters) and they seem to come across into excel well enough.

I then tried importing a long string into a short field in Word. Although the display is truncated, it seems like all the data is there. Are you sure that the information is truncated in Word, or is it just the display? (Try converting the table to text in a temp document).

Not sure that I can think of much more to suggest.

- g

 

[Andy248]

Report System

Hi Gromit,

Well I'll have to investigate all the Excel issues. In the mean time I got the mail merge to work by setting the access macro to export the output table in rtf format and the mail merge now works a treat!

Many thanks,

Andy

 

[gromit]

Good work, Andy. I think I was just along for the ride on this one. You did all the work!!

- g