Code does not work in MS Access 2010

[naungsai]

Dear Friends

Can somebody varify the following code for me? It works in MS Access 2003, but not in 2010.
It ask me to debug at line
Set qryDef = CurrentDb.QueryDefs(strQueryName)


Thanks

Option Compare Database
Private Sub Command2_Click()
Dim strCrit As String
Dim strCritNO As String     'Criteria for new old
Dim QueryName As String
Dim QueryName2 As String
Dim qryDef As DAO.QueryDef
Dim qryDef2 As DAO.QueryDef
Dim strSelect As String
Dim strSelect2 As String
 
strCrit = Me.cobCrit
strCritNO = Me.FirstDate
 
QueryName = "000MainService"
QueryName2 = "111OtherOR"
strNewT = newTable
strQueryName = QueryName
strQueryName2 = QueryName2
 
strSelect = "SELECT " & strCrit & " AS Period,"
strSelect = strSelect & strCritNO & " AS 1stDate,"
 
strSelect = strSelect & "[0000MainService].Code, [0000MainService].CaseMx, [0000MainService].Sex, [0000MainService].Age, [0000MainService].Risk1, [0000MainService].Risk2, [0000MainService].ProjSite, [0000MainService].Project, [0000MainService].Staff, [0000MainService].DorO, [0000MainService].Diagnosis, [0000MainService].DrugSex, [0000MainService].NDist, [0000MainService].SDist, [0000MainService].NSReturn, [0000MainService].DWater, [0000MainService].Condom, [0000MainService].DCSL, [0000MainService].FUCSL, [0000MainService].PreCSL, [0000MainService].Testing, [0000MainService].PostCSL, [0000MainService].FCSL, [0000MainService].PSC, [0000MainService].YCSL, [0000MainService].GCSL, [0000MainService].Referral, [0000MainService].To, [0000MainService].Fr, [0000MainService].For, [0000MainService].Meal, [0000MainService].HE, [0000MainService].Remark, [0000MainService].BHCID,[0000MainService].Dx1  from 0000MainService  "
strSelect2 = "SELECT " & strCrit & " AS Period,"
strSelect2 = strSelect2 & "OtherOR.ORID, OtherOR.ProjSite, OtherOR.Project, OtherOR.ClientType, OtherOR.Male, OtherOR.Female, OtherOR.Condom, OtherOR.HE, OtherOR.Meal, OtherOR.Referral, OtherOR.To, OtherOR.For, OtherOR.Staff, OtherOR.Remark FROM OtherOR;"
 
Set qryDef = CurrentDb.QueryDefs(strQueryName)
Set qryDef2 = CurrentDb.QueryDefs(strQueryName2)
qryDef.SQL = strSelect
qryDef2.SQL = strSelect2
qryDef.Close
qryDef2.Close
'DoCmd.OpenQuery QueryName, acNormal, acEdit
'DoCmd.OpenQuery QueryName2, acNormal, acEdit
End Sub

 

[vbaInet]

What is the error message?

 

[naungsai]

The error message is:

Run-time error '3265':
Item not found in this collection.

 

[vbaInet]

Are you sure this query "000MainService" exists in your db?

 

[naungsai]

Yes. I do have it.

 

[vbaInet]

Can you upload a stripped down version of your db?

 

[naungsai]

Sorry for I can not send the original file.
In stead I am attaching the similar file. It has the same structure.
I will choose same value in both field of "00ReportedPeriod", then excute it.

Thanks in advanced.

 

[vbaInet]

I forgot that I haven't got Access 2010 on this laptop but I've got Access 2007 and that works. Perhaps someone with Access 2010 can give it a try.

 

[sortdata]

The code behind "command2" in your example database works. But it looks nothing like the code you have listed in your first comment.

 

[naungsai]

Dear Friends

Thank you for your quick response.
When you execute it, did the Period and 1stDate resulted in "000DTCMain" change according to 6monthly, Quarterly, Monthly or so on?

Thanks again!

 

[sortdata]

No, it's not going to change. you have not inserted your "strSelect" code into your query. When I ran "DoCmd.RunSQL (strSelect)" I received an error message. Tell me again what you are trying to do.

 

[naungsai]

Thanks
I have solved it with your suggestion.
Thanks to all our altruistic friend.