Conditional Primary Key 1

[Thines]

I have a table where I am trying to do a conditional primary key to group multiple rows (between 15-25 rows in the database) together. I would like to assign an autonumber to based on the value for field1 where field1 = 'INS'. I want that same autonumber value to be repeated until it finds field1 = 'INS' again then have the autonumber increase by 1 and so on. I've looked online for various different snippets of VBA code but none of it seems to give me what I want. Can anyone help steer me in the right direction?

 

[gol4]

Obviously not normalized and this would not be a true autonumber. And who is to say the fields are in the proper order but something like this.

using dao

dim rst as recordset, x as integer
set rst=currentdb.openrecordset("tablename")
with rst
do until .eof
.edit
if rst(0)="INS" then x=x+1
rst(0)= cstr(x)
.update
.movenext
loop
.close
end with
set rst = nothing

anyway goodluck

 

[Thines]

When I put this into my module the .Edit reference gives me an error stating

"Compile Error: Method or Data member not found"

I commented out the .Edit portion of the code and the next error I get is here:

Set rst = CurrentDb.OpenRecordset("tbltemp")

It tells me Run-Time Error '13' Type Mismatch

I'm also running Access 2002 using DAO in the module and my table is "tbltemp", the field I'm searching for the "INS" is "field1" and the field I want to dump my number into is "ID" if that helps at all.

 

[gol4]

try dim rst as dao.recordset if not look if your references as set properly to dao. Or do the same thing using ADO

 

[Thines]

That did it! soon as I changed it to dim rst as dao.recordset it accepted the .Edit piece and ran. The only thing now is that it is replacing field1 with the number but I can play with what I have to make it work.