Using DoCmd.RunSQL to prepopulate a few fields on my opened form.

[nouveauricheinvestments]

Any ideas what is wrong with this query? I am trying to run a query
via code and use the returned results to populate a few fields in the
opened form DocumentATicket.

Private Sub DocumentTicket_Click()

Dim MyTempQuery As String
DoCmd.OpenForm "DocumentATicket"
MyTempQuery = "SELECT Account, Explanation, DAT, Manager FROM
ViewSelectedOpenTicket;"

DoCmd.RunSQL MyTempQuery

Form_DocumentATicket.Account.Value = MyTempQuery.Account
Form_DocumentATicket.Description = MyTempQuery.Explanation
Form_DocumentATicket.StartTime = MyTempQuery.DAT

End Sub

 

[Douglas J. Steele]

Several things are wrong.

First, you cannot use RunSQL with SELECT queries: it only works with action
queries (INSERT INTO, UPDATE, DELETE)

Second, you cannot refer to fields in a query like you are. Typically, you'd
either use DLookup, or open a recordset and retrieve the values of the
fields in the recordset.

However, looking at the SQL for MyTempQuery, you don't have any WHERE
clause, meaning that every row in ViewSelectedOpenTicket will be returned.
And since you have no ORDER BY clause either, any attempt to retrieve data
from the query will essentially return a random value.

 

[nouveauricheinvestments]

Several things are wrong.

First, you cannot use RunSQL with SELECT queries: it only works with action
queries (INSERT INTO, UPDATE, DELETE)

Second, you cannot refer to fields in a query like you are. Typically, you'd
either use DLookup, or open a recordset and retrieve the values of the
fields in the recordset.

However, looking at the SQL for MyTempQuery, you don't have any WHERE
clause, meaning that every row in ViewSelectedOpenTicket will be returned.
And since you have no ORDER BY clause either, any attempt to retrieve data
from the query will essentially return a random value.

I don't have any WHERE clause because the ViewSelectedOpenTicket query
is returning a single record based on a boolean column in my table.

Thank you for the info about the RunSQL statement. The help guide
says 'for an action query or a data definition query.' I assumed the
data definition meant a select query. That is my mistake.