Message Box

[dr223]

Hi,

I have the following message box which works fine for the 1 field only

Dim rs As DAO.Recordset
Set rs = CurrentDb.OpenRecordset("QryCalcWSM")
If Not rs.EOF Then
   MsgBox " Business Options Based on WSM Score " & rs.Fields(0)
                                                
End If

Results (Message box appears like..)

Business Options Based on WSM Score MPESA

However, I have a list of businesses extracted on QryCalcWSM;

MPESA
SUZUKI


SAFARI
TELECOM

Now, I want to display on the message box

Business Options Based on WSM Score
MPESA
SUZUKI
SAFARI
TELECOM

any help please!! Thank you

[SoftwareMatters]

Something like this should do it:

 
Dim rs As DAO.Recordset, strMessage
Set rs = CurrentDb.OpenRecordset("QryCalcWSM")
If Not rs.EOF Then
 strMessage  = ""
 rs.movefirst
 do until rs.eof
  strMessage = strMessage & rs.Fields(0) & chr(13) & chr(10)
  rs.movenext
 loop
    MsgBox " Business Options Based on WSM Score " & strMessage 
                                                
End If

[dr223]

Excellent..

But still comes up as

Business Options Based on WSM Score MPESA
SUZUKI
SAFARI
TELECOM

Can I justify the business names centre and get something like;

Business Options Based on WSM Score
MPESA
SUZUKI
SAFARI
TELECOM

Thnak you so much ...

[SoftwareMatters]

Not sure about justification but this should solve the first issue:

If Not rs.EOF Then
strMessage = Chr(13) & chr(10)
rs.movefirst

[dr223]

Thanks Softwarematters - anyone can help with the centre justification

[Robeen]

You want this:

Business Options Based on WSM Score 
MPESA
SUZUKI
SAFARI
TELECOM

. . . to look like this:

 
Business Options Based on WSM Score 
                MPESA
                SUZUKI
                SAFARI
               TELECOM

Is that what you mean by Center justify??

Your example didn't make it clear to me . . .

[orange]

Centre of what? You have a string which is left justified by default and the values have a certain length. What exactly are you expecting?

You can use Space(10) or vbTab etc to move the string, but only you know the context of your question.


OK I see Robeen has the same concern.

[dr223]

Yes Robeen - Exactly...

How can I update the code to accomodate this?

[Robeen]

If you wanted it 'perfectly' center-justified, it could be tricky but you could calculate the Length of your string [Len(String)] and then pad with spaces like Orange suggested - using the Space() function to add spaces in front of each string.

[dr223]

where should I put the code? Can you please put a sample on the code then I will try to add spaces to try to make it look justified..

Thank you so much

[Robeen]

for example, instead of this:

 
strMessage = strMessage & rs.Fields(0) & chr(13) & chr(10)

try this:

 
strMessage = strMessage & Space(10) & rs.Fields(0) & chr(13) & chr(10)

This will start every new name after 10 spaces on the line - so it won't be prefectly center-justified.

[dr223]

Thank you so much