DMAX previous record

[oldmanbumper]

I am have one large database and a few pop up forms. I am wanting to go to one of my pop of forms on a new record and lookup 5 fields from the previous record and have them copied over with a click of a button. I have a table with the name Order Tracker and I have an ID field that auto updates on each record. I tried this but its not working. Can someone guide me?




Private Sub Copy_Last_Record_Click()

Dim ID As Long

ID = Dmax ("ID", "Order Tracking")
DoCmd.GoToRecord , , acPrevious
Contract_Signer_Title = DLookup("Contract_Signer_Title", "Order Tracking", "ID=")
Contract_Signer_First_Name = DLookup("Contract_Signer_First_Name", "Order Tracking", "ID=" & ID)

End Sub

[Welshgasman]

Dmax does not locate a record, merely returns a value?

You would need to locate that record with that Dmax ID, and only then move previous.

However if table is not sorted asc by ID, that is never going to work?

You also have not included the ID value for the first Dlookup() ?

[oldmanbumper]

The table is sorted by ID. So is it possible to copy certain fields from the most recent record and fill out vs having to retype? I am placing orders and sometimes I get three separate orders from the same customer however their local contact is all the same, so I just want to hit copy and it fills it out for me.

[Welshgasman]

The way I did it years ago, was to set the default value for the control from the current value, as I had lots of entries for the same date, ship and rank

Private Sub Date_ID_AfterUpdate()
    If Me.chkCopy Then
        Me![Date_ID].DefaultValue = """" & Me![Date_ID].Value & """"
    Else
        Me![Date_ID].DefaultValue = 0
    End If
End Sub

Because of that, I had a checkbox to indicate whether I should copy previous value or not.

http://allenbrowne.com/ser-24.html

[Gicu]

Another way to do it would be to set the default value of those 5 fields as the value of the previous record, many examples in the forums:
https://www.access-programmers.co.uk...fields.307738/
https://www.access-programmers.co.uk...-value.120910/
https://www.access-programmers.co.uk...record.287145/ <------this one has code similar to yours

To do it how you started (with a button) please review this updated code:

EDITED CODE BELOW - added square brackets to table name in the two dLookups:

Dim lngID As Long


lngID = Dmax ("ID", "[Order Tracking]","[ID]<" & Me.ID) 'assumes ID is an autonumber and already created for the current new record
'DoCmd.GoToRecord , , acPrevious 'not needed
Me.Contract_Signer_Title = DLookup("Contract_Signer_Title", "[Order Tracking]", "ID=" & lngID) 'Contract_Signer_Title is the name of the textbox control bound to the Contract_Signer_Title field in [Order Tracking] table
Me.Contract_Signer_First_Name = DLookup("Contract_Signer_First_Name", "[Order Tracking]", "ID=" & lng)
'add your other fields using a similar syntax)

Cheers,

[oldmanbumper]

This helps. let me try these out. Thank you.

[Gicu]

Please see the revised code, initially I forgot to add the brackets to the dLookups (you shouldn't have spaces in any objects names).

Cheers,

[oldmanbumper]

This helps. let me try these out. Thank you.

I tried this but its not working: Is what I did right?

Private Sub Copy_Last_Record_Click()

Dim lngID As Long

lngID = DMax("ID", "[Order Tracking]", "[ID]<" & Me.ID)
Me.Contract_Signer_Title = DLookup("Contract_Signer_Title", "[Order Tracking]", "ID=" & lngID)
Me.Contract_Signer_First_Name = DLookup("Contract_Signer_First_Name", "[Order Tracking]", "ID=" & lng)

End Sub

[June7]

"not working" means what - error message, wrong result, nothing happens?

You show variable lng in the last DLookup instead of lngID.

Do you have Option Explicit in module header? Review https://chandoo.org/forum/threads/ho...default.32072/

[oldmanbumper]

Nothing happens. The fields just stay blank. I checked record 72 and its filled out. When I go to record 73 and hit "copy last record" button, with the code provided, it does nothing.


Private Sub Copy_Last_Record_Click()

Dim lngID As Long

lngID = DMax("ID", "[Order Tracking]", "[ID]<" & Me.ID)
Me.Contract_Signer_First_Name = DLookup("Contract_Signer_First_Name", "[Order Tracking]", "ID=" & lng)
Me.Contract_Signer_Last_Name = DLookup("Contract_Signer_Last_Name", "[Order Tracking]", "ID=" & lng)

End Sub

[June7]

Again, you declare variable lngID but use lng.

Did you read the thread I linked?

[oldmanbumper]

I updated it to the below code and it still does not work. I did read the link to send my database, I have to delete alot of info but I can do that then upload it.

Private Sub Copy_Last_Record_Click()

Dim lngID As Long

lngID = DMax("ID", "[Order Tracking]", "[ID]<" & Me.ID)
Me.Contract_Signer_First_Name = DLookup("Contract_Signer_First_Name", "[Order Tracking]", "ID=" & lngID)
Me.Contract_Signer_Last_Name = DLookup("Contract_Signer_Last_Name", "[Order Tracking]", "ID=" & lngID)

End Sub

[June7]

I meant the link in post 9 about using Option Explicit in modules - not the link in my post signature. But providing db would be my next suggestion.

[Welshgasman]

Start walking through your code with F8.
See the debugging link in my signature.

[oldmanbumper]

here is the database. If you go to the Contact Info form you will see the copy button. Thank you for your help.