Windows XP 
Access 2007
Hello. Novice question, if I may. I'm still contemplating my design, so not sure if this is a simple question.
I will have an app where the user will be entering records (to a single table) via a datasheet on a form. The datasheet will be ordered by a text field, call it txtMySequence, which will consist of a five character string. The first four characters will be numerals, the fifth will be a blank, except when the user wishes to "insert" a record into the list of existing ones. For such insertions he wants txtMySequence to be incremented alphabetically. For example if the preceding record has txtMySequence = "1234 " the "inserted" record would have the value "1234a" and so forth. In other words, any record "inserted" after the initial dataset is entered, should be suffixed with a letter character but remain displayed just beneath the record that was current at the time of the "insertion."
I've been working out ideas for the VBA functions I'll need to automate the construction of txtMySequence, but I'm not sure how to sort on it. I suppose I could arrange it so that txtMySequence is actually a composite of two hidden fields, one containing the numeric characters and the other the alphabetic suffix. Then sorting would be done on those fields. Or I guess i could have a (hidden) numeric field whose sole purpose is to define the sort order - though I'd have to figure out how to renumber all records "beneath" an inserted one. (Obviously, simple autonumbering won't work, since the desired display order is not the record creation order.)
I was wondering if there's a standard - or simpler - approach to this sort of thing. Suggestions would be appreciated. (I'm using Access 2010 under Win7x64, but the user will be operating only in the run time environment - ie. I want to compile the app.)
Thanks in advance, -Ron
Reply With Quote
