Last Record(QueryDef or Recordset method??)

[Adam McReynolds]

My question is which method of finding the last record is best, QueryDef or Recordset, and can someone point me in the right direction? Here is my data:

Table:
tbl_module_repairs

Field:
aps_rma

Textbox to insert last record RMA into:
txt_test

Here is some code I tried but get an invalid argument msgbox:

'''''''''''''''''''''''''''''''''
'Opens last RMA into textbox (For opening tag sheet)
    Dim dbs As DAO.Database
    Dim rst As DAO.Recordset
    
    'Get the database and Recordset
    Set dbs = CurrentDb
    Set rst = dbs.OpenRecordset("tbl_module_repairs")

    'Search for the first matching record
    rst.FindLast "[APS_RMA]"
    Me.txt_test =  RS!APS_RMA

    'Check the result
    If rst.NoMatch Then
        MsgBox "Record not found."
        GoTo Cleanup
    End If
   
Cleanup:
        rst.Close
        Set rst = Nothing
        Set dbs = Nothing

Any help would be awesome.

 

[pbaldy]

I'd probably just use a DMax(). The method you have is probably the least efficient. Better would be:

Set rst = dbs.OpenRecordset("SELECT Max(APS_RMA) As LastOne FROM tbl_module_repairs")

Me.txt_test = RS!LastOne

plus adding a check for no records.

 

[Adam McReynolds]

I'd probably just use a DMax(). The method you have is probably the least efficient. Better would be:

Set rst = dbs.OpenRecordset("SELECT Max(APS_RMA) As LastOne FROM tbl_module_repairs")

Me.txt_test = RS!LastOne

plus adding a check for no records.

Thanks for the help. I got object required as a msgbox this time. My table is a linked table, is this the problem?

Here is my code now:

'''''''''''''''''''''''''''''''''
'Opens last RMA into textbox (For opening tag sheet)

    Dim dbs As DAO.Database
    Dim rst As DAO.Recordset

    'Get the database and Recordset
    Set dbs = CurrentDb
    Set rst = dbs.OpenRecordset("SELECT Max(APS_RMA) As LastOne FROM tbl_module_repairs")

'Inserts into textbox
Me.txt_test = RS!LastOne

 
    'Check the result
    If rst.NoMatch Then
        MsgBox "Record not found."
        GoTo Cleanup
    End If
   
Cleanup:
        rst.Close
        Set rst = Nothing
        Set dbs = Nothing

 

[pbaldy]

Linked table shouldn't matter. You're setting one variable and then checking another. I'd get rid of the NoMatch test and do:

If rst.EOF Then
  Msgbox "no records"
Else
  Me.txt_test = rst!LastOne
End If

 

[CJ_London]

I think this is the problem

 Set [COLOR=red]rst[/COLOR] = dbs.OpenRecordset("SELECT Max(APS_RMA) As LastOne FROM tbl_module_repairs")

'Inserts into textbox
Me.txt_test = [COLOR=red]RS[/COLOR]!LastOne

RS should be rst

 

[pbaldy]

I must not have said that well enough?

You're setting one variable and then checking another.

 

[Adam McReynolds]

Linked table shouldn't matter. You're setting one variable and then checking another. I'd get rid of the NoMatch test and do:

Ok, so I followed your changes and I am still getting a record that is not the last. There is a field "prikey" which is a sequential number and when I replace "APS_RMA" it pulls the last "prikey" number, so I set it up with a dlookup because it is the only way I know how. Any better suggestions? And thanks again for all the help! Heres what I have now that is working:

'''''''''''''''''''''''''''''''''
'Opens last RMA into textbox (For opening tag sheet)

    Dim dbs As DAO.Database
    Dim rst As DAO.Recordset

    'Get the database and Recordset
    Set dbs = CurrentDb
    Set rst = dbs.OpenRecordset("SELECT Max(prikey) As LastOne FROM qry_Module_Repairs_Quick_Update_Form_RF")
 
    'Check the result
If rst.EOF Then
  MsgBox "no records"
Else
  Me.txt_test = DLookup("APS_RMA", "tbl_module_repairs", "prikey = " & rst!LastOne & "")
End If

 

[pbaldy]

I guess I don't understand the data. Do you need the max prikey to get the aps_rma?

 

[Frothingslosh]

I'm sorry to jump in, but what jumps out at me is this line:

Me.txt_test = DLookup("APS_RMA", "tbl_module_repairs", "prikey = " & rst!LastOne & "")

It looks to me like rst!LastOne is text, and he's trying to concatenate it into the where clause.

If LastOne really is text, I'd suggest changing that line to...

Me.txt_test = DLookup("APS_RMA", "tbl_module_repairs", "prikey = '" & rst!LastOne & "'")

...and see if that fixes it. If it's not text, then there's no need to concatenate in that empty string at the end.

 

[pbaldy]

You must have missed:

Heres what I have now that is working:

Though you are correct about the empty string at the end.

 

[Frothingslosh]

Whoops, yeah, I did. That's what I get for trying to read things before my coffee infusion is complete!

 

[Adam McReynolds]

I guess I don't understand the data. Do you need the max prikey to get the aps_rma?

Yes. It would not pull the last RMA for me so I changed it to prikey because it is a sequential number(Auto) and this allowed me to then use dlookup to find the field from that record. It is really slow though, so that's why I was inquiring about a better method other than dlookup, such as criteria in the OpenRecordset. Sorry if I made it more confusing but I am unfamiliar so far in using Recordset type code. Thanks for all the help.

 

[Adam McReynolds]

I'm sorry to jump in, but what jumps out at me is this line:

Me.txt_test = DLookup("APS_RMA", "tbl_module_repairs", "prikey = " & rst!LastOne & "")

It looks to me like rst!LastOne is text, and he's trying to concatenate it into the where clause.

If LastOne really is text, I'd suggest changing that line to...

Me.txt_test = DLookup("APS_RMA", "tbl_module_repairs", "prikey = '" & rst!LastOne & "'")

...and see if that fixes it. If it's not text, then there's no need to concatenate in that empty string at the end.

Prikey is an autonumber and whenever I put the code the way you suggested I always get an error. For some reason this way always works but I am never sure to the reason why.

 

[Frothingslosh]

Yeah, the way I suggested was the way you concatenate a string. To force a quote mark, either use an apostrophe inside the quotes, """" , or Chr(34).

So this:

"Text = '" & variable & "'"
=
"Text = " & """" & variable & """"
=
"Text = " & Chr(34) & variable & Chr(34)

That said, since Prikey is an autonumber field, you wouldn't use quotes at all; you'd just do it like this:

"Prikey = " & variable

All you're adding by adding "" at the end is an empty string, which is literally the same as adding 0 to a number.

That said, it's not harming anything by being there, and not slowing the code appreciably, so I wouldn't worry about it. All it offfends is my sense of tidiness!

 

[pbaldy]

Would a DLookup() against the second query here work?

http://www.baldyweb.com/LastValue.htm

 

[WayneRyan]

Adam,

I'm confused, what field are you trying to update?

Replace the ??? with your field name.

Dim rst As DAO.Recordset
Set rst = CurrentDb.OpenRecordset("SELECT * " & _
                                  "From tbl_module_repairs " & _
                                  "Order by APS_RMA Desc")
rst.Edit
rst!??? = Me.txt_test
rst.Update

Wayne