1

I have read many posts related to my question, but none that actually discuss my particular problem.

I have a form in an Access database that I am using as a data entry form. Currently, the textboxes are unbound. My theory was to let people either enter new data and save to the final record (this side of the form works), or use a combobox to look up an existing record to update.

I have a combobox (cmbEntryID) that selects the record using a query (qryEntryID). When I run this query not on the form, it gives me all the information including several Long Text columns that are NOT limited to 255 characters. When I run it using the form, the record information in all the text boxes with long text is truncated to 255 characters. I have switched them from plain text to rich text, checked any associated tables, and looked at every formatting option I could see and it appears the problem may lie in my code. Do any of the following methods limit total characters for text fields?

Private sub cmbEntryID_AfterUpdate()
    Dim rs as recordset

    me.txtEntryNum = me.cmbEntryID(0)
    me.txt1 = me.cmbEntryID.Column(1)
    me.txt2 = me.cmbEntryID.Column(2)

    Set rs = currentDb.openrecordset(qryEntryID)
    with rs
        rs.Edit
        rs!EntryID = me.txtEntryNum.value
        rs!Comment1 = me.txt1.value
        rs!Comment2 = me.txt2.value
        rs.update
    end with
    rs.close
end sub
GSerg
  • 76,472
  • 17
  • 159
  • 346
Zeella
  • 13
  • 3
  • Does this answer your question? [Any way to have long text (memo) parameters in DAO and MS Access?](https://stackoverflow.com/questions/12101247/any-way-to-have-long-text-memo-parameters-in-dao-and-ms-access) – GSerg Apr 25 '21 at 19:01
  • Not really. From what I read, it shows that it could allow it. My problem is that something is stopping the fully query value from getting from the query results to my ending text boxes. I know the query pulls all the characters, but somewhere between the query and it being moved textboxes, it is shortened to 255 characters. – Zeella Apr 25 '21 at 19:08
  • 2
    Combobox column cannot have more than 255 characters. Combobox truncates long text (memo) field. So even if not a DAO recordset, if Column(1) or Column(2) are pulling from long text field, data will be truncated. – June7 Apr 25 '21 at 19:12
  • I had no problem using DAO recordset to update a record with a string of more than 255 characters. – June7 Apr 25 '21 at 19:26
  • 1
    Why don't you just use bound form? Use unbound combobox to select criteria to apply filter or 'go to' record. And if you are copying text, how are updating an existing record? – June7 Apr 25 '21 at 19:32
  • this was an "inherited" database, meaning I did not originally set anything up. The original guy did a very foolish thing and had a few columns in his record table for things like date and employee id, then had all other information such as time, location, what happened, thrown into one memo column that changed over the years. There have been issues in the past with people who are not computer savvy at all, accidentally deleting entire records. one case, a person deleted an entire month of records. – Zeella Apr 25 '21 at 19:51
  • So, my task was to find a way to prevent deletions. I planned a way (save button with an 'if then' statement to either append the record table or update additional fields like comment2 instead of comment1.) That is why I am trying to pull a record with the combobox. I suppose I could pull the query into the temporary table and bind the text boxes to the temp table. I was just wondering what the culprit was as to why my original query wasn't pull across. – Zeella Apr 25 '21 at 19:53
  • If it's a combo box, you can either track down the selected record and use data from that record (open a second recordset, navigate to the selected record, copy data), or, in a more ideal situation, create a relationship and refer to the data instead of copying it. – Erik A Apr 25 '21 at 20:47
  • It will take a lot to prevent deletions. Set form AllowDeletions property to no for a start. Then customize ribbon, hide Navigation pane, disable shortcut menus. Good luck. – June7 Apr 25 '21 at 20:57
  • @June7 Preventing deletes is actually fairly trivial. Just create a "Before delete" data macro that always raises an error, and no-one can delete anything on that table without removing that data macro. It's just not something that's usually desirable. – Erik A Apr 26 '21 at 12:40
  • Ah, yes, I forget about Data Macros (never used). For split db would have to program within the backend. – June7 Apr 26 '21 at 17:25

0 Answers0