Auto populate with next available number

[ccondran08]

Hi, I have just tried to work through the example below but it appears that the 'After Update' action isn't working. I have attached the file also. Any suggestions ??


http://answers.microsoft.com/en-us/...b/ee4aeb93-7c7b-4e58-a4a4-73ad4d61b5c2?auth=1

 

[JHB]

It is a little too vague questions, so where, when and with what doesn't it work?

 

[ccondran08]

Sorry about that, I was in a bit of a rush. If you look at the Asset table the AssetID field is supposed to auto populate with the next available number from the Categories table, incrementing from the NextID field. I have followed everything step by step from the instructions (Before Change, After Insert, After Update actions) but it is not incrementing as you can see from the Assets table, AssetID field with the last 3 records being the same.

 

[JHB]

.. I have followed everything step by step from the instructions (Before Change, After Insert, After Update actions) but it is not incrementing as you can see from the Assets table, AssetID field with the last 3 records being the same.

And where/when?

 

[ccondran08]

Thanks for your patience JHB, If you open up the Assets table and add in new item, then choose an item from the combo box in the "Category" field then click to the next record you will see that the AssetID field will concatenate the second column of the "Category" field with the "NextID" field from the "Categories" table.

What is supposed to happen is that the AssetID field in the Asset table should increment each time a record is created based on the second column of the combo box in the "Category" field.

 

[ccondran08]

These are the macros that I have used which is straight from the article. The 'Before Change' works fine, it is the 'After Insert/After Update' that is the problem by not incrementing the '+1' ;

Before Change
If Updated("Category") Then
Look Up A Record In Categories
Where Condition = [ID]=[Assets].[Category]
SetField
Name = Assets.AssetID
Value = [Categories].[Prefix] & Right("0000" & [Categories].[NextID],4)
End If

After Insert/After Update
If Updated("Category") Then
Look Up A Record In Categories
Where Condition = [ID]=[Assets].[Category]
EditRecord
SetField
Name = Categories.NextID
value = [Categories].[NextID]+1
End EditRecord
End If

 

[ccondran08]

After some persistence, I have finally solved this in case anyone is interested. My first problem was the at the Categories.NextID field was set to Text instead of number so I had to change this to Number so that the number could increment. Then I played around with the first line of the Before Change and After Update/Insert macros [If Updated("Category")=True]and came up with the following (file attached);

Before Change
If Updated(“Category”)=True Then
Look Up A Record In Categories
Where Condition [ID]=[Assets].[Category]
Alias
SetField
NameAssets.AssetID
Value= [Categories].[Prefix] & Right(“0000” & [Categories].[NextID],4)
End IF

After Update
If Updated(“Category”)=True Then
Look Up A Record In Categories
Where Condition [ID]=[Assets].[Category]
Alias
EditRecord
Alias
SetField
NameCategories.NextID
Value= [Categories].[NextID]+1)
End EditRecord
End IF