Solved Select Last Record

[Emma35]

Hi All,
I've been using some code in the OnClick event of a command button to make the last entered record appear in the text boxes of a new record. The txt boxes record the operators name and two other items and rather than getting them to keep selecting the same things they can just press the command button to add the last record details to the new record. Just this morning however, the command button is adding a record from two days previous and not the latest record which is what i need. My code is below if anyone can see any bugs ?

Thank you

Private Sub Refresh_Click()
If IsNull(OperatorName) Then         ' Blank Field
        
        OperatorName = DLast("OperatorName", "tbl_MainRecords")
    End If
    
    If IsNull(Plant) Then         ' Blank Field
        
        Plant = DLast("Plant", "tbl_MainRecords")
    End If
    
    If IsNull(Recipe) Then         ' Blank Field
        
        Recipe = DLast("Recipe", "tbl_MainRecords")
    End If
End Sub

 

[Gasman]

I think you have been very lucky or not been paying attention?
Have a look at the command description.
Probably safer to get the max ID record if you are using incremental autonumber, which most people do?, and use that to get your data, or queries ordered correctly and TOP.

If you want to do it after first entry, then just set the DefaultValue of each.

 

[Emma35]

Cheers....looks like i've been pretty fortunate up to now. I've tried setting up a query and sorting the autonumber field to Descending but it still doesn't work ?
How do i get the max ID number directly from the table ?

Thanks

 

[plog]

With DMax()

MS Access: DMax Function

This MSAccess tutorial explains how to use the Access DMax function with syntax and examples. The Microsoft Access DMax function returns the maximum value from an Access table (or domain).

www.techonthenet.com

 

[Gasman]

Firstly, let's find out when you want to do this.
As soon as you open a form, or after the first data entry?

I have already mentioned a way, if it is the second option?

 

[arnelgp]

you can also try this code:

Private Sub Refresh_Click()
With Currentdb.OpenRecordset("SELECT TOP 1 * FROM tbl_MainRecords Order By ID DESC;")
    If Not .EOF Then
        OperatorName = !OperatorName
        Plant = !Plant
        Recipe = !Recipe
    End If
    .Close
End With
End Sub

 

[Emma35]

Firstly, let's find out when you want to do this.
As soon as you open a form, or after the first data entry?

I have already mentioned a way, if it is the second option?

I needed a command button to populate three txt boxes with the very last record added to the table. When a new operator takes over, they change the txt box to their own name, after which the button will add their name to the next record

 

[Emma35]

you can also try this code:

Private Sub Refresh_Click()
With Currentdb.OpenRecordset("SELECT TOP 1 * FROM tbl_MainRecords Order By ID DESC;")
    If Not .EOF Then
        OperatorName = !OperatorName
        Plant = !Plant
        Recipe = !Recipe
    End If
    .Close
End With
End Sub

Thanks arnel that code works fine

 

[Emma35]

With DMax()

MS Access: DMax Function

This MSAccess tutorial explains how to use the Access DMax function with syntax and examples. The Microsoft Access DMax function returns the maximum value from an Access table (or domain).

www.techonthenet.com

Cheers plog i got that method to work also. Now i've got two options for the future

 

[Emma35]

You haven't answered the question regarding timing. Is it the last record ANYBODY entered or is it the record you were just working on? If it is the latter, @Gasman gave you the idea. If you need more help to implement it. Just ask.

Even using Max() or Arnelgp's suggestion to get the most recently entered ID is not reliable if you are in a multi-user environment. You might be getting the last record Suzy entered rather than the last record you entered. I'm guessing that Gasman's suggestion is what you actually need.

Hi Pat....yes it's the very last record added to the table. I realise that when the operators change the new person will get the previous operator's name but that's ok they just have to change it once

 

[Emma35]

Thanks everyone for the great suggestions as usual....i appreciate you giving your time for me

Em x