Make input form bigger to allow for multi line text

sheusz · #1 (**permalink**) 02-07-12, 00:34

Hi all,

Just wondering if there is a way that I can increase the size of the area that a user can type in data on a form.

I have an input form that pops up before a report is printed to ask for additional user input.

Code:

Private Sub Report_Open(Cancel As Integer)

Me.UserInput.Caption = InputBox("Enter any additional comments to add to the run sheet. These comments will be printed on the bottom of the sheet.")

End Sub

This works well, and the 256 character limit isn't an issue, but I would like the user to be able to see everything that they have entered on to the form before they continue on.

Currently I get the default form as shown.

Is there a way to increase the size of the input area to allow text to automatically wrap the text to a new line?

Any thoughts or solutions would be greatly appreciated.

Sinndho · #2 (**permalink**) 02-07-12, 05:23

Create your own:

1. Create a form named "Frm_InputBox" with (see attachment):
- 1 Label named "Label_IB"
- 1 TextBox named "Text_IB"
- 1 Command button named "Command_OK":
- 1 Command button named "Command_Cancel":

2. Open the form module and paste the following code into it:

Code:

Option Compare Database
Option Explicit

Private m_clsDEEP As Cls_InputBox

Private Sub Command_Cancel_Click()

    m_clsDEEP.answer = ""
    DoCmd.Close acForm, Me.Name

End Sub

Private Sub Command_OK_Click()
    
    m_clsDEEP.answer = Me.Text_IB.Value
    DoCmd.Close acForm, Me.Name
    
End Sub

Private Sub Form_Open(Cancel As Integer)

    Set m_clsDEEP = New Cls_InputBox
    m_clsDEEP.DEEP_Attach Me
    
End Sub

3. Create a new Class Module named "Cls_InputBox" and paste the following code into it:

Code:

Option Compare Database
Option Explicit

Private Form As Form_Frm_InputBox
Private m_strRetVal As String

Public Function C_InputBox(ByVal Prompt As String, _
                           Optional ByVal Title As String = "Microsoft Office Access", _
                           Optional ByVal Default As String, _
                           Optional ByVal xpos As Long = -1, _
                           Optional ByVal ypos As Long = -1) As String
                           

    DoCmd.OpenForm "Frm_InputBox", , , , , acDialog, Prompt & ";" & Title & ";" & Default & ";" & xpos & ";" & ypos
    C_InputBox = CurrentDb.Properties("C_InputBox").Value
    
End Function

Public Function DEEP_Attach(ByRef frm As Form)

    Dim varArguments As Variant
    
    Set Form = frm
    Form.Visible = False
    Form.Command_Cancel.OnClick = "[Event Procedure]"
    Form.Command_Cancel.Caption = "Cancel"
    Form.Command_Cancel.Cancel = True
    Form.Command_OK.OnClick = "[Event Procedure]"
    Form.Command_OK.Caption = "OK"
    Form.Command_OK.Default = True
    varArguments = Split(Form.OpenArgs, ";")
    ReDim Preserve varArguments(0 To 4)
    Form.Label_IB.Caption = varArguments(0)
    Form.Caption = varArguments(1)
    Form.Text_IB.Value = varArguments(2)
    If varArguments(3) = -1 Then varArguments(3) = Form.WindowLeft
    If varArguments(4) = -1 Then varArguments(4) = Form.WindowTop
    Form.Move varArguments(3), varArguments(4)
    Form.Visible = True

End Function

Public Property Let answer(ByVal Value As String)
    
    Dim dbs As DAO.Database
    Dim pty As DAO.Property
    
    If Value = "" Then Value = Chr(255)
    Set dbs = CurrentDb
    For Each pty In dbs.Properties
        If pty.Name = "C_InputBox" Then Exit For
    Next pty
    If pty Is Nothing Then
        Set pty = dbs.CreateProperty("C_InputBox", dbText, CStr(Value))
        dbs.Properties.Append pty
    Else
        Set pty = dbs.Properties("C_InputBox")
        pty.Value = CStr(Value)
    End If
    dbs.Properties.Refresh
    Set pty = Nothing
    Set dbs = Nothing

End Property

4. In a standard module, paste the following code:

Code:

Function C_InputBox(ByVal Prompt As String, _
                    Optional ByVal Title As String = "Microsoft Office Access", _
                    Optional ByVal Default As String, _
                    Optional ByVal xpos As Long = -1, _
                    Optional ByVal ypos As Long = -1) As String

    Dim cls As Cls_InputBox
    
    Set cls = New Cls_InputBox
    C_InputBox = Replace(CurrentDb.Properties("C_InputBox").Value, Chr(255), "")

End Function

You can know use C_InputBox the way you use InputBox, e.g.:

Code:

Sub TestInputBox()

     Debug.Print C_InputBox("Hello", "Test C_InputBox", "World")
   
End Sub

sheusz · #3 (**permalink**) 02-07-12, 06:49

Hi Sinndho

Thanks for the reply. I'm not going to try it at 10.30pm!

I'll have a go tomorrow and advise.

Thanks again.

Sinndho · #4 (**permalink**) 02-07-12, 07:04

You're welcome!

sheusz · #5 (**permalink**) 02-08-12, 01:04

Hi Sinndho

I followed your steps as best I thought how but haven't been able toget this to work.

I haven't had much experience with modules so that could be where I am going wrong.

Here is what I did.

1. Created the form as instructed.

2. Opened the form module by selecting the form then going to View|Code - pasted your code as instructed.

3. Created a new class module and pasted your class modue code.

4. This step may be where I am going wrong. What is a standard module? The only module I have in the modules list is called Module 1 (which I created for a password routine) Do I paste your code in to this module or do I create a new module and call it say Module 2

5. Finally I am not sure about your last step. Do I run this in the immediate window for testing or replace the code in my report On Open event? Do I have to create a new label on my report named C_InputBox?

Sorry about all the questions, I am however learning a LOT from this exercise and appreciate your considerable effort in your reply.

Sinndho · #6 (**permalink**) 02-08-12, 02:51

Quote:

Originally Posted by sheusz

4. This step may be where I am going wrong. What is a standard module? The only module I have in the modules list is called Module 1 (which I created for a password routine) Do I paste your code in to this module or do I create a new module and call it say Module 2

A standard module is a module that is not associated with an object (Form, Report) and is not a class module. See: About modules - Access - Office.com. In this case, you can use Module 1 or create a new module. Contrarily to a class module, the name does not matter.

Quote:

Originally Posted by sheusz

5. Finally I am not sure about your last step. Do I run this in the immediate window for testing or replace the code in my report On Open event? Do I have to create a new label on my report named C_InputBox?

In any module (standard, class, Form or Report associated) you can use the C_InputBox function as you would use the built-in function InputBox. What I provided was just an example.

Quote:

Originally Posted by sheusz

Sorry about all the questions, I am however learning a LOT from this exercise and appreciate your considerable effort in your reply.

You're welcome!

sheusz · #7 (**permalink**) 02-08-12, 06:40

Hi Sinndho

Thanks for the reply. I think I get it now,

I created a module (Module2) as shown below;

Code:

Option Compare Database

Function C_InputBox(ByVal Prompt As String, _
                    Optional ByVal Title As String = "Microsoft Office Access", _
                    Optional ByVal Default As String, _
                    Optional ByVal xpos As Long = -1, _
                    Optional ByVal ypos As Long = -1) As String

    Dim cls As Cls_InputBox
    
    Set cls = New Cls_InputBox
    C_InputBox = Replace(CurrentDb.Properties("C_InputBox").Value, Chr(255), "")

End Function

And in my report On Open event procedure have the following;

Code:

Private Sub Report_Open(Cancel As Integer)

Me.UserInput.Caption = C_InputBox("Enter any additional comments to add to the run sheet. These comments will be printed on the bottom of the sheet.")

End Sub

But when I run the report I get the following error

Runtime error 3270 - Property not found

and when I open the debugger the following line from Module2 is highlighted

C_InputBox = Replace(CurrentDb.Properties("C_InputBox").Value, Chr(255), "") - Image attached

I have checked everything that I can think of but can't see where I have gone wrong.

Any ideas?

Sinndho · #8 (**permalink**) 02-08-12, 07:15

1. Open any module (Alt+F11), then open the Immediate window (Ctrl+G).

2. Paste this line of code into the immediate window:

Code:

currentdb.Properties.Append currentdb.CreateProperty("C_InputBox", dbText, chr(255))

3. Position the cursor at the end of the line and press the Enter key.

4. Retry.

sheusz · #9 (**permalink**) 02-08-12, 07:35

Hi Sinndho

Did as instructed and appended the new property and retried.

The error message is now gone but the text box does not appear.

Here is the code for the text box again. Is there something wrong with this?

Code:

Private Sub Report_Open(Cancel As Integer)

Me.UserInput.Caption = C_InputBox("Enter any additional comments to add to the run sheet. These comments will be printed on the bottom of the sheet.")

End Sub

Sinndho · #10 (**permalink**) 02-08-12, 07:40

It should work (at least it does here). Can you compile the project?

sheusz · #11 (**permalink**) 02-08-12, 07:51

I tried it again and still no luck.

When I try to compile it (make MDE file) I get an error saying that Access was unable to create an MDE database.

I didn't have any forms or objects open.

I am using access 2003 if that make any difference.

Sinndho · #12 (**permalink**) 02-08-12, 08:25

I'm using Access 2003 too. You do not need to create a .mde file. When in the VBA Editor (Alt+F11), open the Debug menu and select Compile...

I'm joining a sample database with just the necessary elements into it, so you can compare with what you have.

sheusz · #13 (**permalink**) 02-08-12, 08:28

Thanks Sinndho,

Think I'll tackle it again in the morning.

I'll post again tomorrow and let you know how I went.

Thanks again for you patience and assistance.

Sinndho · #14 (**permalink**) 02-08-12, 08:30

Please let me know how it worked. You're welcome!

sheusz · #15 (**permalink**) 02-09-12, 00:27

Hi Sinndho

SUCCESS

Turned out there was a problem with the form I created. I am not sure what because I copied yours over mine and now it works perfectly!!!!

By the way I had trouble opening your zip file with Windows Explorer. Kept coming up with a currupt file error. I have seen posts about this on this forum before but never spotted a resolution.

I ended up using WinRAR to open it with no problems at all?

Once again than you very much for your help.

Hope one day I can be of assistance to someone out there.

Internet Services

Web Development

Digital Marketing

Consumer Tech