DMAX on [Criteria] Field

[raychow22]

I am using a DMAX function on VBA code from a Form:



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

[NCRNumber] is in the tblNCR

The problem is I'm trying to creating a criteria of where the function only looks up the "Type field" that contains words "NCR" from tblNCR. Please see picture below.

[ssanfu]

Not sure what the question is, but it seems that you have a form that you want to add 1 to the NCRNumber if the "TYPE" field equals "NCR".

Maybe this will help:

Private Sub Form_BeforeUpdate(Cancel As Integer)
    If [Type] = "NCR" And IsNull(Me.ncrnumber) Then
        Me.ncrnumber = Nz(DMax("[NCRNumber]", "tblNCR", "[Type]='NCR'"), 0) + 1
    End If
End Sub

NOTE: "Type" is a reserved word (it is a property) and shouldn't be used as an object name.

[raychow22]

ssanfu,

I want to search the crtieria in tblNCR for Type="NCR" and only add +1 of the max number of my NCRNumber for Type="NCR".

So there's two Types of Forms I want to sequentially and incrementally +1 to depending on which Type of Form i select (CDR or NCR). Let's say the last Number on the Table for "NCR" was 6. Then If i select Type = "NCR" on my form, the NCR number should be 7 on the form.

[raychow22]

My form looks like this

[ssanfu]

OK, how about adding the code to the "NType" combo box afterupdate event

    If [Type] = "NCR" And IsNull(Me.ncrnumber) Then
        Me.ncrnumber = Nz(DMax("[NCRNumber]", "tblNCR", "[Type]='NCR'"), 0) + 1
    End If

OR

Private Sub cboNType_Afterupdate()
    If IsNull(Me.ncrnumber) Then
        Select Case cboNType
            Case "NCR"
                Me.ncrnumber = Nz(DMax("[NCRNumber]", "tblNCR", "[Type]='NCR'"), 0) + 1
            Case "CDR"
                Me.ncrnumber = Nz(DMax("[NCRNumber]", "tblNCR", "[Type]='CDR'"), 0) + 1
        End Select
    End If
End Sub

[raychow22]

ssanfu,

Still doesn't work. There's no errors; however the number is not changing. Somehow the Criteria Part isn't working. DMAX works without the crtieria condition.

Any other suggestions would be helpful. Here are some pictures.

[ssanfu]

Can/would you post your dB?


Maybe you have a default value of 0 for NCRNumber. How about

Private Sub cboNType_Afterupdate()    
    If NZ((Me.ncrnumber),0)= 0 Then
        Select Case cboNType
            Case "NCR"
                Me.ncrnumber = Nz(DMax("[NCRNumber]", "tblNCR", "[Type]='NCR'"), 0) + 1
            Case "CDR"
                Me.ncrnumber = Nz(DMax("[NCRNumber]", "tblNCR", "[Type]='CDR'"), 0) + 1
        End Select
    End If
End Sub

[raychow22]

Can/would you post your dB?


Maybe you have a default value of 0 for NCRNumber. How about

Private Sub cboNType_Afterupdate()    
    If NZ((Me.ncrnumber),0)= 0 Then
        Select Case cboNType
            Case "NCR"
                Me.ncrnumber = Nz(DMax("[NCRNumber]", "tblNCR", "[Type]='NCR'"), 0) + 1
            Case "CDR"
                Me.ncrnumber = Nz(DMax("[NCRNumber]", "tblNCR", "[Type]='CDR'"), 0) + 1
        End Select
    End If
End Sub

Good morning ssanfu,

I can send you my db via email? Is that okay?

[CJ_London]

just an observation. Your NCRNumber appears to be text (preceding zeros). If this is the case then you can't add+1 to it, you need to convert it to a number first, then change it back to text

Me.ncrnumber = format(val(Nz(DMax("[NCRNumber]", "tblNCR", "[Type]='NCR'"), 0)) + 1,"000")

or perhaps

Me.ncrnumber = format(Nz(DMax("val([NCRNumber])", "tblNCR", "[Type]='NCR'"), 0) + 1,"000")

[CJ_London]

one other observation - is the NType field a lookup field to another table? If so, then it may be it does not have a value of 'NCR' but is a FK to the other table

[raychow22]

Thanks Ajax for the quick response.

NCRNumber is actually a Number formatted "000". NType does have a row source query to another table for either "NCR" or "CDR".

[CJ_London]

as you can see from your post, NType is a number - 'NCR' is not a number.

You need to use

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

or whatever number 'NCR' is in the other table.

It is best not to use lookups in table design, otherwise you end up with this sort of problem

[raychow22]

as you can see from your post, NType is a number - 'NCR' is not a number.

You need to use

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

or whatever number 'NCR' is in the other table.

It is best not to use lookups in table design, otherwise you end up with this sort of problem

Thanks Ajax,

It seems to work somewhat; however, it doesn't increment the number in which I select either CDR or NCR. It just increments to the next number. Here's a picture of tbl in which the row source is from for NType.



Is it better to now use the row source query? But if I don't use it... how will the user determine to use a "NCR" or a "CDR" form... I'm basically trying to include both types into one form but with separate sequentially numbering for both.

[raychow22]

Nevermind. It's not working.. I forgot I still had my code active that didn't include the criteria part.

[CJ_London]

how will the user determine to use a "NCR" or a "CDR" form

tables do not dictate how forms work - your form would still use a combo box

you probably need to change this code

Private Sub cboNType_Afterupdate()    
    If NZ((Me.ncrnumber),0)= 0 Then
        Select Case cboNType
            Case "NCR"
                Me.ncrnumber = Nz(DMax("[NCRNumber]", "tblNCR", "[Type]='NCR'"), 0) + 1
            Case "CDR"
                Me.ncrnumber = Nz(DMax("[NCRNumber]", "tblNCR", "[Type]='CDR'"), 0) + 1
        End Select
    End If
End Sub

to just

Private Sub cboNType_Afterupdate()   

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

End Sub