SQL keeps getting deleted!???

H

hermanko

Hi,

I have an issue with my SQL queries getting deleted after running some
VBA code. I've read the other posts and they dont really help my
situation. Can anyone please take a look at my code and see what the
problem is?

I have a form called "Preview Data" with a combo box listing 3
different types of reports a user can view. "All funds" is a report
based on a query that is built thru VBA code because it depends on the
"Business Unit" in question (also shown in a text box on the same
form). The other two reports are based on queries that are created
normally. They are "Duplicate Fund Name (sameProxy)" and "Duplicate
Fund Name (different Proxy)".

The SQL for these latter two queries, respectively, are:

SELECT [Duplicate Fund Name].*
FROM [Duplicate Fund Name]
WHERE ((([Duplicate Fund Name].[Fund Name]) In (SELECT [Fund Name] FROM
[Duplicate Fund Name] As Tmp GROUP BY [Fund Name],[Proxy] HAVING
Count(*)>1 And [Proxy] = [Duplicate Fund Name].[Proxy])))
ORDER BY [Duplicate Fund Name].[Fund Name], [Duplicate Fund Name].Date;

SELECT [Duplicate Fund Name].*
FROM [Duplicate Fund Name]
WHERE ((([Duplicate Fund Name].[Fund Name]) Not In (SELECT [Fund Name]
FROM [Duplicate Fund Name] As Tmp GROUP BY [Fund Name],[Proxy] HAVING
Count(*)>1 And [Proxy] = [Duplicate Fund Name].[Proxy])))
ORDER BY [Duplicate Fund Name].[Fund Name], [Duplicate Fund Name].Date;

These two SQL's keep getting erased when i do the following on my form:
I have an EXPORT command button that calls VBA code (shown below), as
well as a PREVIEW command button that calls very similar code (also
shown after). I can execute the EXPORT code with each query/report, and
I can PREVIEW the "All Funds" report, but once i click on the other
two, i get the error "Query must have at least one destination field"
and then i go to check my query and those two are empty!

Private Sub cmd_export_Click()

On Error GoTo Err_Handler

' Declare variables
Dim db As DAO.Database
Dim qdf1 As DAO.QueryDef
Dim strBU As String
Dim strSQL As String

' Get the database
Set db = CurrentDb()

' Set the stored query
Set qdf1 = db.QueryDefs("All Funds")

' Store selected business unit in text string
strBU = [Forms]![Preview Data]![txt_viewdata]

' Build the new SQL statement incorporating the string

strSQL = "SELECT [Main Database].ID, [Main Database].[Fund Name], [Main
Database].Proxy, [Main Database].[Business Unit], [Main
Database].[Historical Data Range], [Main Database].Beta, [Main
Database].[R-Squared], [Main Database].[Mean Tracking Error], [Main
Database].Comments, [Main Database].Date, [Main Database].[Yes/No] FROM
[Main Database] WHERE ((([Main Database].[Business Unit])=" & Chr(34) &
strBU & Chr(34) & ")) ORDER BY [Main Database].[Fund Name];"

' Apply the new SQL statement to the query
qdf1.SQL = strSQL

' Export the selected report to Excel
If Me!cbo_preview = "All Funds" Then
DoCmd.OutputTo acOutputQuery, "All Funds", acFormatXLS
End If

If Me!cbo_preview = "Duplicate Funds Using Different Proxy" Then
DoCmd.OutputTo acOutputQuery, "Duplicate Fund Name (different
Proxy)", acFormatXLS
End If

If Me!cbo_preview = "Duplicate Funds Using Same Proxy" Then
DoCmd.OutputTo acOutputQuery, "Duplicate Fund Name (same Proxy)",
acFormatXLS
End If

' Empty the memory
Set db = Nothing
Set qdf1 = Nothing

Exit_Handler:
Exit Sub

Err_Handler:
MsgBox Err.Description
Resume Exit_Handler

End Sub
--------------------------------------------------------

Private Sub cmd_preview_Click()

On Error GoTo Err_Handler

' Declare variables
Dim db As DAO.Database
Dim qdf1 As DAO.QueryDef
Dim strBU As String
Dim strSQL As String

' Get the database
Set db = CurrentDb()

' Set the stored query
Set qdf1 = db.QueryDefs("All Funds")

' Store selected business unit in text string
strBU = [Forms]![Preview Data]![txt_viewdata]

' Build the new SQL statement incorporating the string

strSQL = "SELECT [Main Database].ID, [Main Database].[Fund Name], [Main
Database].Proxy, [Main Database].[Business Unit], [Main
Database].[Historical Data Range], [Main Database].Beta, [Main
Database].[R-Squared], [Main Database].[Mean Tracking Error], [Main
Database].Comments, [Main Database].Date, [Main Database].[Yes/No] FROM
[Main Database] WHERE ((([Main Database].[Business Unit])=" & Chr(34) &
strBU & Chr(34) & ")) ORDER BY [Main Database].[Fund Name];"

' Apply the new SQL statement to the query
qdf1.SQL = strSQL

' Open the selected report and maximize
If Me!cbo_preview = "All Funds" Then
DoCmd.OpenReport "All Funds", acViewPreview, , , acWindowNormal
DoCmd.Maximize
End If

If Me!cbo_preview = "Duplicate Funds Using Different Proxy" Then
DoCmd.OpenReport "Duplicate Funds Using Different Proxy",
acViewPreview, , , acWindowNormal
DoCmd.Maximize
End If

If Me!cbo_preview = "Duplicate Funds Using Same Proxy" Then
DoCmd.OpenReport "Duplicate Funds Using Same Proxy", acViewPreview,
, , acWindowNormal
DoCmd.Maximize
End If

' Empty the memory
Set db = Nothing
Set qdf1 = Nothing

Exit_Handler:
Exit Sub

Err_Handler:
MsgBox Err.Description
Resume Exit_Handler

End Sub
-----------------------------------------------

This is extremely frustrating as I dont know what is happening. Should
i use different variable names? please help if you can :)

Herman
 
J

John Spencer

Try commenting out the line

Set qdf1 = Nothing

What happens when you do that?

Hi,

I have an issue with my SQL queries getting deleted after running some
VBA code. I've read the other posts and they dont really help my
situation. Can anyone please take a look at my code and see what the
problem is?

I have a form called "Preview Data" with a combo box listing 3
different types of reports a user can view. "All funds" is a report
based on a query that is built thru VBA code because it depends on the
"Business Unit" in question (also shown in a text box on the same
form). The other two reports are based on queries that are created
normally. They are "Duplicate Fund Name (sameProxy)" and "Duplicate
Fund Name (different Proxy)".

The SQL for these latter two queries, respectively, are:

SELECT [Duplicate Fund Name].*
FROM [Duplicate Fund Name]
WHERE ((([Duplicate Fund Name].[Fund Name]) In (SELECT [Fund Name] FROM
[Duplicate Fund Name] As Tmp GROUP BY [Fund Name],[Proxy] HAVING
Count(*)>1 And [Proxy] = [Duplicate Fund Name].[Proxy])))
ORDER BY [Duplicate Fund Name].[Fund Name], [Duplicate Fund Name].Date;

SELECT [Duplicate Fund Name].*
FROM [Duplicate Fund Name]
WHERE ((([Duplicate Fund Name].[Fund Name]) Not In (SELECT [Fund Name]
FROM [Duplicate Fund Name] As Tmp GROUP BY [Fund Name],[Proxy] HAVING
Count(*)>1 And [Proxy] = [Duplicate Fund Name].[Proxy])))
ORDER BY [Duplicate Fund Name].[Fund Name], [Duplicate Fund Name].Date;

These two SQL's keep getting erased when i do the following on my form:
I have an EXPORT command button that calls VBA code (shown below), as
well as a PREVIEW command button that calls very similar code (also
shown after). I can execute the EXPORT code with each query/report, and
I can PREVIEW the "All Funds" report, but once i click on the other
two, i get the error "Query must have at least one destination field"
and then i go to check my query and those two are empty!

Private Sub cmd_export_Click()

On Error GoTo Err_Handler

' Declare variables
Dim db As DAO.Database
Dim qdf1 As DAO.QueryDef
Dim strBU As String
Dim strSQL As String

' Get the database
Set db = CurrentDb()

' Set the stored query
Set qdf1 = db.QueryDefs("All Funds")

' Store selected business unit in text string
strBU = [Forms]![Preview Data]![txt_viewdata]

' Build the new SQL statement incorporating the string

strSQL = "SELECT [Main Database].ID, [Main Database].[Fund Name], [Main
Database].Proxy, [Main Database].[Business Unit], [Main
Database].[Historical Data Range], [Main Database].Beta, [Main
Database].[R-Squared], [Main Database].[Mean Tracking Error], [Main
Database].Comments, [Main Database].Date, [Main Database].[Yes/No] FROM
[Main Database] WHERE ((([Main Database].[Business Unit])=" & Chr(34) &
strBU & Chr(34) & ")) ORDER BY [Main Database].[Fund Name];"

' Apply the new SQL statement to the query
qdf1.SQL = strSQL

' Export the selected report to Excel
If Me!cbo_preview = "All Funds" Then
DoCmd.OutputTo acOutputQuery, "All Funds", acFormatXLS
End If

If Me!cbo_preview = "Duplicate Funds Using Different Proxy" Then
DoCmd.OutputTo acOutputQuery, "Duplicate Fund Name (different
Proxy)", acFormatXLS
End If

If Me!cbo_preview = "Duplicate Funds Using Same Proxy" Then
DoCmd.OutputTo acOutputQuery, "Duplicate Fund Name (same Proxy)",
acFormatXLS
End If

' Empty the memory
Set db = Nothing
Set qdf1 = Nothing

Exit_Handler:
Exit Sub

Err_Handler:
MsgBox Err.Description
Resume Exit_Handler

End Sub
--------------------------------------------------------

Private Sub cmd_preview_Click()

On Error GoTo Err_Handler

' Declare variables
Dim db As DAO.Database
Dim qdf1 As DAO.QueryDef
Dim strBU As String
Dim strSQL As String

' Get the database
Set db = CurrentDb()

' Set the stored query
Set qdf1 = db.QueryDefs("All Funds")

' Store selected business unit in text string
strBU = [Forms]![Preview Data]![txt_viewdata]

' Build the new SQL statement incorporating the string

strSQL = "SELECT [Main Database].ID, [Main Database].[Fund Name], [Main
Database].Proxy, [Main Database].[Business Unit], [Main
Database].[Historical Data Range], [Main Database].Beta, [Main
Database].[R-Squared], [Main Database].[Mean Tracking Error], [Main
Database].Comments, [Main Database].Date, [Main Database].[Yes/No] FROM
[Main Database] WHERE ((([Main Database].[Business Unit])=" & Chr(34) &
strBU & Chr(34) & ")) ORDER BY [Main Database].[Fund Name];"

' Apply the new SQL statement to the query
qdf1.SQL = strSQL

' Open the selected report and maximize
If Me!cbo_preview = "All Funds" Then
DoCmd.OpenReport "All Funds", acViewPreview, , , acWindowNormal
DoCmd.Maximize
End If

If Me!cbo_preview = "Duplicate Funds Using Different Proxy" Then
DoCmd.OpenReport "Duplicate Funds Using Different Proxy",
acViewPreview, , , acWindowNormal
DoCmd.Maximize
End If

If Me!cbo_preview = "Duplicate Funds Using Same Proxy" Then
DoCmd.OpenReport "Duplicate Funds Using Same Proxy", acViewPreview,
, , acWindowNormal
DoCmd.Maximize
End If

' Empty the memory
Set db = Nothing
Set qdf1 = Nothing

Exit_Handler:
Exit Sub

Err_Handler:
MsgBox Err.Description
Resume Exit_Handler

End Sub
-----------------------------------------------

This is extremely frustrating as I dont know what is happening. Should
i use different variable names? please help if you can :)

Herman
Click to expand...
 
J

Jon Ley

Hi there,

I had a similar issue in that the SQL of a query got deleted if I executed a
SendObject on the query. Never actually got to the bottom of why it happened
- just one of those great Mcrosoft "features" that I have learned to expect
every now and then. Anyway, a couple of suggested workarounds.

I ended up deleting and recreating the query every time I needed to use it,
and this is now working for me.

However, a simpler option for you could be to never change the SQL of your
query at all. Just build the query and save it the same as you have for the
other two, but include a parameter for the [Business Unit] field which looks
to the form by using

[Forms]![Preview Data]![txt_viewdata]

You export button code can then become:

Private Sub cmd_export_Click()

On Error GoTo Err_Handler

' Export the selected report to Excel
Select Case Me!cbo_preview
Case "All Funds"
DoCmd.OutputTo acOutputQuery, "All Funds", acFormatXLS
Case "Duplicate Funds Using Different Proxy"
DoCmd.OutputTo acOutputQuery, "Duplicate Fund Name (different
Proxy)", acFormatXLS
Case "Duplicate Funds Using Same Proxy"
DoCmd.OutputTo acOutputQuery, "Duplicate Fund Name (same Proxy)",
acFormatXLS
End Select

Exit_Handler:
Exit Sub

Err_Handler:
MsgBox Err.Description
Resume Exit_Handler

End Sub


By the way, when using object variables it's a good idea to put your 'Empty
the memory' code inside the Exit_Handler in case your main code errors after
these variables have been set. For example:

Exit_Handler:
On Error Resume Next 'you don't really want the Exit handler generating
errors!
Set db = Nothing
Set qdf1 = Nothing
Exit Sub


Hope this is all of some use to you.

Jon.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

SQL help 2
SQL help - queries very slow 1
complex SQL help needed 2
Queries on Forms for User Input 1
SQL help - update query to select one duplicate each 6
export excel data to access table using ADO 0
Duplicate record in Form and Subform 0
export vba not working 0

Top