Learn Microsoft Access Advanced Programming Techniques, Tips and Tricks.

Dots and Exclamation Marks Usage with Objects in VBA3

MS-Access Application Objects (Table, Query, Forms, Text box, Label etc.) needs some meaningful names, when we create them. If we don't, then MS-Access assigns default names, like Form1, Form2, Text1, Label1 and so on. These default names doesn't give any clue as what those names represents. We are free to assign appropriate names in relation to what we are building in the Database. That way it is easy to remember those names (if not all of them), when we need them in calculations,  in VBA or in wherever they are referenced. We need these names to address them easily in VBA, like Forms!Employees!Salary rather than the usage  Forms("Employees").Controls("Salary").Value. 

Last week we have started with a simple example, where we can use the symbol ! , to shorten the lengthy object address when dot separators are used, giving you enough insight into what it is all about. When we have the Form’s name and Control name the expression can be written in short form with the symbol ! .  This is true when recordset fields are referenced, like rset!LastName instead of rset.Fields(0).Value.

If you are new on this page then please visit the earlier two pages and continue from here. The links are given below:

I will repeat the first example here, introduced in the first page of this three page series, to go further on this discussion.

? Forms!frmMain!frmSubForm.Form!frmInsideSubForm.Form!Text7
'
'The above command without the use of the ! Symbol, you must write it in the following manner to get the same result.
'
? Forms("frmMain").Controls("frmSubForm").Form.Controls("frmInsideSubForm").Form.Text7.value

Note: The frmSubForm when placed as Sub-Form on the frmMain it becomes a control (with it's own controls and properties) of the Main Form and listed among the Controls list. If we take a list of controls of the Main form we can see that the frmSubForm is listed among them.

Open a Form with a Sub-Form  then type the following command in one line, with changes in the Debug Window and press Enter Key to get a listing of control names of main form

for j=0 to forms!frmMain.controls.Count-1:? j, forms!frmMain.controls(j).name:next
'Result of the above command, on my Form.
'
 0            Label0
 1            Text1
 2            Label2
 3            Text3
 4            Label4
 5            frmSubForm
 6            Label5

frmSubForm is listed as a Control of the frmMain with index number 5

Now, about the example given at the beginning, we have three open Forms: frmMain, frmSubForm & frmInsideSubForm, layered one inside the other. We are trying to print the Text7 Text Box contents, from the innermost form in the debug window.  Look at the above address of  Text7 textbox, all elements are joined with the symbol ! except .Form after the name frmSubForm and frmInsideSubForm. This command will work without the .Form part, try the command given below.

? Forms!frmMain!frmSubForm!frmInsideSubForm!Text7

If the address works without the .form part why we need it in the address and what it means? It works without explicit reference because the system knows that it is a Sub-Form control by default.

When you drag and drop a Sub-Form on to the Main Form Microsoft Access creates a container control on the main form and inserts the Sub-Form into it. To be more specific, if you select the outer edge of the sub-form control you can select this container control. Display it's Property Sheet(F4) check the Source Object Property setting. You can see that the sub-form's name is inserted there. This is the control where we set the Link Master Fields and Link Child Fields properties to set relationship between data on master form and sub-form.

You can re-write this property value with any other form's name  to load another form into it, in real-time. When you do that consider the relationship change, if the new form's source data is not related.

Coming back to the point, i.e. what the .Form part in the above address means? It means that the Sub-Form Control created by Access is a control for loading a Form into it and it will be always a form-control, whether you explicitly add the .Form part in the address or not.

But, the interesting part is that you can insert a Table or a Query (not Action Query) into this control as well.

Try that, if you have a Form with a sub-form, open it in design view.

  1. Click on the outer edge of the Sub-Form to select the Sub-Form control.
  2. Display the Property Sheet (F4) and select the Source Object Property.
  3. Click on the drop-down control to the right of the property to display the available forms, Tables and Queries.

    On top of the list all forms will appear, after that the list of Tables and then the Queries list. All the Tables are listed with Table.TableName format and queries with Query.QueryName format indicating the category of object you can insert into the Source Object Property of the Form control.

  4. Select a Table or Query to insert into the Source Object Property of the Sub-Form control.
  5. Save the Form and open it in Form View.
  6. You will find the Table or Query result is displayed in the Sub-Form control.

  7. Try to print the value of one of the field in display in the debug window.

    Tip: It will print the value of the active record in the sub-form, if selected, otherwise the first record field value.

Is this the command you have typed in the Debug Window?

? Forms!frmMain!frmSubForm.Table!LastName

Then you are wrong, it is not a Table Control, still it is a Form control only. When you set the Source Object Proerty Value with a Table's name the system already added the category name to the object's name (Table.TableName or Query.QueryName) to identify what type of object is loaded into the sub-form control.

So the correct command is:

? Forms!frmMain!frmSubForm.Form!LastName
'
'OR
'
? Forms!frmMain!frmSubForm!LastName
Share:

No comments:

Post a Comment

Translate



PageRank
Your email address:

Delivered by FeedBurner

Search

Infolinks Text Ads

blog.feedspot.com Microsoft Access Blogs

Popular Posts

Search This Blog

Blog Archive

Powered by Blogger.

Labels

Forms How Tos Functions MS-Access Security Reports msaccess forms Animations msaccess animation Utilities msaccess controls Access and Internet MS-Access Scurity MS-Access and Internet Queries External Links msaccess reports msaccess tips Accesstips Menus and Toolbars MsaccessLinks Process Controls Art Work Downloads msaccess How Tos Graph Charts msaccessQuery Array List Boxes Command Buttons Emails and Alerts Query Combo Boxes Custom Wizards DOS Commands Data ms-access functions msaccess functions msaccess graphs msaccess reporttricks Calculation msaccessprocess security advanced Access Security Custom Functions Data Type Macros Menus Property Report Top Values VBA Variables msaccess email msaccess menus progressmeter Access2007 Auto-Number Command Button Copy Field Type Fields Form Join Microsoft Numbering System Objects Records Security Split SubForm Table Utility Workgroup database msaccess wizards Access2003 Accounting Year Action Animation Attachment Binary Numbers Bookmarks Budgeting ChDir Class Module Color Palette Conditional Formatting Controls Data Filtering Defining Pages Diagram Disk Dynamic Lookup Error Handler Excel Export Expression External Filter Form Instances Formatting Groups Hexadecimal Numbers Import Labels List Logo Macro Mail Merge Main Form Memo Methods Monitoring Object Reference Octal Numbers Operating System Paste Primary-Key Product Rank Reading Recordset Rich Text Sequence SetFocus Summary Tab-Page Tables Time Difference Union Query User Users Water-Mark Word automatically commands function hyperlinks iSeries Date iif ms-access msaccess msaccess alerts pdf files reference restore switch text toolbar tutorial updating upload vba code

Featured Post

Ms-Access VBA Class Object Arrays

Last Week we had a brief introduction of a simple Custom Class Object with only two Variables, for Length and Width Values. A Method for ...

Labels

Blog Archive

Recent Posts