Memo in a text box

DeutchBose

Board Regular
Joined
Mar 22, 2004
Messages
83
I have a continuous form with a few memo fields...Is there a way for a user to click the text box and have the text box expand so they can see the entire field?
 

Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,318
Office Version
  1. 365
Platform
  1. Windows
Why not create a new form that only contains a textbox, sized accordingly, with the memo field and a, possibly hidden, textbox that contains the unique ID for the record.

Then add a command button to the existing form that opens the new form at the appropriate record.
 

DeutchBose

Board Regular
Joined
Mar 22, 2004
Messages
83
I was thinking of that, but couldn't figure out how to get the pop-up form to go to the appropriate record.

The new form would have to have something in its OnOpen event that tells it to GoToRecord where [UniqueID] = [UniqueID] in the original form.

I don't think the GoToRecord method can do that...unless I'm completely missing something...
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,318
Office Version
  1. 365
Platform
  1. Windows
You do not need to use the OnOpen event.

Take a look at the arguments of the OpenForm method of DoCmd.

Here is some code I used in the past to switch between forms:
Code:
Function ChangeForms(ByRef NForm As String, ByRef OForm As String, Optional frmCriteria As String, _
                    Optional subForm As String, Optional subField As String, Optional subCriteria As String)
' this function will change from OForm to NForm
' the optional arguments allow the following
' frmCriteria - will open OForm to record according to this criteria
' subForm, subField, subCriteria - if Nform contains a subform with records
' related to a record on OForm then if these arguments are present (and correct)
' focus will move to related record on the subform

Dim subFind As Boolean
On Error GoTo Err_ChangeForms

If frmCriteria = "" Then
    DoCmd.OpenForm NForm
Else
    DoCmd.OpenForm NForm, , , frmCriteria
    ChangeForms = True
    GoTo Exit_ChangeForms
End If

subFind = Not (subForm = "" Or subField = "" Or subCriteria = "")

If subFind Then
    If IsSubForm(NForm, subForm) Then
        Forms(NForm).SetFocus
        DoCmd.GoToControl subForm
        DoCmd.GoToControl subField
        DoCmd.FindRecord subCriteria
        ChangeForms = True
    End If
Else
    ChangeForms = True
End If

Exit_ChangeForms:
    If OForm <> NForm Then
        If ChangeForms Then
            DoCmd.Close acForm, OForm
        Else
            DoCmd.Close acForm, NForm
            Forms(OForm).SetFocus
        End If
    End If
    
    Exit Function

Err_ChangeForms:
    MsgBox Err.Description, vbInformation, "Error message"
    ChangeForms = False
    Resume Exit_ChangeForms
    
End Function

This checks for a subform:

Code:
Function IsSubForm(ByVal frmName As String, ByVal subfrmName As String) As Boolean
Dim frm As Form
Dim ctrl As Control

    If Not (IsLoaded(frmName)) Then Exit Function
    Set frm = Forms(frmName)
    
    If IsControl(frmName, subfrmName) Then
        Set ctrl = frm.Controls(subfrmName)
        IsSubForm = (ctrl.ControlType = acSubform)
    Else
        IsSubForm = False
    End If
End Function

This checks if a control exists on a form:
Code:
Function IsControl(ByVal frmName As String, ByVal ctrlName) As Boolean
' returns true if ctrlName is the name of a control on the form frmName

Dim frm As Form
Dim ctrl As Control
    
    If Not (IsLoaded(frmName)) Then Exit Function
    
    Set frm = Forms(frmName)
    
    For Each ctrl In frm.Controls
        IsControl = IsControl Or (ctrl.Name = ctrlName)
        If IsControl Then Exit Function
    Next
    
End Function

And this checks if a form is loaded:
Code:
Function IsLoaded(ByVal frmName As String) As Boolean
'returns true if a form is open in datasheet or form view

Dim frm As Form
    For Each frm In Forms
        IsLoaded = (frm.Name = frmName) And ((frm.CurrentView) <> 0)
        If IsLoaded Then Exit Function
    Next
End Function

And here's a function to 'swap' forms:
Code:
Function SwapForms(NForm As String, OForm As String) As Boolean
' given two form names this function will try and swap them
' if succesful returns true

    On Error GoTo Err_SwapForms
    If Not (IsLoaded(NForm)) Then
        DoCmd.OpenForm NForm
    Else
        DoCmd.Close acForm, NForm
        DoCmd.OpenForm NForm
    End If
    
    If IsLoaded(OForm) Then
        DoCmd.Close acForm, OForm
    End If
    SwapForms = True
  
Exit_SwapForms:
    Exit Function

Err_SwapForms:
    MsgBox Err & ": " & Err.Description
    SwapForms = False
    Resume Exit_SwapForms

End Function
 

Forum statistics

Threads
1,147,673
Messages
5,742,530
Members
423,736
Latest member
dracula cyrus

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top