DMAX on [Criteria] Field

[CJ_London]

so what is the problem now? Selecting NCR puts 004 in NCRNumber which is what you wanted?



I see the code has been moved to the form beforeupdate event and doesn't cater for if the user does not select an NCR type or selects CDR

Also, you are not using the primary keys which is the intention for fast retrieval of data

[raychow22]

But when i select CDR, it does not dmx the CDR increments. It's just dmax whatever is on NCRnumber from tblNCR. I think ill just have to keep CDR and NCR tables and forms separate if i cant get this to work

[CJ_London]

because of your code

at the moment it says

Me.ncrnumber = Nz(DMax("[NCRNumber]", "tblNCR", "[NType]='NCR'"), 0) + 1

it should say

Me.ncrnumber = Nz(DMax("[NCRNumber]", "tblNCR", "[NType]='" & NType & "'"), 0) + 1

[raychow22]

because of your code

at the moment it says

Me.ncrnumber = Nz(DMax("[NCRNumber]", "tblNCR", "[NType]='NCR'"), 0) + 1

it should say

Me.ncrnumber = Nz(DMax("[NCRNumber]", "tblNCR", "[NType]='" & NType & "'"), 0) + 1

Ajax! You are the best! It worked. Thanks!!!

[raychow22]

Ajax,

Should I Put anything else in the afterupdate code to ensure that the Dmax function will always work for CDR or NCR? Right now I have it as

Private Sub NType_AfterUpdate()


Me.NCRNumber = Nz(DMax("[NCRNumber]", "tblNCR", "[NType]='" & NType & "'"), 0) + 1

End Sub

[CJ_London]

no it will work for either. But you may need some code if the user was to select say NCR, then clears the control (i.e. make it null again)

Also, you need to cater for the situation where more than one user is entering data - if two users enter an NCR at the same time, both will have the same number. Usually you would not assign a value until the form beforeupdate event (which is where you had the code in the db you posted) to minimise the time between assigning a value and updating the table.

[raychow22]

no it will work for either. But you may need some code if the user was to select say NCR, then clears the control (i.e. make it null again)

Also, you need to cater for the situation where more than one user is entering data - if two users enter an NCR at the same time, both will have the same number. Usually you would not assign a value until the form beforeupdate event (which is where you had the code in the db you posted) to minimise the time between assigning a value and updating the table.

Yes! Ajax,

I want to make sure that users do not duplicate the numberings if 2 or more were on at the same time. Is there anyway that this can happen? I split my database to front/back ends and I tested the front ends at the same time to see how the data will transfer to the backend. It seems that access sometimes makes a copy of the backend database if data is not successfully transferred over. Any suggestions would be nice. Thanks!

[raychow22]

Just a quick update on what I did... here's my code below. This prevents the DMAX working if it's not a new record which helps a little bit, but it will create duplicates... is there a way to error-proof this code?

Private Sub NType_AfterUpdate()


If Me.NewRecord = True Then
Me.NCRNumber = Nz(DMax("[NCRNumber]", "tblNCR", "[NType]='" & NType & "'"), 0) + 1
End If

End Sub

[CJ_London]

as I explained - put the code into the form beforeupdate event rather than the control - but means the user won't know the number until the record is saved. That still leaves a very small change of two users saving at exactly the same time.

To overcome that, create a multi field index of ntype and number and set allow duplicates to no

so if the first user saves ok, the second user trying to save with the same number will generate an error. you can trap the error and save again having recalculated a new number. This is usually on a repeat loop in case there is more than two users involved

[raychow22]

as I explained - put the code into the form beforeupdate event rather than the control - but means the user won't know the number until the record is saved. That still leaves a very small change of two users saving at exactly the same time.

To overcome that, create a multi field index of ntype and number and set allow duplicates to no

so if the first user saves ok, the second user trying to save with the same number will generate an error. you can trap the error and save again having recalculated a new number. This is usually on a repeat loop in case there is more than two users involved

Ajax, I'm trying to create a multi-field index for ntype and number. Do I want to ignore the nulls set to "No" or "Yes".

Also, is there a way to better ensure that data from the front end database gets to the backend database without Access having to create "duplicate" dbs because it could not successfully transfer the data into it.

[raychow22]

Ajax,

I just want to make sure I did this correct. Here's a picture.

[CJ_London]

you shouldn't have any nulls so ignore = no

don't understand what you mean by 'front end database'

[raychow22]

Do I need to create another field for the Indexes? "NTypeNumber" and then set "Duplicates to No?"

[raychow22]

you shouldn't have any nulls so ignore = no

don't understand what you mean by 'front end database'

Front end database as in user interface side which include queries , forms and reports. Front end cannot modify tables. This is when I split the database to backend and frontend. Or do you think it's best to have one shared database where users will open the same file?

[CJ_London]

Do I need to create another field for the Indexes? "NTypeNumber" and then set "Duplicates to No?"

No. It should look something like

do you think it's best to have one shared database where users will open the same file?

Absolutely not. Backend contains all tables, nothing else. And is stored in a mutually convenient folder for all users on the network. Front end contains everything else. Each user has their own copy of the front end linked to the backend. They do not share it otherwise there is risk of corruption.