Automatic Next number generation

[MicroMan]

Hi

I have an access 2007 database that I have cobbled together, but struggling
with the following:
It is a stock database that uses a single table (Stock) to hold all
information, including the Stock code. I have a form that I use to enter the
information into the table called Stock Details. One of the boxes is 'stock
Code' that at the moment I manually enter with a prefix depending upon the
category of the item, i.e. CO for computers, PH for phone etc. After this is
a number 001, 002 etc so each has a specific stock code.

So what programming do I need to get this automated, I have seen other
threads but I can not seem to get it to do anything - sorry for being a
little thick!


I would like it to generate the code automatically when I select the category
from the category drop down list.

Any thoughts?

MicroMan

[jzwp11]

Welcome to the forum!

Doing a stock/inventory application is generally one of the more challenging databases. With that said, having everything in 1 table will probably not work very well for you in the long run. You might check out this site by Allen Browne where he shows a multi-table design (along with some code to determine inventory on-hand which you might find useful later on). Your stock table would be equivalent to Allen's tblProduct

Now regarding your stock code, I would actually not store the code itself but rather the category and the sequence number in separate fields. You can always show the two values combined in the format you need.

I would have a separate table that holds the various category names and category codes

tblCategory
-pkCatID primary key, autonumber
-txtCategoryName (the long name of category, i.e. computer)
-txtCatCode (the code you discuss, i.e. CO for computer etc.)

tblStock
-pkStockID primary key, autonumber
-fkCatID foreign key to tblCategory; this needs to be a long number field
-longSeqNo (a long number field to hold the sequence number)


In the before update event of the form based on tblStock, you would use a little code to increment the longSeqNo depending on the category chosen.

When you want to see the combined code & sequence number, you would used an expression to bring the two values together.

I have attached a simple database that illustrates how this is done.

[MicroMan]

Hello

Thanks for that I will have a play and let you know.

Kind Regards

MicroMan

[jzwp11]

I did not explain in detail what I did with respect to the database I posted. In the form for entering a new stock item (frmEnterNewStockItems), I used code in the before update event of the form to first check if a product already exists in the chosen category. I used the DCount() function in and IF...THEN statement to determine this. If there is no existing product in a category (DCount()=0), then sequence number control on the form is set to 1. If there is an existing product (DCount()>0), then I used the DMax() function to find the current highest sequence number in that category and add 1 to it and assign that to the sequence number control.

The other form (frmStockList) is just a form to view the products and how to display the concatenated product code (CO, PH) and sequence number. I did not add code to this second form so if you try to add a new record via this form it will not assign the category and sequence number correctly. I should have disabled the add a new record capability for this form--sorry about that.

[MicroMan]

Hi

I have been looking at the example database and trying to back engineer the code etc.

All seems fine at the moment - Many Thanks for your help!

MicroMan

[jzwp11]

You're welcome. Please post back if you have any questions.

[dltoney2]

Did you ever get this to work? I am so confused. I'm trying to do the same thing auto generating a 4-digit Bidder number with the first 2-digits deriving from a Table Number. I can't get it to work and am not good with code but I can copy and paste.

[jzwp11]

Can you provide a little more info on your table structure?

Obviously a table number can have many bidders. Can a person bid on more than 1 table?

[dltoney2]

Can you provide a little more info on your table structure?

Obviously a table number can have many bidders. Can a person bid on more than 1 table?

ReservationsTable
fields I need help with: TableNumber and BidderNumber
There are 10 seats per table. After a person makes a reservation they are assigned a table number. When the table number is input, they need to have an autogenerated bidder number (one per person) that begins with 2 digit table number and ends with 2 digit number, not to exceed 10 per table. So we end up with a 4 digit bidder number assigned to each person.

[jzwp11]

I think you will need more tables, in fact, I think you may need at a minimum of 3 database tables

One table to hold the people (the bidders, assuming a person can bid on more than 1 table over time)

tblPeople
-pkPeopleID primary key, autonumber
-txtFName
-txtLName

A table that holds the table number information

tblReservationTable
-pkResTableID primary key, autonumber
-TableNumber (2 digit long number field)
-other field relative to the table

...and a table to join the bidders (people) to the table

tblPeopleTables
-pkPeopleTableID primary key, autonumber
-fkResTableID foreign key to tblReservationTable
-fkPeopleID foreign key to tblPeople
-BidderNo (2 digit long number field)

In tblPeopleTables, you would relate the various people (bidders) to the table since there are many people/bidders per table (one-to-many relationship)


When you relate the tblReservationTable with the bidder in tblPeopleTable (as in a query), you can bring the TableNumber & BidderNo fields together to construct your 4 digit code.

[RachelC]

Your initial description fits my case perfectly. However, I've looked at the sample database and I can't find any active expressions or code - maybe because I don't know where to look? I'm on a steep learning curve with expressions and code - is there any chance you could give me the actual code you used, and then I can substitute my own fields? Many thanks in anticipation!

[jzwp11]

The code is located the Before Update event of the form (frmEnterNewStockItems). Open the form in Design View then go to the property sheet-->Event tab--> Before Update and then click in the line where you see Event Procedure. A button with three dots (...) will show up to the right, click on the button and you will be taken to the code window.

The following is the actual code. The Me.xxxxx refers to a control on the form.

Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim holdcount As Long
holdcount = DCount("*", "tblStock", "fkCatID=" & Me.cboCat)
Debug.Print holdcount, Me.cboCat


If DCount("*", "tblStock", "fkCatID=" & Me.cboCat) = 0 Then
 Me.longSeqNo = 1
Else
 Me.longSeqNo = DMax("longSeqno", "tblStock", "fkCatID=" & Me.cboCat) + 1
End If
End Sub

[RachelC]

Thanks very much for your help. However, I am still struggling to get any sensible number generation. In desperation I have attached a copy of my database, minus most of the data in the hope that you can tell me where I am going wrong.

I have compiled an Asset Register from a collection of Excel spreasheets and consolidated the data. Hence the existing data has asset numbers assigned, all starting with two letter which were originally intended to denote their category. However, over time this has become lax so I'd like to start a new Asset number sequence starting from entries into the new database, still starting with two letters (Table: Category txtCatID) followed by a number sequence - which can be either a single sequence or a separate sequence for each category, whichever is easiest. I created a fkCatID field in the Computers table - but I think I'm struggling with the relationships!

The Data Entry form has a field for the original Asset Number (for all existing data and I then created an additional field for a New Asset Number for newly entered data - which is the one I am attempting to create an expression for.

If you could possibly manage to look at this it would be very much appreciated!

[jzwp11]

You can only increment a number field, so if you want your asset number to have letters and numbers, you will have to have the letters in a different field and then combine them using an expression. But that should wait since you have some serious structural issues with your database that need to be fixed first.

Your database essentially looks like the spreadsheets that you mentioned rather than a relational database which is why you are having problems. Relational databases like Access are not glorified spreadsheets, and they take a different mind set in analyzing and structuring the data compared to spreadsheets.

Any relational database has to follow the rules of normalization (which spreadsheets violate). This site gives an overview of those rules. This tutorials on this site might help as well.

Just as a start, I see that you have 3 tables that pertain to software. One of the rules of normalization is that like data should be in 1 table, so all software should be in 1 table. You can have a field that distinguishes the type (AV, OS, Office etc.) of software.

I also see that you have computer manufacturers. Software also has manufacturers. You also have a table of locations which appear to be schools. In a general sense, manufacturers and schools are all organizations, so you can put them in 1 table with a field to distinguish them by type

tblOrganizations
-pkOrgID primary key, autonumber
-txtOrgName
-fkOrgTypeID foreign key to tblOrganizationTypes

tblOrganizationTypes (2 records at present: manufacturer, school)
-pkOrgTypeID primary key, autonumber
-txtOrgTypeName

It looks like you are tracking computers and the software on them as well as who has possession of them. Since a computer can have many different software applications loaded, that describes a one-to-many relationship, so the software should be records in a related table. You also have another relationship in that a piece of software can be on many computers (another one-to-many relationship). When you have 2 one-to-many relationships between the same two entities (computers and software) you have a many-to-many relationship which requires a junction table.

Also in a general sense software and computers are just manufactured products, so technically speaking they can be held in the same table

tblProducts
-pkProductID primary key, autonumber
-txtModelNumber
-txtModelName
-fkOrgID foreign key to tblOrganizations (to capture the manufacturer) long number datatype
-fkProductTypeID foreign key to tblProductTypes (long number datatype

tblProductTypes (computer, software etc.)
-pkProductTypeID primary key, autonumber
-txtProductTypeName


Now to track the actual assets

tblAssets
-pkAssetID primary key, autonumber
-YourAssetID field
-yourcategoryfield (can be a foreign key field if need be)


Each asset will consist of multiple products, so a one-to-many relationship

tblAssetProducts
-pkAssetProdID primary key, autonumber
-fkAssetID foreign key to tblAssets long number datatype
-fkProductID foreign key to tblProducts
-txtSerialNumber


Also, if an asset can be assigned to multiple people over time that describes another one-to-many relationship which would be handled with a separate but related table. I'll let you work on that table structure


Just some other recommendations:
1. Do not use spaces or special characters in your table or field names
2. Do not use reserved words as table or field names. Here is a list.
3. I recommend using an autonumber primary key field in every table.

[RachelC]

Thank you for that analysis JZWP11.

I'll take a longer look at it and it seems I have some work to do! It helps!

It's probably only fair to point out that I had to delete so many relationships and tables in an attempt to bring the database down to a size I could upload, that you only had a very truncated version! (That was before I realised how much the size would shrink if I zipped it up! I could actually have probably sent a complete one!)

I'm afraid I took the shortcut of basing the the database on the multiple spreadsheets in which the data was originally recorded because I had a very short time-slot to produce something which worked - which it does, apart from the auto-number! At least I've rationalised the data in the process which gives a better starting block but it seems shortcuts are not always the best solution!