Crosstab Query Help (Create a record number that restarts)

[bdhFS]

Yes, the data already exists...so I will try the Sub routine as you suggested alcapps. Here I thought normalizing the data was a good thing...but I think it has caused more headaches for me.

[alcapps]

Thanks first time I've run into that.

[bdhFS]

When running Debug I get a Compile error:
User-defined type not defined

and it highlights... db As DAO.Database

[alcapps]

You must have Microsoft ado 3.6 object library loaded in references which you find in Vba tools

[June7]

You are running this code in Access 2003? DAO is the default recordset library in Access so the code should work. But see what happens when selecting the ADO library in VBA: Tools>References>Microsoft ActiveX Data Objects 2.x Library

However, I don't think you need the db variable, especially since it is referenced only once.

Try using the New qualifier in declaring the recordset variable. Does table name have spaces or special characters? To be safe, include [] in the code. Same would apply to field names.

Dim rst As New DAO.Recordset
strSQL = "Select * from [" & datTableName & "] where num is NULL order by ID, inkmark"
Set rst = CurrentDb.OpenRecordset(strSQL, dbOpenDynaset)

[bdhFS]

I am running ACCESS 2010

[June7]

My last post comments still apply to 2010.

[bdhFS]

Sorry for my ignorance, but do I call this from a form?

[June7]

Call what - the procedure? Yes, if you want to. Create a button and put code in the click event.

Alternatively, if this is a one-time event, just put the code in a general module. Position cursor anywhere in the procedure and click Run from the VBA tool bar.

[bdhFS]

I tried run it while in the code and it pops up a macro window

[June7]

The procedure must be in a general module, not behind a form.

How many records are involved in this exercise?

[bdhFS]

There are 1417 records

[June7]

So a few too many for a manual edit.

Is the code in a general module?

[bdhFS]

Yes; under (General); and under Modules

[bdhFS]

This is actually something I need to do with data often. Most of the data post processing is done in SAS or GIS which needs a flat file.