Generate catalog numbers

[b9791]

I am attempting to create a catalog number similar to the decimal system used in a library for the cataloging and organization of computer software.

Each software title is organized into a category (applications, drivers, etc) and each category has sub categories. I want my catalog number to reflect that categoryID and subcategoryID as well as the count of all records in the selected category.

For example, if entering MS Office XP, I would select Applications (ID 1), Productivity Suites (ID 22) and it is the 1st title in Applications to be entered. Its catalog # therefore should be 1.22.1.

Is this possible to acheive and if so how do I approach it? The DB as a decent amount of code, but I have no idea on the approach for this one. Thanks!

Brian

 

[dcx693]

This problem amounts to searching to see if the current category has any previous entries. If so, is there already an entry for what you want? Then how many already exist? The Dcount function is the simplest way to determine this, though can also use some DAO/ADO methods to do this.

You'd probably want to make yourself a custom funtion to return the catalog numbers in the format you want.

 

[b9791]

I am unfamiliar with DCount. Can you give me an example of how it works in this situation?

 

[Pat Hartman]

You wouldn't use DCount() you would use DMax() to find the highest value already existing. DCount() would cause duplicates if records were ever deleted.

You should store the three fields separately for ease of use. Search the archives for posts on autonumbers and DMax() to see how to generate your own sequential id numbers.

 

[WayneRyan]

b,

How "deep" does your tree go? If it is only three levels or so then
you can make a table like:

tblPrimary:
PrimaryID - Autonumber
PrimaryNumber - Numeric (1)
CategoryName "Applications"

tblSecondary:
PrimaryID
SecondaryID - AutoNumber
SecondaryNumber - Numeric (22)
CategoryName "Productivity Suites"

tblItems:
PrimaryID "Applications"
SecondaryID "Productivity Suites"
ItemName "MS Office XP"
ItemID - AutoNumber
ItemNumber - Numeric (1)
UserName
DateInstalled ... (and other info)

If you reference them with #'s then you won't have sorting problems
with 1.11 being before 1.2

For dealing with the data entry/maintenance you can use cascading
combo boxes to traverse your items. You can use the NotInList event
to determine that it is a new entry:

ItemNumber = Nz(DMax("[ItemNumber]", _
                     "tblItems", _
                     "[PrimaryID] = " & Me.PrimaryID " And " & _
                     "[SecondaryID] = " & Me.SecondaryID), 0) + 1

Don't try to treat the 1.22.1 as a string, it will just make you
write a lot of code to deal with it.

Wayne

 

[dcx693]

You wouldn't use DCount() you would use DMax()

Durn it! That's the second time I've made that mistake. Thanks Pat.

 

[Pat Hartman]

I don't keep score

 

[dcx693]

I don't keep score

Then...Durn it! That's the first time I've made that mistake. Thanks Pat.