Wrox Programmer Forums
|
Access Discussion of Microsoft Access database design and programming. See also the forums for Access ASP and Access VBA.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old November 15th, 2003, 05:25 PM
Authorized User
 
Join Date: Oct 2003
Posts: 27
Thanks: 0
Thanked 0 Times in 0 Posts
Default URGENT Help needed

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
 
Old November 15th, 2003, 05:42 PM
Authorized User
 
Join Date: Jun 2003
Posts: 75
Thanks: 0
Thanked 0 Times in 0 Posts
Default

What is the structure of table documents? I copied your code and it works beyond 10.
 
Old November 15th, 2003, 05:45 PM
sal sal is offline
Friend of Wrox
 
Join Date: Oct 2003
Posts: 702
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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
 
Old November 15th, 2003, 06:20 PM
Authorized User
 
Join Date: Oct 2003
Posts: 27
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Quote:
quote:Originally posted by Dataman
 What is the structure of table documents? I copied your code and it works beyond 10.
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!
 
Old November 15th, 2003, 07:42 PM
sal sal is offline
Friend of Wrox
 
Join Date: Oct 2003
Posts: 702
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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
 
Old November 15th, 2003, 07:57 PM
sal sal is offline
Friend of Wrox
 
Join Date: Oct 2003
Posts: 702
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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
 
Old November 15th, 2003, 07:58 PM
Authorized User
 
Join Date: Oct 2003
Posts: 27
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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
 
Old November 16th, 2003, 01:01 AM
sal sal is offline
Friend of Wrox
 
Join Date: Oct 2003
Posts: 702
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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
 
Old November 16th, 2003, 01:15 AM
sal sal is offline
Friend of Wrox
 
Join Date: Oct 2003
Posts: 702
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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



Sal
 
Old November 16th, 2003, 02:40 PM
Authorized User
 
Join Date: Oct 2003
Posts: 27
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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





Similar Threads
Thread Thread Starter Forum Replies Last Post
Needed Very Urgent ndrnathan C# 0 June 8th, 2007 07:43 AM
urgent help needed ramabharti Javascript How-To 2 November 21st, 2006 01:17 AM
Very Urgent Help Needed Vinay Chugh ASP.NET 1.0 and 1.1 Basics 1 June 7th, 2006 12:32 AM
Urgent Help Needed rameshnarayan XSLT 0 September 19th, 2005 03:46 AM
urgent help needed sugandh .NET Web Services 0 January 10th, 2004 12:44 PM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.