Auto-Updating Table

[Kapellu2]

Hey everyone, got yet another question for you.

I'm trying to set up a logistics system which assigns each product a locating number.

There are three types of parts, one ranging from 0-10,000, one from 10,001-20,000, and lastly one ranging from 20,001-30,000.

I plan on having my system set up like the following:

One main table with the value "Logistics Number"

Three sub tables
-One starting at 1 and ranging up to 10,000 (Named "Range1")
-One starting at 10,001 and ranging to 20,000 (Named "Range2")
-One starting at 20,001 and ranging to 30,000 (Named "Range3")

So my code looks like the following

If Product = ProductType1
[Logistics Number] = DMax("[Range1]", "[Logistics Number]") + 1
End If

If Product = ProductType3
[Logistics Number] = DMax("[Range2]", "[Logistics Number]") + 1
End If

If Product = ProductType3
[Logistics Number] = DMax("[Range3]", "[Logistics Number]") + 1
End If

Everything works fine in regards to the DMax function, but since my three range tables arnt updating with the logistics number the logistics number always remains the same. ProductType1 is always 1, ProductType2 is always 10,001, and ProductType3 is always 20,001.

Is there a way to make my "logistics number" field in the main table feed into the appropriate range table?


I tried using the following code but it didnt seem to work

If Product = ProductType1
[Logistics Number] = DMax("[Range1]", "[Logistics Number]") + 1
Table.[Range1].[Logistics Number] = [Logistics Number]
End If

 

[PNGBill]

Shouldn't spaces in object names.
What do the three tables do apart from assign numbers? Couldn't this be three records in one table?

What is your code trying to do? assign the next available number to the Product ?
Careful using Product, may be reserved (list too long to check) ProductName, ProductID is safer.
Caption property can hold your preferred name to view, if req'd.