How to dynamically create an Access 2003 form

[Guest]

I have a view in SQL server which will have a variable number of columns; I
want to be able to dynamically create an Access form based on this view so
that the form will pick up the new columns.
Any ideas?

Thanks.

 

[Allen Browne]

In general, you don't want to create forms on the fly in an Access
application. It prevents you creating an MDE for the end user.

An alterantive might be to create a form with the maximum number of text
boxes you could need, and save it unbound. In the form's Open event
procedure, examine the SQL statement of the view (which is presumably in the
RecordSource property of the form), assign the Control Source of each text
box to match the fields, hide the unused ones (Visible = False), and set the
location (Top, Left) and Width to match (in twips.)

If this is not a serious application, using OpenQuery to open the data sheet
might suffice.

 

[Guest]

Thanks for getting back to me. The OpenQuery idea was good. I then tried
using DoCmd.OpenStoredProcedure "spXXX 'parm1", but it didn't recognize the
the procedure. When I omitted the parm, it prompted me for it and them ran. I
don't want to be prompted. Some ideas?

Thanks.

 

[wphx]

I'm thinking about a 'plug-in' kind of concept to allow users to extend the
use of the database but use the same front-end. Is it possible to open
forms in another.mdb file without creating another instance of microsoft
access running? I.e. can I open a remote form from within the current
database?

Thankyou for any thoughts.

 

[Allen Browne]

One way we do this in Access is to open a form where the user can enter the
parameter, and then name the parameter like this:
[Forms].[Form1].[Text0]

Not sure if that will apply for you.

 

[Guest]

Thanks, Allen.

I've gotten rid of the passed parameter. When I use the OpenStoredProcedure
method in VBA, Access open the result set of the stored procedure in a
separate window, not a child window. How can I use this method and display th
result set in a child window inside the overall application? Actually, I'd
like to show the result set on a tab inside a tab control.
Hope to hear back ASAP.

Thanks. Eaton

One way we do this in Access is to open a form where the user can enter the
parameter, and then name the parameter like this:
[Forms].[Form1].[Text0]

Not sure if that will apply for you.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Thanks for getting back to me. The OpenQuery idea was good. I then tried
using DoCmd.OpenStoredProcedure "spXXX 'parm1", but it didn't recognize
the
the procedure. When I omitted the parm, it prompted me for it and them
ran. I
don't want to be prompted. Some ideas?

Thanks.