How to copy a calc & concatenated field into a text box field that is the Primary Key for the Table

[Roberto Belmar]

Hi Forum experts,
I have a situation where someone may have a solution to the problem that I have encountered whilst building this Access 2016 application for work.

Form Design à Form Name: FrmDocNoGen
[IMG]file:///C:/Users/rbelmar/AppData/Local/Temp/msohtmlclip1/01/clip_image002.gif[/IMG]

Form Data Sample:
[IMG]file:///C:/Users/rbelmar/AppData/Local/Temp/msohtmlclip1/01/clip_image004.gif[/IMG]
The situation is that I have “QryDocNoGen” that has some smart expressions that work out the next sequential numbers based on the DeptDisc and DocTypes fields.
Below are the expressions of each field for clarity.
[IMG]file:///C:/Users/rbelmar/AppData/Local/Temp/msohtmlclip1/01/clip_image005.gif[/IMG][IMG]file:///C:/Users/rbelmar/AppData/Local/Temp/msohtmlclip1/01/clip_image006.gif[/IMG][IMG]file:///C:/Users/rbelmar/AppData/Local/Temp/msohtmlclip1/01/clip_image007.gif[/IMG][IMG]file:///C:/Users/rbelmar/AppData/Local/Temp/msohtmlclip1/01/clip_image008.gif[/IMG][IMG]file:///C:/Users/rbelmar/AppData/Local/Temp/msohtmlclip1/01/clip_image010.gif[/IMG]
1. CalcSN: DCount("[DeptDisc]","TblDocumentNumberGenerator","[DeptDisc] = '" & [DeptDisc] & "' and [DocTypes] = '" & [DocTypes] & "' AND [M_Id] <= " & [M_Id])
2. SeqNo: Format([CalcSN],"0000")
3. CalNewDN: ([DeptDisc] & "-" & [DocTypes] & "-" & [SeqNo])
4. NGDocNum

What I want to achieve is that, once the data that is automatically concatenated in field “CalNewDN” a Temp value (e.g. a single letter of number) is allocated so that it will satisfy the Primary Key condition that it cannot be a NULL, then the code will copy the last concatenated entry from the “CalNewDN” field into the Primary Key field “NGDocNum”.


Would anybody be able to help? As I have searched high and low to find a way to get this done but, it’s been a-non-event so far!!
Kind regards,

[ssanfu]

The images did not get attached..... try again????

[June7]

Your images did not embed in post.

Populating a text field is simple code. Trick is figuring out what event to put it in.

Generating custom unique identifier is a common topic. Start with https://www.accessforums.net/showthread.php?t=23329

[Roberto Belmar]

Hi Forum experts.zipTitle: How to copy a calc & concatenated field into a text box field that is the Primary Key for the Table

Hi Forum expert - ssanfu,
I have attached the information in word and database for ease.

Could you also please have a look into the UserName and ComputerName VBA so that when a user logs into the database it will automatically recognise the User's Name and their Computer Name/ID.

Kind regards,

Roberto Belmar

[June7]

Field and textbox have same name. VBA code would be:
Me!NGDocNum = Me.NGDocNum

Now the trick is figuring out which event to put this code into.

I am getting duplicate sequence for the same DeptDisc & DocType. (should that be DeptDesc?)

You are using DCount() in query to dynamically calculate this sequence and it is causing duplicate number on form until record is committed to table.

DMax()+1 expression in DefaultValue is usually used to populate a numeric sequential ID field.

Have you reviewed the link in post 3?

Why CalNewDN and NGDocNum fields? - they have identical values.

I don't see any VBA for grabbing UserName and ComputerName. I use Environ("USERNAME") and Environ("COMPUTERNAME").

Advise not to use spaces nor punctuation/special characters (underscore only exception) in naming convention.

[Roberto Belmar]

Hi June7
If there is another suggestion that you can make please do so. This database is by no means set in concrete, as it’s in development stages and yet to be finalised.
“DeptDisc” is a field that was created with the intention of being a Department / Discipline field however, can be left as “Dept” if required for simplicity and functionality.
The reason why I chose “CalNewDN” and “NGDocNum” fields it’s simple. As per my initial explanation to the forum I have a calculated field that works using a DCount() which does what I want but it duplicates the number on form until record is committed to table as you have pointed out.
This came about because the “SeqNo” field in the query expression must interrogate both fields “DeptDisc” and “DocTypes”, then compare and if these two fields are the same based on the “Doctypes” but, different from “DeptDisc” then, the same “SeqNo” will be calculated as per below examples.

Example:
· COPP-POL-0001
· OHS-POL-0001
· COPP-PRO-0001
· OHS-PRO-0001
· OHS-PRO-0002
· OHS-PRO-0003
· COPP-FRM-0001
· COPP-FRM-0002
· COPP-FRM-0003
· OHS-FRM-0001
· OHS-FRM-0002
· OHS-FRM-0003
· OHS-FRM-0004


Given that field “CalNewDN” is the calculated and concatenated field which I cannot assign a Primary Key I have created NGDocNum which on Table “TblDocumentNumberGenerator” I have assigned it to be Primary Key (One-to-Many), thus linking it with “Document Number” on Table TblMaster, thus being able to use the information on TblMater to produce the base data for reports.


As far as the Environ("USERNAME") and Environ("COMPUTERNAME") I have nothing as I have never done this before. So, if you would kindly provide me with help on this in how to create it and set it up I would be most grateful.
I hope the above explanations shine a bit of light on the reasons in how/why the database application has come to be.

Kind regards,
Roberto Belmar

[June7]

I already gave you a link on how I manage creating a custom unique identifier. The procedure could be modified to incorporate your compound criteria. Or you can try the DMax()+1 approach to populate a field instead of DCount() calculation in query.

Here is another link on how I manage user login and validation: https://www.accessforums.net/showthread.php?t=23585

[ssanfu]

This is in response to a PM......

@Roberto

After re-reading these posts and your questions, I looked at your dB. Having "NGDocNum" as the PK field presented problems. I modified your dB like how I would have designed the tables/relationships. That meant I had to modify the queries and forms.

I don't use split forms - in fact this is the first time I played with one.
You could get rid of the columns "CalcSN", "SeqNo" and "CalNewDN" in the query "QryDocNoGen". Code now does all of that.


Here is a site to help you with the username and computername code and how to use the code.
http://www.databasedev.co.uk/get_use...putername.html

AFTER you look at the site, then you can look at the example dB.


You might also see http://www.fmsinc.com/free/newtips/primarykey.asp about Primary Key Tips and Techniques


Good luck with your project.....