How to ask Access to find last serial number for a certain category?

[Keshia]

I have a database that is for keeping track of inventory. I need to have my form for entering new inventory to automatically make a new serial number based on the category of the new item.

The serial numbers are currnently like this:
AA######

"AA" = Category code
"######" = the next number

I currently have a category combobox and an item combobox and the serial number txt field. The options in the item combobox change based on the category selected. i currently have the serial number generating fine but the problem is that it generates the next number over all not for each specific category. for example, if there are three items; BO000001, CA000001 and CA000002, and i want to ad another item under the BO category, currently the serial number that pops up will be BO000003 because it is taking the number from "CA000002". i want it to only look at the BO's and make the serial BO000002.

Not sure if i explained this very well but here is the code i have now:

Option Compare Database
 
Private Sub cboCategory_AfterUpdate()
Me.cboItem = Null
Me.cboItem.Requery
Me.cboItem = Me.cboItem.ItemData(0)
Me.txtSerialNo = Me.cboItem.Column(2) & Format(Val(Right(DLast("[Serial Number]", "Inventory"), 6)) + 1, "000000")
End Sub
 
Private Sub Form_Current()
Me.cboItem.Requery
Me.txtSerialNo.Requery
End Sub
 
Private Sub Form_Load()
Me.cboCategory = Me.cboCategory.ItemData(0)
Call cboCategory_AfterUpdate
End Sub
 
 

Any and all help is appreciated!

[TheSmileyCoder]

First note that the first line of help for DLast(AC2003) says:

You can use the DFirst and DLast functions to return a random record from a particular field in a table or query when you simply need any value from that field.

making it quite unsuitable for your needs.

Me.txtSerialNo = Me.cboItem.Column(2) & Format(Val(Right(dmax("(tx_Serial)","Inventory","left(tx_Serial,2)='AA'"),6)) + 1, "000000")

Assuming you have your category code somewhere in your form, lets presume cmb_Category you could write:

Me.txtSerialNo = Me.cboItem.Column(2) & Format(Val(Right(dmax("(tx_Serial)","Inventory","left(tx_Serial,2)='" & me.cmb_Category & "'"),6)) + 1, "000000")