Loop update table

[Luka]

Hi everyone

I have a form and a subform both linked to the table. Subform has increment line number.

1 - first record
2 - second record
3 third record,...

The question is how to reset increment line numbers in subform if i delete second record?

Soo the result would be:

1 - first record
2 - third record

All values are stored in table so I was thinking about updating and then requery the subform.

I could realy use some help. Thanks

 

[mhartman]

Hello:

Here is a link to another forum with the same question:

Regards
Mark
http://www.dbforums.com/archive/index.php/t-327946.html

 

[Luka]

Thanks for reply, but link wasn't much of a help.

I forgot to say that i don't use Autonumber field but a text field for increasing number. And i calculate sequential number in VBA and put it into table and then requery the subform.

I want to know how to make loop (or any other code) to update the field in table where that sequential number is stored?

I searched forum but nothing seems to work.

This is the code that i use and doesn't work:

Dim Counter As Integer
Dim CountLines As Integer
Dim rst As DAO.Recordset

Counter = 1

CountLines = DCount("MyFieldId", "MyTable", "MyFieldId= Forms!MyForm!MyFieldId")

Set rst = CurrentDb.OpenRecordset("MyTable")
With rst

Do Until Counter = CountLines

rst.Edit
!SequentialNumberField = Counter
rst.Update

.MoveNext
Counter = Counter + 1

Loop
.Close
End With


Thanks for help.

 

[pbaldy]

It would help to know what doesn't work. Offhand, I'd assume you'd want to open the recordset on the same restricted dataset as used in the DCount, not the whole table.

 

[Luka]

Yes i have a problem with recordset. I can open (recordset) a table or a query or a SQL string but without WHERE clause.

If i put where clause in recordset then i get error message Too few parameters. Excpeted 1.

I guess its something wrong with sql then?

 

[Luka]

Thanks, pbaldy i found your answer on a similar problem in some other thread.

In WHERE clause i should include brackets '" & Me!ControlName & "'.

Now the code works excellent.

Thanks again.