The Openform action was canceled...worked OK with mdb not with accdb

[attman]

Hi guys -

I work at a school and have a form that lists the classnames in a listbox. I have code that allows the selection of multiple items before it opens the form with pupils from the selected classes.

This code worked when my database was mdb(2000) but now it is accdb (2007) it comes up with the error above.

Pointers please?

Many thanks


Private Sub Command47_Click()
On Error GoTo Err_Command47_Click

Dim stDocName As String

stDocName = "Pupil Profile"
Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim varItem As Variant
Dim strCriteria As String
Dim strSQL As String

Set db = CurrentDb()
Set qdf = db.QueryDefs("qryMultiSelect")

For Each varItem In Me!List44.ItemsSelected
strCriteria = strCriteria & "," & Chr(34) & Me!List44.ItemData(varItem) & Chr(34)
Next varItem

If Len(strCriteria) = 0 Then
MsgBox "You did not select anything from the list" _
, vbExclamation, "Nothing to find!"
Exit Sub
End If

strCriteria = Right(strCriteria, Len(strCriteria) - 1)

strSQL = "SELECT * FROM Pupils " & _
"WHERE Pupils.Form IN(" & strCriteria & ");"

qdf.SQL = strSQL

Rem DoCmd.OpenQuery "qryMultiSelect"

DoCmd.OpenForm stDocName

Set db = Nothing
Set qdf = Nothing

Exit_Command47_Click:
Exit Sub

Err_Command47_Click:
MsgBox Err.Description
Resume Exit_Command47_Click

End Sub

 

[CJ_London]

you'll need to check your references in the VBA.

Pre 2007 DAO was the standard, post ADODB was the standard so if you want to use DAO you need to link to the DAO reference Microsoft DAO 3.6 Library

Alternatively try removing the DAO part of the definition

 

[attman]

Thanks for the reply. I'm a programmer of old and managed to get this code working initially by asking our friend Mr Google and found the code when I did so.

I have to say I'm a little flummoxed. I removed the references to DAO (not really sure what DAO is though) and it didnt work. Tried replacing DAO with ADODB but then I think I've realised that you dont use ADODB.Database (recordset maybe?) but dont know what to do from there.

In my database I have a query called qrymultiselect that initially works but when the code has been run against it it no longer works. After that I start receiving message (The Microsoft Database Engine cannot find the input table or query ". Make sure it exists etc...)

Thanks again.

 

[attman]

On further investigation it looks as though I had a corruption somewhere.

I tried to open the target form and it gave an error about the query, so i changed the record source to point directly at the table. This too gave an error saying it couldnt find the table. The database is split so I removed the link to the table and readded it and all is now fine. Thank you VERY much for you help and support.