Add-Ins Problem

[scking]

I've built an Add-In that works great. Changed all instances of CurrentDb to CodeDb and was successful. I do, however, have a problem where I use DMax against a query to get the highest available number in a table. The purpose is to autogenerate unique number for problem reports. In subsequent processing I increment the strlastActionNbr by one and apply it. My problem is that this does not work on an Add-In even though it works fine otherwise. I'm trying to find a way of running the query using the CodeDb object and placing the value in strLastActionNbr. Anybody have any solutions?

strLastActionNbr = DMax("LongIR", "qryIncidentReports", "[ProjID] = " & "'" & .LstProjID & "'&quot

Note: LongIR is a numberic value using the full 4 characters of the year and concatenating it with the sequence number. 20010001
ProjId merely uses the value selected on the lstProjID listbox.

 

[RickSpr]

If the query you want to access isn't in the add-in database, I don't think you can get DMax() to find it.
How about this:

strLastActionNbr = LastActionNbr(.lstProjID)
...
Private Function LastActionNbr(ProjectID as Variant) As String
    Dim db As Database, rst As Recordset
    Dim SQL As String

    Set db = CurrentDb
    SQL = "SELECT Max(LongIR) As LastIR FROM qryIncidentReports " _
        & "WHERE ProjID = '" & ProjectID & "';"
    Set rst = db.OpenRecordset(SQL, dbOpenDynaset)
    If rst.EOF Then
        LastActionNbr = "0000"
    Else
        LastActionNbr = rst!LastIR
    End If
    rst.Close
    Set rst = Nothing
    Set db = Nothing
End Function

I haven't played around with Add-Ins yet, but it seems to me this should work. (Read: This is untested code! )