Solved Using Default text - 240 character limit (1 Viewer)

[stu_c]

Hi all
I have a drop down list of reset wordings that the user can select the presses insert which will then insert that working into a field on a form, all works good but it seems that if the default text goes over 240 characters it wont past it all into the field any suggestions?

Public Function FUNC_BTNText()
    Dim sText As String
    sText = [Forms]![FRM_TBLALL_FullDetails]![SFRM_TBLALL_HRDetails].[Form]![Summary] & vbCrLf & [Forms]![FRM_TBLALL_FullDetails]![SFRM_TBLALL_HRDetails].[Form]![CmbDefaultText].Column(1)
    
    If Len(sText) Then
    sText = sText & Replace$([Forms]![FRM_TBLALL_FullDetails]![SFRM_TBLALL_HRDetails].[Form]![Summary].DefaultValue, """", "")
Else
    sText = Replace$([Forms]![FRM_TBLALL_FullDetails]![SFRM_TBLALL_HRDetails].[Form]![Summary].DefaultValue, """", "")
End If
[Forms]![FRM_TBLALL_FullDetails]![SFRM_TBLALL_HRDetails].[Form]![Summary] = sText

[Forms]![FRM_TBLALL_FullDetails]![SFRM_TBLALL_HRDetails].[Form]![Summary].SetFocus
pfPositionCursor [Forms]![FRM_TBLALL_FullDetails]![SFRM_TBLALL_HRDetails].[Form]![Summary], Len([Forms]![FRM_TBLALL_FullDetails]![SFRM_TBLALL_HRDetails].[Form]![Summary] & "")
End Function

 

[stu_c]

Bit of an update, I think the problem is that the dropdown list columns only allow up to 240 characters even if my table where the data is set to more, is there a way to increase the characters in a drop down column?

 

[Minty]

There is a limit on a short text field of 256 characters.
There is no way around that.

You could change the field to a Long text (Memo field in older versions) that has a much bigger storage capacity (at least 65,000 characters), but it does have other limitations.

 

[Minty]

Edit / double post.
I misread your question - No I don't think you can display more than 255 characters in the drop-down column.

And even if you could you would run out of space to read it surely, as it displays on one line?

 

[stu_c]

There is a limit on a short text field of 256 characters.
There is no way around that.

You could change the field to a Long text (Memo field in older versions) that has a much bigger storage capacity (at least 65,000 characters), but it does have other limitations.

Hi Minty,
My table where the data is stored in in Long text and all the data is there but in the actual drop down list the column limit is only around 240 which is the issue, even when I go into the Query where the dropdown list looks into all the data is there but not on the dropdown

 

[Minty]

I think it's an inbuilt restriction on the combo design.
As I said - how could you display more than that, and what purpose would it serve?

I would imagine if you have a list of standard comments/boiler-plate text each one would have a short description associated with it for quick lookup. I would use that in the combo for ease of use, not the full text

 

[stu_c]

Edit / double post.
I misread your question - No I don't think you can display more than 255 characters in the drop-down column.

And even if you could you would run out of space to read it surely, as it displays on one line?

if there a way to pull the data from the column Query the dropdown list is linked to rather from the column?

 

[stu_c]

I think it's an inbuilt restriction on the combo design.
As I said - how could you display more than that, and what purpose would it serve?

I would imagine if you have a list of standard comments/boiler-plate text each one would have a short description associated with it for quick lookup. I would use that in the combo for ease of use, not the full text

the first column is the name of the default text for example Delivery text then column 2 is the full data and the drop down list only shows the title, but the issue I have is that it wont paste the whole of the full data

 

[Minty]

You can still bring in the full text, in the combo column data, just don't display it.
if you are doing any aggregation in the combo source query it will also truncate your memo field.

Review

Microsoft Access tips: Truncation of Memo fields

Identifies the reasons why Memo fields may be truncated after 255 characters in queries or exports in Microsoft Access.

allenbrowne.com

 

[stu_c]

Hello Minty
I am using a combo box
Column 1 is a short Title of the default text
Column 2 holds the whole default length (hidden)

I am only showing the column 1 in the combo box which users click on which works fine, the issue I have is if the default text is over 240+ characters it wont paste the whole lot into the Field summery as the Column 2 only holds up to a certain number of characters.

What I need to be able to do is pull the data form the Query that the drop down list is linked to rather than pulling it form the drop down box column

 

[stu_c]

I think maybe it might be better to do a DLookup?

 

[Josef P.]

.. or use the recordset of the combobox.

 

[stu_c]

.. or use the recordset of the combobox.

how would you do that as a record set?

 

[Minty]

Something like

Dim rst     as DAO.Recordset
rst = Me.MyCombo.Recordset

However - what happens if you debug.print your column(2)
Are the full contents definitely available in the query?

If not then a simple dlookup will work without the overhead searching the recordset.

 

[stu_c]

I have managed to do it using a Dlookup, thanks of the defaultID number

 

[Josef P.]

Example with Recordset:

Private Sub cbxSelectText_AfterUpdate()
   Me.txtTextToCheckRst.Value = GetComboboxColumnValueFromRecordset(Me.cbxSelectText, 1)
End Sub

Private Function GetComboboxColumnValue(ByVal cbx As ComboBox, ByVal ColumnIndex As Long) As Variant

   If IsNull(cbx.Value) Then
      GetComboboxColumnValue = Null
      Exit Function
   End If

   If Len(cbx.Column(ColumnIndex)) < 255 Then
      GetComboboxColumnValue = cbx.Column(ColumnIndex)
   Else
      GetComboboxColumnValue = GetComboboxColumnValueFromRecordset(cbx, ColumnIndex)
   End If

End Function

Private Function GetComboboxColumnValueFromRecordset(ByVal cbx As ComboBox, ByVal ColumnIndex As Long) As Variant

   Dim BoundFieldName As String

   With cbx
      If .Value = .Recordset.Fields(.BoundColumn - 1).Value Then
         GetComboboxColumnValueFromRecordset = .Recordset.Fields(ColumnIndex).Value
      Else
         BoundFieldName = .Recordset.Fields(.BoundColumn - 1).Name
         GetComboboxColumnValueFromRecordset = FindValueInRecordset(.Recordset.Clone, BoundFieldName, .Value, ColumnIndex)
      End If
   End With

End Function

Private Function FindValueInRecordset(ByVal rst As DAO.Recordset, ByVal FieldName As String, ByVal ValueToFind As String, ByVal FieldIndexToReturn As Long) As Variant

   With rst
      .FindFirst FieldName & " = '" & Replace(ValueToFind, "'", "''") & "'"
      If .NoMatch Then
         Err.Raise vbObjectError, "GetComboboxColumnValueFromRecordset", "Value '" & ValueToFind & "' not found"
      Else
         FindValueInRecordset = .Fields(FieldIndexToReturn).Value
      End If
   End With

End Function

Note: Code is relatively easy to prepare as reusable code.