Hello,

I'm using the Custom Demo Counter example from MSDN (http://support.microsoft.com/kb/140908 )and i want to change it in order to keep 12 counters (one for each month).

I added 12 rows in the CounterTable each one starting from zero. How can i refer to each one? For example how can i refer to NextCounter = rs!NextAvailableCounter for March (which is row 3)?

CounterTable has one field the NextAvailableCounter.

Thank you in advance!

I found the solution in case someone else is interesting:)

i added the following in the code and works

If Month(Me.SampleDate) <> 1 Then
MsgBox (Month(Me.SampleDate))
rs.Move (Month(Me.SampleDate) - 1)
End If

NextCounter = rs!NextAvailableCounter
.....


where SampleDate is the date i'm using for each counter.

I think I would try the where clause, and open the recordset with only the relevant record (counter for the correct Month). It could look something like this.

Set MyTable = MyDB.OpenRecordset("SELECT NextAvailableCounter FROM CounterTable WHERE MyMonthNo = " & Month(Me!SampleDate))

The reason for that, is that it seems you are relying on the order of the table, and that's not recommended. You could open the whole table, and use the .FindFirst method, but I think I would prefer using the where clause (as the sample above)

Then, the next question, what would you do next year - continue June 2008 numbers from the June 2007 numbers? I think that's what this will do, unless you for instance reset them every year.

You have right for the WHERE clause, i didn't think about it (i just sorted the CounterTable based on MonthNo that might causes wrong results) thanks :)

For next year i thought of building a function (autoexecute it as macro) that checks if Year(Date()) > [CounterTable].[Year]. If true then will reset NextAvailableCounter and set the [CounterTable].[Year] to the new year for the particular recordset. Any better suggestions are welcome :)


Another problem i might have is when a user tries to insert an old record with SampleDate of the previous year. If i reset the values on the CounterTable i cannot apply the correct number to that specific record. (this probably will never happen, but if it does how do i deal with?)

Thanks again for the help!

If I understand correct, it seems you alredy have a year column, then it should be easy (don't use year as a name, btw, as it is a reserwed word)

...WHERE MyMonthNo = " & Month(Me!SampleDate) & " AND MyYear = " & Year(Me!SampleDate))

where you keep the month counters per each year. At some time, you just populate the table with the next Year(s) and Month and 0 for the "NextAvailableCounter" thingie.

Edit: list of reserved words
http://support.microsoft.com/kb/321266/EN-US/
http://support.microsoft.com/default.aspx?scid=kb;en-us;286335

Edit: list of reserved words
http://support.microsoft.com/kb/321266/EN-US/
http://support.microsoft.com/default.aspx?scid=kb;en-us;286335
Roy - Great lists and as such I am going to put a modified copy of this post into the FAQ category as it is definitely an important thing to know.

Thank you!

I see I didn't get a say in the process ;)

Thank you!

I see I didn't get a say in the process ;)

Nope - Executive Decision :D

Thanks RoyVidar for all! :)

and the links are pretty useful!