Learn Microsoft Access Advanced Programming Techniques, Tips and Tricks.

Opening Access Forms

Introduction

Using the same method that we have seen in the Closing Forms topic, we can create Command Buttons on the Control Screen and link the Forms to each Command Button by setting the Form Name to the Hyperlink SubAddress Property. This is the easiest method to open forms from a central point, when you have several forms in your Application. But the problem with this method is that you may end up with several command buttons on your Control Screen.

We will look into different methods to organize and open the Forms from the Control Screen. We will look into one of them here and we need a Table for this method.

Creating a Table

First of all, create a Table with the following Fields:

SRL Field Name Field Type Field Size
1. ID Number Integer
2. DESC Text 35 Characters
3. FORM Text 20 Characters
4. MACRO Text 20 Characters
5. TYPE Number Integer

and save it with the name: DataFiles.

An Image of a Table with sample data filled in is given below:

Fill the table with your data files' list for which you already have Forms designed for them with a Serial Number and Description in the ID & Desc fields and the Form Name for each Table in the Form Field. Fill the Type field with zero (0), for the time being and we will see the use of that information soon.

Create a ListBox

Now create a List Box on the Control Screen and select only ID & Desc fields from our DataFiles table as source data for the list box.

When you open your Control Screen you will see the serial number and description of your data files in the List Box. A Sample Screen with Listbox is given below for reference.

Click to Enlarge

Now, go back to the design view of the Control Screen (if you have turned on the Form View earlier) and click on the ListBox and display the Property Sheet (View- ->Properties). Change the Name Property to Dat1.


Creating the FormOpen() Function

Display the VB Module (click on the Module Tab of your Database and click on the New option on the database window, which will open a new Global Module. Copy and Paste the following Code in that Module and save it.

Public Function FormOpen(ByVal FileName As String, ByVal ID As Integer) As Boolean 
Dim vartyp As Variant, varFormName As Variant 
Dim varMacName As Variant 
Dim Criteria As String  
On Error GoTo FormOpen_Err  
FormOpen = False  
Criteria = "ID = " & Format(ID)  
vartyp = DLookup("Type", FileName, Criteria)  
If vartyp = 0 Then
 varFormName = DLookup("Form", FileName, Criteria) 
   If IsNull(varFormName) = False Then
      DoCmd.OpenForm varFormName, acNormal
   End If
 ElseIf vartyp = 1 Then 
   varMacName = DLookup("Macro", FileName, Criteria) 
      If IsNull(varMacName) = False Then
         DoCmd.RunMacro varMacName
       End If 
End If
    FormOpen = True

FormOpen_Exit: 
Exit Function  

FormOpen_Err: 
MsgBox Err.Description, , "FormOpen" 
FormOpen = False 
Resume FormOpen_Exit 
End Function 

Now, we have to write a line of code for the On DblClick Event for the listbox. Go back to the listbox design view and display its Properties Sheet. Click on the On Dbl Click property and select [Event Procedure] from the combobox and click on the build ( ) button to open up the form's code module.

You will find an empty On Dbl Click Event Procedure with its starting line Private Sub Dat1_DblClick(Cancel As Integer) and End Sub lines. Complete it by inserting a line of code between these lines as shown below without making typing mistakes:

Private Sub Dat1_DblClick(Cancel As Integer)
   FormOpen "DataFiles", Me![Dat1]
End Sub

If you are not sure, highlight the middle line code above, copy and paste it correctly between the code lines appearing in your form module. It is important that you name the listbox as Dat1 and it is appearing in the code. The list box source data table name is in quotes and a comma is separating both the parameters to the FormOpen function. If you have used any of this objects name differently you have to correct them here.

Now, close the Form's Code Module and save the Form. It is time to test our project. Before testing, check the accuracy of the Form Names that you have filled in, in the Form field of the DataFiles Table.

Open the Control Form. Double-Click on any line in the listbox and you must see your Form corresponding to that item you have double-clicked opens immediately.

If you end up with some errors then double-check the Name of the listbox, the source data table name, the Form Name in the Form field of the DataFiles Table and ensure that zero is filled in the Type field as well. The serial numbers in the DataFiles Table must be continuous numbers. It is convenient if you give shorter names to your Forms so that you don't make mistakes while using their names.


How it works:

When you double-click on the listbox the sequence number corresponding to the item, and the Listbox Source Data Table's Name are passed to the function FormOpen(). The FormOpen() function picks the Type value of the item double-clicked and checks its status and executes.

If the Type Value is Empty - Ignores the Double-Click action (nothing happens).

0 - picks the Form's Name from the Form field of the DataFiles Table and opens it.

1 - the program looks for the Value in the Macro Field of the DataFiles Table and runs that macro.

This is useful if you want to open a table in Read only mode, or to open a form after running a series of Queries to create an output table/Query linked to a Form which expects different results every time etc. by writing a macro and putting it's name in the Macro Field.

This method can be used for Reports also. If you design more Forms/Reports later all you need to do is to add that item in the Listbox source table and the double-click action takes care of the rest.

Next we will see how to use the Hyperlink method in a listbox like design.

Click Next to see use of Hyperlinks in Listbox

Share:

1 comment:

  1. [...] other databases also once you know few basics of it. Take a look at the Article on this topic: Opening Access Forms __________________ http://www.msaccesstips.com (Learn MS-Access Tips and Tricks) Learn [...]

    ReplyDelete

Comments are subject to moderation before publishing.

Translate



PageRank
Subscribe in a reader
Your email address:

Delivered by FeedBurner

http://clicky.com/101199826
Blog Directory Visit blogadda.com to discover Indian blogs

Search

Popular Posts

Search This Blog

Blog Archive

Powered by Blogger.

Follow by Email

Labels

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

Featured Post

Opening Access Objects from Desktop

Frequently Used Methods. Set the Form Name in Display Form Option of Current Database in Access Options . BIG DEAL!, this is the first t...

Labels

Blog Archive

Recent Posts