• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 201
  • Last Modified:

How Can I Add an Automatic Seq # to the Value of Another Text Field?

How Can I Add an Automatic Seq # to the Value of Another Text Field, and Then Set That as The Value of a Different Field?

For example, one of the fileds may be KO-11-01, I would then want to be able to populate a field as KO-11-01-01, the next record as KO-11-01-02, etc...
0
Rex85
Asked:
Rex85
  • 4
  • 4
  • 2
2 Solutions
 
GRayLCommented:
To do this you need a primary key for the table.  Assuming the key is named pk, the table as myTable and the fld as fldText

SELECT a.fldText,  a.fldText & "-" & Format((SELECT Count(b.pk) FROM myTable b WHERE b.pk>=a.pk AND b.fldText = a.fldText),"00") AS NoSeq FROM myTable a ORDER BY a.fldText, a.pk

0
 
Rex85Author Commented:
I'm sorry. I wasn't clear. I am doing this in a form, not a query. It would be some VBA that is triggered I believe,
0
 
Boyd (HiTechCoach) Trimmell, Microsoft Access MVPCommented:
I recommend storing the seq number in a separate field.

You would calculate the KO-11-01-01 as needed.

To get the next  number you can use the DMax()+1 to the value.

I like to use the Form's On Current event for this:

Example code in a sub form:
Private Sub Form_Current()
                   
  If Me.NewRecord Then
     Dim lngSortOrder As Long

     lngSortOrder = Nz(DMax("[SortOrder]", "[MyTableNme]", "ForeignKeyFile=" & Me.Parent.[PrimaryKeyControl]), 0)

     Me.SortOrder = lngSortOrder + 1
  
  End If

' save the record
If Me.Dirty Then Me.Dirty = False

End Sub

Open in new window


This is from an actual working application. I have change the object names to hopefully make it easier to understand..
0
Cloud Class® Course: SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

 
GRayLCommented:
Whether in code or in a query you still need a primary key in the table.
0
 
GRayLCommented:
By doing it in a form do you want to 'save 'save' the number in the table or just 'see' it on each new iteration?
0
 
Rex85Author Commented:
I would definitely want to Save it in the table.
0
 
Boyd (HiTechCoach) Trimmell, Microsoft Access MVPCommented:
I As previously stated, I would store the sequence number is a separate field.
0
 
GRayLCommented:
Rex05:  Have you been able to try my solution?  I'm ready to help if you have a problem.
0
 
Rex85Author Commented:
I am working with all of your comments now. thank you.
0
 
Rex85Author Commented:
Sorry it took me so long.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 4
  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now