DMax Value +1

[kidzmom3]

This is revisiting existing postings. I have a form to add new records and I need one field to increment by 1 based on the employee ID chosen in a dialog box. For each employee ID there can be unlimited Items, but each employee's items must start at 1 and increment from there. I set a LostFocus event on the field I want incremented as follows:

If Me.NewRecord Then
On Error Resume Next
Me.Item = Nz(DMax("[Item]", "tblThings", "[EmplID] = #" _
& Forms!frmAddThings!Employee & "#"), 0) + 1

The problem is it does nothing. It doesn't error and it doesn't populate anything in the [Item] field. When I debug the VB it goes from Me.NewRecord to the On Error Line and right back to the Me.NewRecord.

Thanks for any help on debugging this!!!

 

[smercer]

I have tried doing this with my own database using count and that is a problem because every time I leave a certain field (when editing) the number would change and then I would have a duplicate Primary key. so now I am just geting by with a autonumber field that my code gets a random number from and creates one from that.

May be it may work with a diferent event

If you find a solution please let me know. thanks

 

[smercer]

after looking at your code I have fixed my own problem.

how I did this:
1) created a text box with a

=Count([ISBN_Number])

in the subform footer

2) In VBA:

Private Sub Cost_Price_BeforeUpdate(Cancel As Integer)
If Me.NewRecord Then
Book_ID = Forms!frm_Book_Entry_form!sfrm_Record_selected_FormView!For_Book_Id & "-" & Forms!frm_Book_Entry_form!sfrm_Record_selected_FormView!Qunitity.Value + 1

ISBN_Number = Forms!frm_Book_Entry_form!sfrm_Record_selected_FormView!ISBN_Number
End If
End Sub

this only has spaces where they are needed. the forum will put in spaces in words that have a certain length
Hope this helps

 

[pbaldy]

Surrounding the form reference with "#" would only be appropriate for a date value. Presuming EmplID is a numeric value, try:

Me.Item = Nz(DMax("[Item]", "tblThings", "[EmplID] = " _
& Forms!frmAddThings!Employee), 0) + 1

 

[kidzmom3]

Thanks PBALDY the # signs were the problem!!! It always iseems to be one errant character that causes problems.