limiting SQL to return one record to the form

[RickH]

You can limit to one record returned in the Edit SQL part of Data Connections.
If you turn that on, any query that used to return multiple records now
errors out.
I can't find any info on how to properly implement a query using that feature.
I use dropdown listboxes for my query values. Unfortunately, they default to
nothing if you are populating them from a database table...so if some just
hits submit, they get an error.

Anyone try it before?

 

[Steve van Dongen [MSFT]]

You can limit to one record returned in the Edit SQL part of Data Connections.
If you turn that on, any query that used to return multiple records now
errors out.
I can't find any info on how to properly implement a query using that feature.

"Turn on"? There's nothing to "turn on" in Edit SQL. You can only
edit the SQL statement. You should only see an error while querying
the datasource if you have an error in your SQL statement. What SQL
did you enter?

I use dropdown listboxes for my query values. Unfortunately, they default to
nothing if you are populating them from a database table...so if some just
hits submit, they get an error.

That's intentional to ensure that a value is selected. Go to Tools |
Form Options..., click on the Edit Default Values button, and enter
default values for the fields if you want a value to be preselected..

Regards,
Steve

 

[Steve van Dongen [MSFT]]

There is a checkbox under Modify Table that allowa you to limit the records
returned, or maybe it just limits the number the form can see.
Anyway, if you set it to not let the form show more than one record, then,
of the query returns more than 1, you get an error popup.
I can find no doc on how this feature is meant to be used.

That option appears to dictate only whether the node that corresponds
to a table row is marked in the schema as a repeating node or not. It
does not affect or limit what SQL data is returned.

If you want only 1 row of data then you have to edit your SQL query to
only return 1 row. You can check or uncheck this option depending on
whether you want the node to be a repeating node or not.

Regards,
Steve