Update certain records in table.

[LEXCERM]

Hi there all,

I am looking for a way to update certain records in a table with an incremented value of +1 each time.

For example, TBL_MAIN will have fields REF_NO and INCREMENT_VALUE. In field REF_NO, the same record may appear more than once and the INCREMENT_VALUE field needs to update by +1 each time. I have attached a simple snapshot of the table. In this example, I would like ref ABC123 have it's INCREMENT_VALUE increased by +1 each time following on from the last incremented value of 12460. I believe this may involve DMAX function, For Next loop and possible DAO recordset code but don't really know where to start!

Rgds,
Paul.

 

[Uncle Gizmo]

Have a look at these posts, if there's nothing there that suits you, post back.

 

[LEXCERM]

Thanks Uncle Gizmo for the reply. Checked the links, but it's not quite what I was looking for. I'll try and put the question another way.

I have already created an UPDATE QUERY to look for, say, ABC123 in the table. In the query there is a DMAX+1 function. But what the query does is look for ABC123 in the table, then adds 1, and each ABC123 INCREMENT_VALUE is updated to 12461 - rather than 12461, 12462, 12463 and so on.

Rgds,
Paul.