1) I believe when you're referring to the Recordset size you mean the number of records that a particular form is accessing. They're probably around 100 records. Each record has 23 fields, 6 of which are memos, the rest are either text, number, date/time, and one multi-select combo box. If memory is an issue, then it seems that I could try using a form that has all records as its recordset, which at this point is ~1300 records, as opposed to the forms the users have been working in that contain only a subset of all records. If that's the case, then I should be able to reproduce the problem they're having if I tried using the full recordset in a form?
Data types like memo, text, and date are large in comparison to some other data types. 23 columns is not small. 100 records is not large at all.
Retrieving more records into the form's recordset may help to reproduce the symptom. So increasing it from 100 to 1300 would require additional memory. To further test the memory theory you could try tabbing and editing data rapidly. The fact that only a couple users are experiencing the symptom may indicate someone is working hard. I usually budget extra money for power user's equipment. You could also have additional programs running in the background like, outlook, paint, internet explorer, etc. The more graphic heavy, the more memory required.
2) How might a subform be of benefit here?
My understanding is that subforms will be allocated their own memory because they are an additional object within the stack. Because Access is likely a 32bit system, you are not likely to see a huge benefit. However, it may be worth a try if you are running out of options. It is just something that I threw out there and would try myself.
3) How would adding a couple Me.Dirty = False statements help? I'm not familiar with VBA or SQL.
You can save a record from a form using VBA
Code:
If Me.Dirty = True then
Me.dirty = false
End if
As a user makes changes to fields, their machine will store these changes in memory until the record is saved. Freeing memory as you go could be helpful. However, if the user depends on tools like Undo, they may be in for a surprise.
The ultimate solution may be lots of VBA, restructuring the tables, or both.