Is there a 'Return' in VBA coding for printing on the next line in a text box? 2

[InkyRich]

Hi, I may just be a bit thick, but I will explain my problem.
I have created coding within a loop to produce a list of birthdays that are coming up within the 'next' 14 days, and I am trying to output these to a text box in a form at the click of a button. This is working, but each time it goes round the loop it prints on top of the previous one therefore only showing the last one in the text box.
What I need is code to select the next line on the text box each time it goes around the loop.
I hope this explains it adequately.


Hampshire UK.

A fool and his money are soon parted - so you might as well send it to me!

 

[SkipVought]

hi,

vbLF

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[dhookom]

It would have helped if you had shared your code. I would use vbCrLf.
You mentioned it prints over the top of the previous value which is a different issue.

Duane
Hook'D on Access
MS Access MVP

 

[strongm]

Quite so

 

[InkyRich]

Hi, thanks for your replies.
dhookem you mentioned including the code so I have included the relevant part, I have not included all as its not necessary.


'Read birthdates until end of file and assign values to array variables
x = 0
GetBirthDate.Open "SELECT Dateofbirth FROM TBLMembersBirthDates;", conn

Do While Not GetBirthDate.EOF
x = x + 1

BirthdateVar(x) = GetBirthDate("Dateofbirth").Value
BirthDateDiff = Right(BirthdateVar(x), 4) 'produces the year of birth
Age(x) = Right(Date, 4) - BirthDateDiff ' age of person
BirthDateNow(x) = Left(BirthdateVar(x), 6) ' complete date of birth this year
FullDate(x) = CDate(BirthDateNow(x) + ThisYear) 'adds results together to make the correct date in date form

'Sort birthdays between two dates
If FullDate(x) > TodaysDate And FullDate(x) < EndDate Then 'find the bracket between the dates

'Combine all vaiables to one variable
TotalBirthdayInfo(x) = (FirstNameVar(x) & " " & LastNameVar(x) & "'s birthday is on " & FullDate(x) & " aged " & Age(x))

'Print variable to text box on form
Me.TxtBirthdays = TotalBirthdayInfo(x)

Else
End If

GetBirthDate.MoveNext
Loop
GetBirthDate.Close

You mentioned "You mentioned it prints over the top of the previous value which is a different issue." and this is the issue as all the rest is working. Can you advise how I can prevent this from happening please?


Hampshire UK.

A fool and his money are soon parted - so you might as well send it to me!

 

[PHV]

You always replace the textbox content with the last value.
Set the the textbox value to an empty string before the loop and then replace this:
Me.TxtBirthdays = TotalBirthdayInfo(x)
with this:
Me.TxtBirthdays = Me.TxtBirthdays & TotalBirthdayInfo(x) & vbCrLf

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[InkyRich]

Brilliant, thank you very much. It's printing out fine now.

Hampshire UK.

A fool and his money are soon parted - so you might as well send it to me!

 

[dhookom]

Brilliant deserves giving a star.

Duane
Hook'D on Access
MS Access MVP

 

[Andrzejek]

You may also consider this approach:

Dim strBDays As String

Do While Not GetBirthDate.EOF
    ...
    strBDays = strBDays & TotalBirthdayInfo(x) & vbNewLine
    Debug.Print strBDays
    ...
Loop
Me.TxtBirthdays = strBDays

This way your Debug.Print will show you what you get in every loop.

Just a suggestions...

Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.