DMax Set-Up

[Kapellu2]

Hey guys,

I am trying to set up a logistics system to store products.

Currently there are three different product types. I would like one type to be numbered 0-10,000, the second 10,001 to 20,000, and the third 20,001 to 30,000.

You guys pointed me in the right direction and told me I had to use a dmax command to do this. I tried to research it a little bit but im stuck.

Right now I have a field in my main table called "logistics number". From what I understand I will have to make a table. So would I make a table that autonumbers 1-10,000, 10,001-20,000, and 20,001-30,000 and then tell the main query to pull from the appropriate table using if then statements?

I was also trying to understand the actual dmax statement I would have to use:

[part number text box].value=dmax("Part number", "Parts")+1

I was a little bit confused on the ("Part Number," "Parts") section of the code. Would this be ("Field Name" , "Query Name")?

Sorry for the long post. Thanks for any help in advance.

 

[jzwp22]

The autonumber data type is generally reserved for the primary key field of the table. In general, the primary key field will not have any significance to the user, so since your logistics number has significance to you, I would not make it the primary key field of the table and thus, I would not make it the autonumber field. Additionally, it would not work correctly since you might have gaps in your sequence when you first start adding products. By the way, how will you guarantee that you will never have more than 10000 products in any one category?

Also, it is generally not recommended to have spaces and special characters in your table and field names, otherwise you must enclose each in square brackets.

You will need a data entry form and when a new record is added to the products (before insert event of the form if I remember correctly) is when you need to increment your logistics number value using the DMAX() function. You will also need to identify the type in the function in order to increment the value of the correct type since you have 3 types.

In terms of the DMax() structure; the general structure goes like this: DMax("fieldtogetmaxvalue", "table or query", "criteria")

DMax("[logistics number]", "[products table]", "[type]=" & someformcontrolname ) + 1

You will have to supply the type via some control on the form. A combo box might be the best way.

 

[Kapellu2]

I would not make it the autonumber field. Additionally, it would not work correctly since you might have gaps in your sequence when you first start adding products.

So would I just make three tabes and make one contain just the number 10000, one 20001, and one 30001?

By the way, how will you guarantee that you will never have more than 10000 products in any one category?

We only release about 10-20 products a year so we should be ok from a capacity standpoint.


You will need a data entry form and when a new record is added to the products (before insert event of the form if I remember correctly) is when you need to increment your logistics number value using the DMAX() function. You will also need to identify the type in the function in order to increment the value of the correct type since you have 3 types.

In terms of the DMax() structure; the general structure goes like this: DMax("fieldtogetmaxvalue", "table or query", "criteria")

DMax("[logistics number]", "[products table]", "[type]=" & someformcontrolname ) + 1

So I understand all of this context except the "[Type]=" that you have listed. What is the signifigance of that?


I appreciate the help, sorry for all of the questions.

 

[jzwp22]

You would not have multiple tables for products. Similar data should be in 1 table. The type field would distinguish the three different product types you mentioned in your initial post. A table structure might look like this:

tblProducts
-pkProductID primary key autonumber
-txtProductName (text field)
-fkProductTypeID foreign key to tblProductTypes
-LogisticsNumber (long number field)

tblProductTypes (your 3 types go in this table as records)
-pkProductTypeID primary key, autonumber
-txtProductType (text field)

With this structure, the DMax() function would look like this:

DMax("LogisticsNumber", "tblProducts", "fkProductTypeID=" & someformcontrolname ) + 1