Dear Gents,

I would like to ask your kind assistance in modifying the below code which i found in the internet:

Public Function GetAccountNumber(AccountDate As Date) As Integer
' this function determines the maximum sequence number depending on the date the account is created
' uses the DateSerial function to do that
Dim fybegin As Date, fyEnd As Date ' fybegin beginning of the carendar year....fyEnd...end of Carender year
fybegin = DateSerial(Year(AccountDate), 1, 1)
fyEnd = DateSerial(Year(AccountDate), 12, 31)
GetAccountNumber = Nz(DMax("Sequence", "tblTest", _
"AccountDate between " & Format(fybegin, "\#mm/dd/yyyy\#") _
& " and " & Format(fyEnd, "\#mm/dd/yyyy\#") & "" & " and PrefixID=" & Me.Prefix.Column(0)), 0) + 1
End Function

Private Sub Form_BeforeUpdate(Cancel As Integer)
Me.Sequence = GetAccountNumber(Me.DateMade)
End Sub
==

My intention is instead of using the date, i would like to use two fields from two different tables, then combined this two fields to get a new sequence number:

1st table= TP-LocatorCode_Tb
field=LocCode - this is drop down list

2nd table= TP-TagCodes_TB,
field=Code (combo 14) - this is drop down list

3rd table = TP-TagMech_Tb,
field=Tag Number - (from the two fields above the next sequence number should be save here)

The Form is frmTagSeq which i modified from the original tblTest.

please see the image and sample mdb(zipped file).

Appreciate your kind assistance. Many thanks.

Welcome to UtterAccess!

First, since the tag number will be concatenated from the locator code, the tag code and the sequence, it is essentially a calculated value. In general, calculated values are not stored in the table. You would typically just display the concatenated values when you need them in forms, queries and reports. Second, the Sequence field must be a number field if you intend to increment it. It cannot be a text field.

So your DMAX() function would just need to get the greatest sequence number given the locator and tag code and then you would increment it


me.sequence=1+DMax("sequence","[TP-TagMech_Tb]","[Locator Code]='" & me.locatorcomboboxname & "' AND code='" & me.combo14 & "'")

The above assumes that the locator code and code stored in TP-TagMech_Tb are foreign key fields and correspond to the bound fields of the two combo boxes.

Just looked at your database. The form you referenced is tied to tblTest not TP-TagMech_TB

By the way, it is best not to use spaces or special characters (-,#, ? etc.) in your table or field names. The underscore is OK.

many thanks for response.

I have renamed the tables as per your advised and points the form to TBTagMech_Tbl

May i ask a dumb question, where should i put the code?
Me.Sequence = 1 + DMax("sequence", "[TPTagMech_Tbl]", "[Locator Code]='" & Me.LocCode & "' AND code='" & Me.Combo14 & "'")

I tried diff things (see below) but it seems not correct:
- disabled Public Function GetAccountNumber, insert the code in Private Sub Form_BeforeUpdate, but the results nothing.
- input the code inside Function GetAccountNumber, still error.

Tried also this,
GetAccountNumber = Nz(DMax("tag number", "[TPTagMech_Tbl]", "[Locator Code]='" & Me.LocCode & "' AND code='" & Me.Combo14 & "'"), 0) + 1
I'm trying the code if it will get the next available number in combination of LocCode & Code in Table TPTagMech_Tbl under field Tag number, is the code correct? or maybe i'm putting it in the wrong place?

I'm sorry that i'm not really expert on making this type of VBA, just a novice.

Appreciate your kind assistance.

Typically you would put the code in the before insert event of the form. I am a little confused by your table structure. Could you explain how the prefix and test tables are related to the other 3 tables? Also, you did not designated a field in those 3 tables as your primary key. I noticed a couple of duplicate codes, so the code fields in the locator and tag tables cannot be used as primary keys. I went ahead and added primary key fields to the 3 tables and I also established relationships between the three. I had to change datatypes of the fields you had in the TagMech table to allow for the joins. I also removed the tag number field since that will be a calculated value (and should not be stored). Once I understand your relationships, then we can work on putting the code where it belongs. I've attached the database with the revised structure.

I'm glad that you have allotted youre valuable time in my inquiry. appreciated this a lot.

The prefix and test tables are just sample tables where in the original code was. This is just there for testing, nothing to do with the main table which is TPTagMech_Tbl. The primary keys and the links that you have done is great.

This is the scenario.

To get the next available Tag Number, the user choose and click the Locator Code (combo box which gets the list from TPLocatorCodes_Tbl) then the next, choose and click for the Code (combo box that gets the list from TPTagCodes_Tbl), maybe after lost focus, the "Tag Number" (see image) will give the next available sequence of Tag Number in combination of both LocCode+Code-0001. hopes make sense.

I have attached zipped file which i added the form that this will really be used.

Again, many thanks for the assistance.

Now you have another table tbl_MechTagID. What is the purpose of this table?

I'm planning to use that as a tracker or reference to issued Tag Numbers on particular date.
example: below Tag numbers are all issued at April-20-2012 with ref# Mech-001 (this is the field MechTag-Track from tbl_MechTagID).
SM85-CN-0001
SM85-CN-0002
SM88-CI-0001
SM88-CI-0002

But for the mean time you can disregard that.

Sorry for the confusion.

Isn't it that Tag Numbers are provided by the Vendors?
Try Smart Plant Management I am sure your aware of that.

By database rules, you would only have the information in 1 table. You can use a query to extract the particular records you want to see by using criteria in that query. I have deleted tbl_MechTagID.


In the attached database, I created 2 new forms to illustrate how the sequence number is generated and how the tag number is displayed (no need to store the tag number).

In form frmTPTagMech_Tbl_ComboBox, I used the after update event of the tag combo box for the code.

As a caution, if the database is going to be used by multiple users simultaneously, you will want to move the code from the after update event of the combo box to the before update event of the form. The reason for this, is that you want the sequence number to be generated as the record is committed. For example, let's say that you are using the frmTPTagMech_Tbl_ComboBox form, a user opens the form and fills out several fields and the sequence number is generated as 0001. Then the user gets interupted and leaves his computer for over an hour with the form still open. The sequence number and the tag number are shown but the sequence number has not yet been entered into the table. While user 1 is away from his computer, user 2 opens up the form and enters a new record for the same locator & tag as user 1 but completes the data entry. When user 1 returns and finishes his data entry, he ends up creating a record with the same sequence number (and hence same tag number). So you now have a duplicate.

To remedy this, you would use the frmTPTagMech_TBL in the attached database. This does not generate the sequence number until just before the record is committed to the table. The problem here is that the user will probably only see the new sequence/tag number briefly as the form migrates to the next blank record. To handle this, I added a message box that pops up and displays the newly created tag number.

Hi, i view the information and is exactly what i need. But i need the steps to configure the sequence number with the selected sufix in a table or query. Can you please help me with this.

You would just need an expression to display the constructed sequence number with the suffix. Take a look at the tag number control on the forms I provided. You do not need to store the tag number but rather just construct it from the values of other controls. So, is the suffix you mention in the same table as the sequence number or in a related table? We'll need a little more detail on your table structure.

Hi, Yes i will need to store the sequence number in my data base.
The example is as follow:
I have to departments A & B, and if i select the department A it should appear the number 0001. to be a sequence of A-0001.
If in the next record i select the department B it also has to appear the 0001. to display B-0001.
If the next record i select deprtment B again it should display B-0002. An so on.
I need to store this information rather than the ID number for consolidation issues in the company.
I hope i have explain clearly. I'm new in the Access area so that's wy i'm asking.
Thanks again for your help.

We have to start with your table structure. You will need a table that holds the departments

tblDepartments
-pkDeptID primary key, autonumber
-txtDept

Then you will need a table to hold your data relative to the sequence number you mention that references the department. I'll call the table tblData for example purposes. What type of data are you capturing in this table?

tblData
-pkDataID primary key, autonumber
-fkDeptID foreign key to tblDepartments
-SequenceNumber
other fields


The database I posted earlier is very similar in structure to the above except that there were 2 values (locator code and tag code) in addition to the sequence number. If you open the database I posted and open the table TPTagMech_Tbl in design view, you will see that they are similar.

Next you will need to create a form based on tblData with a combo box based on tblDepartments and a control for the sequence number. (You can refer to the form: frmTPTagMech_Tbl in the database I attached earlier). You can go to the property sheet and look under the Events tab for the Before Update event (click on that line after the words [Event Procedure]). Then click on the button with the 3 dots (to the right of the words Event Procedure), it will take you to the code. You will have to modify the code to your particular table and field names.

many thanks Jzwp11.

I really appreciate your valuable time on creating the database. This is what i need.

In addition, may i ask, how to handle if a user logged several tag numbers in particular instance, how this can be extracted if there is no tracking reference?

example:
user created around 7 tag numbers (mixed combinations), this will be issued to a vendor1 for their use.
Then another 12 tag numbers will be required to be issued to Vendor2, once finished inputing then prints it and issued it. then another 20 tags to Vendor1.

How will this can be handled if in-case i want to go back to the database and would like to bring up the tags that i have issued to Vendor1 (7 tags only) and re-prints it again? bear in mind that we may issue another set of diff tag numbers again to Vendor1 in a diff date(s).

That is my other option is making a tracking references which i may store it in another table (tbl_MechTagID) and ref it to the main table, or otherwise i'll just add another field and let it duplicated as a reference, is this a correct thing to do?

by the way, is it ok to change the form to continous so that we can see and browse the list of tags that are currently being generated before printing it or exporting it to excel?

Many thanks again.

You can use the issue date field to filter the results you need, however, I do not see a vendor table. You will need both to do the filtering you want to do. I assume that a vendor can have many tags issued. Can a tag apply to more than one vendor or just one vendor?

Hi Jzwp11.

Yes your correct, Vendor/Contractor/Originator can have many tags issued, but not a tag applies to more than one vendor, etc.

I have attached an image of the actual Tag Numbers (in Excel) that have been issued to two different Originator(Vendor) in two different projects but the same Date Issued. with this situation i may need to filter in combination of both.

With my earlier intention of having a tracker or reference to each issued tags, i think that will be much better, but this will create repeatitive inputs.

With regards to my earlier request, you have answered it correctly and have been very helpful!

Hi Jz,

Just a quick query, what will be the best possible solution if i have existing tag numbers that are already been issued? do i need to manually input the pkTagMechID, including the fkLocCodeID and fkTagCod?

Many thanks again.

Since the form in the database I provided is bound to the table, you just need to select to the applicable locator and tag codes from the combo boxes. Access will bind the appropriate values to the foreign key fields (fkLocCodeID and fkTagCode) in the underlying table. Additionally, since the pkTagMechID is an autonumber field, Access will take care of that one too.

From your earlier post, if a vendor is issued a tag, we need to relate the vendor and tag, so we need a table to hold the vendor information

tblVendors
-pkVendorID primary key, autonumber
-txtVendorName
-txtAddr
etc.

We now need to add a vendor reference (i.e. a foreign key) to TPTagMech_Tbl

TPTagMech_Tbl
-pkTagMechID primary key, autonumber
-fkLocCodeID foreign key to locator code table
-fkTagCodeID foreign key to tag code table
-fkVendorID foreign key to tblVendors
-Sequence
-Type
-Drawing
-Originator
-Comments
-Project
-DateIssue
-Discipline
etc.

To populate the vendor, you can add a combo box (tied to vendor table) to the form I provided earlier, or you can create a new vendor form and use the form I provided as a subform within the vendor form.

BTW, you have a project field in TPTagMech_Tbl. What is its purpose? Can a project have many related records in TPTagMech_Tbl or only 1?

Also, I assume the Originator field refers to a person. Do you have a table that holds all people? If so, you can use a foreign key here instead.

Can you provide some additional details about the other fields in TPTagMech_Tbl? You might have more relationships there that need some attention.

Hi,

Below the details:

Project is just a reference to the Tag Numbers, like what project are these tag numbers for.
Originator is the person/vendor/contractor that requested the tag number(s).
Type can be drop down list or manual input if it's not listed in the drop down.
Comments is just any note(s) to consider.

Just a sample:
Tag Number, Type, Drawing, Originator, Comments, Project, Dateissue, Discipline
ADMIN-A-0001, Equipment, Drawing-123, WP-Name1, , Project1, Apr-23-2012, Mechanical
ADMIN-A-0002, Equipment, Drawing-345, WP-Name1, , Project1, Apr-23-2012, Mechanical

another issued tags on the same date:
ADMIN-CN-0001, Service, Drawing-567, WP-Name1, , Project1, Apr-23-2012, Mechanical
ADMIN-CN-0002, Service, Drawing-678, WP-Name1, , Project1, Apr-23-2012, Mechanical

another issued tags on the same date:
K01-PS-0001, , Drawing-123, WP-Name1, , Project1, Apr-23-2012, Instrumentation
K01-PS-0002, , Drawing-567, WP-Name1, , Project1, Apr-23-2012, Instrumentation

Space means no input, ,.

Based on the sample above, there will be several tags can be issued at the same date but some difference in the fields. In order to track these, maybe requires TagRef-Issued-ID, like, the first batch was issued under this reference TAGRef-0001, the second batch issued with TAGRef-0002, so on.. is like a transmittal reference that these were issued just to track it.

Many thanks again.

I believe that you have other opportunities for normalization based on the data you posted. For example, since a project can have many tags, it would be best to have a project table and relate the project to the tag. Similarly, you can have many tags with the same discipline. You can have a table that holds all disciplines and relate that table to the tag table. The same would hold for types. Now, regarding people, companies, vendors etc., we can generalize the vendor table to people (or groups of people: i.e. companies) as follows:

tblPeople
-pkPeopleID primary key, autonumber
-txtPrimaryName (the last name of an individual or a company name)
-txtSecondaryName (the first name of an individual)
-txtAddress
etc.

I have attached my earlier database with the additional tables and relationships I described above. (I have not altered the forms)



You can use any field in the TPTagMech_Tbl to do a search for specific tags, but if you have a logical need to group certain tags, then you would create each group in a table and reference the applicable tags via a foreign key in TPTagMech_Tbl. Having many tags related to a group describes a one-to-many relationship. Something like this:

tblGroups
-pkGroupID primary key, autonumber
-txtGroupName
-dteGroup (date field perhaps)

TPTagMech_Tbl
-pkTagMechID primary key, autonumber
-fkLocCodeID foreign key to locator code table
-fkTagCodeID foreign key to tag code table
-fkPeopleID foreign key to tblPeople
-Sequence
-fkTypeID foreign key to tblTypes
-fkGroupID foreign key to tblGroups
-Drawing
-Comments
-fkProjectID foreign key to tblProjects
-DateIssue
-fkDisciplineID foreign key to tblDisciplines