Auto Increment Value problem

[Joseppi]

I know that multiple threads discuss similar situations but I am currently at a loss to find the correct solution.

I am trying to update a table and related subform (tblEvidenceItems) that tracks Computer Security incidents which includes the tracking of evidence items.

What I need to do is automatically create an incremental number for each piece of evidence based on the IncidentID.

For example:

If IncidentID 07-0067 has 6 pieces of evidence, I would have to show Item 001, Item 002, ... Item 006, etc.

If the next IncidentID has 12 pieces of evidence, I need to show Item 001, Item 002, ... Item 012.

The fieldname for the Evidence Items in tblEvidenceItems is ItemDetailNo.

Can anyone provide me with some assistance??

[GolferGuy]

Here is a sample of how I have done this in the past:

  NextSONumber = CSng(Int(Nz(DMax("SortOrder", "tblPlanElevOption", "ProjectID=" & ProjectID))) + 1)

In this code I retrieved the current Maximum "SortOrder" number for the ProjectID I'm currently working with. I use the Nz() function to change a Null (a null is returned if there are as of yet no records for this ProjectID) into a 0. Then that 0, or the number returned, I make sure is an integer with the Int() function, and then I change that Integer into a Single (CSng() function). After all that, I then add a 1 so I have then next number.

HTH,

[Joseppi]

Not to sound like a complete newbie BUT where do I put this code do I have to do anything else like declare variables etc.?

[GolferGuy]

I used the BeforeInsert event to create a number for the SortOrder field.

Is this a number that the user really needs to use and see? In my case it is. They can use this number to rearrange the list as they so desire. But in your case, is this number just being used to give you a unique key value? If so, I would suggest using AutoNumber.

By the way, there is nothing wrong with being a Newbie! We all where, and sometimes still are.

[crusader121]

I know that multiple threads discuss similar situations but I am currently at a loss to find the correct solution.

I am trying to update a table and related subform (tblEvidenceItems) that tracks Computer Security incidents which includes the tracking of evidence items.

What I need to do is automatically create an incremental number for each piece of evidence based on the IncidentID.

For example:

If IncidentID 07-0067 has 6 pieces of evidence, I would have to show Item 001, Item 002, ... Item 006, etc.

If the next IncidentID has 12 pieces of evidence, I need to show Item 001, Item 002, ... Item 012.

The fieldname for the Evidence Items in tblEvidenceItems is ItemDetailNo.

Can anyone provide me with some assistance??

Hi!

I am just wondering how you have the relationship for the tables set up, and what fields you are using.

[crusader121]

I know that multiple threads discuss similar situations but I am currently at a loss to find the correct solution.

I am trying to update a table and related subform (tblEvidenceItems) that tracks Computer Security incidents which includes the tracking of evidence items.

What I need to do is automatically create an incremental number for each piece of evidence based on the IncidentID.

For example:

If IncidentID 07-0067 has 6 pieces of evidence, I would have to show Item 001, Item 002, ... Item 006, etc.

If the next IncidentID has 12 pieces of evidence, I need to show Item 001, Item 002, ... Item 012.

The fieldname for the Evidence Items in tblEvidenceItems is ItemDetailNo.

Can anyone provide me with some assistance??

Hi Joseppi

First off, who have you linked the IncidentID, and the ItemDetailNo together.

[Joseppi]

The IncidentID is currently not linked to the ItemDetailNo. The IncidentID in tblEvidenceItems is linked to the IncidentID in tblIncidents

[Joseppi]

Here is a sample of how I have done this in the past:

  NextSONumber = CSng(Int(Nz(DMax("SortOrder", "tblPlanElevOption", "ProjectID=" & ProjectID))) + 1)

In this code I retrieved the current Maximum "SortOrder" number for the ProjectID I'm currently working with. I use the Nz() function to change a Null (a null is returned if there are as of yet no records for this ProjectID) into a 0. Then that 0, or the number returned, I make sure is an integer with the Int() function, and then I change that Integer into a Single (CSng() function). After all that, I then add a 1 so I have then next number.

HTH,

And how would I code that for my database? Is the NextSONumber simply an alias for something? I figure I would replace "SortOrder" with ItemDetailNo, tblPlanElevOption would be replaced by tblEvidenceItems BUT I'm not sure what I would put in place of the ProjectID entry??

[GolferGuy]

ProjectID would be replaced by IncidentID.

NextSONumber in my code is just that, it is the Next Sort Order Number. You are after the Next Item Detail Number as I understand your request. Therefore, wherever you need to put your Next Item Detail Number, this is the variable that will have that number for any new Item Detail records.

[Joseppi]

Code is generating a run-error (3075). Syntax error (missing operator) in query expression 'IncidentID='.

[GolferGuy]

Please show the entire SQL string if this is a query, or all the VBA code that builds the SQL string if this is done in VBA code.

[Joseppi]

Below is the code I used on the BeforeInsert of the subform:

Option Compare Database

Private Sub Form_BeforeInsert(Cancel As Integer)
NextItemDetailNo = CSng(Int(Nz(DMax("ItemDetailNo", "tblEvidenceItems", "IncidentID=" & IncidentID))) + 1)
End Sub

[GolferGuy]

The error you are receiving is because IncidentID does not have a value in it yet. So, you need to find out why IncidentID has no value. Where is IncidentID defined? If it is not defined on the subform you are trying to use it in, why not? I would think IncidentID would be the field you would be using for the Child/Master link field for the subform. But, no matter what, you need to search for where IncidentID is, and get that data into your subform if that really is the field you need to use to calculate NextItemDetailNo.

[Joseppi]

I've corrected that oversight but the nextItemDetailNo value is still not populating the field. Am I supposed to do something else with the ItemDetailNo field??

[GolferGuy]

but the nextItemDetailNo value is still not populating the field

Somewhere, you need to set whatever field needs this newly set number to this new number. For example, if the field that is being used as the "autonumber" field is ItemDetailNo, then somewhere you need to set ItemDetailNo = NextItemDetailNo. I know of no other way to get this new number into the field where it needs to be.