Carriage Return ? 1

[serino64]

Hi,

I have a calendar that I am using in my database. The calendar I found on the web and works superb! for what I am using it for. Now, I am looking for a way to structure or cleanup the way the data is pulled into the textbox on the form. The textbox I am referring to represents a day on the calendar form.

The table where the form draws it data from is called:
T_237_Concanates_EmployeeNames.

InputID InputDate InputText
1 7/1/2010 30-Mller, 90-Gordon, 180-Cox, 90-Deleway

The Form "fCalendar" and Textbox "text7" are unbound. The textbox currently displays the information in the following way:

30-Miller, 90-
Gordon, 180-Cox, 90-
Deleway

What I am looking for is something like this:

30-Miller,
90-Gordon,
180-Cox,
90-Deleway




After doing some research I think possibly this can be done using a carriage return but I am not sure how to implement this, so here is where I need some help.

The calendars on Load Event:
[blue]
Dim i As Integer, j As Integer
Dim mycontrol As Control
Dim strYear As String, nextYear As String
Dim f As Form
Dim mynum

Set f = Forms!fCalendar

For i = 1 To 37
Forms!fCalendar!("Text" & i).Visible = False
Forms!fCalendar!("Day" & i).Visible = False
Next


j = 1990

strYear = j

For i = 1 To 60
nextYear = j + i
strYear = strYear & ";" & nextYear

Next i

Me.year.RowSource = strYear
Me!month = Format(Now, "m")
Me!year = Format(Now, "yyyy")
Call Cal([month], [year])

[/blue]

The Module is called modCalendar:

[blue]
Option Compare Database
Option Explicit

Function Cal(m, Y)
Dim a
Dim DayOne
Dim gOffset
Dim f As Form
Dim workdate

Set f = Forms!fCalendar

f!month.SetFocus
m = f!month
Y = f!year

For a = 1 To 37
f("Day" & a + gOffset).Visible = False
f("Text" & a + gOffset).Visible = False
f("date" & a + gOffset) = Null
f("day" & a + gOffset) = Null
Next

DayOne = DateValue(m & "/1/" & Y)
workdate = DayOne
gOffset = Weekday(DayOne) - 1

For a = 1 To LenMonth(DayOne)
f("Day" & a + gOffset).Visible = True
f("Text" & a + gOffset).Visible = True
f("date" & a + gOffset) = workdate
workdate = workdate + 1
f("day" & a + gOffset) = a
Next

Call PutInData
End Function
----------------------------
Function LenMonth(d)
Dim start, finish

start = DateValue(month(d) & "/1/" & year(d))
finish = DateAdd("m", 1, start)
LenMonth = finish - start
End Function

----------------------------

Public Sub PutInData()
Dim sql As String
Dim f As Form
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim mynum
Dim i As Integer

Set f = Forms!fCalendar

For i = 1 To 37
f("text" & i) = Null
Next i

sql = "SELECT * FROM [T_237_Concanates_EmployeeNames] WHERE ((MONTH(InputDate) = " & f!month & " AND YEAR(InputDate) = " & f!year & ")) ORDER BY InputDate;"

Set db = CurrentDb()
Set rs = db.OpenRecordset(sql, dbOpenSnapshot)
If rs.RecordCount > 0 Then
For i = 1 To 37
If IsDate(f("date" & i)) Then
rs.FindFirst "inputdate=#" & f("date" & i) & "#"
If Not rs.NoMatch Then
f("text" & i) = rs!InputText
End If
End If
Next i
End If
End Sub
--------------------------
Function ConvertNulls(v As Variant, subs As Variant) As Variant
ConvertNulls = IIf(IsNull(v), subs, v)
End Function

Function IsLoaded(ByVal strFormName As String) As Boolean
'Purpose: Determines if a given form is loaded
'If IsLoaded("FormName") then ...

Const conObjStateClosed = 0
Const conDesignView = 0
If SysCmd(acSysCmdGetObjectState, acForm, strFormName) <> conObjStateClosed Then
If Forms(strFormName).CurrentView <> conDesignView Then
IsLoaded = True
End If
End If
End Function
[/blue]


I am using Access 2007.

 

[kjv1611]

I didn't read all the code, but assuming you CAN use a Carraige return in the object you're looking at, the string values you want for that would be one of the following:

Chr(13) [green]'ASCII Character code for Carriage Return, <Enter> Button[/green]

Chr(10) [green]'Line Feed - in some places, where Chr(13) won't work, Chr(10) will.[/green]

Just keep in mind, this won't work in text boxes from what I've read and seen myself. Tried to do this just a month or two ago.

 

[MajP]

you can use this in a query or calculated control to take the string value and turn it into a multiline value.


[/code]
Public Function getMultiLine(varLine As Variant) As String
Dim aLine() As String
Dim i As Integer
If Not IsNull(varLine) Then
aLine = Split(varLine, ",")
For i = LBound(aLine) To UBound(aLine) - 1
If getMultiLine = "" Then
getMultiLine = aLine(i)
Else
getMultiLine = getMultiLine & vbCrLf & aLine(i)
End If
Next i
End If
[/code]

I believe in your code you could also simply

f("text" & i) = getMultiLine(rs!InputText)

 

[serino64]

MajP

I tried simply inserting [blue]f("text" & i) = getMultiLine(rs!InputText) [/blue] but I receive the message "Sub or Function not defined" Is there somthing I am missing here?

 

[serino64]

Dim sql As String
Dim f As Form
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim mynum[blue]
Dim getmultiLine[/blue]
Dim i As Integer

Set f = Forms!fCalendar

For i = 1 To 37
f("text" & i) = Null
Next i

sql = "SELECT * FROM [T_237_Concanates_EmployeeNames] WHERE ((MONTH(InputDate) = " & f!month & " AND YEAR(InputDate) = " & f!year & ")) ORDER BY InputDate;"

Set db = CurrentDb()
Set rs = db.OpenRecordset(sql, dbOpenSnapshot)
If rs.RecordCount > 0 Then
For i = 1 To 37
If IsDate(f("date" & i)) Then
rs.FindFirst "inputdate=#" & f("date" & i) & "#"
If Not rs.NoMatch Then
[blue] f("text" & i) = getmultiLine(rs!InputText)[/blue]
End If
End If
Next i
End If

I now defined the funcition and I now see this message "type mismatch".

 

[MajP]

you need to drop the function into a standard module.

Public Function getMultiLine(varLine As Variant) As String
  Dim aLine() As String
  Dim i As Integer
  If Not IsNull(varLine) Then
    aLine = Split(varLine, ",")
    For i = LBound(aLine) To UBound(aLine) - 1
      If getMultiLine = "" Then
        getMultiLine = aLine(i)
      Else
        getMultiLine = getMultiLine & vbCrLf & aLine(i)
      End If
    Next i
  End If

I now defined the funcition and I now see this message "type mismatch".

No, you created a variable with the name "getMultiLine" or type variant.

 

[serino64]

Wow!! It Worked.

Thank You, MajP

 

[MajP]

Your welcome.

 

[PHV]

Anyway, I don't see the need of an UDF (User Defined Function):
f("text" & i) = Replace(rs!InputText, ",", vbCrLf)

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