Using Nz and Dmax

[timmy]

Hi everyone

I recently converted my DB backend from MySQL to access (was having stability issues).

In MySQL, i had set my primary key to start at 2000 and auto-increment from there. Converting it back, obviously i now can't use auto-number.

The primary key is used as a reference for the call (its a call management DB). I know i'm not supposed to expose the user to a PK or use it for anything but internal referencing, but unfortunately i didn't stick to that.

Now obviously before, when i entered a new workorder, it would automatically go to the next number, now i need this to happen with the new access backend.

I have entered this as the control source on the form for workorderID:

=Nz(DMax([WorkorderID],[Workorders]),0)+1

Im getting a circular reference error when in design view and the always helpful #Name when in form view.

Any help would be appreciated .

Thanks a lot.

Edit: I just realised, i view workorders as well as create them, using the same form. Heres the code i use to determine if we are viewing or creating:

       If Forms![Workorders by Customer]![Workorders by Customer Subform].Form.RecordsetClone.RecordCount > 0 Then
           DoCmd.GoToControl "WorkorderID"
           DoCmd.FindRecord Forms![Workorders by Customer]![Workorders by Customer Subform].Form![WorkorderID]
       End If

Should i be adding the Nz(Dmax) functions into that code, rather than the control on the form?

[Bob Fitz]

Hi

This works for me when used as the Defult Value of the WorkorderID field.

 
=Nz(DMax("[WorkorderID]","[Workorders]"),0)+1

[timmy]

Doesn't work for me, i get a circular reference error. I have relationships set up correctly... Not sure whats going on here.

[Bob Fitz]

Hi
A couple of questions:

1 Are you sure you are putting the code in the "Default Value" property of the "WorkorderID" field?

2 Is this a multi user app with each user having there own front end?

[timmy]

1 Are you sure you are putting the code in the "Default Value" property of the "WorkorderID" field?

Sorry, i was being dumb and putting it in the control value. It works when put into the default value.

2 Is this a multi user app with each user having there own front end?

It's shared on a network drive, viewed by a few but data only entered by 1 person at any time.

So now i have a progressing workorderID that i wanted, however it doesnt work alongside my "check if its a new workorder, or if we are viewing an existing workorder" code. I have tried this(it doesnt work):

If Forms![Workorders by Customer]![Workorders by Customer Subform].Form.RecordsetClone.RecordCount > 0 Then
    DoCmd.GoToControl "WorkorderID"
    DoCmd.FindRecord Forms![Workorders by Customer]![Workorders by Customer Subform].Form![WorkorderID]
Me.WorkorderID=Nz(DMax([WorkorderID],[Workorders]),0)+1
End If

[Bob Fitz]

Hi

It's shared on a network drive, viewed by a few but data only entered by 1 person at any time.

I'm not sure if this means it is or isn't split. If it is not, it should be, to avoid coruption of the app.

If data is ever added by more that one at the sane time, you will need to use a different method to create the ID number. Maybe as the record is saved instead of when it is first being created, otherwise both users will be trying to create a new record with the same ID.

I'm not sure what you want to do in the code that you posted but perhaps it would be better to check if it is a new record in the form's on current property.

 
If Me.NewRecord = True Then
   What you want to do goes here
End If