Creating a Form Programmatically

[fulltime]

How can i create a form programmatically?

I read somewhere tat i can use the createForm function to create a form and i can also specify the template of an existing form in my DB.

CreateForm( , existing_form)

is the above code correct assuming existing_form exists in my DB?

In addition, how can i link the record source of the form to another query? say inputQuery?

Any advice is greatly appreciated.

FT

 

[Robert88]

Hi

Try this code;

Private Sub Form_Template_Click()

    Dim frm As Form
    
    'Create a new form based on the XXXXX form
    Set frm = CreateForm(, "existing_form")
    DoCmd.Restore
    
    'Set RecordSource property to inputQuery query
    frm.RecordSource = "inputQuery"

End Sub

I hope it helps.

Robert88

 

[fulltime]

hi robert, thks for your reply. However, i do encounter a few problems

Set frm = CreateForm(, "existing_form")

If the name of my form is English, i just use

Set frm = CreateForm(, "English")

Am i correct?

In any case, onli a BLANK form pops up in design view and i am required to REDESIGN the entire form again. This not wat i want, i want the codes to copy an existing format(i.e the textboxes, buttons) of the form, onli difference is tat it needs to change the record source...

Pls help cos its urgent...realli appreciate any form of assistance..

Regards
FT

 

[ansentry]

Why do you want to make a "copy" of the form using code?

Your could;
1>Make a copy of the form and change the record source
or
2> Use the same (Original) form and change the record source using code. You could use and option group, List box or combo for your choice of record source.

If you need help re selecting record source then post back.

 

[fulltime]

Why do you want to make a "copy" of the form using code?

Your could;
1>Make a copy of the form and change the record source
or
2> Use the same (Original) form and change the record source using code. You could use and option group, List box or combo for your choice of record source.

If you need help re selecting record source then post back.

Hi,
Bascially, I am done with my "Language" database, however, should the user wan to define a new LAnguage in my DB, i need to have a new form for it, tats y i need to make a copy of the form. Everything has to be done in coding, tats the requirement tat i have from my supervisor.

So ur option 1 is ruled out for me..

 

[ScriptMaster]

This is an old post but if someone was looking here is a quick, elegant solution.

Declare a public variable at the beginning of your code module.

Public frm As Form

Now you can do this in your procedure:

Set frm = New Form_Table1

frm.RecordSource = "SELECT TOP 1 * FROM Table2" ' Set your new record source, Table, Query or a SQL

DoCmd.Maximize

To close your form just do : Set frm = Nothing


Hope this helps!

Neo

 

[bcmarshall]

I was searching for something similar and came across this old thread which really relates. I want to programmatically generate a form on the fly which is physically identical to one in my database, but with a new name. I'll describe below.

Sorry for the long explanation but I've found that it really helps if those reading the post understand the background and reasoning.

The form I've created is a visual duplicate of a Message Box, same color background, no record source, a label for the message and an OK button to close the form. The caption of the label is generated by concatenating fixed text with temporary variables.

To explain functionality and reasoning, we have a busy office and when people leave for lunch or break they initiate a logoff routine in Access. It doesn't actually close Access, but sets them as inactive along with the reason for them being gone (Lunch, Break) and the time they're expected back in a table. When they return, a click of an "I'm Back" button clears the info from the table and everything continues as normal.

A timed routine runs every minute and compares the expected return time with current time. If the person has not returned and current time is later than the expected return time, the simulated Message Box pops up on admin users screens saying, "Jesse was expected back from Break 4 minutes ago at 10:25 AM and has not logged back in yet."

You can see how the message is composed of a concatenated value of fixed and variable text.

The reason I want to use a form instead of a Message Box is because several users may be away. I'd like a form to be created as needed for any user who is late because suppose an Admin user is away from his/her desk and the message above pops up. Jesse returns and clears the routine as indicated, but the message remains on screen. Admin user returns and doesn't know if the person has returned or not.

The only way I can send a specific command to close is if I'm using a form instead of a Message Box, so that's the reasoning behind it. When late user returns we want to close the form on Admin screens.

The reasoning behind wanting a new form for each is so that there can be several forms and messages open on Admin screens. I'm going to name the forms UserName() & "Late". Now I would have a specific form for each person, along with the ability to close it on Admin screens centrally when user returns to their work station.

So, in a nutshell, I want to duplicate existing form LunchBreakReminder, and then delete the duplicate after closing it when the user returns so that I don't have fourteen gazillion forms in the system.

Does anyone have suggestions on how to accomplish this?

 

[MarkK]

You just need one form that you can open multiple instances of, which are called "non-default instances." Creating forms programmatically doesn't really make sense for what you are trying to accomplish. You can look into it though: check out the CreateForm method of the Access.Application object, but it's a headache to do, and making a form show different data is sooooooo easy.

You just need a form to show different data and have a bunch of them open at the same time. Non-default instances will do this just fine.

 

[sneuberg]

I'd avoid creation or copying of forms. If you create forms and delete them you don't recoup the space until you compact and repair. I think doing a lot of this is an invitation to ending up with a corrupt database. Also I'm pretty sure you'll need full Access for this to work, i.e, you can't create or copy forms in a runtime environment.

I suggest you try this with continuous forms. Instead of creating or copying a form you would be adding records. Granted adding and deleting records can cause bloat also but I suspect less that you'd get by adding and deleting forms.

If continuous forms don't work then I suggest just creating a bunch of these forms, more than you'd ever need at once, and modify them in code to suit your needs at the moment.

 

[Galaxiom]

Sneuberg is correct. Adding a form requires the full version of Access. A simple rule of thumb with the operation of a database is that it should never involve design changes. "If is can't be done in runtime then it shouldn't be done".

Creating forms, adding controls etc (as opposed to simply changing properties of existing objects) are design changes.

 

[bcmarshall]

You just need one form that you can open multiple instances of, which are called "non-default instances." Creating forms programmatically doesn't really make sense for what you are trying to accomplish. You can look into it though: check out the CreateForm method of the Access.Application object, but it's a headache to do, and making a form show different data is sooooooo easy.

You just need a form to show different data and have a bunch of them open at the same time. Non-default instances will do this just fine.

Thank you, but I don't understand how I could use this for the application I intend. My reason for wanting a form in the first place was so that I could send a specific message to close that particular form when the user returns. Obviously I'd need a unique name to accomplish that.

How can I create unique names for multiple instances of the same form?

And if this is applicable, how do I create the "non-default instances" you referred to?

Thanks for your suggestion.

 

[bcmarshall]

I'd avoid creation or copying of forms. If you create forms and delete them you don't recoup the space until you compact and repair. I think doing a lot of this is an invitation to ending up with a corrupt database. Also I'm pretty sure you'll need full Access for this to work, i.e, you can't create or copy forms in a runtime environment.

I suggest you try this with continuous forms. Instead of creating or copying a form you would be adding records. Granted adding and deleting records can cause bloat also but I suspect less that you'd get by adding and deleting forms.

If continuous forms don't work then I suggest just creating a bunch of these forms, more than you'd ever need at once, and modify them in code to suit your needs at the moment.

Every work station is running the full version of Access 2007. We have no runtime environment.

And you will see that the compact/repair is not an issue for us.

I am constantly working on/improving the database. I work in my own copy, and when I've finished adding some new feature I compact and repair that copy and put it on the public drive. Every morning when each user logs in there's an automatic download of the latest copy to the local C: drive of the work station, and of course that is the fully compacted file. There's also a script on the desktop that will allow manual updates at any time should I make one available.

The point is that no user works with the same copy of the database for more than one work day. It's always overwritten by the latest version on login, and main file for that version is always fully compacted. It is only copied to the local hard drives of different machines, but is never run within the public drive, so that file would not experience any bloating.

Given this situation, do you still foresee any issues?

 

[bcmarshall]

Sneuberg is correct. Adding a form requires the full version of Access. A simple rule of thumb with the operation of a database is that it should never involve design changes. "If is can't be done in runtime then it shouldn't be done".

Creating forms, adding controls etc (as opposed to simply changing properties of existing objects) are design changes.

I understand your point and I can see the validity of the reasoning you offer. As I'm thinking about this during this discussion I think I may have come up with a simple and clean way to accomplish what I want. I'd like to bounce it off all of you for your consideration and comments.

Suppose I create three physically identical forms with unique names, all looking like a Message Box. I can't imagine ever needing more than three open on admin screens at any given moment. Keep in mind that the actual content/text is controlled by concatenating variables and fixed text. There is no record source for the form and no fields other than the label to populate.

I have a table already where the logout time, return time, and reason are currently saved. I can add another field to it and use it for saving the unique name of the form that I opened for that person. I can work the logic that would sequentially open the three available forms if needed, or just repeat opening the same form if there isn't more than one late return.

As long as I have saved the name of the form that I opened for that user in that table, then I can do a lookup to the form name field where the username matches the late return. Then it would be a simple matter to send the command to close that form.

I hope I've explained myself clearly. Does this make sense as a solution?

 

[Tieval]

Suppose I create three physically identical forms with unique names, all looking like a Message Box.

Have you considered using Message Boxes instead of forms?

 

[static]

Obviously I'd need a unique name to accomplish that.

How can I create unique names for multiple instances of the same form?

And if this is applicable, how do I create the "non-default instances" you referred to?

Thanks for your suggestion.

You can have multiple copies of the same form open at the same time, all displaying different data. something like

Dim frms As New Collection

Private Sub Command0_Click()
    Dim f As Form
    Set f = New Form_Form1
    f.Visible = True
    f.Caption = "hello. this form was opened @ " & Now
    frms.Add f
End Sub

Pop up screens are very annoying and not easy to manage. And as already said, you can do all of this in a query, with it's records displayed on a single form.

 

[sneuberg]

This Allen Browne Web page expands on static's post but hWnd is used in the code to distinguish instances of the form. Since you would want to be able to close the form by difference procedures you would probably want to modify this so that instances were identified by something else. Maybe the user name i.e., Environ("username").

But I concur with

Pop up screens are very annoying and not easy to manage. And as already said, you can do all of this in a query, with it's records displayed on a single form.

 

[bcmarshall]

Have you considered using Message Boxes instead of forms?

Yes. In my earlier post I explained that I want to be able to programmatically close specific messages, and only a form will allow that.

 

[bcmarshall]

Thank you all for your replies and help. I agree about popups. They can be annoying and distracting, but that's the way they want it done.

I'm going to experiment with the different methods offered and try to decide which is most effective.

No one responded to my own suggestion of identical forms and saving the form name in a table for use in a close command. Does that make sense?

 

[gemma-the-husky]

I think the bottom line is, it will be hard work, and I doubt it's anything many of us have ever considered doing.

It's one thing to create a query on the fly say, but it's hard to understand why you would ever need to create a form.

If you want different languages, then just change all the captions on the form when the form opens. If you want messages in different languages, then you could use a table with the translations, and load the correct text at the appropriate time. You will have to do something similar if you create a form - so just modifying an existing one is far easier.

What are you doing with system errors though? I can't see how you can get those in different languages at run time.

good luck anyway.

 

[sneuberg]

No one responded to my own suggestion of identical forms and saving the form name in a table for use in a close command. Does that make sense?

If you use the Allen Brown methods I think it might make sense to store the keys in a table.