|
Subject:
|
URGENT Help needed
|
|
Posted By:
|
scott
|
Post Date:
|
11/15/2003 4:25:47 PM
|
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
|
|
Reply By:
|
Dataman
|
Reply Date:
|
11/15/2003 4:42:00 PM
|
What is the structure of table documents? I copied your code and it works beyond 10.
|
|
Reply By:
|
sal
|
Reply Date:
|
11/15/2003 4:45:49 PM
|
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
|
|
Reply By:
|
scott
|
Reply Date:
|
11/15/2003 5:20:00 PM
|
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!
|
|
Reply By:
|
sal
|
Reply Date:
|
11/15/2003 6:42:27 PM
|
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
|
|
Reply By:
|
sal
|
Reply Date:
|
11/15/2003 6:57:08 PM
|
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
|
|
Reply By:
|
scott
|
Reply Date:
|
11/15/2003 6:58:09 PM
|
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
|
|
Reply By:
|
sal
|
Reply Date:
|
11/16/2003 12:01:09 AM
|
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
|
|
Reply By:
|
sal
|
Reply Date:
|
11/16/2003 12:15:34 AM
|
What is your primary key? do you have any indexes at all on the table?
Sal
|
|
Reply By:
|
scott
|
Reply Date:
|
11/16/2003 1:40:16 PM
|
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
|
|
Reply By:
|
scott
|
Reply Date:
|
11/16/2003 1:57:12 PM
|
No luck on this.... 
I'd be happy to email what I have to anyone who may be able to help!!
Desperately...
Scott
quote:
Originally posted by scott
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
|
|
Reply By:
|
sal
|
Reply Date:
|
11/16/2003 2:02:17 PM
|
Ok, e-mail me a copy. Create a new database and IMPORT your current table (Delete the data) and import the form as well. I will look at it.
csal@charter.net
Sal
|
|
Reply By:
|
scott
|
Reply Date:
|
11/16/2003 2:13:22 PM
|
THanks, Sal! I've just emailed it to you.
I owe you a BIG BEER!!
quote:
Originally posted by sal
Ok, e-mail me a copy. Create a new database and IMPORT your current table (Delete the data) and import the form as well. I will look at it.
csal@charter.net
Sal
|
