make QRY, get data, Del QRY...Surely there's a better way

[Jbozward]

Hi all.

Just a quick question.....

I have a database which is constantly filling up, and a unique "Customer Complaint number" field. mad up from "CCN-xxxx/06" (where xxxx is the identifier.)

I have written a bit of code that accepts claims for different companies via email by searching through my inbox for new atttachments and importing what it finds (from a standard Excel sheet)

Thing is, when I add to my table, I have to find the last (max) "Customer Complaint number", add one and carry on from there.

To do this I have used;

    Dim dbs As DAO.Database
    Dim qdf As DAO.QueryDef
    Dim Updatetable As DAO.Recordset
    Dim strSQL As String
    Set dbs = CurrentDb
    strSQL = "SELECT Last(tblWarrantyData.DEXCCN) AS LastOfDEXCCN FROM tblWarrantyData;"
    Set qdf = dbs.CreateQueryDef("qryLastrecord", strSQL)
    Set Updatetable = dbs.OpenRecordset("qryLastrecord")
    Updatetable.MoveFirst
        lastrecord = Updatetable("LastOfDEXCCN")
    Updatetable.Close
    DoCmd.DeleteObject acQuery, "qryLastrecord"
    lastrecord = Mid(lastrecord, 6, (Len(lastrecord) - 8))

This works fine, and I have no complaints, but to make a query, run it and delete it seems wastefull.
Is there a way of getting this single piece of data other than this?

Ta

Bozzy

 

[reclusivemonkey]

You don't have to create the query; just use RunSQL to create your recordset, then close it when you have finished. It seems to me from your code you know what you are doing. Just search the forum for "Recordset" and I *think* most of the examples are done this way (i.e. without actually making the query).

 

[SammyJ]

No, you never need to use queries, go straight from SQL string to recordset. I'm always needing a value from the database from a piece of SQL code. You can get it down to one line, though it is not advisable to do so.

Dim dbs As DAO.Database
Dim rsUpdatetable As DAO.Recordset 'Sorry to rename this. Looked wrong
Dim strSQL As String

Set dbs = CurrentDb
strSQL = "SELECT Last(tblWarrantyData.DEXCCN) AS LastOfDEXCCN FROM tblWarrantyData;"
Set rsUpdatetable = dbs.OpenRecordset(strSQL)
lastrecord = rsUpdatetable("LastOfDEXCCN")
lastrecord = Mid(lastrecord, 6, (Len(lastrecord) - 8))

That will do it, as will this cheekier code: (again, I only use this in temporary work)

lastrecord = CurrentDB.OpenRecordset("SELECT Last(tblWarrantyData.DEXCCN) AS LastOfDEXCCN FROM tblWarrantyData;")(0)

Well, except for the trimming!

Sam.

 

[Jbozward]

Thanks for the help all.
I can't belive it can be completed in 1 line......awesome!

Boz