Creating a Reference number that will serve as primary key

[Gari]

Dear All,

Back to my project database (if you have read my previous thread requests ^^).

I have a project database.

I will add an edit form that will be used to add new projects.

I would like that when adding a new project; a reference is created for this one. This reference will be used as primary key.

I want the reference to look like this:

[Account Number]/[Current Year]/Proj[Numbers of projects already created for this Account Number during this Current Year + 1]

Is this possible? If yes, how?

Thank you very much for your help.

Best regards,

G.

[NeoPa]

If you want us to refer to another thread you will need to post a link to that one in this one.
Otherwise this isn't very easy to pick up and run with.

[Gari]

Hello NeoPa,

Thank you for your answer. Actually I was refering to no threads in particular but I guess this one might help to understand:

Button linking a continuous sub-form to a form (both built around several tables)

My tables are all listed in there.

Best regards,

G.

[NeoPa]

Gari,
That's a good one to pick :)
Just as an aide to anyone reading this post, I'll post the layout information from the other thread :

My tables are as follows:

[Company] – Primary Key: [Account Number]
[Currency] – Primary Key: [Currency ID]
[Group] – Primary Key: [Group ID]
[Owner] – Primary Key: [Owner ID]
[Project Lines – Existing] – Primary Key: [ProjectID]
[Project Lines – Oustanding] – Primary Key: [ProjectID]
[Project Lines – Proposed] – Primary Key: [ProjectID]
[Projects] – Primary Key: [ProjectID]
[RM] – Primary Key: [RMID]

The relationships of the tables are as follows:

[Group] One-to-many [Company] through [Group ID]
[RM] One-to-many [Company] through [RMID]
[Company] One-to-many [Projects] through [Account Number]
[Owner] One-to-many [Projects] through [Owner ID]
[Currency] One-to-many [Projects] through [CurrencyID]
[Project Lines – Existing] One-to-one [Projects] through [ProjectID]
[Project Lines – Oustanding] One-to-one [Projects] through [ProjectID]
[Project Lines – Proposed] One-to-one [Projects] through [ProjectID]

I have a main form called ‘Company Projects List’, which regroup data from tables [Company], [Group], [Owner].

I have then a continuous sub-form to this main form called ‘Projects Subform’, which regroups data from tables [Projects], [Currency], [Owner]

I have finally a form called ‘Project Details’, which regroups data from all the tables cited above ([Company], [Currency], [Group], [Owner], [Project Lines – Existing], [Project Lines – Oustanding], [Project Lines – Proposed], [Projects], [RM])

[NeoPa]

Right.
Assuming that all the values you require to build the new value with are available in the (sub)form you are intending to create the item in (well in the source data of it to be more precise), then there should be a way of setting this up as the PK. Where the code would be put depends on information we don't have so I can't advise there, but it should certainly be possible.
The details of the form you want to do this on would be required for more detailed help (including Source and relevant Control names).

BTW I'd advise that the Project Lines tables be merged into one table with an extra field to flag between the three types. I don't know exactly what you're doing and wouldn't want to, so there may be good reasons for having it the way you do. It would surprise me though, if that were so.

[Gari]

Dear NeoPa,

Thank you for your post.

Assuming that all the values you require to build the new value with are available in the (sub)form you are intending to create the item in (well in the source data of it to be more precise), then there should be a way of setting this up as the PK. Where the code would be put depends on information we don't have so I can't advise there, but it should certainly be possible.

Actually all the value are not available in the form:
- The only available value is [Account Number].
- [Current Year] is the ... current year.... But I do not plan to put a Textbox for this. Actually, what I was thinking about is that the system will automatically input the current year.
- Proj[Numbers of projects already created for this Account Number during this Current Year + 1]: What I want here is the word 'Proj' followed by the number representing the Nth project of this particular [Account Number] inputed on this [Current Year]
To sum up, one field is available on the form and the two others should be generated automatically, then this combination recorded as a PK.

The details of the form you want to do this on would be required for more detailed help (including Source and relevant Control names).

Actually the form is not yet build as I need other programming information to do it. If required, I can post a 'Draft form' here.

BTW I'd advise that the Project Lines tables be merged into one table with an extra field to flag between the three types. I don't know exactly what you're doing and wouldn't want to, so there may be good reasons for having it the way you do. It would surprise me though, if that were so.

I note that. After having thought about it I think you're right and that it would be better to merge the tables.

Please let me know if you need more information.

Best regards,

G.

[NeoPa]

Actually all the value are not available in the form:
- The only available value is [Account Number].
To sum up, one field is available on the form and the two others should be generated automatically, then this combination recorded as a PK.

That's fine. I really meant "Are all the ones not already defined available on the form?". I was just trying not to waste too much time just getting the basic details sorted out.

Actually the form is not yet built as I need other programming information to do it. If required, I can post a 'Draft form' here.

Yes please. This should be done first really. The layout (concept) of the form doesn't require the programming info. Try to prepare all the relevant info before posting as this greatly helps us to answer in a straightforward manner.

In this case, I will try to work on the info I have - assuming names for the form and item(s) on your form. Assuming also, that the number at the end wants to be formatted as four digits (Free-form is not a good idea here as it will muck up your sorting order).

[NeoPa]

In the AfterUpdate event procedure of the Account Code TextBox (I will name it txtAccount) you need to set up the value for the PK TextBox (I will name it txtProjectID) something like :

Private Sub txtAccount_AfterUpdate()
    Dim strProjectID As String
    Dim intAccLen As Integer

    'In case the length varies but also to handle an empty field
    intAccLen = Len(Me!txtAccount)
    'Set up search value
    strProjectID = Me!txtAccount & Format(Date, "/yyyy/Proj\*")
    'Find latest project for this Account & Year
    'If none found then leave the search value in strProjectID
    strProjectID = Nz(DMax("[ProjectID]", _
                           "[Projects]", _
                           "[ProjectID] Like '" & strProjectID & "'"), _
                      strProjectID)
    'If none found, convert value to one before first required one
    strProjectID = Replace(strProjectID, "*", "0000")
    'Update to new (required) value
    Mid(strProjectID, intAccLen + 11, 4) = _
        Format(Int(Mid(strProjectID, intAccLen + 2, 4)) + 1, "0000")
    'Save in txtProjectID on form
    txtProjectID = strProjectID
End Sub

It would still be useful to post your form design with relevant control names as this makes reading the thread later easier and more understandable.

[Gari]

Hello NeoPa,

Sorry for the delay in answering you but I was away on holidays and did not have access to the internet. I came back to work today.

I'll have a close look on your post and I'll post the form design later.

Best regards,

G.

[NeoPa]

That's not a problem G.
I wasn't short of questions to keep me busy while you were away ;)

[Gari]

Well… I took my time to answer because I faced problems with the form design… that I did not manage to do btw.

Here is a picture of the form (attached).


From top to bottom, the names of the labels are:

Company Details_Title
Company Name_Label
Company_Account Number_Label
Rating_Label
Group Name_Label
RM Name_Label
Project Details_Title
Owner Name_Label
Projects_Accoun t Number_Label
Project Name_Label
ProjectID_Label
Payment terms_Label
Currency Code_Label
Value_Label
Variations_Labe l
Start Date_Label
Ending Date_Label
Time Extention_Label
Maintenance Period_Label

From top to bottom, the names of Combo Box and Text Box are:

Company Name (Combo Box: Row source is “SELECT Company.[Account Number], Company.[Company Name] FROM Company;”)
Company_Account Number (Text Box)
Rating (Text Box)
Group (Combo Box: Row source is “SELECT Group.GroupID, Group.[Group Name] FROM [Group];”)
RM (Combo Box: Row source is “SELECT RM.RMID, RM.[RM Name] FROM RM;”)
Owner (Combo Box: Row source is “SELECT Owner.OwnerID, Owner.[Owner Name] FROM Owner;”)
Projects_Accoun t Number (Text Box)
Project Name (Text Box)
ProjectID (Text Box)
Payment terms (Text Box)
Currency (Combo Box: Row source is “SELECT Currency.Curren cyID, Currency.[Currency Code] FROM [Currency];”)
Value (Text Box)
Variations (Text Box)
Start Date (Text Box)
Ending Date (Text Box)
Time Extention (Text Box)
Maintenance Period (Text Box)

Here is how I would like to have it work:

1/ For Text Boxes, the user will fill in the required details.
2/ For Combo Boxes, the user can choose an existing name picked up from the list OR the user can write a new name. The new name will be automatically added in the corresponding table.
3/ When a user choose an existing Company Name, then the boxes Company_Account Number, Rating, Group and RM automatically gets updated with the corresponding information.
4/ When the box Company_Account Number is updated (automatically or manually), then the ProjectID Text box gets automatically updated (this is the code for what this thread was primarily intended to and that was given by NeoPa)
5/ When the user clicks on the Button “Add Project”, the data automatically update, and a report is generated saying that the database has been correctly updated with such and such data. This buttons currently runs an Update Query with the following code:

Private Sub Add_Project_Click()

    DoCmd.OpenQuery "Update_New_Project"
        
End Sub

And the code of the query is:

UPDATE RM INNER JOIN 
(Owner INNER JOIN 
([Group] INNER JOIN 
([Currency] INNER JOIN 
(Company INNER JOIN 
Projects 
ON Company.[Account Number] = Projects.[Account Number]) 
ON Currency.CurrencyID = Projects.CurrencyID) 
ON Group.GroupID = Company.GroupID) 
ON Owner.OwnerID = Projects.OwnerID) 
ON RM.RMID = Company.RMID 
SET 
Company.[Account Number] = Forms!Projects_Test![Company_Account Number],
Company.[Company Name] = Forms!Projects_Test![Company Name], Company.Rating = Forms!Projects_Test!Rating, 
[Currency].[Currency Code] = Forms!Projects_Test!Currency, 
[Group].[Group Name] = Forms!Projects_Test!Group, 
Owner.[Owner Name] = Forms!Projects_Test!Owner, 
Projects.[Project Name] = Forms!Projects_Test![Project Name], 
Projects.[Project Account Number] = Forms!Projects_Test![Projects_Account Number], 
Projects.ProjectID = Forms!Projects_Test!ProjectID, 
Projects.[Payment terms] = Forms!Projects_Test![Payment terms], 
Projects.[Value] = Forms!Projects_Test!Value, 
Projects.Variations = Forms!Projects_Test!Variations, 
Projects.[Start Date] = Forms!Projects_Test![Start Date], 
Projects.[Ending Date] = Forms!Projects_Test![Ending Date], 
Projects.[Time Extention] = Forms!Projects_Test![Ending Date], Projects.[Maintenance Period] = Forms!Projects_Test![Maintenance Period], 
RM.[RM Name] = Forms!Projects_Test!RM;

For the moment, the query does not work as it tells me that Microsoft Access did not update due to “key violations”.

So, in a nutshell, I do not know how to program the form, and thus couldn’t test the code given by NeoPa.

Anyone can help?

If you need whatever more details, please let me know....

Thank you very much and best regards.

G.

[NeoPa]

Gari,
I'm not sure where you're going with this but I'm afraid it is really far too impractical for us to debug your whole project for you remotely by forum. Apart from anything else the task would be enormous. We can, and are happy to, answer specific questions, that it is your responsibility to express clearly and succinctly.
I will try to post an update to my earlier code with the correct names used, but otherwise I can only suggest that you cut down the scope of your form question drastically and post it again. It is after all, a separate question.
Some of the information you've posted in here is good quality work though, so I'm not going to close the thread or anything and I suggest that any future related questions link to this one for these details.

A Tip (General - not just for you Gari).
Try to limit the scope of any questions posted on here (or any forum site really) by setting up a test system that you play with and remove anything which is not relevant to your current question. That way, any potential Expert has less extra information to wade through when trying to think through your question to find an answer for you. There will also (generally) be less Q&A about what's actually being asked for.

[NeoPa]

Some general points anyway :

1. Visit Normalisation and Table structures as I'm sure it would sort out half of your problems.
2. Use Bound Forms to accomplish addition and updates of your data. Running a SQL query to update data that your Form is built on or around will naturally produce conflicts.

[NeoPa]

This is in answer to your original thread question.

This is a little difficult atm because your form has two entry fields for Company Name & Account Number (which is just plain wrong IMHO - see link in earlier post (#13)). I will use the [Company_Account Number] control in this version.

Private Sub Company_Account_Number_AfterUpdate()
    Dim strProjectID As String
    Dim intAccLen As Integer

    'In case the length varies but also to handle an empty field
    intAccLen = Len(Me![Company_Account Number])
    'Set up search value
    strProjectID = Me![Company_Account Number] & Format(Date, "/yyyy/Proj\*")
    'Find latest project for this Account & Year
    'If none found then leave the search value in strProjectID
    strProjectID = Nz(DMax("[ProjectID]", _
                           "[Projects]", _
                           "[ProjectID] Like '" & strProjectID & "'"), _
                      strProjectID)
    'If none found, convert value to one before first required one
    strProjectID = Replace(strProjectID, "*", "0000")
    'Update to new (required) value
    Mid(strProjectID, intAccLen + 11, 4) = _
        Format(Int(Mid(strProjectID, intAccLen + 2, 4)) + 1, "0000")
    'Save in ProjectID on form
    ProjectID = strProjectID
End Sub