Concatenation - what the devil?!

[gussy]

Ok, before I finally nail my head to the desk with my stapler, I would really appreciate some help on this one.

Summary: I am trying to produce a report that concatenates approx 100 text & memo fields into one long text string.

Problem: The Text Box object seems to have a limit, so I can't fit the many, many lines of [x] & [y] & [z] & ...etc code in its 'control source' field.

I've tried: breaking up the lines of concatenations into 2/3 seperate text boxes, then having a seperate text box that concatenates those 2/3 text boxes together. uh uh, doesn't work - the #error I get I assume is it is maxing the limit.

Please can anyone suggest a way of doing this?

One thought is perhaps to do it in more stages - produce 2/3 new memo fields in a new table that concatenates the previous strings into these 2/3 fields, then concatenating these fields into a text box....but I really need to streamline the database, as I've got 180 of these reports to produce.

Any suggestions would be really appreciated.

Cheers
Gus

 

[KenHigg]

Any suggestions would be really appreciated.

Cheers
Gus

Ok, but first you'll need to put some clothes on and put the cat down...

 

[gussy]

[...lays the pussy on the floor]

 

[KenHigg]

[...lays the pussy on the floor]

tmi -

Have you tried concatenating these in the underlying query?

 

[gussy]

Interesting...I like your thinking....

Let me just have a tinker....

 

[Bodisathva]

ok...now that I've recovered from the whole wet cat/dark room thing

check the relationship of the boxes in the controls array. The array is populated in the same order that you place the objects on the report/form, so assuming that objects 25 through 150 need to be concatenated, you can use:

for fieldIDX = 24 to 149
    newString =  new string & "[insert delim. of your choice here]" & me.controls(fieldIDX)
next fieldIDX

I have seen VBA throw errors for too many concatenations, line continuances, and string size(where concats are used), so this may bypass if you can concat in the query.

 

[KenHigg]

And as a side note; I suspect you may need to more normalization in your next release if you're having to do that much concatenation stuff...

 

[gussy]

Ok thanks Ken, I've made progress doing the Concats in the underlying query, although even that is maxing out eventually. It has at least got me a few stages further - much appreciated for the idea.

In fact, this d/b is writing html files; the html code is being written by the database, concatenating info inserted into the d/b with other html code already pre-inserted, to produce one complete text string. The text string is then saved as an .html file.

Hence the reason so many concats.

Bodisathva - If I could remember my vb I'd have done the whole thing that way, but I couldn't so I didn't! It really would have been an awful lot easier, but I can't find a copy of VB6 any more to re-learn, it's all gone .net now.

 

[Bodisathva]

ahhh...but the VBA behind Access 2003 is VB6...or a reasonable facimile thereof

 

[gussy]

Yes very true, Bodisathva, but it's been so long since I've used it, whilst I can remember the basic programming structure, I've forgotten much of the syntax etc. Still, one day when I get off my oversize posterior, I will re-learn...


Ken, by the way, a huuuge thanks - I cracked it over the weekend using the underlying query, so job is now sorted. You've saved me from a very long and painful headache!

Now on to one other problem which I think is going to mean I'm having to blow the cobwebs from my tired, old brain and venture into VB once again... I think I'll start a new thread begging help...

 

[gussy]

I'm so nearly there.... I just can't find how to define a field's value within a table object. I'm trying to save this report as a .html file, with the filename the actual value within a field:


Function M1_Report()
On Error GoTo M1_Report_Err

DoCmd.OutputTo acReport, "M1", "MS-DOSText(*.txt)", "value of field" & ".html", True, "", 0


M1_Report_Exit:
Exit Function

M1_Report_Err:
MsgBox Error$
Resume M1_Report_Exit

End Function




If I could just find out how to address this value in the above red bit, I've cracked it... anyone able to assist...pleeeeease?

 

[KenHigg]

DoCmd.OutputTo acReport, "M1", "MS-DOSText(*.txt)", myFldName & ".html", True, "", 0


???

 

[gussy]

Tried that... didn't work.

Presumably I need to address it correctly, so it can find the field within the table, ie something along the lines of:

TableName.FieldName.value

I've tried so many variations on this, but can't get the syntax for it to work.

 

[KenHigg]

I assume you are clicking on a button which is trying to run this line of code. ?

Is the form which has this button on it bound to the table that has the value you are trying to retrieve/use?

 

[gussy]

Yes, I'm running the code from a button on a form, and the form is tied to data from 3 separate tables.

I've tried to address any of the variables from any of these tables, it just won't do it....

[reaching for that stapler to staple my head to the desk again...]

 

[KenHigg]

Yes, I'm running the code from a button on a form, and the form is tied to data from 3 separate tables.

I've tried to address any of the variables from any of these tables, it just won't do it....

[reaching for that stapler to staple my head to the desk again...]

If the value is in a text box on the form then it's simply:

DoCmd.OutputTo acReport, "M1", "MS-DOSText(*.txt)", me!mytextboxname & ".html", True, "", 0

Try this as a test; Comment out the DoCmd line and just do something like:

msgbox me!mytextboxname

(of course substitute for your real text box name...)

 

[gussy]

ah ha!

The crucial thing here is the "me!" bit - this is the address/reference I was trying to establish to tell Access where the variable was.

This line now works:

DoCmd.OutputTo acReport, "M1", "MS-DOSText(*.txt)", Me!fieldname & ".html", True, "", 0

I'm going to have a further tinker with this, but this is now looking very promising.

Ken, this may be premature, but I may owe you another pint

 

[KenHigg]

ah ha!

The crucial thing here is the "me!" bit - this is the address/reference I was trying to establish to tell Access where the variable was.

This line now works:

DoCmd.OutputTo acReport, "M1", "MS-DOSText(*.txt)", Me!fieldname & ".html", True, "", 0

I'm going to have a further tinker with this, but this is now looking very promising.

Ken, this may be premature, but I may owe you another pint

Cool - If you're still holding the cat I'll pour my own...

 

[gussy]

Wahey, we're in business, this seems to be working a treat, so once again a huuuge thanks, Ken, job seems to be sorted!

Now, where did I put that cat...