Calling a query from VBA

[DaveMere]

Hi,

I'm having problems calling a simple query from the VBA associated with one of my forms. The code goes thus:

Private Sub Form_Current()

Dim rs As Recordset
Dim db As Database
Dim qryMaxAlp As QueryDef

If Me.NewRecord = True Then
    'query database here and find out next alp_key
    Set db = DBEngine.Workspaces(0).Databases(0)
    Set qryMaxAlp = db.QueryDefs("qryMaxAlpKey")
    Set rs = qryMaxAlp.OpenRecordset()
Else

End If
End Sub

The problem occurs on Set qryMaxAlp = db.QueryDefs("qryMaxAlpKey") where I get the error "3265 Item not found in this collection". However the query does exist, and when I run it independantly works fine (it brings back the maximum primary key of a linked table.

So, what am I missing?

 

[allan57]

Dim dbs As Database
Dim rstGetQueryData As Recordset

If Me.NewRecord = True Then

Set dbs = currentdb
Set rstGetQueryData = dbs.OpenRecordset("qryMaxAlpKey")
rstGetQueryData.movelast
rstGetQueryData.movefirst
msgbox rstGetQueryData!alp_key

Else

End If
End Sub

or you could dlookup:-

msgbox dlookup("[alp_key]", "qryMaxAlpKey")

 

[DaveMere]

Thanks Allan, your approach worked fine. I always seem to have problems calling queries from code . . . perhaps this will see the end of them.

Cheers!