Creating form controls using MS Access VBA coding

This article illustrates a very simple approach to generate form controls using VBA coding in MS Access. A simple automation feature enables me to accomplish this task. Article provides adequate knowledge to exploit this feature and could be used in any kind of automation tool.

Creating form controls using MS Access VBA coding Fig 1.1

Fig 1.1

As shown in Fig 1.1 in next step first of all we have to create database with form named frmControlContainer .Form will contain a button named Form and its Controls. Moreover we will be creating a table along with common fields .Purpose of that table is to get bounded with form that is going to create during run time.

As shown in Fig 1.2 now create a table named tblCustomerOrder along with four different field.This table will be served as a record source for form to be generated using VBA Coding.

Creating form controls using MS Access VBA coding Fig 1.2

Fig 1.2

Finally when you click on button new form named Form1 will be created along with required controls in it.

Creating form controls using MS Access VBA coding Fig 1.3

Fig 1.3

VBA Code set to On Click event of button:-

Private Sub cmdCreateControls_Click() Dim frm As Form
Dim cntrlLabel As Control, cntrlText As Control
Dim intDatX As Integer, intDatY As Integer
Dim intLblX As Integer, intLblY As Integer
' Create new form with Orders table as its record source.> Set frm = CreateForm
frm.RecordSource = "tblCustomerOrder"
' Set positioning values for new controls.
intLblX = 100
intLblY = 100
intDatX = 1000
intDatY = 100
' Create unbound default-size text box in detail section.
' for ID field
Set cntrlText = CreateControl(frm.Name, acTextBox, , "", "ID", intDatX * 4, intDatY)
Set cntrlLabel = CreateControl(frm.Name, acLabel, , cntrlText.Name, "ID", intLblX, intLblY)
' for Customer Name field
Set cntrlText = CreateControl(frm.Name, acTextBox, , "", "Customer Name", intDatX * 4, intDatY * 6)
Set cntrlLabel = CreateControl(frm.Name, acLabel, , cntrlText.Name, "Customer Name", intLblX, intDatY * 6)
' for Ordered Quantities
Set cntrlText = CreateControl(frm.Name, acTextBox, , "", "Ordered Quantities", intDatX * 4, intDatY * 12)
Set cntrlLabel = CreateControl(frm.Name, acLabel, , cntrlText.Name, "Ordered Quantities", intLblX, intDatY * 12)
' for Unit
Set cntrlText = CreateControl(frm.Name, acTextBox, , "", "Unit", intDatX * 4, intDatY * 18)
Set cntrlLabel = CreateControl(frm.Name, acLabel, , cntrlText.Name, "Unit", intLblX, intDatY * 18)
' Restore form.
DoCmd.Restore
DoCmd.Save acForm, frm.Name
DoCmd.OpenForm frm.Name, acNormal, , , , acWindowNormal
End Sub


DISCLAIMER

It is advised that the information provided in the article should not be used for any kind formal or production programming purposes as content of the article may not be complete or well tested. Access Guru will not be responsible for any kind of damage (monetary, time, personal or any other type) which may take place because of the usage of the content in the article.