467,894 Members | 1,491 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 467,894 developers. It's quick & easy.

Autonumber Oddity

I have a form with one textbox (PermitNumber) where the default value
is:

=IIf(DatePart("yyyy",Date())=Left(DMax("PermitNumb er","tblBLPermitMain"),4),DMax("PermitNumber","tbl BLPermitMain")+1,nextBldgPermit()).
I also have a module (nextBldgPermit() that has the following code:

Function nextBldgPermit()
nextBldgPermit = DatePart("yyyy", Date)
nextBldgPermit = CLng(CStr(nextBldgPermit) & "0001")
End Function

This code assigns a custom autonumber (20050001) for each new record
and works fine. This number is not my primary key.

I also have a second textbox (PlumPermitNumber) where I would like to
assign a second custom autonumber at the click of a command button
(cmdPlumPermit). I have the following code tied to the OnClick event
of a command button:

Private Sub cmdPlumPermit_Click()
Me!PlumPermitNumber = IIf(DatePart("yyyy", Date) =
Left(DMax("PlumPermitNumber", "tblBLPermitMain"), 4),
DMax("PlumPermitNumber", "tblBLPermitMain") + 1, nextPlumPermit())
End Sub

I also have a second module that has the following code:

Function nextPlumPermit()
nextPlumPermit = DatePart("yyyy", Date)
nextPlumPermit = CLng(CStr(nextPlumPermit) & "0001")
End Function

I had hoped that this command button would assign an auto number to my
second textbox and it does, however, the numbers are not incrementing.
The number asigned is always 20050001, and will not go up to 20050002,
0003, etc. I put a test record in my tblPermitMain table with the
number 20050001, and it still will not increment by 1.

I'm not sure why this second number is not incrementing by 1, as the
first one does. The code is the same, and I had hoped that it would
function the same but it doesn't.

Anyone see an error in what I'm doing? I am a complete novice at
writing code.

Thanks for any help you can give.
dskillingstad

Nov 13 '05 #1
  • viewed: 1236
Share:
4 Replies
ds***********@gmail.com wrote:
I have a form with one textbox (PermitNumber) where the default value
is:

=IIf(DatePart("yyyy",Date())=Left(DMax("PermitNumb er","tblBLPermitMain"),4),DMax("PermitNumber","tbl BLPermitMain")+1,nextBldgPermit()).
I also have a module (nextBldgPermit() that has the following code:

Function nextBldgPermit()
nextBldgPermit = DatePart("yyyy", Date)
nextBldgPermit = CLng(CStr(nextBldgPermit) & "0001")
End Function

This code assigns a custom autonumber (20050001) for each new record
and works fine. This number is not my primary key.

I also have a second textbox (PlumPermitNumber) where I would like to
assign a second custom autonumber at the click of a command button
(cmdPlumPermit). I have the following code tied to the OnClick event
of a command button:

Private Sub cmdPlumPermit_Click()
Me!PlumPermitNumber = IIf(DatePart("yyyy", Date) =
Left(DMax("PlumPermitNumber", "tblBLPermitMain"), 4),
DMax("PlumPermitNumber", "tblBLPermitMain") + 1, nextPlumPermit())
End Sub

I also have a second module that has the following code:

Function nextPlumPermit()
nextPlumPermit = DatePart("yyyy", Date)
nextPlumPermit = CLng(CStr(nextPlumPermit) & "0001")
End Function

I had hoped that this command button would assign an auto number to my
second textbox and it does, however, the numbers are not incrementing.
The number asigned is always 20050001, and will not go up to 20050002,
0003, etc. I put a test record in my tblPermitMain table with the
number 20050001, and it still will not increment by 1.

I'm not sure why this second number is not incrementing by 1, as the
first one does. The code is the same, and I had hoped that it would
function the same but it doesn't.

Anyone see an error in what I'm doing? I am a complete novice at
writing code.

Thanks for any help you can give.
dskillingstad


I would go into your debug window and enter
? DMax("PlumPermitNumber", "tblBLPermitMain")
or open up the table and see what the Max is by sorting descending.

I will assume that the left 4 digits of that record do NOT match the
year so every time you call it it simply creates a "first permit number"
key.

IOW, the DMAX() may return 20060001 and/or greater...due to testing or
playing around or debugging...and you never go rid of that record's
value....so you'd always get 20050001.
Nov 13 '05 #2

Salad wrote:
ds***********@gmail.com wrote:
I have a form with one textbox (PermitNumber) where the default value
is:

=IIf(DatePart("yyyy",Date())=Left(DMax("PermitNumb er","tblBLPermitMain"),4),DMax("PermitNumber","tbl BLPermitMain")+1,nextBldgPermit()).
I also have a module (nextBldgPermit() that has the following code:

Function nextBldgPermit()
nextBldgPermit = DatePart("yyyy", Date)
nextBldgPermit = CLng(CStr(nextBldgPermit) & "0001")
End Function

This code assigns a custom autonumber (20050001) for each new record
and works fine. This number is not my primary key.

I also have a second textbox (PlumPermitNumber) where I would like to
assign a second custom autonumber at the click of a command button
(cmdPlumPermit). I have the following code tied to the OnClick event
of a command button:

Private Sub cmdPlumPermit_Click()
Me!PlumPermitNumber = IIf(DatePart("yyyy", Date) =
Left(DMax("PlumPermitNumber", "tblBLPermitMain"), 4),
DMax("PlumPermitNumber", "tblBLPermitMain") + 1, nextPlumPermit())
End Sub

I also have a second module that has the following code:

Function nextPlumPermit()
nextPlumPermit = DatePart("yyyy", Date)
nextPlumPermit = CLng(CStr(nextPlumPermit) & "0001")
End Function

I had hoped that this command button would assign an auto number to my
second textbox and it does, however, the numbers are not incrementing.
The number asigned is always 20050001, and will not go up to 20050002,
0003, etc. I put a test record in my tblPermitMain table with the
number 20050001, and it still will not increment by 1.

I'm not sure why this second number is not incrementing by 1, as the
first one does. The code is the same, and I had hoped that it would
function the same but it doesn't.

Anyone see an error in what I'm doing? I am a complete novice at
writing code.

Thanks for any help you can give.
dskillingstad


I would go into your debug window and enter
? DMax("PlumPermitNumber", "tblBLPermitMain")
or open up the table and see what the Max is by sorting descending.

I will assume that the left 4 digits of that record do NOT match the
year so every time you call it it simply creates a "first permit number"
key.

IOW, the DMAX() may return 20060001 and/or greater...due to testing or
playing around or debugging...and you never go rid of that record's
value....so you'd always get 20050001.

I cleared out the table and started with a test record of 20050001 and
then tried the button with no luck. Still not sure what the deal is.
I built a quick db with two text boxes and identical code, modules,
etc. and everything works fine, two text boxes with custom counters,
created for each new record. My problem is, I cannot have a counter
assigned to each new record, as I have a subform that may or may not
need a new record...

The problem is in my command button. Again the OnClick event code is:

Private Sub Command7_Click()
Me.plnumber = IIf(DatePart("yyyy", Date) = Left(DMax("plnumber",
"building"), 4), DMax("plnumber", "building") + 1, nextPlumPermit())
End Sub

this code is not incrementing. It always has 20050001....

GRRRRRRRRRRR I NEED this couter to assign a number from a command
button.

Anyone see anything wrong with this code??? Any help you all can give
would really be appreciated!!

This thing is driving me NUTS!!

Thanks,

dskillingstad

Nov 13 '05 #3
Actually I just got the above to work by taking out
IIf(DatePart("yyyy", Date) = Left(DMax("plnumber",
"building"), 4),

and it increments...so possibly the problem is in the date code..???

dskillingstad

Nov 13 '05 #4
It would be much easier to track the problem if you were to write a VBA
Function that does the same thing
========================
Function DefValue() As Long

If DatePart("yyyy",Date())=Left(DMax("PermitNumber"," tblBLPermitMain"),4)
Then
Defvalue = DMax("PermitNumber","tblBLPermitMain")+1
Else
DefValue = nextBldgPermit()
Endif

End Function

And then put a break on it and watch it step through, watching the values at
each line. You could probably diagnose the problem that way.

<ds***********@gmail.com> wrote in message
news:11**********************@g47g2000cwa.googlegr oups.com...
Actually I just got the above to work by taking out
IIf(DatePart("yyyy", Date) = Left(DMax("plnumber",
"building"), 4),

and it increments...so possibly the problem is in the date code..???

dskillingstad

Nov 13 '05 #5

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

3 posts views Thread by Ilan Sebba | last post: by
35 posts views Thread by Traci | last post: by
26 posts views Thread by jimfortune | last post: by
5 posts views Thread by jmdocherty | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.