Hello, All!

Please see http://p2p.wrox.com/topic.asp?TOPIC_ID=5398 for a description of my problem and the solutions that have gone before.

All seems to be working fine with the following code in my Form's "Current" event:

Private Sub Form_Current()

    Dim datCur As Date
    Dim datMax As Date
    Dim intDailyID As Integer

    datCur = Me!DateIn
    datMax = Nz(FMax("SELECT MAX(DateIn) FROM Documents"), Date)

    If Me.NewRecord Then
        If datCur = datMax Then
            intDailyID = Nz(FMax("SELECT MAX(DailyID) FROM Documents " & _
                                 "WHERE DateIn = #" & Me!DateIn & "#"), 0) + 1
        Else
            intDailyID = 1
        End If

        Me!DailyID = intDailyID

    End If

End Sub

Public Function FMax(strSQL As String) As Variant

    Dim rst As ADODB.Recordset
    Dim intCount As Integer

    Set rst = New ADODB.Recordset
    rst.Open strSQL, CurrentProject.Connection, adOpenStatic

    If IsNull(rst(0)) Then
        FMax = Null
    Else
        FMax = rst(0)
    End If

    rst.Close
    Set rst = Nothing

End Function

HOWEVER: After my "DailyID" increments to "10" it will not go beyond ten; in other words, no "11", "12", etc. I'm not sure what the problem is but I must get it fixed before tomorrow evening!! (Dontcha hate deadlines!!!)

Thanks in advance!
Scott

What is the structure of table documents? I copied your code and it works beyond 10.

Do you have a constraint on the table (<11) or something close to that? Are you getting any errors at all?

I will keep checking.



Sal

The table "Documents" is set up with the following fields:

ID, DailyID, DateIn, TimeIn, Shift, Originator, DocumentNumber, MatterNumber, DateNeeded, TimeNeeded... etc. (the rest of the table doesn't relate to this problem). I'm using the code in the "OnCurrent" event in my form, in which the DailyID needs to fill automatically, automatically increment by 1, and restart at "1" at the change of date. DateIn and TimeIn both have default values of Current Date and Current Time respectively.

Thanks!
Scott -- stuck at work on a Saturday!

Hey, I just got out of work myself. I went in at 7:00 AM. (I need some coffee). I also have to go in tomorrow.

Have you ever been able to add a value Greater than 10 on the table?
Open the table in design view.
click on the fiels that holds the value for the daily increment.
on the bottom left of the screen, on the General tab. Is there anything at all under "Validation Rule". Is there anything at all for any other fields under "Validation Rule"?

Please let me know.

Do you have any other fields that have anything under that



Sal

By the way, even if the other table fieldss do not relate to the previous post, they will still affect whether or not you can insert a record into the database. There may be a unique constraint problem somewhere.




Sal

Sal: Nothing at all in any of the fields as far as Validation rules. I've even created a "blank" table and form to work out the issue and have not added any validation rules. And still, nothing beyond 10 in the DailyID. The user will input data into the form, which will automatically increment the DailyID field, the value of which is stored in Table "Documents"...

Scott

ke sure you re-create it by creating a new table and a new form. Do not import the table and the form into a new database. I went all the way up to 25 with default values on the table of Date(), Date()+1,. . .Date()+10. It all worked well.

Have you ever been able to add more than 10 values for any day since youstarted doing this with code?




Sal

What is your primary key? do you have any indexes at all on the table?



Sal

Sal: My primary key is a field "ID" (autonumber). There are indexes on DAILYID and MatterNumber. Perhaps if I do not Index "DailyID"?? Thanks for the tip... I'll check it out and let you know!
Scott