Dlast Error looking at linked table separate to form

[rob0r]

Hello,

I'm trying to run some code that references the last entry made in a seperate table to the one that the form is assigned to. The form writes to a table called "log" and the seperate table is "Userlog". Userlog contains a time and date that I want to use in my records that the form writes to the table "log". If I run this code in a module, away from the form VBA, it works just fine. But if I put into into my form_load code it gets the error:

Run-time error 3625 - file specification "userspec" does not exist, you cannot link etc

I used and saved "userspec" to link in the txt table "Userlog", but I'm not actually running any transfertext code in my forms anymore. More puzzling is why it does work when I just run the code in a module in the VBA editor. It seems the form doesn't like the refresh of the linked table upon load...

This is my code:

Private Sub Form_Load()
Dim datetime As String
Dim ddate As String
Dim ttime As String
datetime = DLast("Timedate", "Userlog")
ddate = Mid(datetime, 10, 10)
ttime = Mid(datetime, 1, 8)
DoCmd.Maximize
DoCmd.GoToRecord , , acFirst
If Textdate = "" Then
        Me.Textdate = ddate
        Me.Texttime = ttime
        Activitycmb = "Select Activity"
        Comments = "-"
        Modulecmb = "Select Module"
        Reasoncmb = "Select Reason"
        Partscom = "-"
    Else
        DoCmd.GoToRecord , , acNewRec
        Me.Textdate = ddate
        Me.Texttime = ttime
        Activitycmb = "Select Activity"
        Comments = "-"
        Modulecmb = "Select Module"
        Reasoncmb = "Select Reason"
        Partscom = "-"
End If
End Sub

And this code works if I run it on it's own in a module

Sub findrecord()
Dim datetime As String
Dim ddate As String
Dim ttime As String
Dim msg As String
datetime = DLast("Timedate", "Userlog")
ddate = Mid(datetime, 10, 10)
ttime = Mid(datetime, 1, 8)
msg = ddate & " " & ttime
MsgBox msg
 
End Sub

Any pointers where I might be able to sort this

Thanks,

Rob

 

[Pat Hartman]

You don't want to use DLast(), you want to use DMax(). You want the highest value for the date time. DLast() will not always give you the correct answer. Also "" is a zero-length-string which is different from null.