Need help debugging a Visual Basic/SQL routine - MS Access?

Question

I have the following routine that I call when I click on a button in a form:
Private Sub View_Report_Of_Features_And_Activites_Click()
On Error GoTo Err_View_Report_Of_Features_And_Activites_Click

Dim stDocName As String
Dim SQL As String

SQL = "SELECT All_Trial_Details.[Technology]" & _
"FROM [All_Trial_Details]" & _
"WHERE All_Trial_Details.[Activity Name]=ActivityNameComboBox;"

DoCmd.RunSQL SQL

If All_Trial_Details.[Technology] = "Java" Then
stDocName = "Java Activities And Features Report"
Else
stDocName = "CD Activities And Features Report"
End If

DoCmd.OpenReport stDocName, acPreview

Exit_View_Report_Of_Features_And_Activit:
Exit Sub

Err_View_Report_Of_Features_And_Activites_Click:
MsgBox Err.Description
Resume Exit_View_Report_Of_Features_And_Activit

End Sub

When I run this, I get an error that says that my SQL statement is invalid. Help.

Answer 1

If you're trying to reference a ComboBox, your SQL is going to assume that AcitivityNameComboBox is a field in your table. You'll need to quote out after the "=" and ampersand (concatenate) ActivityNameComboBox.Value after.

eg. [Activity Name]=" & Me.ActivityNameComboBox.Value & ";"

The "Me" and the "semi-colon" are just a formality.

The Data Analyst - http://www.squidoo.com/thedataanalyst

Answer 2

When you are assembling your SQL string most of the syntax is correct except where you are trying to read the contents of a combo box and equate it to the [Activity_Name] column.

you wrote:

"WHERE All_Trial_Details.[Activity Name]=ActivityNameComboBox;"

You need to add a space on either side of the equals..
Then break the string apart on the right side of the equal and concatenate the Value of the text/selected item in the combo box. and finally concatenate the semicolon to the end.

"WHERE All_Trial_Details.[Activity Name] = " & ActivityNameComboBox.Text & ";"


As a tip use the debug to print your sql string to the immeadiate window or assign it to a label so you can check it out and see if you have assembled the string correctly and it is what you expect....

Answer 3

i think get admission to 2003 databases have not got a connection with the DAO library by employing default. then you definately could desire to explicitly declare the variables like : Dim dbs As DAO.database If it nevertheless will enhance an errors, you are able to desire to reference the Microsoft DAO merchandise Library from procedures|References contained in the VBE examine those references the Microsoft get admission to merchandise Library, Microsoft DAO merchandise Library, and Microsoft DAO reference

Answer 4

We can't read the whole statement. Looks like you're missing quotes in first line.