Incremental ID # increase default value

  • Thread starter Thread starter BrettS
  • Start date Start date
B

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.
 
Hello Brett,

Try this line of code:

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

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

JString said:
Hello Brett,

Try this line of code:

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

Does it work?

BrettS said:
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.
Click to expand...
Click to expand...
 
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.

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

JString said:
Hello Brett,

Try this line of code:

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

Does it work?

BrettS said:
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.
Click to expand...
Click to expand...
Click to expand...
 
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))

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

JString said:
Hello Brett,

Try this line of code:

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

Does it work?

BrettS said:
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.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top