As you are adjusting the numbers they clearly have no semantic significance, so why store the sequential numbers at all? You can compute them at runtime in a query. You'll find examples of row numbering and ranking queries as RowNumbering.zip
in my public databases folder at:
https://onedrive.live.com/?cid=44CC60D7FEA42912&id=44CC60D7FEA42912!169
Note that if you are using an earlier version of Access you might find that the colour of some form objects such as buttons shows incorrectly and you will need to amend the form design accordingly.
If you have difficulty opening the link copy its text (NB, not the link location) and paste it into your browser's address bar.
In this little demo file the following query numbers transactions by date:
SELECT COUNT(*) AS RowNumber, T1.TransactionDate, T1.CustomerID, T1.TransactionAmount
FROM Transactions AS T1 INNER JOIN Transactions AS T2
ON (T2.TransactionID<=T1.TransactionID OR T2.TransactionDate<>T1.TransactionDate)
AND (T2.TransactionDate<=T1.TransactionDate)
GROUP BY T1.TransactionDate, T1.TransactionID, T1.TransactionAmount, T1.CustomerID;
If we first create a query, QryTemp, to simulate your table by including YearID and MonthID columns rather than specific dates:
SELECT YEAR(TransactionDate) AS YearID,
MONTH(TransactionDate) AS MonthID, *
FROM Transactions;
We can then amend the first query above to number the rows chronologically:
SELECT COUNT(*) AS RowNumber,T1.YearID, T1.MonthID, T1.CustomerID, T1.TransactionAmount
FROM QryTemp AS T1 INNER JOIN QryTemp AS T2
ON (T2.TransactionID<=T1.TransactionID
OR T2.YearID & FORMAT(T2.MonthID,"00")<>T1.YearID & FORMAT(T1.MonthID,"00"))
AND (T2.YearID & FORMAT(T2.MonthID,"00")<=T1.YearID & FORMAT(T1.MonthID,"00"))
GROUP BY T1.YearID, T1.MonthID, T1.TransactionID, T1.TransactionAmount, T1.CustomerID;
Note how the autonumber primary key TransactionID is used to act as a tie breaker in the case of two or more transactions being on the same date in my original table, on the same year/month in the QryTemp query which simulates your table. So, in your
case you'd replace the FileID column with an autonumber primary key if you don't have such a column already:
When inserting a new row into your table via a bound form you then simply need to insert the year and month, and the sequential numbers will be computed in the query, taking account of any new rows inserted wherever they might fall in the chronology
You don't actually need to return sequential numbers to maintain a chronological order, of course; you can simply return an ordered result table with a query, e.g.
SELECT YearID, MonthID, CustomerID, TransactionAmount
FROM QryTemp
ORDER BY YearID, MonthID, TransactionID;
Unlike the query which numbers the rows this will return an updatable result table, so could be used as a form's RecordSource. If you want to return an updatable result table which includes row numbers you can do so by calling the VBA DCount function;
my demo includes examples of this.
Ken Sheridan, Stafford, England
