cannot run SQL select from VBA 2

[ProgramError]

I have the following code which works until i try to run the SQL string. I know it works with action statements but can anyone tell me what to use for Select statements?

Private Sub Frame2_Click()
Dim strSQL As String
Dim strWHERE As String
Dim strORDER As String
Dim strFIELD As String
  strSQL = "SELECT tblEquipment.MSNumberText, tblEquipment.RecID, tblEquipment.RecNum, tblEquipment.Make, tblEquipment.Model, tblEquipment.Serial, tblEquipment.PCode, tblEquipment.Status, tblEquipment.StatusDate, tblEquipment.ASTIprefix, tblEquipment.Site, tblEquipment.Building, tblEquipment.Room, tblEquipment.DeptCode, tblEquipment.Supplier, tblEquipment.OrderNo, tblEquipment.[Order Funding], tblEquipment.Cost, tblEquipment.PurchaseDate, tblEquipment.GuarPeriod, tblEquipment.LifeExp, tblEquipment.ServContExp, tblEquipment.ServLevel, tblEquipment.Charge, tblEquipment.Comments, tblEquipment.Loanable, tblEquipment.PatTest, tblEquipment.Make1, tblEquipment.Model1, tblEquipment.Description1, tblEquipment.ESU1, tblEquipment.PolyNo1, tblEquipment.[Year Bought1], tblEquipment.Location1, tblEquipment.Building1, tblEquipment.Room1, tblEquipment.DeptCode1, tblEquipment.Contact1, tblEquipment.Department1, tblEquipment.Supplier1, tblEquipment.OrderNo1" & _
         " FROM tblEquipment"
  Select Case Frame2
  Case 1
    strFIELD = "serial"
  Case 2
    strFIELD = "MSNumberText"
  Case 3
    strFIELD = "RecNum"
  End Select
  strWHERE = " WHERE (((tblEquipment." & strFIELD & ") In (SELECT [" & strFIELD & "] FROM [tblEquipment] As Tmp GROUP BY [" & strFIELD & "] HAVING Count(*)>1 )))"
  strORDER = " ORDER BY tblEquipment." & strFIELD
  strSQL = strSQL & strWHERE & strORDER
  DoCmd.OpenQuery strSQL, acViewNormal
End Sub

Ian Mayor (UK)
Program Error
Programming is 1% coding, 50% error checking and 49% sweat as your application bombs out in front of the client.

 

[Remou]

You would need to use a QueryDef.

[tt]'Using an existing query and overwriting the SQL
Set qdf=CurrentDB.QueryDefs("qryQuery")
qdf.SQL=strSQL[/tt]

[tt]'Creating a new query
Set qdf = CurrentDb.CreateQueryDef("qryQuery", strSQL)[/tt]

You can then OpenQuery qryQuery.

 

[ProgramError]

Thanks Remou

After adding a private qdf as querydef to the form and using the the first information you gave me I managed to get it to work.


Ian Mayor (UK)
Program Error
Always make your words sweet and nice. Because you never know when you may have to eat them.