custom primary key (1 Viewer)

willsnake

Registered User.
Local time
Today, 17:46
Joined
Dec 3, 2018
Messages
52
Good day,

Sorry for the bother. But I have been browsing to look for answer... Hope you could help me.. Its my 1st time making access database, is it possible to format primary key to YY-MM-series number? and series number will reset back to 1 for next year...

Thank you...
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 05:46
Joined
Feb 19, 2002
Messages
42,970
Just because you can create a custom PK, doesn't mean that it is a good idea. Best practice is to simply use an autonumber PK. Then you can create a "public" unique identifier where you can pull together multiple pieces of data to provide a code that is user friendly. Just make sure that you make a compound unique index for all the fields that are port of your "custom key". I would not mush the pieces together for storage. You can easily concatenate them for display if you don't want the user to see them as separate fields although you can put the fields right next to each other with the dashes separating them. ALWAYS use dashes or underscores to separate the stanzas of your custom key. If you ever have to change the way you make the key in the future, you will thank me for that advice.
 

willsnake

Registered User.
Local time
Today, 17:46
Joined
Dec 3, 2018
Messages
52
Thank you sir... will follow your advice... Would you be kind to take a look of the database I made so far? I am trying to link multiple tables.

"Lot" is like a category for each PRParticulars like materials, labor, meals, etc... in 1 Purchase request, there can be many "Lot"... What I did to far is first to open the Form Purchase Request then after in inputting data in it, a command button to open the form "Lot" and in Lot I will input the Lot Number and in the subform will be the PR Particular where I input the different Items categorized in a Lot...

Please Advice... thnx...
 

willsnake

Registered User.
Local time
Today, 17:46
Joined
Dec 3, 2018
Messages
52
I cannot attach my access because it is 3.06mb.. the limit is only 2.0mb

please advice on how can i let u view my file...

Thnx...
 

willsnake

Registered User.
Local time
Today, 17:46
Joined
Dec 3, 2018
Messages
52
this is my relationship
 

Attachments

  • access.jpg
    access.jpg
    82 KB · Views: 67

GinaWhipp

AWF VIP
Local time
Today, 05:46
Joined
Jun 21, 2011
Messages
5,901
Hmm, what is this database to do? Also, have you tried zipping the database?
 

Galaxiom

Super Moderator
Staff member
Local time
Today, 20:46
Joined
Jan 20, 2009
Messages
12,849
To post a database, remove the data and leave just a few dummy records as samples. Then you need to Compact and Repair which will recover the deleted stuff. Then zip it.

BTW. When you are starting out, it is common to do a lot of adding and deleting objects as you experiment to become familiar with it. This leaves behind consequences that are not obvious to the outside.

It is a good idea to export everything to a new database sometime before deployment or posting here. It kind of resets a lot of things to new.

BTW Access has a feature called NameAutoCorrect. It tracks changes to object names and updates them in other objects. New developers are prone to edit names so NAC gets a workout, increasing the chance of it going wrong. Many developers turn it off for fear of corruption and most people who use it have at least one experience of trouble. Moreover it doesn't actually fix everything.

However it can be handy if used sparingly and you know what it actually changes and what it doesn't. NAC doesn't actually implement the changes immediately but when the related object next opens. If you change a field name in a table, it is a good idea to immediately open all the queries based on that table so NAC updates before continuing development. Similarly forms and reports.
 

willsnake

Registered User.
Local time
Today, 17:46
Joined
Dec 3, 2018
Messages
52
Sorry, never though of zipping...

What I am trying to do with this is 1st it opens the Form "Purchase Request" then after that It links to open another form "Lot". "Lot" is like a category for the details of the Purchase Request. in 1 Purchase Request, there can be many Lot.

I had tried the form wizard, but when I am at inputting the details "PR Particular" I get Error saying it cannot record because related record is required in the Purchase Request...
 

Attachments

  • William1.zip
    351.7 KB · Views: 56

willsnake

Registered User.
Local time
Today, 17:46
Joined
Dec 3, 2018
Messages
52
By the way, this is what I am trying to achieve...

The PR Number is wrong, it is supposed to be 18-12-370 because it is december that the PR is created...
 

Attachments

  • 47275231_545244025940371_2362308153980223488_n.jpg
    47275231_545244025940371_2362308153980223488_n.jpg
    89.5 KB · Views: 68

GinaWhipp

AWF VIP
Local time
Today, 05:46
Joined
Jun 21, 2011
Messages
5,901
Hmm, well I see what the first problem is, you are trying to design a Form not a database. So, forgetting about the whole Primary Key thing you need to set those tables up properly. Your tables should more like...

tblPurchaseRequests
prPurchaseRequestID (Autonumber, PK)
prPurchaseRequestNumber (Text, Formatted to 18-12-370)
prDate
prSectionID
prRequestingPartyID (FK, Number (Long) - related to tblEndUsers)
etc...

tblPurchaseOrderRequestDetail
prdID
prdPurchaseRequestID (FK, Number (Long) - related to tblPurchaseRequests)
etc...

tblEndUsers
edEndUserID (PK, Autonumber)
edDepartmentID (FK, Number (Long) - related to tblDepartments)

I didn't do all the tables but that should get you started.

And now for a few tips...
1. Get rid of the spaces in the Table and Field names, they cause extra typing down the road.
2. What out for use of Reserved Words as you will need to bracket them so Access doesn't get confused. See the link below for the complete list...
http://allenbrowne.com/AppIssueBadWord.html
3. You do not want to store attachments in your database as it will cause it to quickly grow to its limit of 2 gig. To handle attachments have a look at...
https://www.access-diva.com/d15.html

And since this is your first database...
Jeff Conrad's resources page...
http://www.accessmvp.com/JConrad/accessjunkie/resources.html

The Access Web resources page...
http://www.mvps.org/access/resources/index.html

A free tutorial written by Crystal (MS Access MVP)...
http://allenbrowne.com/casu-22.html

MVP Allen Browne's tutorials...
http://allenbrowne.com/links.html#Tutorials

UtterAccess Newcomer's Reading List
http://www.utteraccess.com/forum/Newcomer-s-Reading-List-t1998783.html

Help with Normalization
http://www.access-programmers.co.uk/forums/showpost.php?p=1146957&postcount=2

Sample data models...
http://www.databasedev.co.uk/table-of-contents.html
http://www.databaseanswers.org/data_models/

Naming Conventions…
http://www.access-diva.com/d1.html

Other helpful tips…

Setting up a Model Database
http://www.access-diva.com/d11.html

My Database Standards...
http://regina-whipp.com/blog/?p=102
 

willsnake

Registered User.
Local time
Today, 17:46
Joined
Dec 3, 2018
Messages
52
WOW!!!

Many thank for these tips and tutorials...

I will browse them thoroughly and will be back here in case I encounter something that I don't understand...

Would that be OK?

Thank you again... This is a huge help...
 

Eugene-LS

Registered User.
Local time
Today, 12:46
Joined
Dec 7, 2018
Messages
481
Good day,

Sorry for the bother. But I have been browsing to look for answer... Hope you could help me.. Its my 1st time making access database, is it possible to format primary key to YY-MM-series number? and series number will reset back to 1 for next year...

Thank you...
Yes, it possible!
How I could understand your condition ...


Code:
Public Function GetNewKeyVal(ByVal vDate As Variant) As String
'Function returns new key value in format "YY-MM-series number" = "18-12-0000002" etc
'... The field [YourKeyField] is text - size = 14
'--------------------------------------------------------------------------
Dim iYearNo$, s$ ' all as string
Dim sFieldName$, sTableName$
Dim v As Variant
'?GetNewKeyVal(#12/12/2018#)
'--------------------------------------------------------------------------

    sFieldName = "YourKeyField" ' If there are no spaces in field name , or = "[Your Key Field]"
    sTableName = "DataTest"     ' If there are no spaces in table name, or = "[Data For Test]"

On Error GoTo GetNewKeyVal_Err
    If vDate = Null Then vDate = Date
    iYearNo = CInt(Format(vDate, "YY"))
        
'Find a hew value of key field by date
    s = "CLng(Mid(" & sFieldName & ", 1, 2)) = " & iYearNo
    v = DMax("CLng(Mid(" & sFieldName & ",7))", sTableName, s)
    v = Nz(v, 0) + 1 'New no
   
    GetNewKeyVal = Format(vDate, "YY\-MM") & "-" & Format(v, "0000000")
    
GetData_Bye:
    On Error Resume Next
    '... if some cases
    Exit Function

GetNewKeyVal_End:
    Exit Function

GetNewKeyVal_Err:
    'MsgBox "Error: " & Err.Number & vbCrLf & Err.Description & vbCrLf & _
    "in Function: GetNewKeyVal in module: 00ModuleForTests", vbCritical, "Error in Application"
    Err.Clear
    Resume GetNewKeyVal_End
 
Last edited:

willsnake

Registered User.
Local time
Today, 17:46
Joined
Dec 3, 2018
Messages
52
Good day,

Thank you very much to helping me masters...

This thread is now solved and closed...
 

Users who are viewing this thread

Top Bottom