Logistics System

[Kapellu2]

Hey guys,

I was wondering if you could help me out with a problem regarding a part locating system.

We have three different types of parts and would like to group them by type. We would also like to give each part a number that could be used to locate them.

Ideally we would like our numbering system to look like the following:

Type 1: 1-10,000
Type 2: 10,001-20,000
Type 3: 20,001-30,000

My question is, how do I get Access to assign each part a number automatically while inputing the data? I would like the numbering scheme for Type 1 to go from 1 to 10,000 in order without any repeats. Same for Type 2 and Type 3.

I figured I would have to use if, then statements since there are three different types but was wondering if anyone could point me in the right direction as to what the code should look like for the number scheme.

Thanks

 

[JamesMcS]

You could use the dmax function. I'm assuming you're setting the type of part first during data entry? If that's the case I'd have the three types in a combo box, and in its afterupdate event you could put something like [part number text box].value=dmax("Part number", "Parts")+1 (check the syntax out but you see what I'm getting at)

 

[gemma-the-husky]

yes you can do that. I would actually still have a autonumber key as well, as the PK.

At some point you will probably decide to change a number system of this sort, eg - you may run out of product codes in 1 section.

also, you may end up having 2 part keys (ie the part type AND the product code) - and these are harder to manage than a single autonumber key.

 

[Kapellu2]

Thank you for the replies guys.

I am not too familiar with the dmax command. I was thinking that I could just add a hidden list box to the product input page to store the part number.

I'm a little stuck on the code though. I'm guessing it would be something like the following:

If Product = [Type 1] Then

[Logistics Number] = (0-10,000)

ElseIf Product = [Type 2] Then

[Logistics Number] = (10,001-20,000)

ElseIf Product = [Type 3] Then

[Logistics Number] = (20,001-30,000)

End If

The part I was stuck on is what to put in the locations that say (min value - max value). I would like for the parts to be numbered in order from low to high, and for there to be no repeats. Is this where I would impliment the dmax function?

 

[gemma-the-husky]

no - you can input the value manually

or you search the table to find the highest value (ie use dmax), and add 1 to it for the next value.

you do have to understand stuff like this. you won't get a lot of success if you can't organise your data appropriately.

 

[Kapellu2]

I was a little bit confused by the dmax functionality.

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

So "part number text box" is just the text box that feeds information into the table.

What do the "Part Number", and "Parts" variables represent?

Also, would I be able to use this method since there are three different number ranges depending on product type?

 

[Rabbie]

I was a little bit confused by the dmax functionality.

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

So "part number text box" is just the text box that feeds information into the table.

What do the "Part Number", and "Parts" variables represent?

Also, would I be able to use this method since there are three different number ranges depending on product type?

If you get your DMAX value from a query that only selects the required product type the method should work for you.

 

[Kapellu2]

Good idea.

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

So the ("part Number","Parts")+1 represents (Query Name, Column Field Name)?

how do i set up a dmax function exactly?