<div>
I'm not exactly sure what you are doing but in general, if you are doing a sequential process with a code loop, it is best to use a query so you can explicitly sort your input data.<br />
<br />
Access fools people into thinking that tables are always sorted by their primary key but that isn't quite true. &nbsp;In an empty table, rows are appended physically one after the other. &nbsp;Whey the primary key is an autonumber, it seems like ID2 will always follow ID1 and that will be true UNLESS you update the record for ID1 and increase its size so that it no longer fits into the same slot as the original record. &nbsp;In that case, Access simply adjusts its pointers and places the changed record at the physical end of the file. &nbsp;Other database engines do this slightly different in that they use a more sophisticated method and are capable of reusing dead space nearer where the original record was stored. &nbsp;Now before you all go out and try this, you need to keep in mind that the file system doesn't view a logical record necessarily as a physical record. &nbsp;Physical records are blocks of data that depend on disk format and size and the bitness of the hardware so you actually have to increase the size of a physical record before you can make this happen. &nbsp;That means you can't a table with 10 records each containing 3 10 character fields and make this happen.<br />
<br />
Anyway, one of the jobs of compact and repair is to rewrite tables into PK sequence. &nbsp;So every time you CR your table is physically reordered.
</div>


<div>
I tried stuffing text fields with spaces when the record is first created and that didn't help.<br />
<br />
How can i increase the physical size? &nbsp;I suppose I could create a stalking horse field and populate it with 50 letter &quot;A&quot;s when the record is created. &nbsp;Then clear that record to &quot;&quot;&nbsp;when I update the other fields. &nbsp;??
</div>


<div>
Why do you insert empty records? &nbsp;Rather than updating existing records, you could insert new new ones in the code loop.<br />
<br />
If you have meaningful defaults, you can define them at the table level and they will be inserted. &nbsp;I would not attempt to insert spaces. &nbsp;That will only cause confusion down the road since you can't distinguish a ZLS string from nulls visually. &nbsp;It also will force you to have to consider both ZLS and null in your criteria. &nbsp;Personally, i set the flag in the table definition to not allow ZLS at all in any field.<br />
<br />
The best course of action is to always use queries with an order by clause to ensure meaningful order. &nbsp;<br />
<br />
In the relational database world tables and queries are unordered sets and so without a specific order by clause, the database engine is allowed to return rows to you in whatever order is convenient for the engine. &nbsp;When you open a table in DS view, Access creates a query so you never actually &quot;see&quot; a table under any conditions.
</div>


<div>
This gets weirder and weirder. &nbsp;I tried populating the default values in 12 text fields to be &quot;None&quot; to try to increase the record size. &nbsp;Then in code as the record is created, I edit and update those fields to be &quot;&quot;. &nbsp;But the next time I address those fields, they are back to &quot;None&quot;<br />
<br />
There's only one record at this point.
</div>


<div>
The database is created by copying from a blank MDB. &nbsp;All the fields, indexes, and PrimaryKeys are present.<br />
<br />
To try to capture a sufficiently physically large table for the one in question, I imported the same table from another previously used MDB which was fully populated and replaced the existing, then initialized the fields. &nbsp;That didn't seem to help.<br />
<br />
Frustrating
</div>


<div>
I just realized what is happening.. &nbsp;The table refuses to accept updates in this form after it is created. &nbsp;If I populate the default value with &quot;None&quot;, I can't update it. &nbsp;It shows the new value as I watch it through debug and when I invoke Update. &nbsp;But if I move the record pointer, the values go back to default.<br />
<br />
If the initial value is Null, I can't update it. &nbsp;If the initial value is &quot;&quot;, I can't update it. &nbsp;It's like the table is locked somehow.
</div>


<div>
Gentlemen, I found it using Compare. &nbsp;In a structure rebuild of the table, I had changed all text fields to be AZL = false thinking that was good practice. &nbsp;Well, it wasn't in this case. &nbsp;When I changed those text fields back to AZL = True, every thing started working. &nbsp;<br />
<br />
Thank you both for your time. &nbsp;EE is an invaluable resource for a hacker like me.
</div>


<div>
Thanks for your time and help. &nbsp; I am grateful.
</div>


<div>
I'm glad you figured it out. &nbsp;I for one still have no idea what your problem actually was. &nbsp;I never allow ZLS because I never want a situation where an &quot;empty&quot; field might be null or might be ZLS. &nbsp;If you allow ZLS in your text fields, then the only thing that makes sense is to make them required so at least they can't ever be null. &nbsp;Of course ZLS doesn't apply to anything except string data.<br />
<br />
When importing fixed length files from other applications, you will end up with ZLS and even worse - padded fields.
</div>


<div>
Once I figured it out, the behavior made perfect sense. &nbsp;<br />
<br />
Not all of the text fields are populated in this step. &nbsp;Some are and some are not. &nbsp;It looked like it was going to update but after I did Update, &nbsp;it did not due to the text fields that were left blank at this juncture. AZL = No declined the udpate, It returned to the values prior to the operation, which was Null..<br />
<br />
I agree with you in general practice for AZL's but I originally wrote this code with Access 97 and it was my first ever. &nbsp;Learned a lot since then.
</div>


<div>
<div class="content wysiwyg-content">
I have an Access table which has a Primary Key for the index as usual. &nbsp;The key is necessary for Crystal Reports to properly link fields in related tables. &nbsp;But as long the key is in place, I can't successfully move the record pointer with a MoveNext or FindFirst. &nbsp;Field data is lost from the previous .Edit and .Update.<br />
<br />
This operation worked fine for years in the past. &nbsp;Something has changed obviously, but I can't figure out what.
</div>
</div>


I have an Access table which has a Primary Key for the index as usual.  The key is necessary for Crystal Reports to properly link fields in related tables.  But as long the key is in place, I can't successfully move the record pointer with a MoveNext or FindFirst.  Field data is lost from the previous .Edit and .Update.

This operation worked fine for years in the past.  Something has changed obviously, but I can't figure out what.

Indexed fields

Microsoft Access is a rapid application development (RAD) relational database tool. Access can be used for both desktop and web-based applications, and uses VBA (Visual Basic for Applications) as its coding language.

Microsoft Access

Visual Basic is Microsoft’s event-driven programming language and integrated development environment (IDE) for its Component Object Model (COM) programming model. It is relatively easy to learn and use because of its graphical development features and BASIC heritage. It has been replaced with VB.NET, and is very similar to VBA (Visual Basic for Applications), the programming language for the Microsoft Office product line.