Reset consecutive numbers at start of month

[boliches]

I have a seperate table to generate consecutive numbers. Using "Dmax" to find the largest number to increment .
My problem is that I want the number to begin at 1000 at the start of each month, deleteing the previous numbers created in the table.

Table : tblNewNum
Field: NewNum

Januarys Contents of field would read ie. 1000, 1001, 1002, 1003 etc

How can I get the first number generated at the start of each month to be 1000?

Hope this makes sense and that there is a solution, I am using MS Access 2000 with XP.

Regards

[Rabbit]

What's your experience with coding?

[boliches]

What's your experience with coding?

Limited, but am learning as I go along. I am not fazed by a bit of difficult coding even if I dont fully understand it. I tend to search the web for answers to my problems as and when I come across one.

Do you need any of the code I am using to generate the consecutive numbers?

[Rabbit]

I don't need the code you use to generate the number, I think I know what you did for that.

To do what you want, you're gonna have to run a check.

If Day(Date()) = 1 Then
  ' Check to see if you've already run this for the month.
  ' If No, then delete all records and insert one record with the value 999
End If
 

To check if you've already run the delete query for the month you'll have to store the last month you ran the delete query in either the same table or in a different table.

Let me know if you need any more specifics.

[boliches]

Sorry if I am being a bit thick! But if you could explain a little more I would be very grateful. It sounds as if you understand what I am trying to achieve, it is just the how and where.

Cheers.

[Rabbit]

What's you want to do is make another table that has one field and one record. The record will basically be the number of the month when the other table was last cleared.

So what you'll have is 2 tables.

tbl_AutoNumber
tbl_Month

Now in the same place that you generate the Auto Number, before everything else, you need to check if 1) It's the first of the month and 2) If the table has been cleared yet. This is checked by seeing if the month in the table is the same as the month of the current date.

If both of these are true, then it deletes all records from the 2 tables and inserts the value 999 into tbl_AutoNumber and the value of the current month into tbl_Month.

If Day(Date()) = 1 Then
  If DLookup("LastMonth", "tbl_Month") = Month(Date()) Then
    DoCmd.SetWarnings False
    DoCmd.RunSQL "Delete * From tbl_Month"
    DoCmd.RunSQL "Delete * From tbl_AutoNumber"
    DoCmd.RunSQL "Insert Into tbl_Autonumber Values (999)"
    DoCmd.RunSQL "Insert Into tbl_Month Values (" & Month(Date()) & ")"
    DoCmd.SetWarnings True
  End If
End If
 

[boliches]

Have tried the code but am getting a "Type Mismatch" warning re:

If DLookup("LastMonth", "tbl_Month") = Month(Date()) Then ........

It appears the error involves the Month(Date())

What have I forgotten to do? Is the Month(Date()) looking for the number of month ie. "1" or the word "January" in tbl_Month, or is it something else that I should have done?

Thanks

[boliches]

Further assistance please:

I thinh I have sorted the previous problem, thnank. But have now encountered a further one.
This was working but now, for some reason, does not:

It is the consecutive number using DMax to look up the greatest number used last! It has stopped looking for the greater number and each time I run the code I get the number generated : 1000 (it would actually read "02BP1000")

Here is the complete code I am using:

 
Dim strTable As String
Dim Month As String
Dim Month2 As String
 
Month1 = Format([Date], "mm")
 Month2 = Month1 - 1
  Me.txtBrokerName = Me.cboBrokersID
  Me.txtDealNo = Me.DealNo
  Broker = Me.txtBrokerName
  strTable = "tblNewDealNum" + Broker
  strNum = Broker + "QiNum"
 
   If IsNull(DMax(strNum, strTable)) Then
             SQLtxt2 = "INSERT INTO " & strTable & " (" & strNum & ")  SELECT 999"
                   DoCmd.RunSQL (SQLtxt2)
                                End If
 
 If Day(Date) <= 31 Then
  If DLookup("LastMonth", "tblCheckMth") = Month2 Then
    DoCmd.SetWarnings False
    DoCmd.RunSQL "Delete * From tblCheckMth"
    DoCmd.RunSQL "Delete * From " & strTable
    DoCmd.RunSQL "Insert Into " & strTable & " Values (999)"
    DoCmd.RunSQL "Insert Into tblCheckMth Values (" & Month1 & ")"
    DoCmd.SetWarnings True
  End If
End If
 
Num = DMax(strNum, strTable) 'This does not appear to work!
        NewNum = Num + 1
        Me.txtDealNo = "" & Month1 & "" & Broker & "" & NewNum & " "
        Me.DealNo = Me.txtDealNo
        strMsg = Me.DealNo
        Me.txtBrokerUpdate = Me.txtBrokerName
 
MsgBox "Ticket Number " & strMsg & "."
    SQLtxt1 = "INSERT INTO " & strTable & " (" & strNum & ")  SELECT " & NewNum
         DoCmd.RunSQL (SQLtxt1)
            DoCmd.GoToRecord acDataForm, "frmAddDeal", acNewRec
                cboBrokersID.SetFocus
 

[boliches]

Sorry!

May have sorted it !!!!

I had the table "QiNUm" set to "Text" I have changed it to "Number" and it seems to be working.


Big thanks for all your help.

Regards

[NeoPa]

Sorry!

May have sorted it !!!!

I had the table "QiNUm" set to "Text" I have changed it to "Number" and it seems to be working.


Big thanks for all your help.

Regards

Just an idea, and I know it's a bit late, but if the data you get is to add a new value into your main table and that includes an indicator for the month, then you can use the same concept to get the value you need without recourse to a separate table at all.
As an illustration, say your main table has items with a PK made up of (including would work too, but for simple illustration...) MMMXXXX where MMM ==> 3 letters of Month & XXXX ==> 4 digit number starting from 1000.

Dim strPK As String
 
strPK = Format(Date(),"mmm")
strPK = strPK & _
        Val(Right(Nz(DMax("[PK]", _
                          "[tblMain]", _
                          "[PK] Like " & strPK & " & '*'"), _
                    "XXX0999"),4))+1