Need help with Auto-Counter in Access 2000

[kophjager]

Hello all,

First off I would like to thank anyone who may be able to help me.
I have one table, Increment, that has two fields, Field1 and Field2. I want Field2 to increment itself whenever I do a select query to access it's stored value. So if I say 'Select Field2 from Increment'and get 5 back. The next time I do it I want 6. I'm not inserting or deleting anyhting, just accessing the value.
Field1 is a volume number and Field2 is an issue number. So when I access Field2, to assign it to an issue's details I am inserting, I want it to get incremented so the next time I can get the next number in the series. The issue number can only be from 1 to 7. When Field2 reaches 7 I would like it to go back to 1 and at the same time increment Field1.
I don't know if any of this is possible as I could find nothing on the web so I appreciate all the help. Thank you.

--kophjager

 

[polljoh]

Hope this code helps.

Function QueryAndIncrement() As Integer

Dim db As Database
Dim rst As Recordset
Dim strSQL As String

strSQL = "Select Field2 from Increment"

Set db = CurrentDb()
Set rst = db.OpenRecordset(strSQL)

QueryAndIncrement = rst.Fields("Field2&quot.Value

rst.Close


If QueryAndIncrement = 7 Then
db.Execute "UPDATE Increment " _
& "SET Field2 = 1;"
Else
db.Execute "UPDATE Increment " _
& "SET Field2 = '" & QueryAndIncrement + 1 & "';"

End If

Set rst = Nothing
Set db = Nothing

End Function


Sub TestFunction()

MsgBox QueryAndIncrement

End Sub

 

[kophjager]

Thanks polljoh. Worked like a charm!