VBA for query wont work

[shapman]

Hi

I have set up a form to run a query, setting the following VBA to pick up values from the combo boxes in the form. The only problem is that I cant seem to get this working. When i press the command button it won't even get past the Dim db As Database line.

Private Sub Command10_Click()
On Error GoTo Errorhandler

Dim db As Database
Dim qd As QueryDef
Dim vWhere As Variant

Set db = CurrentDb()

On Error Resume Next
db.QueryDefs.Delete "qry_email2"
On Error GoTo 0

vWhere = Null
vWhere = vWhere & " AND [Status]=" + Me.cbostatustype
vWhere = vWhere & " AND [Substatus]=" + Me.cboSubstatus
vWhere = vWhere & " AND [PublicationName]=" + Me.cboPub1
vWhere = vWhere & " AND [PublicationName]=" + Me.cboPub2
vWhere = vWhere & " AND [PublicationName]=" + Me.cboPub3

If Nz(vWhere, "") = "" Then
MsgBox "There are no search criteria selected." & vbCrLf & vbCrLf & _
"Search Cancelled.", vbInformation, "Search Canceled."
Else
Set qd = db.CreateQueryDef("qry_email2", "SELECT * FROM tblgeneralcontactdetails WHERE " & _
Mid(vWhere, 6))

db.Close
Set db = Nothing

DoCmd.OpenQuery "qry_email2", acViewNormal, acReadOnly

Me.Command10.Requery
End Sub

help! I am now proper stuck with this.

Thanks in advance
Shapman

 

[KeithG]

What error do you get? Try

Dim db as DAO.Database

 

[shapman]

What error do you get? Try

Dim db as DAO.Database

the message is
Compile Error:
user-define type not defined

This same message appears when i put the DAO on as well

thanks in advance

 

[KeithG]

Check your references and make sure the Microsoft DAO 3.6 Object library is checked.

 

[boblarson]

Check your references and make sure the Microsoft DAO 3.6 Object library is checked.

Because just as an FYI for you, for future reference:

Access 2000 - has ADO checked by default (not DAO)
Access 2002 - has ADO checked by default (not DAO)
Access 2003 - has both ADO and DAO checked by default.
Access 2007 - DAO checked by default.

 

[shapman]

Check your references and make sure the Microsoft DAO 3.6 Object library is checked.

hi, i have checked the DAO box as guided. It doesnt seem to want to run beyond the docomand below, also for a while it was asking me for parameters even though the parameters are being set by the combo boxes.

Private Sub Command10_Click()

Dim db As DAO.Database
Dim qd As QueryDef
Dim vWhere As Variant

Set db = CurrentDb()

On Error Resume Next
db.QueryDefs.Delete "qry_email2"
On Error GoTo 0

vWhere = Null
vWhere = vWhere & " AND [Status]=" + Me.cbostatustype
vWhere = vWhere & " AND [Substatus]=" + Me.cboSubstatus
vWhere = vWhere & " AND [PublicationName]=" + Me.cboPub1
vWhere = vWhere & " AND [PublicationName]=" + Me.cboPub2
vWhere = vWhere & " AND [PublicationName]=" + Me.cboPub3

If Nz(vWhere, "") = "" Then
MsgBox "There are no search criteria selected." & vbCrLf & vbCrLf & _
"Search Cancelled.", vbInformation, "Search Canceled."
Else
Set qd = db.CreateQueryDef("qry_email2", "SELECT * FROM tblgeneralcontactdetails WHERE " & _
Mid(vWhere, 6))

db.Close
Set db = Nothing

DoCmd.OpenQuery "qry_email2", acViewNormal, acReadOnly
End If

End Sub



thanks in advance