where is Memo hiding? (1 Viewer)

[madEG]

Ok I'm losing my mind...

How on Earth do I change a txt box to a memo field on a form? (I need to allow for more than 255 chars...) I've already set the backend DB (sql server) to accept the many chars, and the linked table access see's the datatype as memo. But I can't figure out how to get a memo field on the form, or how to change the current txt box to a memo control to support the extra characters.

I've been looking for 20 minutes... I guess I never used a memo field before, since I can't find/see how to set this. Arg!!!

Good grief - Help!

Access 2010. Thanks!

(how embarrassing)

 

[pbaldy]

I'm not clear on the problem. You should be able to set the control source of the text box to the field name, and simply make it the size you want (physical size).

 

[madEG]

Hello,

(for better or for worse..) Generally I use unbound controls on a form and handle the inserts/updates/deletes using VBA on events.

In this case I had a txt box control that turned out to be too short to house the large descriptions for some data. So I lengthened the varchar to 2000 on the backend, and then "thought" I would come back to the form in design mode, delete the txt box and add a "memo" control that would handle the long strings.

Is there a way to insert a memo control on a form? Or is this only done vicariously by defining the datatype for a field and letting access do the rest?

 

[pbaldy]

Is there a way to insert a memo control on a form? Or is this only done vicariously by defining the datatype for a field and letting access do the rest?

The second one.

 

[madEG]

Thanks, Paul!

 

[pbaldy]

Happy to help!

 

[madEG]

Hello,

Ok, i thought I had this handled - but alas I don't... My text boxes are still truncating at 255 characters.

To reminder us, I'm using MS Access 2010, with sql server 2012 backend. The description fields on the backend are varchar(2000) in the tables that the access front end is linked to.

When I look at the table in design mode, of course it gives me the linked table cannot be modified warning... as normal... I see that the description field is set to memo.

In some other posts I think I am seeing that I may need to set both "text format" properties to "rich text" - is that correct?

Both being defined as, not only the text format in the design mode of the lined table for the memo field, but also the text format being also set to rich text on the form's text box -- is that correct?

I already tested using the rich text enabled on the form's control, but it still truncates. Is that because I need to also enable rich text on the linked table's text format property in access?

I was going to test that, but it said something about going to change all text in that field to rich text. I don't know that I want that, but instead I just want the text to be longer than 255 - not necessarily marked up at HTML.

Thoughts?

 

[pbaldy]

I haven't used memo data types much. Is the form based on the table or a query? I've heard about queries truncating memo fields. You might try basing the form directly on the linked table.

 

[madEG]

The form is using all unbound items, and the inserts/updates are using VBA. e.g.:

Private Sub btnAddNewRecord_Click()

  If Format(Trim(txtStratGoalName)) <> "" Then
        Dim strSQL As String
        
        strSQL = "INSERT INTO dbo_StrategicGoals (StratGoaltypeIDf, StratGoalName, StratGoalDesc) VALUES (" & _
        
[listboxStrategicGoalTypes].[Value] & _
        ", '" & _
        Replace(Format(Trim(txtStratGoalName)), "'", "''") & _
        "', '" & _
        Replace(Format(Trim(txtStratGoalDesc)), "'", "''") & "');"
        'MsgBox ("strSQL: " & strSQL)
        
        'DoCmd.SetWarnings False
        DoCmd.RunSQL strSQL
        'DoCmd.SetWarnings True
    
        txtStratGoalName = ""
        txtStratGoalDesc = ""
        
        ListBoxStratGoals.Requery
    Else
        MsgBox ("Enter Strategic Goal Info and press ADD.")
    End If


End Sub

So, you're saying to try to use a form that is directly based on the table, or a query from the table? And not as above?

 

[madEG]

...what's also interesting is that if I paste in the long strings into the table itself in access (not using the form) the long text is successful in making its way to the backend.

What's odd is that the txt field on the form allows me to paste in the great than 255 chars, but it seems to be getting clipped before it makes it to the backend...

Is that normal? The form overriding what the backend can handle, even if the linked table in access can accept the longer than 255 chars?

 

[pbaldy]

You might try not using the Format() function:

http://allenbrowne.com/ser-63.html

 

[madEG]

THAT WAS IT! I removed format(), [which I think I was doing to turn null into blank some old habit so I wouldn't get errors on insert...] and the clipping stopped!

THANK YOU!

 

[pbaldy]

Happy to help! I'd use Nz() to handle potential Null's, though I don't know if it would cause the same problem.