My loop sometimes starts at 2 or 3

[Magger]

Hi gurus,

I have a access database frontend that books in laboratory samples. The booking system create records for the sample. The sample often has multiple replicates.

During record creation, I have found (not very often I might add) that on occasion the records entered don't start at 1.

eg
expected behaviour- record entry for a replicate count of six should be: 1,2,3,4,5,6.
odd observation - record entry for a replicate count of six: 2,3,4,5,6,1

I have attached the sub that create the record entries in the db. I'd be chuffed if someone could hint at what might cause this.

This isn't a show stopper, as I can remedy directly in the recordset. However, if my staff are completing this activity they won't be able to fix it.

regards
Magger

 

[arnelgp]

it is a minor issue, imo.
actually the records are complete, you just need to Add sort Order to your form/subform.
Sort it by RepNo ASC.

 

[The_Doc_Man]

To add a small explanation to arnelgp's answer (with which I agree), TECHNICALLY a table is not an ordered (sorted) data set. Apparent order can change if someone edits a record, for example. That would happen if someone enters six records in order but then goes back to check record 1 and realizes there was an error - so they fix the error, thus editing the record - and that puts it to the back of the list.

If you want things to appear in a specific order, you can do as arnelgp suggested and impose an OrderBy on the form, accessible from the Form's Properties sheet; choose the Data tab, and its about the 6th property on that tab. OR you can base the form on a QUERY that includes an ORDER BY clause in which case the records will always be in the correct order.

 

[Magger]

thank you both for your support. Your ideas have resolved my issue. I now look at the table as disorderly and presentation layer is where the order is invoked. Thankyou!

 

[gemma-the-husky]

@Magger

Generally, we use a query as a forms record source, rather than a table.
Even if the query is "select * from my table". You can then add the desired sort parameters (and maybe filters) to the query.

Note that if you prepare a report, the query does not provide the sorting. A report needs to provide sorting separately.

 

[The_Doc_Man]

To add to Dave's suggestion, I frequently build forms based a query rather than the raw table, because that way I can build in ordering AND if I needed it, a translation-type of lookup that is query-based rather than using a lookup field. Lookup fields are very hard to properly maintain, particularly if you need to add options to the field.