It's not possible with Access alone.  You can use Excel VBA (via a technique called "automation") to create the counter field, then import the data back to Access.  Or you can use SQL Server's T-SQL, which lets you use coding technique like variables and loops in SQL statements; you can use that to increment a field at every row.

Gary

This video shows how to accomplish record numbering.

YT: Automatic Numbering
TH: Automatic Record Numbering

Do not use these as a Primary or Foreign key.  These are not Auto Numbers.

Hope this helps.

-Raymond

Raymond  Richard's video is for a blank table, whereas Gary already has a table full of data and wants to add counters after the fact.  Also, Gary specifically wants everything in a query.

What I have is a text file that has as a row that starts with API--for A/P invoice header info, then the next row starts with APD for A/P invoice distribution information. The text file will be IMPORTED into our accounting software i.e. Sage CRE 300. What I'm wanting to do is link the text file into Access and then display the A/P invoice on a main form and the distribution detail on a subform. I have a query that that lists the header records using the API for criteria and APD for the detail records. These two queries would the data sources for form and the subform. BUT I need a common key to link them. So, with all that being said.

Now what I'm thinking of doing is to loop through the text file records lines with a recordset and append the correct lines to their own tables and using a counter to assign a Sequence Number key field. I'll have to play with it over the weekend.

This is not really an Access question, but does anyone use accounting software from Sage?

Legacy Tip: Row Number in a Query

I looked at that one earlier. But it's using a ID which I don't have in my text file.

What's the reason you are needing a row number?

If the data in the text file is always formatted the same, would it be easier to import the data into a table, even a temporary table, using VBA where you could then add a field to each row that would contain a linking ID #. Set that to have a composite key such that only there could only be 1 row ID # 1 that is an API and 1 row ID # 1 that is APD and then that would be your master/child link?

If you would be importing multiple files that you wanted to keep, add a filename field or something to identify that.

I haven't tested this, it's just the first thing that comes to mind.

It wouldn't be too difficult to just import that line-by-line using standard text I/O and then figure out where stuff needs to go. It looks like every odd line has the same data, and every even line has similar data. So it's just a matter of figuring out what everything is.

So here is what I ended up doing. And the reason why. This text file is created in Excel (from an invoice-like form) and can have data rows appended to it depending on how many vendors we are paying at one time. I wanted to bring it into Access and into the InvoiceF form so that it could be easily viewed. That's why I had to have an ID and a matching ID in the detail. And after it was viewed, it is all put back into a table that just has text fields and the SQL formats the dates and amounts as text in the format that my accounting program needs. Then that table is exported back out to a text file. I know. I know. I know. It's sort of a run-around. So I can SEE and verify what the data looks like before it's imported into the accounting system. Plus, I may also develop the InvoiceF to ENTER new A/P data to be imported into accounting. Following are 3 pictures.

Looks good. I had put the rs.close commands after your error landing spot. Make sure you close those recordset loops before exiting the sub.