Runtime error 3044 on qry in VB

[sonikudi]

Hello,

I am trying to basically run the qry below, but every time i run it , it gives me an error saying query must have atleast one destination field. So i went under queries and saw that the TempQry was blank.. I thought that the following code would automatically create a TempQry with the StrSQL qry in it..I am not sure whats wrong with my code. My program is in Access.

Private Sub Findit_Click()


On Error GoTo qry

Dim qdf As QueryDef
Dim StrSQL As String

StrSQL = "SELECT * FROM [PCB Incoming_Production Results] " & _
"WHERE [PCB SS #]= #Me.txtserialno# ORDER BY Date; "


' Create new QueryDef object, don't really need it once the query exists

Set qdf = CurrentDb.CreateQueryDef("TempQry", StrSQL)
' Open query in Datasheet view.

qry:
'if query exists
Set qdf = CurrentDb.QueryDefs("TempQry")

DoCmd.OpenQuery qdf.Name
Set dbs = Nothing

DoCmd.Close acForm, "PCBserialno", acSaveNo
End Sub

 

[sonikudi]

thanks for any help!

 

[dhookom]

I would use a saved query and simply change its SQL property on the fly:

Private Sub Findit_Click()
  On Error GoTo qry
  Dim qdf As DAO.QueryDef
  Dim StrSQL As String

  StrSQL = "SELECT * FROM [PCB Incoming_Production Results] " & _
   "WHERE [PCB SS #]= """ & Me.txtserialno & """ ORDER BY [Date]; "


  ' Use an existing querydef. Don't delete it, just change its SQL property

  Set qdf = CurrentDb.QueryDefs("TempQry")
  ' Open query in Datasheet view.

  'if query exists
  qdf.SQL = strSQL

  DoCmd.OpenQuery qdf.Name
  DoCmd.Close acForm, "PCBserialno", acSaveNo
End Sub

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[sonikudi]

Thanks Duane ..it worked!

Do you know if writing the SQL in VB as opposed to having it in access is better...? because sometimes when my query is in access and the user enters a serial no thats not in the Db or for , i am not exactly sure what reason, the SQL statement of the query in Access get automatically delted...I tried to enter a serial no that wasn't in the Db and as of now it doesn't delete the query in VB..but for any other user action could this happen again for my SQL string statement in VB or something similar??

 

[dhookom]

I don't have a clue why a query would be deleted unless your code does this.

I generally create queries in the Access interface. However, when the SQL needs to regularly be updated, I use code to modify the SQL property.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[sonikudi]

Hmmm..okay. thanks.