How to display a specific field from the last record created.

[Yensid]

I am completely in over my head with Access, so please "spell" everything out. I do not understand even the simplest things yet. I will place my "names" in quotations for clarification, although I don't have the quotation marks in the actual names.

I created a template called "AD - Assembly". In this table I have fields for:
"AD_ RecNum" - This is the auto generated number field for each record, and also my Primary Key


"Document Number" - This is the number that is being assigned to each document placed in this table. (i.e. AD-0001-001)
"Description" - This is the description of each of the documents
"Originator" - This is who is assigning the document a number
"Origination Date" - This is a =CDate(Now()) field that will automatically generate the date and time the document was assigned a number.

I then created a form called "AD - Assembly". On this form I created a field called "Last Record". I want to open the "AD - Assembly" Form, and have this "Last Record" field automatically filled with the "Document Number" filed of the last created record based on the "Origination Date" field.

Please tell me how to do this.

[June7]

Do you want to create a new record with a value from last created record?

Or do you want to open form to the existing last created record?

Regardless, code will be required and there is more than one way to achieve the result.

[Yensid]

I want to create a new record.

First - I have removed the "AD_ RecNum" field, and am going to rely on my "Document Number" as the Primary Key (as each document will have a unique number).

Second - The person who is opening the form to create a new record needs to know what document number to assign. The numbering system used for these types of documents is AD-1001-001, AD-1002-001..AD-1010-001 and so on. I would auto-generate these number assignments, but I think I read somewhere that you can't do that if it has both numbers and letters.

Third - So what I have is a field that will show the document number (AD-XXXX-XXX) of the last created record (based off the "Origination Date" =CDate(Now()) field???). For example, if the last record was AD-1001-001, it will display in that field, which will let the user know that the next number to create is AD-1002-001.

Fourth - When a user opens the form to create the new record, I'd like to have this "Previous Record" field automatically filled in.

[June7]

You want to generate custom unique ID. I use code that searches for the last ID and automatically increments the next ID. The process of generating ID is invisible to the user. I don't depend on user to not make mistake when typing a new number, number is generated for them.

Common topic. Search forum or Google. Here is one https://www.accessforums.net/forms/a...ing-23329.html

[Yensid]

How? Below I've pasted the code from the page you suggested:

Public Function NewSample() As String
Dim strLabNum As String
'search for aborted lab number and use that record, else if none then create new record
strLabNum = Nz(DLookup("LabNum", "Submit", "IsNull(DateEnter)"), "")
If strLabNum <> "" Then
CurrentDb.Execute "UPDATE Submit SET DateEnter=#" & Date & "# WHERE LabNum='" & strLabNum & "'"
Else
strLabNum = Nz(DMax("LabNum", "Submit"), "")
If strLabNum = "" Then
'this accommodates very first generated number of blank database
strLabNum = Year(Date) & "A-0001"
Else
'this accommodates change in year
If Left(strLabNum, 4) = CStr(Year(Date)) Then
strLabNum = Left(strLabNum, 6) & Format(Right(strLabNum, 4) + 1, "0000")
Else
strLabNum = Year(Date) & "A-0001"
End If
End If
CurrentDb.Execute "INSERT INTO Submit(LabNum, DateEnter, EnterWho) VALUES('" & strLabNum & "', #" & Date & "#, '" & Form_Menu.tbxUser & "')"
End If
Form_SampleManagement.ctrSampleList.Requery
NewSample = strLabNum
End Function

But I don't know how to modify things to work properly.

If you could help, that would be great.

[June7]

Posting unmodified code from suggested link is not helpful. I don't know your database and business processes to do coding for you. You will have to adapt code
as you see fit and test it. This means you need to learn programming concepts, VBA language, and how to debug. Link at bottom of my post has guidance on debugging techniques.

Google: Access introduction to programming
http://office.microsoft.com/en-us/ac...010341717.aspx

[Yensid]

I appreciate the help you HAVE given me, and the links you've suggested will be useful. I will continue to attempt figuring this out. Should there be anyone else out there with ideas on how I can accomplish my original question...I look forward to your suggestions as well.

Copy/Paste from my original question above: On this form I created a field called "Last Record". I want to open the "AD - Assembly" Form, and have this "Last Record" field automatically filled with the "Document Number" filed of the last created record based on the "Origination Date" field.

[June7]

You could try DMax expression in a textbox ControlSource:

DMax("[Document Number]", "[table name]")

[Yensid]

That worked perfectly (of course) - thank you!