Code - Can someone tell me what is wrong?

[Guest]

Private Sub Command103_Click()
Dim varItem As Variant
Dim strMaterial As String
Dim strSQL As String

If SysCmd(acSysCmdGetObjectState, acQuery, "qryMaterialListQuery") =
acObjStateOpen Then
DoCmd.Close acQuery, "qryMaterialListQuery"
End If

For Each varItem In Me.lstMaterial.ItemsSelected
strMaterial = strMaterial & ",'" & Me.lstMaterial.ItemData(varItem) & "'"
Next varItem
If Len(strMaterial) = 0 Then
strMaterial = "Like '*'"
Else
strMaterial = Right(strMaterial, Len(strMaterial) - 1)
strMaterial = "IN(" & strMaterial & ")"
End If

strSQL = "SELECT tblMaterial.* FROM tblMaterial " & _
"WHERE tblMaterial.[MaterialName] " & strMaterial

Dim db As DAO.Database
Dim qdf As DAO.QueryDef

Set db = CurrentDb
Set qdf = db.QueryDefs("qryMaterialListQuery")
qdf.SQL = strSQL
Set qdf = Nothing
Set db = Nothing

DoCmd.OpenQuery "qryMaterialListQuery"
DoCmd.Close acForm, Me.Name

End Sub

 

[Guest]

I tried your code, and it works perfectly.

What is the problem you are getting

 

[Guest]

Dim db As DAO.Database
Dim qdf As DAO.QueryDef


Item not found in this collection. (Error 3265)
An attempt to reference a name in a collection failed.

Possible causes:

The object does not exist in this collection. Make sure the object is
appended to a collection before referencing it.
There is more than one object with this name in the collection; using its
name is an ambiguous reference. Reference the object by its ordinal position
in the collection (for example, Recordsets(3)).

I am not sure why this is happening.
--
S

I tried your code, and it works perfectly.

What is the problem you are getting

--
Good Luck
BS"D

Private Sub Command103_Click()
Dim varItem As Variant
Dim strMaterial As String
Dim strSQL As String

If SysCmd(acSysCmdGetObjectState, acQuery, "qryMaterialListQuery") =
acObjStateOpen Then
DoCmd.Close acQuery, "qryMaterialListQuery"
End If

For Each varItem In Me.lstMaterial.ItemsSelected
strMaterial = strMaterial & ",'" & Me.lstMaterial.ItemData(varItem) & "'"
Next varItem
If Len(strMaterial) = 0 Then
strMaterial = "Like '*'"
Else
strMaterial = Right(strMaterial, Len(strMaterial) - 1)
strMaterial = "IN(" & strMaterial & ")"
End If

strSQL = "SELECT tblMaterial.* FROM tblMaterial " & _
"WHERE tblMaterial.[MaterialName] " & strMaterial

Dim db As DAO.Database
Dim qdf As DAO.QueryDef

Set db = CurrentDb
Set qdf = db.QueryDefs("qryMaterialListQuery")
qdf.SQL = strSQL
Set qdf = Nothing
Set db = Nothing

DoCmd.OpenQuery "qryMaterialListQuery"
DoCmd.Close acForm, Me.Name

End Sub

 

[fredg]

Dim db As DAO.Database
Dim qdf As DAO.QueryDef

Item not found in this collection. (Error 3265)
An attempt to reference a name in a collection failed.

Possible causes:

The object does not exist in this collection. Make sure the object is
appended to a collection before referencing it.
There is more than one object with this name in the collection; using its
name is an ambiguous reference. Reference the object by its ordinal position
in the collection (for example, Recordsets(3)).

I am not sure why this is happening.

Your Access version number would have been useful here.
Do you have a reference set to the DAO library?

Open any VBA code window. Click on Tools + References.
Is there a reference checked to a DAO library?
If not, scroll down the list and place a check mark in the DAO library
with the highest number available (probably 3.6).
Click OK.

Try and run this code again.

 

[Guest]

I have Microsoft Office Access 2003. Yes, the reference is already checked.
That is why I am confused. I also tried to find another object with this
name in the collection and don't find anything.

I puzzled.

Thanks for all your help.

 

[Guest]

I also tried replacing this code with the second code below.

Dim db As DAO.Database
Dim qdf As DAO.QueryDef

Set db = CurrentDb
Set qdf = db.QueryDefs("qryMaterialListQuery")
qdf.SQL = strSQL
Set qdf = Nothing
Set db = Nothing

Replacement Code:

Dim cat As New ADOX.Catalog
Dim cmd As ADODB.Command

cat.ActiveConnection = CurrentProject.Connection
Set cmd = cat.Views("qryMaterialListQuery").Command
cmd.CommandText = strSQL
Set cat.Views("qryMaterialListQuery").Command = cmd
Set cat = Nothing

The reference checked is Microsoft ADO Ext. 2.8 for DDL and Security

I get an error on the line:

dim cmd as ADODB.Command

Compile error:
User-defined type not defined

Sorry, this way over my head. I actually got the code from
www.fontstuff.com/access/acctut18pfv.htm

Thanks.