modify field in table

[Bobpj]

I hope someone can give a simple solution to this question.

Because of a limitation in another system (not Access) I need to create a
counter that cycles from 0 - 999 - 0. This counter will cycle round over a 3
year period.

So I've decided to store the counter as a field in a single record in a
table, the idea being to read it, increment it and update the field in the
record.

So I'm opening the table using the following code:

Private Sub Combo6_Click()
DoCmd.OpenTable "Vnums", , acEdit
DoCmd.GoToRecord , "Vnums", acFirst

Want I want to do now is get at the field and modify it, but for thr life of
me I can't work out from the 'help' facility what I need to do next.

Can anyone guide me please?

Thanks

 

[pietlinden]

I hope someone can give a simple solution to this question.

Because of a limitation in another system (not Access) I need to create a
counter that cycles from 0 - 999 - 0. This counter will cycle round over a 3
year period.

So I've decided to store the counter as a field in a single record in a
table, the idea being to read it, increment it and update the field in the
record.

So I'm opening the table using the following code:

Private Sub Combo6_Click()
DoCmd.OpenTable "Vnums", , acEdit
DoCmd.GoToRecord , "Vnums", acFirst

Want I want to do now is get at the field and modify it, but for thr lifeof
me I can't work out from the 'help' facility what I need to do next.

Can anyone guide me please?

Thanks

So there's only one record in the table?
Just run an Update query against it.

or something like

DBEngine(0)(0).Execute "UPDATE VNums SET Counter=Counter+1",
dbFailOnError

then you could use an update query to update another table...

 

[Bobpj]

Thanks Piet:

It didn't quite achieve what I wanted, but it started me on a new approach.

The solution I'm going to use is something like this:

Dim Incumbertbl As DAO.Database
Dim IncVnum As DAO.Recordset

Set Incumbertbl = CurrentDb
Set IncVnum = Incumbertbl.OpenRecordset("Incnumber")
IncVnum.Edit
Counter = IncVnum!Vnum
Counter = Counter + 1
IncVnum!Vnum = Counter
IncVnum.Update

Amazingly this works!


Obviously I'll build in a check to ensure that the number doesn't exceed
999, and I'll store the resulting value in a control field for use elsewhere.

Big Thanks