Has SELECT stopped working?

[mud2]

I'm trying to run a :DoCmd.RunSQL" INSERTS and CREATES work, but SELECTS don't! I get a message..."...requires an SQL..."
Here's a sample.

Private Sub Command1_Click()
Dim sql1 As String
Dim sql2 As String
Dim Sqltest As String
Dim sql3 As String
Dim Sql4 As String

Sqltest = "SELECT * FROM Name_Age;"
sql1 = "Create Table SQL_Return ( Count Number);"
sql2 = "Select count(*) from Name_Age;"
sql3 = "Insert into SQL_Return VALUES (444);"
Sql4 = "SELECT age FROM Name_Age;"

'DoCmd.RunSQL sql1 'OK
'DoCmd.RunSQL sql2 ' "Requires an sql statement ..."
'DoCmd.RunSQL sql3 'OK
'DoCmd.RunSQL Sql4 ' "Requires an sql statement ..."
'DoCmd.RunSQL "Insert into SQL_Return VALUES (222);" 'OK
'DoCmd.RunSQL "SELECT name, age FROM Name_Age;" ' "...Requires an.. '.

End Sub

I'm probably too close to see the error!

[Norie]

You don't use RunSQL for SELECT queries, that method is meant for action queries like INSERT, UPDATE etc.

[cath_hopes]

Hi there - I had a similar problem and couldn't get my SQL Select to work, then I discovered that the DoCmd.RunSQL command does not work for non-action queries. A "select" query is a non-action query. "Action"
queries are "append", "delete", “make-table", etc. type of queries.
Sorry I can't help you more - I still struggling myself to find an alternative!! About to re-post my issue.
Good Luck!
Catherine

[Norie]

Catherine

What are you actually trying to achieve?

[mud2]

Catherine...and others: The SELECT was the first part of an attempt to return the results of a SELECT in a form that VBA could use. I've DONE it by
Running an INSERT INTO....SELECT and then accessing the inserted into table. What a lot of mickey mouse!

[cath_hopes]

Hi Norie,

I'm still stuck (its been a couple of days now!) and I'm sure there's an easy solution that I just can't see!!
Anyway, I have two forms based, both based on the same table: Property which has a primary key "Property ID". Form1 asks the user to select a property using the a combo button. The code behind this button is:

DoCmd.Openform "Property Form", , , , , , "[Property ID];[House Name/ Number];[Address Line 1];[Date record created]"

This opens form2 and form2 should then show all the editable record details for the selected property but instead each field show #Name? instead.

What do I need to do to correct this?
Hope you can help!

Thanks,
Catherine

[tebule]

Hi Norie,

I'm still stuck (its been a couple of days now!) and I'm sure there's an easy solution that I just can't see!!
Anyway, I have two forms based, both based on the same table: Property which has a primary key "Property ID". Form1 asks the user to select a property using the a combo button. The code behind this button is:

DoCmd.Openform "Property Form", , , , , , "[Property ID];[House Name/ Number];[Address Line 1];[Date record created]"

This opens form2 and form2 should then show all the editable record details for the selected property but instead each field show #Name? instead.

What do I need to do to correct this?
Hope you can help!

Thanks,
Catherine
Why don't you link the forms to the table and put the fields on the form that way or you can link to a query and that will work too. Otherwise you would have to do the insert into.

[cath_hopes]

I combined the two forms into one with a combo in the header for the user to select a record. The combo is linked to a query so that the columns within it have data sorted usefully. So I've a solution.
What do you mean by 'the insert into'?
Thanks again.

[tebule]

You can base the rest of the fields in the form off of a query that uses the data in to combo box. on the after update. Here is a great site that I use for sql reminders. I hope it helps. There is a section on insert into if you have more questions after reading that, please let me know. But it sounds like you are trying to pull data off of what the user selects in selection box. If this is not correct, please let me know.

[tebule]

Here is the code to make that work... Leave your control properties unbound. Then in the record set move the data to the screen:

This sets up query & the recordset:

strSQLInput = "SELECT tblCash_Detail.Cash_ID, tblCash_Detail.C_Maturities, tblCash_Detail.C_Dividends, " & _
ect......
"FROM tblCash_Detail " & _
"WHERE (((tblCash_Detail.Trade_Date)= #" & dDate1 & "#) "
Set rsADOInput = New ADODB.Recordset
rsADOInput.Open strSQLInput, CurrentProject.Connection, adOpenStatic, adLockOptimistic
With rsADOInput
If rsADOInput.EOF Then 'Error Handeling



This puts the values back into the table if they have changed.

Sub New_Entry()
' this sub is called when the user clicks 'Yes' that
' they would like to create a new entry for the date that they are on.
bolGotError = False
On Error GoTo error_Handler
'SQL to create a new entry
strSQLNew = "insert into tblCash_Detail " & _
"([Fund_ID], [Trade_Date], [C_Post_Date]) " & _
"values ('" & "#" & Me.txtFirstDate & _
"#, " & "#" & Me.txtSecondDate & "#);"

DoCmd.RunSQL strSQLNew
'SQL to find the new entry
strSQLInput = "SELECT [Cash_ID], [C_Maturities], & _ (ect... all your fields)
"FROM tblCash_Detail " & _
"WHERE (((tblCash_Detail.[First_Date])= #" & Me.txtFirstDate & "#) " & _
"AND ((tblCash_Detail.[C_Second_Date])=#" & Me.txtSecondDate & "#) " &

'Same as the record set in the afterupdate. moves the information in the table to the form
Set rsADOInput = New ADODB.Recordset
rsADOInput.Open strSQLInput, CurrentProject.Connection, adOpenStatic, adLockOptimistic
With rsADOInput
If rsADOInput.EOF Then
MsgBox "Error In creating new entry. Please contact DAG for help."
End If
Me.txtCashId = rsADOInput!Cash_ID
Me.txtMaturities = rsADOInput!C_Maturities
.... ect... the rest of the fields...
End With 'rsADOInput
rsADOInput.Close
Set rsADOInput = Nothing
Exit Sub
error_Handler:
MsgBox "Error while creating a new Cash Detail Record. " & _
vbCrLf & "Please contact DAG for assistance.", , "Application Error"
bolGotError = True
End Sub


Hope this helps.