Incremental ID # increase default value

[BrettS]

Hi,

I am trying to get some data entry forms to do an automatic sequence of ID
#'s as users input records, based on the ID #'s already in the table. I
found some basic code through a search here to do basically that:

Private Sub Form_BeforeUpdate(Cancel As Integer)
If Me.NewRecord Then
If RecordsetClone.RecordCount = 0 Then
Me.[Sample #] = 1
Else
Me.[Sample #] = DMax("[Sample #]", "Samp Data") + 1
End If
End If
End Sub

However I need to add something extra. I have these Samples in this table
filtered by [RE Job #], so instead of simply picking the DMax value for all
[Sample #] in the "Samp Data" table, I want it to pick the DMax for just the
[Sample #]s whose [RE Job #] value match the value in this field on a
seperate form (which will always be open), [Forms]![TaskData]![RE Job #].

Does anyone have any idea how to incorporate this filter into the code?
Thanks for any help.

 

[JString]

Hello Brett,

Try this line of code:

DMax("[Sample #]", "Samp Data", "[RE Job #] = " & [Forms]![TaskData]![RE Job
#])

Does it work?

 

[BrettS]

I am getting a data type mismatch. The [Sample #] field is a numerical field
and [RE Job #] is a text field.

Hello Brett,

Try this line of code:

DMax("[Sample #]", "Samp Data", "[RE Job #] = " & [Forms]![TaskData]![RE Job
#])

Does it work?

Hi,

I am trying to get some data entry forms to do an automatic sequence of ID
#'s as users input records, based on the ID #'s already in the table. I
found some basic code through a search here to do basically that:

Private Sub Form_BeforeUpdate(Cancel As Integer)
If Me.NewRecord Then
If RecordsetClone.RecordCount = 0 Then
Me.[Sample #] = 1
Else
Me.[Sample #] = DMax("[Sample #]", "Samp Data") + 1
End If
End If
End Sub

However I need to add something extra. I have these Samples in this table
filtered by [RE Job #], so instead of simply picking the DMax value for all
[Sample #] in the "Samp Data" table, I want it to pick the DMax for just the
[Sample #]s whose [RE Job #] value match the value in this field on a
seperate form (which will always be open), [Forms]![TaskData]![RE Job #].

Does anyone have any idea how to incorporate this filter into the code?
Thanks for any help.

 

[JString]

That shouldn't matter. Are the datatypes of the [RE Job #] fields different
in each table? If so, that's the problem. I would make sure the data types
are the same, but if for some reason you want to keep them as they are you
can temporarily convert the data type of [Forms]![TaskData]![RE Job #] using
additional code so that the function will run.

For example, CLng([Forms]![TaskData]![RE Job #]) will convert to a long
integer, but I can't know specifically which one to use without additional
info.

I am getting a data type mismatch. The [Sample #] field is a numerical field
and [RE Job #] is a text field.

Hello Brett,

Try this line of code:

DMax("[Sample #]", "Samp Data", "[RE Job #] = " & [Forms]![TaskData]![RE Job
#])

Does it work?

Hi,

I am trying to get some data entry forms to do an automatic sequence of ID
#'s as users input records, based on the ID #'s already in the table. I
found some basic code through a search here to do basically that:

Private Sub Form_BeforeUpdate(Cancel As Integer)
If Me.NewRecord Then
If RecordsetClone.RecordCount = 0 Then
Me.[Sample #] = 1
Else
Me.[Sample #] = DMax("[Sample #]", "Samp Data") + 1
End If
End If
End Sub

However I need to add something extra. I have these Samples in this table
filtered by [RE Job #], so instead of simply picking the DMax value for all
[Sample #] in the "Samp Data" table, I want it to pick the DMax for just the
[Sample #]s whose [RE Job #] value match the value in this field on a
seperate form (which will always be open), [Forms]![TaskData]![RE Job #].

Does anyone have any idea how to incorporate this filter into the code?
Thanks for any help.

 

[JString]

Are both forms bound to the same table? If so, this line of code may work:

DMax("[Sample #]", "Samp Data", "[RE Job #] = " & chr(34) &
[Forms]![TaskData]![RE Job
#] & chr(34))

I am getting a data type mismatch. The [Sample #] field is a numerical field
and [RE Job #] is a text field.

Hello Brett,

Try this line of code:

DMax("[Sample #]", "Samp Data", "[RE Job #] = " & [Forms]![TaskData]![RE Job
#])

Does it work?

Hi,

I am trying to get some data entry forms to do an automatic sequence of ID
#'s as users input records, based on the ID #'s already in the table. I
found some basic code through a search here to do basically that:

Private Sub Form_BeforeUpdate(Cancel As Integer)
If Me.NewRecord Then
If RecordsetClone.RecordCount = 0 Then
Me.[Sample #] = 1
Else
Me.[Sample #] = DMax("[Sample #]", "Samp Data") + 1
End If
End If
End Sub

However I need to add something extra. I have these Samples in this table
filtered by [RE Job #], so instead of simply picking the DMax value for all
[Sample #] in the "Samp Data" table, I want it to pick the DMax for just the
[Sample #]s whose [RE Job #] value match the value in this field on a
seperate form (which will always be open), [Forms]![TaskData]![RE Job #].

Does anyone have any idea how to incorporate this filter into the code?
Thanks for any help.