Error: Command isn't available in MDE/ADE database

[Razor1]

How can I determine what part of the code below is causing the error?
Command isn't available in MDE/ADE database

Thanks in advance.

Private Sub cmdOK_Click()
' Declare variables
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()
' Loop through the selected items in the list box and build a text string
If Me!lstCategory.ItemsSelected.Count > 0 Then
For Each varItem In Me!lstCategory.ItemsSelected
strCriteria = strCriteria & "tmp_InvRpts.Category = " & Chr(34) _
& Me!lstCategory.ItemData(varItem) & Chr(34) & "OR "
Next varItem
DoCmd.Close acForm, "frm_WhseCatSelect", acSaveNo

strCriteria = Left(strCriteria, Len(strCriteria) - 3)
Else
strCriteria = "tmp_InvRpts.Category Like '*'"
End If
' Build the new SQL statement incorporating the string
strSQL = "SELECT * FROM tmp_InvRpts " & _
"WHERE " & strCriteria & ";"
DoCmd.OpenReport "rpt_WhseOrderInv1", acViewDesign
Reports![rpt_WhseOrderInv1].RecordSource = strSQL
DoCmd.Close acReport, "rpt_WhseOrderInv1", acSaveYes
DoCmd.OpenReport "rpt_WhseOrderInv1", acViewPreview

' Empty the memory
Set db = Nothing
Set qdf = Nothing
End Sub

Razor1

 

[genomon]

A good start would be to run the code in breakpoint mode, stepping through it one line at a time. When you identify the offending line, post back...

Alcohol and calculus don't mix, so don't drink and derive.

 

[MoLaker]

Just a quick shot from the hip, but I don't think you can design or save a report in a MDE or ADE. One of the features of a MDE/ADE is that objects cannot be altered. (You don't say, But I'm assuming the database is a MDE or ADE)

 

[TheAceMan1]

How are ya Razor1 . . .

There's a space missing . . .

[blue]   & Me!lstCategory.ItemData(varItem) & Chr(34) & "[COLOR=black red] [/color]OR "[/blue]

. . . which will cause the following change:

[blue]strCriteria = Left(strCriteria, Len(strCriteria) - [red]4[/red])[/blue]

Instead of using [blue]OR[/blue], have a look at the SQL [blue]IN[/blue] clause . . .

See Ya! . . . . . .

Be sure to see thread181-473997
Also faq181-2886

 

[TheAceMan1]

Razor1 . . .

Here's an example of your code restructured using the [blue]IN[/blue] clause:

[blue]Private Sub cmdOK_Click()
   Dim SQL As String, itm, LBx As ListBox, DQ As String, Cri As String
   
   Set LBx = Me!lstCategory
   DQ = """"
   
   For Each itm In LBx.ItemsSelected
      If Cri <> "" Then
         Cri = Cri & "," & DQ & LBx.ItemData(itm) & DQ
      Else
         Cri = DQ & LBx.ItemData(itm) & DQ
      End If
   Next
        
   DoCmd.Close acForm, "frm_WhseCatSelect", acSaveNo
         
   If Cri <> "" Then
      Cri = "WHERE [Category] [purple][b]IN[/b][/purple] (" & Cri & ");"
   Else
      Cri = "WHERE [Category] Like " & DQ & "*" & DQ & ";"
   End If
   
   SQL = "SELECT * " & _
         "FROM tmp_InvRpts " & _
         Cri
   DoCmd.OpenReport "rpt_WhseOrderInv1", acViewDesign
   Reports![rpt_WhseOrderInv1].RecordSource = SQL
   DoCmd.Close acReport, "rpt_WhseOrderInv1", acSaveYes
   DoCmd.OpenReport "rpt_WhseOrderInv1", acViewPreview
   
   Set LBx = Nothing
   
End Sub[/blue]

See Ya! . . . . . .

Be sure to see thread181-473997
Also faq181-2886

 

[PHV]

DoCmd.OpenReport "rpt_WhseOrderInv1", [!]acViewDesign[/!]
The above isn't available in MDE/ADE database.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[boblarson]

You can get around this by just setting the underlying query of the report to

strSQL = "SELECT * FROM tmp_InvRpts"

And then build the WHERE Clause dynamically and use the DoCmd.OpenReport command to pass the where clause:

DoCmd.OpenReport "rpt_WhseOrderInv1", acViewPreview,, strWhere

Bob Larson
A2K,A2K3,A2K7,SQL Server 2000/2005,Crystal Reports 10/XI,VB6, WinXP, and Vista
Free Quick Tutorials and Samples:

http://www.btabdevelopment.com

 

[Razor1]

Thanks to all for your help.

Molaker was right on you can not design in and MDE.

Thanks again to all.

Razor1