How to dynamically create an Access 2003 form

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.
0
Utf
7/20/2007 7:30:02 AM
access.forms 6864 articles. 2 followers. Follow

5 Replies
1667 Views

Similar Articles

[PageSpeed] 36

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.

-- 
Allen Browne - Microsoft MVP.  Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Eaton" <Eaton@discussions.microsoft.com> wrote in message
news:551A7704-F6B6-4F7C-AE4E-D4ED66D1D14A@microsoft.com...
>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. 

0
Allen
7/20/2007 8:30:21 AM
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.

"Allen Browne" wrote:

> 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.
> 
> -- 
> Allen Browne - Microsoft MVP.  Perth, Western Australia
> Tips for Access users - http://allenbrowne.com/tips.html
> Reply to group, rather than allenbrowne at mvps dot org.
> 
> "Eaton" <Eaton@discussions.microsoft.com> wrote in message
> news:551A7704-F6B6-4F7C-AE4E-D4ED66D1D14A@microsoft.com...
> >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. 
> 
> 
0
Utf
7/24/2007 11:16:00 PM
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.



"Eaton" <Eaton@discussions.microsoft.com> wrote in message 
news:24641D10-54FA-4F76-BCBA-9CF90CA9F4DB@microsoft.com...
> 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.
>
> "Allen Browne" wrote:
>
>> 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.
>>
>> -- 
>> Allen Browne - Microsoft MVP.  Perth, Western Australia
>> Tips for Access users - http://allenbrowne.com/tips.html
>> Reply to group, rather than allenbrowne at mvps dot org.
>>
>> "Eaton" <Eaton@discussions.microsoft.com> wrote in message
>> news:551A7704-F6B6-4F7C-AE4E-D4ED66D1D14A@microsoft.com...
>> >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.
>>
>> 


0
wphx
7/25/2007 12:34:27 AM
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
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Eaton" <Eaton@discussions.microsoft.com> wrote in message
news:24641D10-54FA-4F76-BCBA-9CF90CA9F4DB@microsoft.com...
> 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.
>
> "Allen Browne" wrote:
>
>> 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.
>>
>> "Eaton" <Eaton@discussions.microsoft.com> wrote in message
>> news:551A7704-F6B6-4F7C-AE4E-D4ED66D1D14A@microsoft.com...
>> >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? 

0
Allen
7/26/2007 5:06:14 AM
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

"Allen Browne" wrote:

> 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
> Tips for Access users - http://allenbrowne.com/tips.html
> Reply to group, rather than allenbrowne at mvps dot org.
> 
> "Eaton" <Eaton@discussions.microsoft.com> wrote in message
> news:24641D10-54FA-4F76-BCBA-9CF90CA9F4DB@microsoft.com...
> > 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.
> >
> > "Allen Browne" wrote:
> >
> >> 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.
> >>
> >> "Eaton" <Eaton@discussions.microsoft.com> wrote in message
> >> news:551A7704-F6B6-4F7C-AE4E-D4ED66D1D14A@microsoft.com...
> >> >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? 
> 
> 
0
Utf
7/30/2007 7:12:00 AM
Reply:

Similar Artilces:

Outlook 2003: reply to all doesn't include self
I just installed Outlook 2003 (love it so far) but when I reply to all, my own address doesn't appear in the TO:, CC: or BC: fields -- and I want it to. In my old Outlook (2000 version) my address was included automatically (assuming it was in the original message I'm replying to) and in installing Outlook 2003 I migrated all my settings, so I don't know why it changed. More perplexingly, I can't figure out how to accomplish this. In sum: When I receive a multi-recipient email and reply to all, how do I rig Outlook 2003 so that my own address is automatically incl...

Outlook 2003 message store limits?
Getting message store has exceeded limits within Outlook 2003. The information store is still getting email, as checking it with webmail or Outlook 2000 works just fine. Does anyone know where or how to change the defaults message store on the Outlook 2003 client As far as i'm aware - there's no client-side settings of this type in Outlook. Are you running on an Exchange server? There are message store limits within Exchange. But you say you have no problem with Outlook 2000 or webmail, implying you just use Outlook as a Internet Mail client? Bobby >-----Origina...

Outlook 2003 won't receive email
When I try to send mail in Outlook 2003, I get a dialog box saying "The operation failed. An object could not be found." I want to make Outlook 2003 the default emailer but can't receive email! -- davidx Have you tried closing Outlook and deleting the <profile_name>.SRS file? (This is going to delete all Send/Receive settings. You will have to reconfigure any special settings you had.) Another option might be to create a new mail profile via the mail applet in the control panel. (Don't copy the existing profile. Make a new one and switch to it to see if ...

Connecting to boxes moved from 5.5 to 2003
I have a small organization, with about 60 clients and I'm working on an AD converison. I worked with a consultant to setup a new AD domain with the requisite trust with my old NT domain and the ADC. I have no problem moving test boxes over to the AD/Exchange03 machine, but I am not certain what the next steps in the process for accessing the moved boxes should be. I though I could simply "enable" the moved accounts I see in AD Users and Computers, but even after resetting pw's and enableing the acct, I am immediately challenged with a bad pw/username error when I atte...

Creating A List of Items Between Two Numbers
I have two worksheets in the same workbook, "Filter" and "Data" In worksheet Filter, Cell A1 is 40%, Cell B1 is 50%, Cells C1 to C100 is where your "magic formula" goes, referencing Cells A1 and B1. On worksheet Data, There is percentile data in BA13:BA6000 On worksheet Data, There are names in D13:D6000 -- In worksheet Filter, Cells C1:C100 I want to create a list of all names (worksheet Data Column D) that contain values (worksheet Data Column BA) between the range of numbers provided (worksheet Filter, cells A1 and B1). Thank you ! O...

Functions in form
I have made an invoice using a form, and have it nearly completed, I have two functions that I don't know how to create. One is simple, I need an automatic number created for each separate invoice printed. And the hard one is I need a function that will give me customer information (name, address, city, state, zip) when I type in a customer code like mcg = mcgregor, 123 fake st., d-town, WA 99999. I have literally looked everywhere on how to do this, even forums. "Zera" <Zera@discussions.microsoft.com> wrote in message news:7DD602A9-E33D-4489-9188-DED9006AD882@micr...

Outlook 2003 HTML formatting problem
After upgrading clients to Outlook 2003 we've started to experience a strange problem with emails formatted in HTML. The scenario goes like this. 1. A local user creates an HTML formatted email and sends it to an external recipient. 2. The external recipient replies to the original message. 3. Originator replies to the reply 4. The external recipient receives the HTML formatted email with portions of the text grossly enlarged. 5. Upon review, it would appear that the external HTML client dropped a (.) changing the font size from 10.0 to 100 causing the enlarged text. Editing the HT...

IE settings from Outlook 2003...
Hi I am trying to get the language information from Internet Explorer When i load this webside inside of Outlook, I can't get the HTTP_ACCEPT_LANGUAGE property If I load it from Internet Explore, outside Outlook, the property is ok Below is the asp code <META HTTP-EQUIV="Refresh" CONTENT="5; "> <% Response.CacheControl = "no-cache" Response.Expires = -1 Response.Write(Now()) Response.Write("<BR>") TheLanguage = Request.ServerVariables("HTTP_ACCEPT_LANGUAGE") TheLanguages = Split (TheLanguage, ",") Numberoflangua...

2003 Excel Macros Run Slowly
I upgraded to Office 2003 and have a macro in one of my spreadsheets. When I execute this macro it runs very, very slowly i.e. mulitple minutes vs 2 sec on Office 2000. Is there anything I should be doing to improve the speed? Regards, Ed Close excel, clean up your windows temp folder. set calculation to manual, run your code, reset it to what it was. Turn screenupdating off when you start and on when you finish. turn the display of pagebreaks off when you start. Lots of my macros have this at the top: Dim CalcMode As Long CalcMode = Application.Calculation Applicatio...

OWA, access meeting types
Hello: We are looking for a way to modify meeting labels (the color of the meeting entry in the Outlook Calendar) in Outlook Web Access. We have been looking around for 3rd party products that may support this, but so far we have not found anything. Does anybody know of any products, that will give users access to the "label" attribute for meetings in Outlook Web Access? Thanks in advance.... -- Chris ...

How to remove the Outlook 2003 system tray icon
Hi, Somebody please tell me that there is an option to remove the rather redundant icon Outlook 2003 places in the system tray area! I've been through the options many times but can't seem to find any way of getting rid of it. Thanks muchly Paul You can't get rid of it. You can however hide it. Just right click the Task Bar. Choose Properties. Choose Customize. You can set the Outlook Icon to always hide (in Windows XP only). -- Patricia Cardoza Outlook MVP www.cardozasolutions.com Author, Special Edition Using Microsoft Outlook 2003 ***Please post all replies to the new...

Quirky Reply and Forward behavior
I'm receiving email from an AOL user that likes to sprinkle her messages with those little smiley faces. The source looks like this; src="http://cdn-cf.aol.com/se/smi/0201e05fd0/04"></DIV></FONT></BODY></HTML> When I attempt to reply or forward the email, Outlook hourglasses for about a minute before the reply or forward dialog comes up (less the smiley). This does not happen on messages without the smiley. Is this behavior because the AOL server isn't coughing up the image within a respectable amount of time? Is there a setting in Outlook th...

Neswreader in Outlook 2003...
Hello, I am trying to enable the newsgroup reader in Outlook 2003 but I having difficulty. I am running WinXP Pro with service pack 2 installed. I've also installed all required updates for WinXP Pro and Office 2003. I've read the following from Microsoft Office Outlook help: The first time you use the newsreader, you must add the News command to the Go menu. 1.. Add the News command to the Go menu. How? 1.. On the Standard toolbar (toolbar: A bar with buttons and options that you use to carry out commands. To display a toolbar, press ALT and then SHIFT+F10.), click the To...

Exchange 2003 and DC
I've just inherited a network setup where the DC and the Exchange 2003 is on the same box. I've built another box as an additional DC. I want to run dcpromo on the exchange server and make it a member server only. Are there any ramifications in doing this and how do I install the exchange connector on the new DC? TIA, Dylan Taylor Changing the role after Exchange 2003 has been installed is not actually supported. See http://support.microsoft.com/kb/822179 -- Neil Hobson Exchange MVP For Exchange news, links, and tips, check: http://www.msexchangeblog.com "Dylan T...

Can not right click items in Outlook 2003 #2
Hello, Has anyone come across a problem in Outlook 2003 11.6359.6360 SP1 where you can not right click items and get the quick menu items? So for example, if I right click a mail message nothing comes up, versus the reply, mark as read, etc items. Thanks Much Lots of people are reporting it, but we don't have a fix yet. Are you using a script blocker? -- Diane Poremsky [MVP - Outlook] Author, Teach Yourself Outlook 2003 in 24 Hours Coauthor, OneNote 2003 for Windows (Visual QuickStart Guide) Author, Google and Other Search Engines (Visual QuickStart Guide) Outlook Tips: http://ww...

Excel front ends to Access Database
We are in the process of converting all our old 95 databases and I have one with excel front ends. Upon converting the DB to Access 2000 and reentering the Excel front end the DB is unrecognized. Any suggestions? Thanks! ...

creating tmp files
Have Excel 2000 whenever click on save file it creates tmp file too. How can I prevent excel from creating these temp files. Thanks Chandra You cant "chandrak@futaiusa.com" <anonymous@discussions.microsoft.com> wrote in message news:9a9701c4343c$e2f2c6a0$a401280a@phx.gbl... : Have Excel 2000 whenever click on save file it creates tmp : file too. How can I prevent excel from creating these temp : files. : Thanks : Chandra But accoding to documents on excel if the file is saved without error messages it renames the temp file with xls why do I still see the xls and tmp fi...

How stop duplicate fields when creating a Report in Access?
no metter what method I use to create the report - it throws double fields into the report. You manually delete one of each and proceed and it continues to add double fields. We did not have this issue in 2003 version. WHat is the problem and how do we fix it? Are you talking about the record source (a query) for the report listing fields twice? If so, check the underlying query and see if it has an asterisk (return all fields) in SELECT clause of the query. It is a property of queries in design view (Output all fields). Set this to No to eliminate the fields showing ...

how to create button commande to refresh data in query in excel 2.
I have a excel spreedsheet that contain external data. I would like to put a button in the excel sheet to update the sheet without doing right click and refresh. My user here are very dummies. Jean Francois If this is external data then bringing it in should launch the external data toolbar. They will only need to click the exclamation (!) mark. Seems like re-inventing the wheel. If you must then put a button on the worksheet and assign it to this macro Sub refreshdata() Dim wks As Worksheet Dim qryTab As QueryTable Set wks = ActiveSheet For Each qryTab In wks.QueryTables qryTab.Refr...

Customize main page on Microsoft Dynamics
Can the Metrics on Microsoft Dynamics be customized to show the user logged on, their sales for 'their' customers A - Z, gross profit [date range], summary of sales report. A basic sales dashboard that offers needed data. Our company is sales driven, each sales person paid on commissionable sales. Great piece of real estate to utilize. ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow t...

Exchange 2003 Load balance and cluster
We have two identical machines that we want to load balance and cluster for exchange 2003. the load balance does not want to start. any reason why? Oh yah. I guess my question is that if it is possible to run the laod balance and the cluster on the same two servers "Ashu" wrote: > We have two identical machines that we want to load balance and cluster for > exchange 2003. the load balance does not want to start. any reason why? No,does not support NLB and cluster on same machine! You should rub cluster with your exchange 2003! -- Jammyù�ٴ� "Ashu" &l...

Drag Email into Access Form
As a way of monortoing Email useage i would like to be able to drag an email from Outlook into a memo field on an access form. Currently it only drags the header, how do i get the body of the email message dragged across as well? ...

Dynamicly change spreadsheet tab names depending on cell value
Is it possible to change tab names dynamicaly ? For example i have 31 tab (1 for each day of the month) These tabs are named - 1,2,3 e.t.c Is it possible to define rule that would ad "!" to day which is weekend day. In other words is it possible to dynamicly change tab name depending from value of cell ? If this is possbile how do i do this ? Right-click on the spreadsheet tab, select View Code and paste this in:- Private Sub Worksheet_Activate() ActiveSheet.Name = Range("A1").Value End Sub To test this I put the date in cell B2 and this formula into A1:- =TEX...

Microsoft Access Tables and Forms
I have made up a table and then created a form from it. The table does not reflect the changes made in the form. I have done everything that I can think of in order to make this work (input info, go to next record, close form), but table still will not update. I want to create reports, but I have no totals showing up, because they will not show in the table. Desperately in need of HELP!!! Helen Helen In a standard Access design, you create a table, (optionally, you create a query based on the table), you create a form bound to the table (optionally, to the query). When you open the for...

Create entities in c#
Hi, I wonder if it's possible to create new entities (like activities here) in an aspx page in c# called from a crm button (a web service would apparently be too slow for my purpose here). I've been looking and found out about some Microsoft.crm packae which I can't seem to be able to find. Any ideas? Thanks So you want to create a custom aspx page (w/ c# code behind) and when the user clicks a button it creates an Activity for example? You're going to have to use the Crm 3.0 Web Service. Unless you know enough about the CRM DB to do straight TSQL inserts which you...