Sequential Numbering (1 Viewer)

[ClaraBarton]

When entering procedures or ingredients in a recipe, I have a default index that I can change manually after entering if I need to reorder the items.
This is the field's default value:

=IIf(IsNull([RecipeIDFK]),Null,DCount("RecipeIDFK","qryIngredients","RecipeIDFK = " & [RecipeIDFK])+1)

This works fine except that I get two 1's. So: 1 1 2 3 4 5 6. Such a simple thing. But not for me.

 

[KitaYama]

Why not DMax?

=IIf(IsNull([RecipeIDFK]),Null,DMax("RecipeIDFK","qryIngredients","RecipeIDFK = " & [RecipeIDFK])+1)

If I understand the situation correctly, I wouldn't even use IIF.
I would use Nz & Dmax+1 in default value of the textbox. In this case, even the first item would give me a 1.

 

[ClaraBarton]

that gives me nothing. I believe I'd need to use the index field then... Still doesn't work

 

[KitaYama]

Can you test this?

= Nz(DMax("RecipeIDFK", "qryIngredients", "RecipeIDFK=" & Nz([RecipeIDFK])), 0) + 1

 

[ClaraBarton]

Error. Wouldn't the IDFK be the same and not maximum?

 

[The_Doc_Man]

I think you are DMax'ing the wrong field. I'm making up names as I go, but it SEEMS that you have, or should have, a recipe number that is the same for all ingredients that are part of the same recipe, yes? And you have a separate ingredient number that is the thing you want to increment? Andin the ingredients table or query, the recipe ID is a foreign key, but if you looked up a recipe in the recipe table, there it is not an FK. So that is how I numbered or named things here.

=NZ( DMax( "[IngredientNumber]", "Ingredients", "RecipeIDFK = " & [RecipeID] ), 0 ) +1

 

[arnelgp]

add an Autonumber (ID) on your table and use it your Count:

=
 IIf(IsNull([RecipeIDFK]),Null,
DCount("1", "qryIngredients", "RecipeIDFK=" & RecipeIDFK & " AND [ID]<= " & Nz(ID, 0)) - 
    (DCount("1", "qryIngredients", "ID = " & ID) = 0))

or use a Custom function:

Public Function fnRecipeCount(ByVal FK As Variant, ID As Variant) As Long
    Dim i As Long
    fnRecipeCount = 1
    If IsNull(FK) Then
        Exit Function
    End If
    'Debug.Print FK, ID
    i = DCount("1", "qryIngredients", "RecipeIDFK=" & FK & " AND [ID]<= " & Nz(ID, 0)) - _
    (DCount("1", "qryIngredients", "ID = " & ID) = 0)
    fnRecipeCount = i
End Function

see Query1 and try to add some records.

 

[Pat Hartman]

If you want to use sequence numbers that allow you to rearrange the order of fields,you might want to take a look at how the subform in this example works. The main form shows a method to generate a unique user friendly key field. The subform shows how to assign a sequence number to each row of a list and then move items around. You will find other examples that actually use a graphical "move" process. This graphic option is fine for short lists but users find it confusing for long lists where you can't see the whole list at once. They have a tendency to select an item but then drop it in the wrong place and "lose" it because they don't know where they dropped it.

Custom Sequence Numbers

Two examples of a custom sequence number. One is a complex ID with concatenated parts. The other is just a sequence number for detail items and is not used as a PK. It has a renumber feature. See below

www.access-programmers.co.uk

 

[ClaraBarton]

I've used both arnelgp and pat hartman's code and I very much appreciate them but I guess I'd just like to know why mine doesn't work. Why I get 2 beginning numbers. I first though it was because the foreign key (RecipeIDFK) wasn't present when the record is first created but that doesn't make sense.

 

[Pat Hartman]

You'd need to post the database if you want someone to test your code.

 

[ClaraBarton]

This isn't actually code. It's the default value for a field. On the subform level.

 

[Mike Krailo]

What is the default value supposed to be and what exactly does it show as the default value. We have no way of seeing what you see if you don't show us.

 

[ClaraBarton]

The field is a number type. It's just to sort things out, like items on an invoice. I want to be able to change it if I want to change the order but I want it to default to the next number when I'm entering data. The RecipeIDFK is the foreign key for the parent form. There's a button above to reorder if the sort numer is changed. The values are as in my first post.

 

[Mike Krailo]

OK, I re-read the first post again and assume that the very first ingredient defaults to a one. Then the second ingredient added also defaults to a one, then it finally starts to count up? Is that right?

 

[Mike Krailo]

See if this is what you wanted to do. I used ArnelGP's demo as the basis for it all. All I did was add a Recipe table and some forms to test it out. I ended up using a recalc in the after update of the subform to get the sequence column working properly.

 

[ClaraBarton]

Thank you for your help, Mike. I get it. I have it working from Arnelpg and I very much appreciate it. But I still do not understand why my first formula won't work on the second item.

 

[Mike Krailo]

I'm using Arnel's exact code as you are showing in the first post. So not sure what you don't get about it. The reason why it did not work out of the box for me after building the forms was while you are editing the first record, the default value is calculated as one but that's while the record hasn't saved yet. After the record is saved, the query that the default value has not refreshed yet. I used me.recalc instead of me.refresh in the after update of the subform so it would properly update.

In your database, as an experiment, after entering in one single record and moving down to a new record, you should see the default value still showing as a ONE. Now just click the refresh button on the ribbon and you should see that value update to a TWO. So that query has to be updated to show the right value.

 

[ClaraBarton]

I am also using your code. It works fine. What you don't get is this:

=IIf(IsNull([RecipeIDFK]),Null,DCount("RecipeIDFK","qryIngredients","RecipeIDFK = " & [RecipeIDFK])+1)

This is the default code in the form. Not in the VBA editor. Not in the table. On the form behind the field. And it works... EXCEPT it puts the numbers 1 1 2 3 4; 1 1 2 3 4. I want to know why it works this way. Why are the first two numbers the same. I'm not going to use it. I'm using your code. I just want to know why?!!

 

[Mike Krailo]

I thought I already explained it. I understand the expression shown is in the form. It's in the form in the demo as well. I asked you to do an experiment to see if the Refresh button in the ribbon would update that second default value from a 1 to 2. If it does, you should understand that the function Arnel made in the subforms recordsource has not updated until after the default expression has rendered its value on the form. The default value expression is dependent on the record source being updated FIRST. So doing the me.recalc in the after update event addresses that problem.

I don't know how to make it any clearer.

 

[ClaraBarton]

Thank you.