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.

 

[Pat Hartman]

And finally, tables can have millions of rows. How many rows does a user need at any one time? So, especially when using a BE other than Jet/ACE such as SQL Server or DB2 or Oracle, it is best to use a query so that you can not only sort the recordset into some meaningful sequence but so you can limit it to the minimum number of rows a user needs at any one time.

When your recordset has a small number of rows, you probably never noticed that the "y" in the "x of y" part of the navigation bar doesn't fill immediately. That is because Access displays the form as soon as it has retrieved enough rows to fill the form. The rest of the records in the recordset get retrieved in the background and eventually the "y" gets filled in. This is generally a waste of resources so it is best practice to limit the records retrieved.

I do this in one of two ways. For complex searches, I use a form that has multiple search options and use that form to create the WHERE argument for the query bound to the RecordSource property. For most forms, the criteria is usually much simpler such as a date range or a CustomerID or CustomerName and for those I use text boxes for text search or combos to get a specific ID. For the complex search, the search form opens the edit form to the specific record if only one record is returned. Otherwise it opens a list form and then the user double clicks on the record he wants to view/edit to open the edit form to that specific record. For the other forms, the form is bound to a query that uses a WHERE argument that references the criteria fields. That means that the form always opens empty and then the user enters his criteria to retrieve the data he wants. In the case of dates, I use a predefined range such as 1 day, week, or month depending on what makes sense.