Adding item numbers with an append query (1 Viewer)

[Brian Martin]

I have the following table in my database (shown with data that I want to be able to put in it using my query):

*List ID Part No Item

CU-1001 IV-R-1 1
CU-1001 HG-G-3 2
TU-1003 VN-N-2 1
TU-1003 FH-I-2 2
SH-1002 FH-I-2 1

I have a form with an edit box for List ID and part no. I have a button on the form that when clicked I want to activate a query which then does something along the following lines:
I want it to append to the Parts List table the list ID, Part No and the item no. How do I append the Item number? How do I make sure the query knows the the last item number for a particular list ID and then appends the next item number with the next part number? eg the next Part that is added to List ID CU-1001 should be 3. I'm pretty clueless about how to do this! Can anyone help.

 

[simongallop]

To get the last item number for a List id use the DMax statement

DMax("[Item]","TableName","[Lidst ID] = '" & Forms![FormName]![TxtBox with ListID data] & "'")

Place this in a textbox on the form and refresh the box when you have selected a listID

Otherwise you can use the formula in code and write directly to the table rather than using a query. To do this, in the property of the button select Event OnClick and select code builder:

In this example presume your button is called btnSaveData, the table is called MyTable, the control on the form with the ListID is called txtList and the control with Part No is called txtPart. Your code ought to look something like:

Private Sub btnSaveData_Click()

Dim MyRS as Recordset
Dim ItemNum as Integer
ItemNum = DMax("[Item]","MyTable","[List ID] = '" & Me.txtList & "'")

Set MyRS = CurrentDB.OpenRecordset("MyTable",dbOpenDynaset)
MyRS.AddNew
MyRS("List ID") = Me.txtList
MyRS("Part No") = Me.txtPart
MyRS("Item") = ItemNum + 1
MyRS.Update
MyRS.Close
End Sub

HTH