Custom Numbering driving me nuts!! Help!!

[Aaron S via AccessMonster.com]

Hey guys.. trying to get a custom "auto" numbering system going here... First
off, YES I've googled it many times, and found many different ways to do it...
but so far, none of them are working right for what I need... I need a
simple yy-00000 format number for each record in a set of tables.... I've got
4 different types of reports users can choose from, to store different types
of data, but need all 4 to be numbered off a central sequence... (Aka: User
could create report A with ID # 05-00001, but if they tried to add a report B
it would be # 05-00002 rather than a different 05-00001 even though it's a
different type of report)

The way I've got it set up at the moment, I have a form that opens to give
users the choice of which type of report they would like to create, via 4
command buttons... in the BeforeUpdate part of this choice form, I have
coding from Jeff Polack that he posted a while back, to determine & assign
the next sequenced # based on a table I have created solely to store the
current #... when a user clicks one of the 4 buttons to open the actual
report form, i would like the new report # stored back into that table, AND
copied into the new report they're entering... Copying it is easy, I have
hidden fields on the 'choice' form that the actual report forms refer to for
values.... the problem is, this code isn't assigning anything into the hidden
fields based off of the table, and it doesn't appear to be updating the table
at all either, as it's still blank... I'll post my code here to see if that
helps anyone solve this for me...

OH AND before I forget... I'm a novice at this.... Been learning the VBA
aspect of things as I go here... I understand most of what Jeff's coding here
does, but I'm not seeing what I'm doing wrong....

Please please please help!

Private Sub Form_BeforeInsert(Cancel As Integer)
RepNum = NextCustomCounter("Current#")
End Sub

Public Function NextCustomCounter(TableName As String) As String

On Error GoTo NextCustomCounterErr

Dim rs As DAO.Recordset
Dim RepNum As String
Dim NextCounter As String
Dim intCount As Integer

Set rs = CurrentDb.OpenRecordset("Current#", 2)

'Open the DAO recordset.
With rs
.FindFirst "TableName = '" & TableName & "'"

If Not .NoMatch Then 'When the table is found,

If Not IsNull(!RepNum) Then 'And a counter entry exists
'Check to see if it's a new year
If Left(!RepNum, 2) = Format(Date, "yy") Then
'Use the next sequental counter
RepNum = !RepNum
Else
'If this is the first transaction of the new year, reset
the counter
RepNum = Format(Date, "yy") & "-0"
End If
Else
RepNum = Format(Date, "yy") & "-0"
End If 'Counter exists
'Update the record with the next entry
.Edit
Else
'Add the new table & counter to the CustomCounters table
RepNum = Format(Date, "yy") & "-0"
.AddNew
!TableName = TableName
End If

'Increments by one the value of the numeric portion of the counter
'Determines the length of the string by subtracting 3 (YY-)
intCount = Val(Right(RepNum, (Len(RepNum) - 3))) + 1

'Formats the updated value of the counter
NextCounter = Format(Date, "yy") & "-" & CStr(intCount)

'Store the counter
!RepNum = NextCounter
.Update

End With 'recordset

'Clear the recordset
Set rs = Nothing

'Sets for return to the calling code
NextCustomCounter = NextCounter

Exit Function

'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'The following error routine should be replaced with a custom
'error routine.
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
NextCustomCounterErr:
MsgBox "Error " & Err & ": " & Error$
'If Err <> 0 Then Resume
'End

End Function

 

[Tim Ferguson]

Hey guys.. trying to get a custom "auto" numbering system going
here... First off, YES I've googled it many times, and found many
different ways to do it... but so far, none of them are working right
for what I need... I need a simple yy-00000 format number for each
record in a set of tables.... I've got 4 different types of reports
users can choose from, to store different types of data, but need all
4 to be numbered off a central sequence... (Aka: User could create
report A with ID # 05-00001, but if they tried to add a report B it
would be # 05-00002 rather than a different 05-00001 even though it's
a different type of report)

First problem is a design problem. You need to separate out the two bits
of information into two fields, say YearNumber and SerialNumber. Remember
that you can always present them to the user as a single string on forms
and reports by setting the controlsource of the textbox to something like

=Format(YearNum,"00\-") & Format(SerialNum,"00000")

The probable second problem is using four tables to keep one entity. It
sounds as though you are using four tables where one would be better. If
the attributes of the four types of reports are very different, consider
a subtyping solution (qv).

Once you get the design right, the usual coding methods will easily
generate the correct numbers.

Hope that helps


Tim F

 

[Aaron S via AccessMonster.com]

Thanks for the reply Tim..

Well, I tried setting it up with the 2 fields separate at first, because that
seemed to make more sense.. (as you said, simply displaying it as one field
on forms & whatnot)... but even then using simple coding with Dmax it still
didn't work...

The 4 tables actually don't control the report # at all... the report # gets
stored into those tables via the forms for entering them, by having the
Defaultvalue look at a hidden field on the previous form (which is the one
that has this coding to determine the #).....

I need to have these 4 types separated out, because they are vastly different
types of incidents/reports... but because they're still "Calls for service",
they need to be numbered off of ONE system... hence the reason for what I'm
trying to set up...

As I said, I'm still a novice at this... and I understand this type of thing
has been asked many times, in different ways... so I appreciate any help I
can get in figuring this out... I'd even be willing to send a copy of my db
to someone if they wanted to look at the code more in-depthly to see what I'm
doing wrong...

 

[Tim Ferguson]

but even then using simple coding with Dmax it
still didn't work...

What was the contents of the DMax() call, and what was wrong: did you get
an error or did it return the wrong number?

The 4 tables actually don't control the report # at all...

Tables don't control numbers in any sense: table are just buckets for
holding records.

I need to have these 4 types separated out, because they are vastly
different types of incidents/reports... but because they're still
"Calls for service", they need to be numbered off of ONE system...
hence the reason for what I'm trying to set up...

Sorry: it's common sense that things that share a common set of
identifiers _are_ the same things, regardless of how 'vastly different'
they are in other respects. Sounds like you need a new entity
CallsForService that you look something like this:

CallsForService (
YearNum integer not null,
SerialNum integer not null,
Owner integer null foreign key references employees,
DateCreated datetime null,
etc // other fields common to all reports go here

primary key (YearNum, SerialNum)
)


TypeOneReports (
YearNum integer not null,
SerialNum integer not null,
MaxPages integer not null,
HeadingText text(32) null

primary key (YearNum, SerialNum)
foreign key (YearNum, SerialNum) references CallsForServices
)

TypeTwoReports (
YearNum integer not null,
SerialNum integer not null,
Colour text(5) foregn key references colours,
GermanSource memo null

primary key (YearNum, SerialNum)
foreign key (YearNum, SerialNum) references CallsForServices
)


and so on and so on. The point is that you have a single table to hold
all the CallsForServices and the serial number is allocated on this table
(the supertype). Each of the subtypes is related to this table with a
one-to-one relationship (it's one of the few respectable uses for 1:1
relationships).

If you have other tables that are related to individual type of report,
then you point their FKs at TypeTwoReports(YearNum, SerialNum) whereas
tables that could relate to any kind of CallForService reference the
supertype itself.

You can prevent a single CallForService being allocated to more than one
subtype by having a constraint something like this:

SELECT 2 > COUNT(*)
FROM (
SELECT ALL YearNum, SerialNum
FROM TypeOneReports
UNION ALL
SELECT ALL YearNum, SerialNum
FROM TypeTwoReports
UNION ALL
SELECT ALL YearNum, SerialNum
FROM TypeThreeReports
) AS sub
WHERE sub.YearNum = YearNum
AND sub.SerialNum = SerialNum


which counts all records with the appropriate (YearNum, SerialNum) from
all the subtype tables and makes sure there are zero or one of them. It's
not easy getting this into an access database, but I think it's possible.
In SQL server you can use a trigger to achieve the same result.

Hope that helps


Tim F

 

[Aaron S via AccessMonster.com]

Well, in Lieu of the looming new year (which my boss set as a deadline to
have this autonumbered thing working... jerk.. lol) I managed to come up with
an alternative using simple queries... I have a select query that compares
the stored year to the system year... works great.... an update query that
adds 1 to the report #... great... an update query for changing the stored
year when the new year hits... great.... and an update query that resets the
report # back to 0... All the queries work perfectly from the DB window,
changing the stored records exactly as I need them to.... but now the problem
is getting it to work behind a command button... How to I get the value of a
query's results, in code? DMax, maybe? I've never tried using it with a
query..... and using DMax("[RepYear]", "Current#", "[RepYear] = Year(Date)")
didn't return anything.... so even though the queries are working perfectly,
they're not returning anything when run in a module.... What did I goof up
THIS time? lol

Again, thanks for the help

 

[Aaron S via AccessMonster.com]

Well, I guess the most appropriate thing to say now is, Nevermind! I FINALLY
figured it out! Turns out my DCount wasn't working because I had forgotten
to Dim it as an integer.... D'oh! But it's workin great now! So all I've
got left to do is cosmetics, and it'll be done! Ahead of schedule even! LOL

Thanks again for the help guys! Take care!