How to dynamically add a control button to a form

[dhlao]

I want to achieve the below tasks:

1. Create a form by VBA code
2. Dynamically add a control button to this form
3. Define the "Caption" for this button
4. Assign click event to this button
5. The click event is to delete the form itself
6. Form_Close() also delete the form itself

Now, only task 1 and 2 success, but don't know how to do task 3 to 6

Below is my code. Please help

Sub CreateTmpForm()
  Dim testfrm As Form
  Dim ctlCloseBtn As Control
  Dim frmOldName As String

  Set testFrm = CreateForm
  testFrm.Caption = "This is a test"

  Set ctlCloseBtn = CreateControl(testFrm.Name, acCommandButton, , , , 4960, 1440, 960)
  ctlCloseBtn.Name = "btnDelTmpFrm"
  
  frmOldName = testFrm.Name
  DoCmd.Close acForm, testFrm.Name acSaveYes
  DoCmd.Rename "tmpForm", acForm, frmOldName
End Sub

The last 3 lines is use to rename the form only

 

[JHB]

...
3. Define the "Caption" for this button

See below:

  ctlCloseBtn.Caption = "Click me"

4. Assign click event to this button

See below:

  ctlCloseBtn.Properties.Item("OnClick") = "[Event Procedure]"
  strcode = "Private Sub btnDelTmpFrm_Click()" & vbCr _
  & "  MsgBox(""" & "You Clicked Me" & """)" & vbCr _
  & "End Sub"
  
  frmOldName = testfrm.Name
  Set mdl = Forms(frmOldName).Module
  mdl.InsertText strcode

5. The click event is to delete the form itself
6. Form_Close() also delete the form itself

You can't destroy an object when code still is running in it, so 5. and 6. are impossible.

The whole code:

  Dim testfrm As Form
  Dim ctlCloseBtn As Control
  Dim frmOldName As String
  Dim mdl As Module

  DoCmd.DeleteObject acForm, "tmpForm"

  Set testfrm = CreateForm
  testfrm.Caption = "This is a test"

  Set ctlCloseBtn = CreateControl(testfrm.Name, acCommandButton, , , , 4960, 1440, 960)
  ctlCloseBtn.Name = "btnDelTmpFrm"
  ctlCloseBtn.Caption = "Click me"
  ctlCloseBtn.Properties.Item("OnClick") = "[Event Procedure]"
  strcode = "Private Sub btnDelTmpFrm_Click()" & vbCr _
  & "  MsgBox(""" & "You Clicked Me" & """)" & vbCr _
  & "End Sub"
  
  frmOldName = testfrm.Name
  Set mdl = Forms(frmOldName).Module
  mdl.InsertText strcode
    
  DoCmd.Close acForm, testfrm.Name, acSaveYes
  DoCmd.Rename "tmpForm", acForm, frmOldName

 

[JHB]

Hmm - did you try the code?

 

[dhlao]

Hmm - did you try the code?

Thanks a lot JHB,

I never use .Properties.Item("OnClick") = "[Event Procedure]" before. Thanks for teaching me.

Since delete the form is not possible. I change the design of this part in my Access database.

The target of this form is to show the duplicate record(s) in the list after compare 2 tables. Then the user click the close button to delete the form.

Now, i change to create the list and the button in an existing form but set Visible to FALSE. When duplicate record(s) found, change the list and the button to visible. After user click the close button, change the list and button back to invisible.

 

[JHB]

Thanks a lot JHB,

You're welcome, good luck.

 

[HiTechCoach]

dhlao,

Curious, why would you want to create a form and then have it delete on close?

I have never heard of that being done on a regular basis or at all.

Warning: There is a limit to how many time you can add and delete objects. IIRC, once you have reach the limit you have to rebuild the database by importing all the objects into a new blank database. That is probably why it is not common practice.

 

[JHB]

...
Warning: There is a limit to how many time you can add and delete objects. IIRC, once you have reach the limit you have to rebuild the database by importing all the objects into a new blank database.

I know it could be a problem by controls on forms/reports but never heard it for objects in a databases, do you've a link to where it is described?
Then it would also be a problem when temporary tables are created and destroyed again!

 

[HiTechCoach]

Yep. That is why I never delete objects are part of the normally functionality.

For temp table I use a "side end". I create on the fly a new separate database in the same folder as the front end. They are side-by-side thus a "side end". When the front end close is deletes the side end database.

 

[JHB]

Yep. That is why I never delete objects are part of the normally functionality.

Do you have a link to where it is described?

 

[JHB]

Hmm - I just created 500000 temporary tables (and deleted them again) in a loop without problem, (just what I expected).
So I'm curious where you have the information from that it is a limit to how many time you can add objects in a database's lifetime.
There is a limit of how many objects you can have in a database - 32768 objects at a time.

 

[HiTechCoach]

Hmm - I just created 500000 temporary tables (and deleted them again) in a loop without problem, (just what I expected).
So I'm curious where you have the information from that it is a limit to how many time you can add objects in a database's lifetime.
There is a limit of how many objects you can have in a database - 32768 objects at a time.

I have not tested this with an ACE (.accdb) database. This may have been fixed in the new database format.

It was an issue with JET (.mdb). Don't remember the version. I actually could duplicate the issue.

Curious, when you did your test have many records were you loading into the temp table each time? Did you get much database bloat?

 

[JHB]

..
It was an issue with JET (.mdb). Don't remember the version. I actually could duplicate the issue.

It was in a MDB database I did the test but running in a MS-Access 2010 version, I don't think it would change anything if I run it using a MS-Access 2000 version, (so until you or someone can convince me otherwise, I'd say your statement is not correct).

..
Curious, when you did your test have many records were you loading into the temp table each time? Did you get much database bloat?

Yes I did, but can't remember how much it grew, and it was only a small table with about 6 records and 5 fields.

 

[HiTechCoach]

JHB, it is not the version of Access but he version of the JET DLL's!

JET is an integral part of Windows XP and later. Windows Update will automatically install the JET updates. Even if you do not have a any version of Access installed. This is not true with ACE. So even if you were to install Access 2000 and open ti you would be using he latest JET version that has the fix.


Glad that the issue has been fixed for JET databases.

Did you test it with ACE (.accdb) database?

 

[JHB]

Coincidentally, I have two old computers standing:

• One with Windows 98 and MS-Access 2000 using MSJet40 - version 4.00.2927.17.
• And the other with Windows 95 and MS-Access 97 using MSJet35 - version 3.51.3328.0

Again I could not provoke some errors, but this time I only created 250000 tables, because it took some time (hours) to created them.

Glad that the issue has been fixed for JET databases.

From my tests, I would say it has never been an issue.