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

 

[Pat Hartman]

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.

 

[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

 

[Pat Hartman]

@Emma35 I guess you didn't understand the problem of using the method you chose in a multi-user environment. If two users are entering data during the same time period you can easily pick up the name of the wrong user. Also, if the users log off and log on when they start using the computer, you never need to rely on them to enter anything. You can simply use Environ("UserName") to obtain the user name. Relying on users to take this particular action is also poor practice. It is better if you can do it for them.

The other method of copying the data from the existing record is far safer than using a query to retrieve the last record. You will never even know if the wrong name is logged so I guess you don't need to worry.

This is the code I use:

Private Sub Form_BeforeUpdate(Cancel As Integer)
    Me.ChangeBy = Environ("UserName")
    Me.ChangeDT = Now()
End Sub

Regardless of which method you choose to capture the User Name, you should NEVER rely on the user to press a button for every record. PERIOD. Your code should always do it for them. I use the BeforeUpdate event because the user doesn't even see that I am recording his name. If you want them to see their name, then you can use the BeforeInsert event. That event runs as soon as the user types the first character into a new record. The BeforeUpdate event is the last event that runs before the record gets saved regardless of what caused the record to be saved - user action or Access decided the record needed to be saved.

If you don't control the population of the User Name and leave it in the hands of the user to press a button then you MUST add validation code to the form's BeforeUpdate event to ensure that the user actually pressed the button to populate the name. Otherwise, you end up with records with no name.