Dynamic creation of fields based on results of a query?

[olivero]

Hi group,

Is there an easy way to make a form create the same set of fields dynamically, once for each record returned by a query?

I have a query that's being called from a form and the results are displayed in a subform in datasheet view. This is nice, but I want a better (and more visually appealing) way to interact with the query results.

Instead of displaying the results in datasheet view, I'd like each field in each row to be displayed in the subform using text boxes and check boxes.

Because the query can return any number of records, I can't use a statically created form. A row of fields needs to be created once for each record returned by the query, and each of these rows needs to be placed one after the other on the form.

Is the only way to do this to use VBA to loop through the query results, create a row for the first record, then a new row X number of pixels below the previous row for each following record?

Thanks for any suggestions!

Oliver

[Minion]

I'm pretty sure there is a way to add elements to a form, but doing so (especially if there were a large number of records) would be very system intensive. Wouldn't it be better to create a seperate continuous form that will display the sub data in a manner you like and then use that form as the sub form for the main form? In the past I have had nice looking results this way that will produce text boxes, check boxes, etc.

Hope this helps.

- Minion -

[ADezii]

Hi group,

Is there an easy way to make a form create the same set of fields dynamically, once for each record returned by a query?

I have a query that's being called from a form and the results are displayed in a subform in datasheet view. This is nice, but I want a better (and more visually appealing) way to interact with the query results.

Instead of displaying the results in datasheet view, I'd like each field in each row to be displayed in the subform using text boxes and check boxes.

Because the query can return any number of records, I can't use a statically created form. A row of fields needs to be created once for each record returned by the query, and each of these rows needs to be placed one after the other on the form.

Is the only way to do this to use VBA to loop through the query results, create a row for the first record, then a new row X number of pixels below the previous row for each following record?

Thanks for any suggestions!

Oliver

Minion has the only 'practical' solution to your problem.

[missinglinq]

"Is there an easy way to make a form create the same set of fields dynamically, once for each record returned by a query?"

No! And there is absolutely no reason to dos so. What you have described is, as Minion suggested, a Continuous View Form! It's not the Continuous Form that the Forms Wizard will produce, which looks very much like a Datasheet Form, but the elements of a Continuous Form can be arranged in any manner that you like, just like a Single View Form. You can adjust the amount of space that lies between each record or even have the fields of a single record appear on multiple lines if you prefer.

Welcome to TheScripts!

Linq ;0)>

[jaxjagfan]

Most forms (including your subform) have various views with datasheet (as you are currently doing), single, and continous as choices.

Back-up your database. Go into design view of the subform and switch the view to single (this is the best view when you want a professional look and feel to your application). This will automatically create textboxes for all of your fields. Change the controls to the desired type (Some controls are datatype specific). Save your work frequently and view it perioically to see how the users are going to see it.

As Minion stated continuous forms are not the recommended method of display - especially for large datasets.

[missinglinq]

jaxjagfan, please read threadsa little more carefully and in their entirety before posting responses!

Olivero's requirements:

A row of fields needs to be created once for each record returned by the query, and each of these rows needs to be placed one after the other on the form.

Obviously, using a Single View in the subform is not going to meet these requirement!

And Minion did not say:

As Minion stated continuous forms are not the recommended method of display - especially for large datasets.

What he did say was:

Wouldn't it be better to create a seperate continuous form that will display the sub data in a manner you like and then use that form as the sub form for the main form?

Linq

[olivero]

Thanks for the replies, I'll check out Continuous Forms and see if it will do what i need.

Cheers,
Oliver

[olivero]

Continuous Forms view is EXACTLY what I was looking for!

With a little tweaking and hiding of unwanted lines and borders, and after removing the ability to edit the fields, I'm perfectly happy!

Thanks again guys!

Oliver

[Minion]

Continuous Forms view is EXACTLY what I was looking for!

With a little tweaking and hiding of unwanted lines and borders, and after removing the ability to edit the fields, I'm perfectly happy!

Thanks again guys!

Oliver

I'm glad it worked out for you Oliver. Sometimes all we need is a bump in the right direction.

- Minion -