Combining a sequential number on form

[jc100]

Hello Friends.
Please Help! I have been trying to solve how to combine several text fields and 3 combo fields on a form to store in one control field on a table.
My 3 Combo boxes are: Plat Class Equip
Text Boxes are: ID_Year ID_Month ID_Asgn ID_Asgn_Rng ID_Asgn_Increment

Right now ID Year is formatted as YYYY and ID Month as MM
ID_Asgn is being generated by Exp Builder with =Nz(DMax("Key","["EquipCtrlInv_tbl]"),0)+1
I have used Macro to Set Value for Visual ID where I also concatenated all these fields as one. "Plat" & "Class" & "ID_Year" & "ID_Month" & "-" "ID_Asgn" & "-" & "ID_Asgn_Rng" & "Equip"

I would prefer to use VBA cause there is more that I want it to do that the Macro is not going to be able to do. My VBA skills might as well be new cause it has been too many years since I have used VBA.
I would like the ID_Asgn to be a sequential number. Starting with 1 and then resetting each Month back to 1
I would like the user to be able to put an Increment in the ID_Asgn_Increment i.e 50 and that be added to ID_Asgn and displayed in ID_Asgn_Rng ...haven't figured this one out. The user needs to be able to sometimes assign a range of sequence numbers and come back later to update the rest of the data in the table.
Lastly the sequential number needs to include (concatenate) and stored in table under Visual ID as "Plat" & "Class" & "ID_Year" & "ID_Month" & "-" "ID_Asgn" & "-" & "ID_Asgn_Rng" & "Equip"

While I am thinking about it, is there an easy way to come back later to update that data in the table. Can Access insert the sequential numbers only with the rest of the columns blanks?

Thank you so very much for your help on this.

I hope you all are having a great day!

 

[Uncle Gizmo]

I think there are too many questions in one question if you know what I mean.

I'm not sure, but it sounds like you could do with rethinking what you are doing from the beginning.

You might be better off giving us an overview of what you are trying to do.

You might get back a more efficient way of doing it.

Sent from my SM-G925F using Tapatalk

 

[jc100]

Thanks for the quick reply Uncle Gizmo. Im sorry for sounding like I was asking too many questions. Wanted to make sure I had enough information and maybe went a little far. I am wanting to create a Visual ID and it needs to be a combined with other data. If that makes sense. It will be made up of the Plat, Class, Year, Month, ID Asgn, ID Asgn Rng, Equip. The ID Asgn needs to be a sequential number and needs to reset to 1 every month. There is one other thing, the increment field, if the user inputs say 50 that needs to add to the ID Asgn and display in the ID Asgn Rng but also increase the ID Asgn field too.
The customer needs the Plat, Class, Year, Month, ID Asgn, ID Asgn Rng and Equip combined in the Visual ID to store the number or numbers issued in the table ie: Visual ID RS-201608-1-10E
Did I explain it better this time?

 

[jc100]

I have included the effort I have made with VBA so far. Keep in mind I said this is like new to me again. I did not use format on Year and Month in VBA cause I have formatted using exp builder on ID Year as Format(Date(),"YYYY") and on ID Month as Format(Date(),"MM").

Private Sub Form_AfterUpdate()
Dim IDYear As Date
Dim IDMonth As Date
Dim ID_Asgn As Integer
Dim ID_Asgn_Increment As Integer
StrID_Asgn As String
StrID_Asgn_Increment As String
If Me.NewRecord Then
Me.ID_Asgn = Nz(DMax("[Key]", "EquipCtrlInv_tbl", "Year[IDYear] = " & Year(Me.IDYear) & " AND Month([IDMonth])= " & Month(Me.IDMonth)), 0) + 1
If StrID_Asgn_Increment <> "" Then
StrID_Asgn = Sum(Me.ID_Asgn) + (Me.ID_Asgn_Increment)
Set Value = Forms!EqCtInv_Fm!Visual_ID
Me.Visual_ID = "Plat" & "Class" & "-" & "IDYear" & "IDMonth" & "-" & "ID_Asgn" & "-" & "ID_Asgn_Rng" & "Equip"
End Sub
Private Sub cmdClose_Click()
On Error GoTo Err_cmdClose_Click
DoCmd.Close

Exit_cmdClose_Click:
Exit Sub
Err_cmdClose_Click:
MsgBox Err.Description
Resume Exit_cmdClose_Click
End Sub

 

[MarkK]

... how to combine several text fields and 3 combo fields on a form to store in one control field on a table.

My 2c is don't save this Frankennumber in a table. Combine your data in a query, maybe, like . . .

SELECT *, Field1 & Field2 & "-" & Field3 As FrankensteinCode
FROM MyTable

... but saving this number in a table is waste of time, since in order to create this number you are simply using data that is already saved somewhere else, and that breaks normalization.
Hope this helps,

 

[jdraw]

Can you step back and tell readers -- in plain simple English -- what these things are and how they fit together in "your business issue/opportunity"?

Access provides a date/time datatype.
A good practice with database is 1 fact 1 field.

 

[jc100]

At MarkK and jdraw, My customer is wanting to make a unique Visual ID number for issuing and tracking equipment. They want the number to consist of the first 2 combo boxes which are the platforms and classes, then year and month, ID Asgn, ID range (if applicable) and E (for Equipment). They want that entire number which has to be concatenated (Visual ID number) to be stored in the table. Usually when you concatenate in Access will not store in table. Unless you set value. They are issuing these Visual IDs out for the equipment and sometimes they want to issue out several Visual IDs at one time. That is why I have the Increment text field. They also want the ID Asgn to reset every month to 1. They will be labeling the equipment with the Visual ID numbers.

 

[jdraw]

You are not the first person to have a Customer tell you what they want-- when in fact they are telling you how to set up the identifier. Having a visual identifier for users is one thing; concocting an identifier from existing fields and storing it is another.
As Markk called it -- Frankennumber -- may come back to cause issues.

Tables should be normalized. Fields should be atomic.

Good luck with your project.

 

[MarkK]

Separate out your problem of creating the sequential numbers. That is a different problem from storing your Frankennumber. Have a distinct and separate field for each sequential number, and then it is very easy to calculate, just add one to the Max() existing number, unless the reset period has elapsed. Code might be . . .

Function GetNextSequential(d1 as Date) As Long
   If ResetPeriodHasElapsed(d1) Then
      GetNextSequential = 1
   Else
      GetNextSequential = DMax("Ordinal", "MyTable", "Month = " & Month(d1) & " AND Year = " & Year(d1)) + 1
   End If
End Function

So that sequential result should be stored in its own field. Then use that field as one member of the frankennumber you construct at retrieval time, in a query. But I would never store the franken-code itself, just store your data where it belongs, and create frankennumbers on the fly, in a query, at retrieval time. IMO

 

[jc100]

Thank you jdraw. We always want to try to make our customers happy. This is just a issuing and tracking of equipment database. So 1 form and 1 table. Oh and they want to print labels. That will be all. I know the number is large but would like to store it so it will be recorded in table and printed on a label.

 

[jc100]

Separate out your problem of creating the sequential numbers. That is a different problem from storing your Frankennumber. Have a distinct and separate field for each sequential number, and then it is very easy to calculate, just add one to the Max() existing number, unless the reset period has elapsed. Code might be . . .

Function GetNextSequential(d1 as Date) As Long
   If ResetPeriodHasElapsed(d1) Then
      GetNextSequential = 1
   Else
      GetNextSequential = DMax("Ordinal", "MyTable", "Month = " & Month(d1) & " AND Year = " & Year(d1)) + 1
   End If
End Function

So that sequential result should be stored in its own field. Then use that field as one member of the frankennumber you construct at retrieval time, in a query. But I would never store the franken-code itself, just store your data where it belongs, and create frankennumbers on the fly, in a query, at retrieval time. IMO

Hi Markk,
I know this might be a stupid question, but what would the "Ordinal" be? And how do I get the sequential number to increment if they want to issue 50 at one time? Can I add the Sequential number to the Increment number and will they have to be Dim as strings?
Are you saying that the frankennumbers when done in the query will not store in the table as to be able to see on a report and the equipment that was issued with that number? Sorry for all the questions just a little confused. Thank you so much for your help.

 

[MarkK]

'Ordinal' a just a name of a field that you might use to store a sequential number, so it is just an example of an idea. Replace that name with the actual name of your field, or use 'Ordinal' if you want.

Each unique row in a database table should have a unique ID field which is the primary key. The eaisest way to implement this in Access is to use an Autonumber field, which is a Long Integer, and which you also define as the primary key. Simple. Done.

If your Customer wants to create a frankennumber to help humans deal with that row, and if that frankennumber consumes data already stored in that row, then the simplest way to satisfy your customer is to calculate that frankennumber on demand, and simplest way to do that is to write it into a query, and use that query instead of the table whever that type of data needs to be used.

So say you have a table tJob, and your customer wants a frankenjobnumber from the JobID, the Month, the Year the first four letter of the customer name, fine. Then I would write a query called qJob with SQL like . . .

SELECT j.JobID & Month(j.StartDate) & Year(j.StartDate) & Ucase(Left(c.Customer, 4)) As FrankenJobNumber, 
j.*
FROM tJob As j INNER JOIN tCustomer As c ON t.CustomerID = c.CustomerID

... in which we calculate, but never store, the FrankenJobNumber. Then, rather than use tJob for jobs, I would use qJob, which automatically calculates--in every row--the FrankenJobNumber from data available in the database.

In this way you can deliver a reliable FrankenNumber very easily without ever storing it, and without ever having a FrankenNumber be in conflict with that data it represents, because it is always written directly from that data.

Here's SQL from an actual system I've deployed in which the customer wanted a FrankenNumber called DrawingNumber . . .

SELECT IIf([IsLayout],"L","B") & Format([Date Recieved],"yymm") & Format([Index],"-0000-") & [TechID] AS DrawingNumber, 
Jobs_TBL.*
FROM Jobs_TBL;

There are Layout jobs and Beam jobs as determined by the field IsLayout, so "L" or "B". There is a date. There is a sequential index, or 'ordinal,' and the ID of the assigned Tech. So anywhere job data is required, I use this query instead of the table. Simple. Done. System wide. Never fails. Doesn't require any construction as storage time, only calculates at retrieval time.

Hope this helps,

 

[jc100]

Hi MarkK, I really appreciate your help. I thought if I uploaded my zipped database that would hopefully give you an idea of what I am doing. If you look at the form in the header section is where the user completes the fields (unbound) which will make up the Visual ID when they click on Save record. All the other text fields and combo fields in the Detail section are bound. That Visual ID number should become a part of that equipment. I want to be able to write this in VBA cause I want them to be able to use the Increment text field and it add to the ID_Asgn. So I know part of what you have given me will work. I just don't know how to get the Increment field to come together. Still a little confused. Do I just need to redo entire form?

 

[MarkK]

That's not what I'm suggesting. I'm suggesting that you not store the number, that you calculate it from data in the row. Using a query.

 

[jc100]

If you get a chance will you please look at my form on the previous post attachment. I do not have all txt fields that make up franken ID as part of the table such as Plat, Class, Year, Month, and Equip. There are only 2 fields ID_Asgn and ID_Asgn_Rng that are fields on the table. When you click on Save Record the Visual ID is generated and becomes part of the table for that piece of equipment. Which is very important. That Visual ID is now the tracking number for that piece of equipment. So, is there no way to do this with VBA?