Trouble with Dmax and increasing by one

  • Thread starter Christine Vollberg via AccessMonster.com
  • Start date
C

Christine Vollberg via AccessMonster.com

I have this procedure that I am using the Dmax function with to find the
max charge seq no for a particular defendant and then with an input request
from the user creating duplicate records the number of times requested by
the user. (This is a case management system for sentencing) If a
defendant has 30 counts of the same charge I need 30 records with the
charge seq no assigned 1 -30. The problem is it works the first time and
if I go back to the form again to say add 5 more it trys to repeat starting
at 10, which gives me a duplicate primary key error. Here is my code:

Private Sub cmdDupREc_Click()
Dim count As Integer
Dim sSql As String
Dim DupSql As String
Dim charseq As String
Dim DupSql2 As String
Dim DupSql3 As String
Dim DupSql4 As String
Dim RepeatValue As Integer
Dim db As DAO.Database
Set db = DBEngine(0)(0)
RepeatValue = InputBox("How Many Counts?", Counts)
Rem this statement goes and gets the highest ChargeSeqNo for this
defendant

charseq = DMax("[ChargeSeqNo]", "tblDefChargesSentence", "[CaseNo]= '"
& Me![CaseNo] & "' And [DefendantId]=" & Me![DefendantId]) + 1
count = RepeatValue - 1
Do While count > 0

DupSql = "INSERT INTO tblDefChargesSentence ( DefendantId, CaseNo,
ChargeCode, ChargeSeqNo, FiledOffense, LastName, FirstName" + IIf(IsNull
(SID), " ", ",SID") + IIf(IsNull(ATN), " ", ",ATN") + IIf(IsNull
(DateOfOffense), " ", ",DateOfOffense") + IIf(IsNull(DateOfArrest), " ",
",DateOfArrest") + IIf(IsNull(ArDate), " ", ",ArDate") + IIf(IsNull
(ConvictionDate), " ", ",ConvictionDate") + IIf(IsNull(PAttorney), " ",
",PAttorney") + IIf(IsNull(PAttorney2), " ", ",PAttorney2") + IIf(IsNull
(DAttorney), " ", ",DAttorney") + IIf(IsNull(DAttorney2), " ",
",DAttorney2")
DupSql2 = IIf(IsNull(TrialBy), " ", ",TrialBy") + IIf(IsNull
(DefPlea), " ", ",DefPlea") + IIf(IsNull(DateOfPlea), " ", ",DateOfPlea") +
IIf(IsNull(TypeChargeFM), " ", ",TypeChargeFM") + IIf(IsNull(ChargeTypeORA)
, " ", ",ChargeTypeORA") + ") values ( " + Str(DefendantId) + ",'" + CaseNo
+ "','" + ChargeCode + "','" + charseq + "','" + FiledOffense + "','" +
LastName + "','" + FirstName + "'" + IIf(IsNull(SID), " ", ",'" + SID + "'")
+ IIf(IsNull(ATN), " ", ",'" + ATN + "'")
DupSql3 = IIf(IsNull(DateOfOffense), " ", ",'" + Format
(DateOfOffense, "mm/dd/yyyy") + "'") + IIf(IsNull(DateOfArrest), " ", ",'"
+ Format(DateOfArrest, "mm/dd/yyyy") + "'") + IIf(IsNull(ArDate), " ", ",'"
+ Format(ArDate, "mm/dd/yyyy") + "'") + IIf(IsNull(ConvictionDate), " ",
",'" + Format(ConvictionDate, "mm/dd/yyyy") + "'") + IIf(IsNull(PAttorney),
" ", ",'" + PAttorney + "'") + IIf(IsNull(PAttorney2), " ", ",'" +
PAttorney2 + "'") + IIf(IsNull(DAttorney), " ", ",'" + DAttorney + "'")
DupSql4 = IIf(IsNull(DAttorney2), " ", ",'" + DAttorney2 + "'") +
IIf(IsNull(TrialBy), " ", ",'" + TrialBy + "'") + IIf(IsNull(DefPlea), " ",
",'" + DefPlea + "'") + IIf(IsNull(DateOfPlea), " ", ",'" + Format
(DateOfPlea, "mm/dd/yyyy") + "'") + IIf(IsNull(TypeChargeFM), " ", "," +
Str(TypeChargeFM)) + IIf(IsNull(ChargeTypeORA), " ", "," + Str
(ChargeTypeORA)) + ")"
sSql = DupSql + DupSql2 + DupSql3 + DupSql4
'display sql statement for testing purposes only
sql_text = sSql
db.Execute sSql, dbFailOnError
charseq = charseq + 1
count = count - 1
If count = 0 Then
Exit Do
End If
Loop
End Sub
 
R

Rick Brandt

Christine Vollberg via AccessMonster.com said:
I have this procedure that I am using the Dmax function with to find the
max charge seq no for a particular defendant and then with an input request
from the user creating duplicate records the number of times requested by
the user. (This is a case management system for sentencing) If a
defendant has 30 counts of the same charge I need 30 records with the
charge seq no assigned 1 -30. The problem is it works the first time and
if I go back to the form again to say add 5 more it trys to repeat starting
at 10, which gives me a duplicate primary key error.
Click to expand...
[snip]

This is what happens when you do DMax() against a text value instead of a number
value. You get to 9 add 1 for 10 and then 10 sorts to the bottom (because
that's how text sorts) and you are stuck there.

Check your table design and you will find that ChargeSeqNo is a text field.
 
C

Christine Vollberg via AccessMonster.com

I sorta thought that is what the problem was, now just have to figure out
how to fix it, that field is an import field from a text string, we
orginally set them all up as text fields for that purpose. Thank you for
the info.

Christine
 
R

Rick Brandt

Christine said:
I sorta thought that is what the problem was, now just have to figure
out how to fix it, that field is an import field from a text string,
we orginally set them all up as text fields for that purpose. Thank
you for the info.

Christine
Click to expand...

Well you can do...

DMax(Val([TextField)) + 1

....or...

DMax(CLng([TextField])) + 1

....but it will not be as efficient as if you had a numeric field to start with
since the entire table will have to be scanned to determine the Max value. With
a numeric field that was indexed (particularly if indexed Descending) then only
the index would need to be read.
 
C

Christine Vollberg via AccessMonster.com

This is what I did, does fine the first time but the second time does the
same thing. I cannot change the field type unless there is a way to
globally change a couple hundred pages of code along with 75 forms and
reports.

charseq = DMax(CLng([ChargeSeqNo]), "tblDefChargesSentence", "[CaseNo]
= '" & Me![CaseNo] & "' And [DefendantId]=" & Me![DefendantId]) + 1


Thanks

Christine
 
D

Douglas J. Steele

I can't see the rest of your post (since AccessMonster seems to have an
annoying habit of not posting history), but you cannot change a field type
in an aggregate function like that. The first argument must be the name of a
field, the second argument is the name of the table or query, and the
(optional) third argument is the WHERE clause.

Try:

charseq = CLng(DMax("[ChargeSeqNo]", "tblDefChargesSentence", "[CaseNo] =
'" & Me![CaseNo] & "' And [DefendantId]=" & Me![DefendantId])) + 1

or, better if there's a chance that you won't have a value for that CaseNo
and DefendantId,

charseq = CLng(Nz(DMax("[ChargeSeqNo]", "tblDefChargesSentence",
"[CaseNo] = '" & Me![CaseNo] & "' And [DefendantId]=" &
Me![DefendantId]),0)) + 1
 
C

Christine Vollberg via AccessMonster.com

The problem is I am dealing with a text field using dmax. it works the
first time no problem. but I try it a second time it trys to restart the
number at 10, and then gives me a duplicate primary key error.
 
R

Rick Brandt

Douglas said:
I can't see the rest of your post (since AccessMonster seems to have
an annoying habit of not posting history), but you cannot change a
field type in an aggregate function like that. The first argument
must be the name of a field, the second argument is the name of the
table or query, and the (optional) third argument is the WHERE clause.
Click to expand...

I just tested (in A97) and...

DMax("CLng([FieldName])", "TableName")

....worked just fine.

I often use expressions as the first argument of domain aggregate functions and
have never had an issue with it.
 
C

Christine Vollberg via AccessMonster.com

It worked the first time around and then the second time tried to do it and
it tries to repeat at 10 again, i know this is the text field issue but
cannot change the field now too much involved. when on the same form and
try to add a second time more than 1 after adding 10 it stops at 10 with
the duplicate primary key error
 
C

Christine Vollberg via AccessMonster.com

Let me explain the issue a little more, if I have an end user entering 25
counts of the same charge and it works. Then he/she says oops i forgot 5
counts and with the same screen still open trys to add 5 more counts it
does not work. now if i close the screen and then try to add it works.
But you know as well as I do that the end user will no do that. I could
send you the file if you like (32MB) so you can take a look, this project
is been on going for over 9 months and it looks great, but now with this
issue I am loosing my mind.
 
C

Christine Vollberg via AccessMonster.com

I did so more testing, I was wrong once the first 25 are in and I close the
screen and try it again it gives me the same error message tries to start
at 10 again. Here is the code at run time:

1st run: INSERT INTO tblDefChargesSentence ( DefendantId, CaseNo,
ChargeCode, ChargeSeqNo, FiledOffense, LastName, FirstName
,DateOfOffense,DateOfArrest,ArDate ,PAttorney ,DAttorney
,TrialBy,DefPlea,DateOfPlea,TypeChargeFM,ChargeTypeORA) values ( 294,'01
000913','14:98.1A','25','14:98.1A','PELLERIN','CHRISTOPHER'
,'06/01/2002','06/01/2002','07/01/2002' ,'21639' ,'02263'
,'1','G','08/01/2002', 7, 4)

2nd run: INSERT INTO tblDefChargesSentence ( DefendantId, CaseNo,
ChargeCode, ChargeSeqNo, FiledOffense, LastName, FirstName
,DateOfOffense,DateOfArrest,ArDate ,PAttorney ,DAttorney
,TrialBy,DefPlea,DateOfPlea,TypeChargeFM,ChargeTypeORA) values ( 294,'01
000913','14:98.1A','10','14:98.1A','PELLERIN','CHRISTOPHER'
,'06/01/2002','06/01/2002','07/01/2002' ,'21639' ,'02263'
,'1','G','08/01/2002', 7, 4)
the number I am trying to increment correctly is after the first 14:98.1A
 
C

Christine Vollberg via AccessMonster.com

I fixed the problem by adding an additional field that was a number, was
easier todo than rewrite and change a lot of code. Thanks for the help on
that issue.

Christine
 
Top