Search code examples
ms-accesstextboxmemolongtext

Best practice for working with long texts in ms-access

I would be very thankful if somebody resolves my problem. I'm new in working with Ms Access and I still gain experience on its basic functionality.

I have a table MyItems. 2 of its fields are: ItemCode and ItemName. ItemName is a very long text (Memo type). I have also a query and a form with many fields. The form's record source also consists of many fields. All these things (associated with 1 field) have the same or similar names so I can't differentiate them quite well.

What I want is when I set the value of ItemCode (in a not bound Combobox or Listbox with name ItemCode) the value of ItemName to be displayed in a control - maybe TextBox.

I can display its value in a ListBox (by sql query in its row source), I have no problems with this, I have no problems with managing events, but the text is very long and is cut. I understood that unfortunately ListBoxes don't have multiline property. So maybe the most appropriate control to deal with is a TextBox. And maybe the most appropriate way to display the value is using DLookUp function in the TextBox's control source. But in this sea of items with similar or the same names I just can't deal with its syntax, I was trying again and again for a very long time. So I have 2 questions:

  1. Are the TextBox control and DLookUp function in its control source the best way to extract long texts from a table without binding or there are more suitable controls (which directly work with sql query)?

  2. What is the right syntax of DLookUp? - where exactly are there ' ', " ", [ ], .Value, =, &, where must I write the path to the table or the form and where it would be mistake? If I just write [ItemCode] what it would be associated with - the form record source, the table, the form control or anything else? I would be grateful if someone writes the correct syntax for my case or if he shares a link with plenty of examples for using DLookUp. Those that I found didn't satisfy me.

Solution

    1. Either a bound control, or an unbound one. If unbound, you need to load the text with VBA code or with DLookup in the control source. There are no other options.
      Personally I'd rather use the AfterUpdate event of ItemCode, and call DLookup there, but that's a matter of preference.

      2. 2.

    It's not that complicated. You basically have the SELECT, FROM, WHERE parts of an SQL query in the 3 arguments. [] are needed for all identifiers containing spaces or other special characters, and when refering to form controls.

    =DLookup("ItemName", "[my Table]", "ItemCode = '" & [ItemCode] & "'")

    The single quotes '' are needed if ItemCode is text, not when it is a number.

    You could also use doubled (escaped) double quotes, but that is much less readable.

    =DLookup("ItemName", "[my Table]", "ItemCode = """ & [ItemCode] & """")

    Now where does [ItemCode] come from?

    • Access first looks for a control on the form with the name ItemCode.
    • If there isn't one, it looks for a field ItemCode in the form's RecordSource.

    These are the only ways [ItemCode] can be evaluated. To avoid confusion, it is recommended to name bound controls with the same name as their source field.

    The syntax above is only valid if everything is on the same form. If [ItemCode] is on a different form, or you refer to it from a query, you use

    =DLookup("ItemName", "[my Table]", "ItemCode = '" & Forms![my Form]![ItemCode] & "'")

    For more complicated cases with subforms, see Refer to Form and Subform properties and controls

    And to use it in VBA (in ItemCode_AfterUpdate):

    Me!ItemName = DLookup("ItemName", "[my Table]", "ItemCode = '" & Me![ItemCode] & "'")