How to view SQL statement run by Access DoCmd.OpenForm

1
votes

I have been tasked with converting an Access application to .NET. I know nothing about Access. My issue is that I need to be able to see the actual query being invoked via VBA from a form.

The VBA code uses a command called DoCmd.OpenForm to open a form and display records. The VBA code is inside an event handler for a button. When clicked, the button opens a form using the DoCmd.OpenForm and its associated parameters. The fourth parameter in the DoCmd.OpenForm is WhereCondition according to MSDN, and the developer used this to insert a WHERE clause (without the word WHERE per MSDN). The WHERE clause used is ProjectNumber = Me![ProjectNumber].

The problem is that I cannot see the rest of the query, i.e., what is the entire SELECT statement and how does Access know what fields to select to populate the form opened by the DoCmd.OpenForm command? Is there anyway to find that out? To add to the confusion, the target form opened by DoCmd.OpenForm has it's own query associated with it. In other words if the form is opened in design mode, under Data-->Record Source, it has a query listed. I can see the syntax for that query, just not the "partial" one that is called from the VBA code using the DoCmd.OpenForm command. It seems like there are two different queries used to populate the target form.

I have tried ODBC tracing and it did not show anything. Since the application talks to a SQL Server backend, I may be able to have our DBA turn on tracing, but this is not a great option since the application is huge and has this scenario present in many forms and I'm not sure it will even catch anything. Any help is greatly appreciated.

2
vbams-access
open the form in layout or design view. The properties for the form includes a data tab. Look in that data tab for the Record Source for the form. That should give you what you're looking for. - tlemaster
Both good answer below but also be aware that there are more ways to manipulate a form's SQL recordsource. Check the OnOpen, OnLoad and OnCurrent events. See if any global variables are set just before calling the form or if the OpenForm OpenArgs argument is used. That is an indication that more information is being sent to the form than just opening it. - SunKnight0

2 Answers

1
votes

The WhereCondition parameter of DoCmd.OpenForm is an additional WHERE clause to the form's recordsource. Or an additional filter, however you want to call it.

E.g. if the form recordsource is

SELECT * FROM Projects WHERE PrjStatus = 1

and WhereCondition is

ProjectNumber = Me![ProjectNumber]

then the actual SELECT statement for the opened form will be

SELECT * FROM Projects WHERE PrjStatus = 1 AND ProjectNumber = Me![ProjectNumber]
1
votes

The form may, or may not be based on a query. Often the base table name is used. So a form data source can be:

A base table name
A saved query
Have embedded sql right in the data source.

To see the underlying data source of a given form, open the form in design mode, display the property sheet (if property sheet not already displayed then just hit alt-enter key. In the data tab you see the data source:

Eg this:

enter image description here

The above source name can be a base table, a name of a saved query or even raw sql right in that record source such as this:

enter image description here

In above, you can hit the small [...] button (that appears when you put the cursor into the data source and that will show the query builder. Eg this:

enter image description here

And from above, you can flip to the sql view by choosing SQL as above shows.

And the result is this:

enter image description here

So OFTEN there is no SQL or query specified for the form but only the base table. (so if Access asks you to invoke the query builder MAKE SURE YOU ANSWER NO!, since if you continue then you are making changes to the application and changing the forms data source to a embedded query for the form.

However in ALL cases, the “where” clause of the openform command is added to the data source as a filter. And if the sql already has a where clause, then they are combined (exactly like using a sql select + where on a SQL server view)