Auto numbering oddity

[Stinky Pete]

Hi,

The db that's used as our corporate non conformace register, uses a NCF
numbering system based on yy-xxxx. The yy is calculated using the year
(sorry seems a bit obvious) and the xxxx bit is calculated for the next
new NCF by adding 1 onto the previously recorded number. What I want
to do is enter the main table and manually add a higher number so that
when a new procedure is implemented, we are starting from a known entry
# and date. What I cannot work out, is even though I can enter the
main table and add the number I want, when using the form to then make
a new entry, the form's not working off the number I've ented. By
that, as it stand now, if a new entry is 06-1001, the next one will be
06-1002. If I wanted to manually add one as 06-1500, the forms not
incrementing to 06-1501. Any ideas? The codes below if it's any help.

Private Sub New_NCR_Click()

Dim rst, NCRNumber, strcriteria, DDate, ddatestart, ddateend

Dim stDocName As String
Dim stLinkCriteria As String
Dim dbs As Database
DDate = Format(Date, "yy")
Set dbs = CurrentDb
Set rst = dbs.OpenRecords et("NCR_Table New", dbOpenDynaset)

For NCRNumber = 1 To 9999
strcriteria = "[NCR] = " & "'" & DDate & "-" & Format([NCRNumber],
"0000") & "'"
With rst
.FindFirst strcriteria
If .NoMatch Then
Exit For
End If
End With
Next NCRNumber

stDocName = "NCF Form"
DoCmd.OpenForm stDocName, acNormal, , , acFormAdd
Forms![NCF Form]![NCR] = DDate & "-" & Format(NCRNumbe r, "0000")
Forms![NCF Form]![Date Initiated] = Date
DoCmd.Save
Forms![NCF Form]![Type].SetFocus

End Sub

[Bob Quintal]

Re: Auto numbering oddity

somebody went to the trouble of writing the for/next loop to
prevent having gaps in your ncr numbers. It obviously works very
well.

replace it with the following 1 line of code
NCRNumber = nz(dmax("NCR"," NCR_Table New",
left([NCR],2 = """ & DDate & """)"

insert it after the NEXT NCRnumber line. so you could go back to
the old code next year.


"Stinky Pete" <Peter.Morris@s ymbionhealth.co mwrote in
news:1160701427 .499530.320810@ k70g2000cwa.goo glegroups.com:

Hi,
>
The db that's used as our corporate non conformace register,
uses a NCF numbering system based on yy-xxxx. The yy is
calculated using the year (sorry seems a bit obvious) and the
xxxx bit is calculated for the next new NCF by adding 1 onto
the previously recorded number. What I want to do is enter
the main table and manually add a higher number so that when a
new procedure is implemented, we are starting from a known
entry # and date. What I cannot work out, is even though I
can enter the main table and add the number I want, when using
the form to then make a new entry, the form's not working off
the number I've ented. By that, as it stand now, if a new
entry is 06-1001, the next one will be 06-1002. If I wanted
to manually add one as 06-1500, the forms not incrementing to
06-1501. Any ideas? The codes below if it's any help.
>
Private Sub New_NCR_Click()
>
Dim rst, NCRNumber, strcriteria, DDate, ddatestart, ddateend
>
Dim stDocName As String
Dim stLinkCriteria As String
Dim dbs As Database
DDate = Format(Date, "yy")
Set dbs = CurrentDb
Set rst = dbs.OpenRecords et("NCR_Table New", dbOpenDynaset)
>
For NCRNumber = 1 To 9999
strcriteria = "[NCR] = " & "'" & DDate & "-" &
Format([NCRNumber],
"0000") & "'"
With rst
.FindFirst strcriteria
If .NoMatch Then
Exit For
End If
End With
Next NCRNumber
>
stDocName = "NCF Form"
DoCmd.OpenForm stDocName, acNormal, , , acFormAdd
Forms![NCF Form]![NCR] = DDate & "-" & Format(NCRNumbe r,
"0000") Forms![NCF Form]![Date Initiated] = Date
DoCmd.Save
Forms![NCF Form]![Type].SetFocus
>
End Sub
>
>

--
Bob Quintal

PA is y I've altered my email address.

--
Posted via a free Usenet account from http://www.teranews.com

[Tom van Stiphout]

Re: Auto numbering oddity

On 13 Oct 2006 01:42:20 GMT, Bob Quintal <rquintal@sPAmp atico.ca>
wrote:

You probably meant: it obviously works very slowly.
The average number of comparisons would be n/2, or likely even greater
than that in the real world when the lower values have been filled in.

Amazing that some people still refuse to use indexes (the powerful
Seek method).

For some applications, expecially with a lot of disconnected logic, I
use a "TakeANumbe r" sproc which takes its value from a table with
TableName / NextNumberToUse records.

-Tom.

>somebody went to the trouble of writing the for/next loop to
>prevent having gaps in your ncr numbers. It obviously works very
>well.

<Clip>

[David W. Fenton]

Re: Auto numbering oddity

Tom van Stiphout <no.spam.tom774 4@cox.netwrote in
news:9l5ui2lkdg ktsle4m422169uv i3qtvdud7@4ax.c om:

You probably meant: it obviously works very slowly.
The average number of comparisons would be n/2, or likely even
greater than that in the real world when the lower values have
been filled in.
>
Amazing that some people still refuse to use indexes (the powerful
Seek method).
>
For some applications, expecially with a lot of disconnected
logic, I use a "TakeANumbe r" sproc which takes its value from a
table with TableName / NextNumberToUse records.

You can find the gaps with SQL without needing to walk through the
whole table.

Indeed, I've never done anything like this except with SQL.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/

[Bob Quintal]

Re: Auto numbering oddity

Tom van Stiphout <no.spam.tom774 4@cox.netwrote in
news:9l5ui2lkdg ktsle4m422169uv i3qtvdud7@4ax.c om:

On 13 Oct 2006 01:42:20 GMT, Bob Quintal
<rquintal@sPAmp atico.cawrote:
>
You probably meant: it obviously works very slowly.

I was being somewhat facetious.

The average number of comparisons would be n/2, or likely even
greater than that in the real world when the lower values have
been filled in.

Worse, assuming that the recordset is returning the PK of the
table, and the numbers are in order.the average number of
comparisons would be n, not n/2. The whole object of such a routine
is to fill in any gaps created by a user abandoning the creation of
a record, as is often seen with autonumbers.

In normal situation if a gap occurs, it will be the second from
highest number.

>
Amazing that some people still refuse to use indexes (the
powerful Seek method).
>

Tell the world how to seek for the number not in the index, please.

For some applications, expecially with a lot of disconnected
logic, I use a "TakeANumbe r" sproc which takes its value from
a table with TableName / NextNumberToUse records.
>
-Tom.

This is also my preferred method. Although a select top 1 PK order
by PK descending works pretty well too, but again can leave gaps in
the numbering.

>
>
>

>>somebody went to the trouble of writing the for/next loop to
>>prevent having gaps in your ncr numbers. It obviously works
>>very well.

<Clip>
>

--
Bob Quintal

PA is y I've altered my email address.

--
Posted via a free Usenet account from http://www.teranews.com

[Bob Quintal]

Re: Auto numbering oddity

"David W. Fenton" <XXXusenet@dfen ton.com.invalid wrote in
news:Xns985BB4C 6232D3f99a49ed1 d0c49c5bbb2@127 .0.0.1:

Tom van Stiphout <no.spam.tom774 4@cox.netwrote in
news:9l5ui2lkdg ktsle4m422169uv i3qtvdud7@4ax.c om:
>

>You probably meant: it obviously works very slowly.
>The average number of comparisons would be n/2, or likely
>even greater than that in the real world when the lower
>values have been filled in.
>>
>Amazing that some people still refuse to use indexes (the
>powerful Seek method).
>>
>For some applications, expecially with a lot of disconnected
>logic, I use a "TakeANumbe r" sproc which takes its value from
>a table with TableName / NextNumberToUse records.

>
You can find the gaps with SQL without needing to walk through
the whole table.
>
Indeed, I've never done anything like this except with SQL.
>

Please supply a sample SQL statement to do so.


--
Bob Quintal

PA is y I've altered my email address.

--
Posted via a free Usenet account from http://www.teranews.com

[David W. Fenton]

Re: Auto numbering oddity

Bob Quintal <rquintal@sPAmp atico.cawrote in
news:Xns985BC13 B1AEB8BQuintal@ 66.150.105.47:

"David W. Fenton" <XXXusenet@dfen ton.com.invalid wrote in
news:Xns985BB4C 6232D3f99a49ed1 d0c49c5bbb2@127 .0.0.1:
>

>Tom van Stiphout <no.spam.tom774 4@cox.netwrote in
>news:9l5ui2lkd gktsle4m422169u vi3qtvdud7@4ax. com:
>>

>>You probably meant: it obviously works very slowly.
>>The average number of comparisons would be n/2, or likely
>>even greater than that in the real world when the lower
>>values have been filled in.
>>>
>>Amazing that some people still refuse to use indexes (the
>>powerful Seek method).
>>>
>>For some applications, expecially with a lot of disconnected
>>logic, I use a "TakeANumbe r" sproc which takes its value from
>>a table with TableName / NextNumberToUse records.

>>
>You can find the gaps with SQL without needing to walk through
>the whole table.
>>
>Indeed, I've never done anything like this except with SQL.

>
Please supply a sample SQL statement to do so.

Non-equi self-join on the ID field. Find the Min() of the first one
lacking having a Null value in the field N+1.

This will give you all the IDs where there isn't an ID+1 value:

SELECT tblMain.ID
FROM tblMain LEFT JOIN tblMain AS NextIDTable ON tblMain.ID =
NextIDTable.ID+ 1 WHERE NextIDTable.ID Is Null;

Then take the Min() of the first column and add 1 to it:

SELECT Min(tblMain.ID) + 1 As NextIDToFillGap
FROM tblMain LEFT JOIN tblMain AS NextIDTable ON tblMain.ID =
NextIDTable.ID+ 1 WHERE NextIDTable.ID Is Null;

That will give you the next available unused ID number.

Many people don't know you can do non-equi joins in Access. You
can't do them in the QBE, but if you create it as an equi-join in
the QBE, you can then edit it in SQL view to change it to any type
of non-equi join you like.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/

[Bob Quintal]

Re: Auto numbering oddity

"David W. Fenton" <XXXusenet@dfen ton.com.invalid wrote in
news:Xns985BD88 BC321Bf99a49ed1 d0c49c5bbb2@127 .0.0.1:

Bob Quintal <rquintal@sPAmp atico.cawrote in
news:Xns985BC13 B1AEB8BQuintal@ 66.150.105.47:
>

>"David W. Fenton" <XXXusenet@dfen ton.com.invalid wrote in
>news:Xns985BB4 C6232D3f99a49ed 1d0c49c5bbb2@12 7.0.0.1:
>>

>>Tom van Stiphout <no.spam.tom774 4@cox.netwrote in
>>news:9l5ui2lk dgktsle4m422169 uvi3qtvdud7@4ax .com:
>>>
>>>You probably meant: it obviously works very slowly.
>>>The average number of comparisons would be n/2, or likely
>>>even greater than that in the real world when the lower
>>>values have been filled in.
>>>>
>>>Amazing that some people still refuse to use indexes (the
>>>powerful Seek method).
>>>>
>>>For some applications, expecially with a lot of
>>>disconnect ed logic, I use a "TakeANumbe r" sproc which takes
>>>its value from a table with TableName / NextNumberToUse
>>>records.
>>>
>>You can find the gaps with SQL without needing to walk
>>through the whole table.
>>>
>>Indeed, I've never done anything like this except with SQL.

>>
>Please supply a sample SQL statement to do so.

>
Non-equi self-join on the ID field. Find the Min() of the
first one lacking having a Null value in the field N+1.
>
This will give you all the IDs where there isn't an ID+1
value:
>
SELECT tblMain.ID
FROM tblMain LEFT JOIN tblMain AS NextIDTable ON tblMain.ID =
NextIDTable.ID+ 1 WHERE NextIDTable.ID Is Null;
>
Then take the Min() of the first column and add 1 to it:
>
SELECT Min(tblMain.ID) + 1 As NextIDToFillGap
FROM tblMain LEFT JOIN tblMain AS NextIDTable ON tblMain.ID =
NextIDTable.ID+ 1 WHERE NextIDTable.ID Is Null;
>
That will give you the next available unused ID number.
>
Many people don't know you can do non-equi joins in Access.
You can't do them in the QBE, but if you create it as an
equi-join in the QBE, you can then edit it in SQL view to
change it to any type of non-equi join you like.
>

interesting technique, and it makes sense.

As to non-equi joins, you can also use the old syntax of using
the where clause to join the tables.
The one drawback to editing the join equation is that if for any
reason the query gets opened in the design mode, it's trash.

I spend more time in the SQL window than in the qbe grid.these
days, I just wish (acc 97) it had find and replace and an insert
field name utility in the SQL window..

I suppose I could build a form.
--
Bob Quintal

PA is y I've altered my email address.

--
Posted via a free Usenet account from http://www.teranews.com