Dmax help with a variable

[merlin666]

I am trying to run an update from a form to auto-increment a number.

Private Sub CatItemNum_Click()

If Me.ItemCat = "3" Then
Me.CatNumber = DMax("CatNumber", "CatItemNum") + 1
ElseIf Me.ItemCat = "4" Then
Me.CatNumber = DMax("CatNumber", "CatItemNum") + 1
Else
Me.CatNumber = Null
End If

The form has a combo box for the ItemCat and I'd like to have the dmax use that value to populate the "Catnumber" field that is used to lookup the
increment number from the CatItemNum table.

CatItemNum - Table used to store the autonum
| 3 | 4 | 5 |
|100|200|300|
So if ItemCat =3 the 100 is used if it's 4 then 200 is used.

Is this possible.

Also after how would I increment the stored value in the CatItemNum table.

Thanks in advance,

End Sub

 

[jzwp22]

I am not really clear on what you are doing, but it sounds like you are assigning incremented catalog numbers to items based on the category in which they fall. Please correct me if I am wrong & please provide some additional detail of your application and the appropriate table structures for the tables involved.

Regarding the following statement, why would you need to store the incremented value when you are already storing it (me.catnum). In fact, the only time you might need the 100|200|300| values is when there is no item in a particular category.

...also after how would I increment the stored value in the CatItemNum table.

Also, what happens if the total number of items in a category exceed 100? You will start to overlap catnumbers.

Out of curiosity regarding the following, are you saying you have 1 record in the CatItemNum table or do you have 3 records?

CatItemNum - Table used to store the autonum
| 3 | 4 | 5 |
|100|200|300|

I would recommend the following table structure for this table

tblCategory
-pkCatID primary key, autonumber
-CategoryName
-catstartnumber

Then in your item table (I assume that this is where you are storing the incremented catnumber)

tblItems
-pkItemID primary key, autonumber
-catnumber
-fkCatID foreign key to tblCategory

Then in your form, have the combo box bring in the catstartnumber in addition to the other fields of tblCategory.

Then in your code


IF DCount("*","tblItems", "fkCatID=" & me.comboboxname )>0 THEN
me.catnumber= DMax("catnumber", "tblItems", "fkCatID=" & me.comboboxname)+1
ELSE
me.catnumber= me.comboboxname.column(x) 'column x refers to catstartnum
END IF