Autogenerate incremented number based on number of records and add a prefix (1 Viewer)

heathxp · Jun 27, 2019

I'd like to improve the data entry for a form so that fewer mistakes are made when entering this number.

I have two tables. One is Projects the other is ProjectActivites
One Project can have multiple activities.

I have a form that shows a Project and in that form I have a subform that shows all the Project Activities.

Users can add new activities or view activities in that subform.
The number for the Project Activities I use is ProjectNr-ActivityNumber
i.e: 123-01

How can I automatically generate and increment this number ( 123-01, 123-02) based on ProjectNr and the number of existing Activities for this Project while maintaining the actual value in the database (Column ActivityNumber from the ProjectActivities table)

I tried something like this:

=DCount("[ActivityNumber]","ProjectActivities")& "-" & + 1

But even if this would work, the data won't be stored.

theDBguy · Jun 27, 2019

Hi. Welcome to the forum. There are many examples on how to do this. When I get back to my computer, I’ll send you a link.

Sent from phone...

heathxp · Jun 27, 2019

theDBguy said:
Hi. Welcome to the forum. There are many examples on how to do this. When I get back to my computer, I’ll send you a link.

Sent from phone...

Hi, Thanks.
Were you able to find links? I searched but the examples are either way to complex or doing something that isn't what I'm trying to do (i.e incrementing already an existing ID which is not the issue in my case). For me the ProjectNr is already known and only the activityNr needs to increase from -01, to -02, etc.

Gasman · Jun 27, 2019

Have a look at the Simiiar Thread links at the bottom of this thread

Mark_ · Jun 27, 2019

Something to note, if you want to link child records (Project activities) to a parent (Project) the normal way to do this is to have a copy of the Project ID in your Project Activities record.

This way, if for some reason someone decides to renumber projects you will still have the link. The ID would be an autonumber field that is never shown to users and is only used to keep these kinds of links intact.

theDBguy · Jun 27, 2019

heathxp said:
Hi, Thanks.
Were you able to find links? I searched but the examples are either way to complex or doing something that isn't what I'm trying to do (i.e incrementing already an existing ID which is not the issue in my case). For me the ProjectNr is already known and only the activityNr needs to increase from -01, to -02, etc.

Take a look here.

heathxp · Jun 27, 2019

Mark_ said:
Something to note, if you want to link child records (Project activities) to a parent (Project) the normal way to do this is to have a copy of the Project ID in your Project Activities record.

This way, if for some reason someone decides to renumber projects you will still have the link. The ID would be an autonumber field that is never shown to users and is only used to keep these kinds of links intact.

Mark, I do have a copy of the Project ID in the ProjectActivities. It's set as a foreign key. I don't understand how would that be an autonumber. It's an autonumber in the Project table, it can't be an autonumber in both.
The real example I have is way more complex than what I presented here. I just tried to keep it simple and focus on the issue.

heathxp · Jun 27, 2019

theDBguy said:
Take a look here[/url].

Whelp, here we go. Thanks for linking the access manual but I do know how to google and looked through the results but my access skills are rusty so I can't really find out exactly what I need if I don't look at a specific example.

Mark_ · Jun 27, 2019

If you are just trying to find how many child records are attached to the parent so you can add one to it, you would be looking for DCount function. To get the "Next" number it would be ActivityNumber =

Code:

DCount([ParentID], ChildTable, "ParentID = " & WhatHoldsParentID)+1

To display this how your asking, you could concatenate ProjectNumber & "-" & ActivityNumber

P.S. I never posted that you should have a separate autoinc in the child to hold the foreign key from the parent. More that you should be using an autoinc in the parent and keep a COPY in the child, as it looks like you've done. Reason I included that is I've seen people try to use user editable fields for referential integrity then wonder why things get disconnected.

theDBguy · Jun 27, 2019

heathxp said:
Whelp, here we go. Thanks for linking the access manual but I do know how to google and looked through the results but my access skills are rusty so I can't really find out exactly what I need if I don't look at a specific example.

Hi. I'm sorry but I'm not catching your drift. The link I posted is pointing to a list of specific examples you can try out. Was it sending you somewhere else than the Code Archive at UtterAccesss? Just curious...

heathxp · Jun 27, 2019

theDBguy said:
Hi. I'm sorry but I'm not catching your drift. The link I posted is pointing to a list of specific examples you can try out. Was it sending you somewhere else than the Code Archive at UtterAccesss? Just curious...

Ok. I clrealy notice that you all think I didn't put that much effort and what I'm trying to do sounds easy to you so I'll go ahead and show you my thought process while I go through the 10 examples you linked.

1. Custom AutoNumbers
Do I really need to use a library with 500 lines of code to perform what I need?
2. DMax Plus one for use with differing categories.
Too old. Cannot open this in access 2013. Even if I manage to convert it, it looks like this is also way to complicated than what I try to acheive.
3. Fake Autonumber
Ok. This might be interesting. It shows how to increment a number using previous numbers already in the databse for that record. Exactly what I need except I'm not sure I can sue it in my example because I need this to also let users enter their own value.

Code:

Public Function getNextOpenKey(ByVal rs As Recordset, ByVal cf As String) As Long
Dim pv           As Long
Dim cv           As Long
Dim cfld         As Field
nbsp;   pv = 0
    For Each cfld In rs.Fields
        If cfld.Name = cf Then Exit For
    Next
    rs.Sort = cf
    rs.MoveFirst
    Do Until rs.EOF
        cv = cfld.Value
        If cv <> pv + 1 Then
            getNextOpenKey = pv + 1
            Exit Function
        End If
        pv = cv
        rs.MoveNext
    Loop
    getNextOpenKey = pv + 1
End Function

4. Function to replace Autonumber (kkeydel)
This defines a lower and an upper limit for examples where a number might be reused. Again, this is way too complex because all I need is to count the number of present rows for this record and cocatenate it with project number.
5. Generating sequential numbers for INSERT/UPDATE statements
Nope. Don't need insert statements for this because the actual record is inserted later.
6. How to Create an Incrementing Record Number
This one looks like it can do what I need. Currently trying it out.

theDBguy · Jun 27, 2019

heathxp said:
Ok. I clrealy notice that you all think I didn't put that much effort and what I'm trying to do sounds easy to you so I'll go ahead and show you my thought process while I go through the 10 examples you linked.
...
6. How to Create an Incrementing Record Number
This one looks like it can do what I need. Currently trying it out.

Hi. As I was saying earlier, I doubt you will find one example to do exactly what you want. The purpose of giving you some examples to look at is to maybe try to see how it could be done, so you can then apply the same or similar techniques to work with your particular situation.

Another option is for you to post a copy of your db, so someone here could try to modify it for you.

Mark_ · Jun 27, 2019

heathxp said:
I'm not sure I can sue it in my example because I need this to also let users enter their own value.

So to be clear, your end users can add in Project-1, ProjectActivity-2 (giving you a 1-2) MANUALLY and they expect you to then get the next value after OR they want you to look for "Missing" values?

These require different approaches to solve.

heathxp · Jun 27, 2019

theDBguy said:
Hi. As I was saying earlier, I doubt you will find one example to do exactly what you want. The purpose of giving you some examples to look at is to maybe try to see how it could be done, so you can then apply the same or similar techniques to work with your particular situation.

Another option is for you to post a copy of your db, so someone here could try to modify it for you.

Thanks, I appreciate it.

I realize now that vba help requires exact parameters so an example database is helpful.

Can someone point me into the right direction here :

So I'm trying to implement this using the helpful resource that I can't link because of insufficient points.
"How to Create an Incrementing Record Number (dashiellx2000)".
In the example, there's a counter table method that does mostly what I need.
Except it doesn't work in my case.

Issue 1: How can I pull the Order Number? in the autonumber? So instead of just incrementing the # 1, 2, 3, 4, 5, 6 I needthe prefix to be the Order Number which can be pulled from the database or displayed from a value in the form that already displays it.

Original code.

Code:

Dim varCriteria As Variant


   On Error GoTo Form_Current_Error

varCriteria = Null 'Clear any previously established criteria
varCriteria = "[OrderID]=" & Nz(Me.Parent.txtOrderID, 0)

  If Me.NewRecord = True Then 'Only Create the New Order Number if the record is a new record
    On Error Resume Next 'Should not happen, but always good to have some error handling
    'Find the last Detail Line Number for the current order and add one
    Me.txtDetailLineNumber.DefaultValue = Nz(DMax("DetailLineNumber", "tblOrderDetailsTableCounter", varCriteria), 0) + 1
  End If

Form_Current_Exit:
   Exit Sub

Form_Current_Error:
    MsgBox "Error: " & Err.Number & " - " & Err.Description, vbCritical, "Error!"
    Resume Form_Current_Exit

Above code that I try to modify to fix the issue:

Code:

Dim varCriteria As Variant
Dim resultcustomProjNr As Variant
Dim rst As DAO.Recordset
Dim db As DAO.Database

 
Set db = CurrentDb

Set rst = db.OpenRecordset("tblOrdersTableCounter")

   On Error GoTo Form_Current_Error

varCriteria = Null 'Clear any previously established criteria
varCriteria = "[OrderID]=" & Nz(Me.Parent.txtOrderID, 0)

  If Me.NewRecord = True Then 'Only Create the New Order Number if the record is a new record
    On Error Resume Next 'Should not happen, but always good to have some error handling
    'Find the last Detail Line Number for the current order and add one
     resultcustomProjNr = Nz(DMax("DetailLineNumber", "tblOrderDetailsTableCounter", varCriteria), 0) + 1

   Me.txtDetailLineNumber.DefaultValue =  rst!OrderNumber & "-" &resultcustomProjNr


  End If

Form_Current_Exit:
   Exit Sub

Form_Current_Error:
    MsgBox "Error: " & Err.Number & " - " & Err.Description, vbCritical, "Error!"
    Resume Form_Current_Exit

theDBguy · Jun 27, 2019

heathxp said:
Thanks, I appreciate it.

I realize now that vba help requires exact parameters so an example database is helpful.

Can someone point me into the right direction here :

So I'm trying to implement this using the helpful resource that I can't link because of insufficient points.
"How to Create an Incrementing Record Number (dashiellx2000)".
In the example, there's a counter table method that does mostly what I need.
Except it doesn't work in my case.

Issue 1: How can I pull the Order Number? in the autonumber? So instead of just incrementing the # 1, 2, 3, 4, 5, 6 I needthe prefix to be the Order Number which can be pulled from the database or displayed from a value in the form that already displays it.

Hi. I think it's a little hard to say what you need to change without knowing much about your database structure. As soon as you are able, it would be quicker to see a sample copy of your db with test data, so we can show you how to get it to work with your specific database. If you can't wait until you have enough posts, you can email it to me, and I'll try to post it for you.

Mark_ · Jun 27, 2019

OK, I think I see what is causing you such an issues.

Code:

 Me.txtDetailLineNumber.DefaultValue =  rst!OrderNumber & "-" &resultcustomProjNr

In your child record you are saving the OrderNumber, ad "-", and the line number all in ONE text field. This works perfectly for display purposes, but you would want to save the LineNumber in its own numeric field. If you did this, DMax or DCount (as needed) would be able to return the next value in sequence.

Please note, you CAN make a function that will return the next in sequence if you are using a text field like this, but the amount of trouble is far greater than simply remembering to display the two related fields.

Autogenerate incremented number based on number of records and add a prefix (1 Viewer)

heathxp

Registered User.

theDBguy

I’m here to help

heathxp

Registered User.

Gasman

Enthusiastic Amateur

Mark_

Longboard on the internet

theDBguy

I’m here to help

heathxp

Registered User.

heathxp

Registered User.

Mark_

Longboard on the internet

theDBguy

I’m here to help

heathxp

Registered User.

theDBguy

I’m here to help

Mark_

Longboard on the internet

heathxp

Registered User.

theDBguy

I’m here to help

Mark_

Longboard on the internet

Similar threads

Users who are viewing this thread