Question on aligning concatenated fields in a string 1

[socomfort]

Hello everybody:

I am inserting a string resulting from a record set loop into an unbound text box. However, I am unable to align the text (column headings) with the rs results.

The record set code and the string assignment follow:

Set db = CurrentDb

sql = "SELECT tblWorkOrder.WOID, tblWorkOrder.SerialNo, tblWorkOrder.PartNumber, "
sql = sql & "tblWorkOrder.machine_req_drwg_num FROM tblWorkOrder WHERE WOID = " & Me.WOID

Set rs = db.OpenRecordset(sql, dbOpenDynaset, dbSeeChanges)

serialCount = 0
serialStr = ""

If rs.RecordCount > 0 Then
rs.MoveLast
rs.MoveFirst
Do Until rs.EOF

serialCount = serialCount + 1

serialStr = serialStr & "Work Order#" & " / " "Serial#" & " / " & "Part#" & " / " & "Drawing#" & " / " & vbCrLf

serialStr = serialStr & rs.Fields("WOID")& " / " & rs.Fields("SerialNo") & " / " & rs.Fields("PartNumber") & " / " & rs.Fields("machine_req_drwg_num") & vbCrLf & vbCrLf

rs.MoveNext
Loop
db.Close

I have tried to separate the fields with 'space()', 'vbTab', and '" "' to no avail. I have finally just separated the values using manual spaces and '/'. When I tried using vbTab, a small square is inserted between the fields.

Is there a way to align the field results with each other and the text I am using as my column headers as if they were in separate columns?

I know that I can set the position of a text box adjusting the position (# of twips), but I don't know if I can set width for a field inside of a string. Also, I cannot get the column headings to stop repeating.

One last thing -- is there a list of VBA functions or shortcuts, I guess like vbCrLf, vb...? I am not sure what these are called so I am unable to look them up.

I really appreciate any help you all can provide.

 

[BoxHead]

socomfort,

There are some vb snippets that would make this work, but I have to ask why you don't save the query and create a report based on that query.

You wouldn't need any grouping. You'd copy the labels into the report header, align the controls the way you want them and it's done.

It sounds like you're trying to create a "report" that looks like a report already looks.

IMHO


John







When Galileo theorized that Aristotle's view of the Universe contained errors, he was labeled a fool.
It wasn't until he proved it that he was called dangerous.

 

[socomfort]

Hi BoxHead:

Thanks for the reply. Actually, I already have a report linked to a subreport. Two separate queries are the record sources for each.

The main report contains header information for each page. The subreport contains all of the detail. What I am trying to do is to show additional information that does not fit easily in either of these sections.

It turns out that there are multiple values for (SerialNo, DrawingNo, and PartNo) for each WOID in some cases only. So, my record set isolates the instances where multiples exist.

My idea is to insert them into a text box in the detail section footer. You are right, it would all fit into the pre-existing fields in either the report or sub-report if there were truly a 1:1 relationship between these fields and the work order id; sometimes this is the case, and sometimes there may be ten rows of data.

Don't really want to make my fields grow as the report would then look kind of funny. Also, where there are multiple values, I just replace the values of the unbound fields with a tag like "See Attached". The "Attached" is the rs text box.

Anyway, I hope this makes sense. So, what are those snippets again or where can I read more about them?

Thanks again for your help.

 

[ZmrAbdulla]

This is from my code snippets.. don't really remember from where I got it.
======================================================
You can't ever use constants like vbCrLf in a control source of a control. These constants are limited to code only.

You can use:
=[Paragraph1] & Chr(13) & Chr(10) & [Paragraph2]

=======================================================

________________________________________________________
Zameer Abdulla
Help to find Missing people
Sharp acids corrode their own containers.

 

[socomfort]

Good morning Zameer:

I will try your code snippet. Quick question: by '[Paragraph1]' do you mean field name (ie, WOID, SerialNo, etc)?

SHUKRIA my friend,

Ben

 

[BoxHead]

Ben, I still don't understand why another subreport wouldn't work, but there are a lot of things I don't understand and it's never kept me quiet before.

First, to negate the repeating headers, move serialStr = serialStr & "Work Order#" & " / " "Serial#" & " / " & "Part#" & " / " & "Drawing#" & " / " & vbCrLf
out of the loop. instead of starting the procedure with serialStr = "", plug in the headers there.

Second, Zameer is absolutely right (as usual) about the constants. Use the Chr() function instead of vbCrLf

Third, decide on a maximum character length for each field and pad the string with the Space() function.

Dim myLenWOID as Integer, myLenSerNum as Integer, myLenPartNum as Integer

[COLOR=green]'These are the maximum lengths.  The actual length of the field wll be
'subtracted from this number and the difference will be 'padded'.[/color]

myLenWOID = 12
myLenSerNum = 21
myLenPartNum = 10


.....
.....

serialStr = serialStr & rs.Fields("WOID")& [b]Space(myLenWOID - Len(rs.fields("WOID"))[/b] & rs.Fields("SerialNo") & [b]Space(myLenSerNum - Len(rs.fields("SerialNo")[/b] & rs.Fields("PartNumber") & [b]Space(myLenPartNum - Len(rs.fields("PartNumber")[/b] & rs.Fields("machine_req_drwg_num") & Chr(13) & Chr(10)

You'll have to play around with this a bit to get the spacing correct. The Header line will need to be edited with spaces and the replacement of vbCrLf.



HTH




When Galileo theorized that Aristotle's view of the Universe contained errors, he was labeled a fool.
It wasn't until he proved it that he was called dangerous.

 

[stix4t2]

You can use multiple sub reports on one report. If you use the space idea, keep in mind you will have to use a fixed font like courier. Proportional fonts like Arial use less space for 'i' and more space for 'm'.

 

[socomfort]

John,

Thanks for your suggestions. I have created another subreport and it is working fine. I just wanted to see what else I could do with an unbound field--that's nifty code.

stix4t2,

Thanks for the heads up on the fonts. I noticed that things weren't aligned exactly right, but it looks o.k. Once again, thanks to all of you for your valuable insights.

-Ben

 

[BoxHead]

Thanks, Ben. Glad to hear it's working.


John