Blog Home  Home Feed your aggregator (RSS 2.0)  
Doctor Access Blog - Tuesday, 04 October 2011
Everything About MS Access
 
# Tuesday, 04 October 2011

If you have ever had to manage the visibility, the enabled or the locked properties of multiple controls on one or more forms, then you need to take a look at a demo database and the accompanying PDF document that I have posted on the Download page of my website at:  http://www.askdoctoraccess.com/DownloadPage.aspx

Here are a few highlights about this technique:

Special Method for Managing Multiple Controls on a Form

I initially started out just using macros to manage these controls. Over the years I then started developing and using VBA code behind the object on my forms like the one described above to manage the various controls on my form. I just created individual lines of code that would make specific controls visible or not based on the button that was clicked. For example, if the Edit button was clicked, I would specifically reference each of the controls that needed to be made visible using one line of VBA code to make each control to make it visible.

Note to my experienced Access colleagues: I am now totally aware of how inefficient and ridiculously time consuming this type of programming was. But hey, we all have to learn and I submit that many of our friends that are new to VBA programming are doing this same kind of programming right now.

Writing VBA code like this was a very time consuming process because I then had to create this type of code had over and over again in the code behind each command button.

This was so time consuming and required such a huge amount of redundant code that I finally figured out that I could create a “user defined function” that would allow me to create the VBA code just one time and, by passing a value of “true” or “false” my function, would cause the referenced list of controls to be visible or not visible based on the value passed to my function.

Although the user defined function made the development of this type of user interface much easier to create and maintain, it still required that I add a single line of code to address each and every control for which I needed to manage various properties.

I recently came across a couple of posting on the Access forums about using the “Tag” property to identify controls that were to be managed in some way. I have now expanded on this concept and come up with what I know to be an even more simple way to use the “Tag” property of controls to accomplish the management of controls on my form. Using my method, I can not only provide all of the same functionality provided by the user defined function method that I have used previously, but I can actually provide even more capability and flexibility for managing the controls on my form. The best news of all is that I can now use one Public function that is defined in a module to manage the appropriate properties of multiple controls on any form I choose by passing the name of the form and a true or false value to the function when it is called.

Note: Previously I had rarely used the “Tag” property of forms and/or controls, and had never found a really significant usage for this property.

In this new method, the “Tag” property becomes an intricate part of the process.

Not only can you define your own values to be used for managing the various properties of the controls instead of using the ones I have suggested, but you could easily add new values that would allow you to manage other properties of the controls.

To download the “ManagingMultipleControls” zip file containing the demo database, the PDF document with the complete explanation of how to implement this technique, including the public function that does the heavy lifting, just visit my website at: http://www.askdoctoraccess.com/DownloadPage.aspx

Tuesday, 04 October 2011 03:44:44 (GMT Daylight Time, UTC+01:00)  #    Comments [0]    |  Trackback
# Sunday, 26 June 2011

I recently posted the following VBA code to the Access World Forums but I though I would also post it here on my blog. 

I had found code that was supposed to work, but did not, so  I modified the code to make it work for me.

Copy the code below and paste it into a new module.  Be careful not to name this new module with the same name as the Public Function:  “RefreshSharePointLinks”. 

'***********Code Start****************
'This code is a modified version of the code that can be found at:
'http://blogs.office.com/b/microsoft-access/archive/2009/02/04/code-to-refresh-sharepoint-link-tables.aspx
'The original code has been modified by Byron Polk
'It is not to be altered or distributed,
'except as part of an application.
'You are free to use it in any application,
'provided the copyright notice is left unchanged.
Public Function RefreshSharePointLinks()
Dim dbs As Database
Dim rst As dao.Recordset
Dim tbl As dao.TableDef
Dim fld As dao.Field
Dim strSql As String
Set dbs = CurrentDb()
On Error GoTo Err_ChkError
'iterate through all of the table objects in the database
For Each tbl In dbs.TableDefs
    'only try to refresh linked or "Attadhed" tables
    If (Mid(tbl.Name, 1, 1) <> "~") And ((tbl.Attributes And dbAttachedTable) = dbAttachedTable) Then
        '***You may need to add additional "If" statements to have the code
        '   ignore specific tabale
        'if the table is named "User Information List" do not relink the table
        If Left(tbl.Name, 21) <> "User Information List" Then
            'if the table is named "UserInfo" do not relink the table
            If Left(tbl.Name, 8) <> "UserInfo" Then
                'if the connection string for the linked or "Attached" table
                'starts with "WSS" then this is a ShasrPoint table.
                If Left(tbl.Connect, 3) = "WSS" Then
                    'try to open an editabel recordset from this table
                    strSql = "SELECT * FROM [" & tbl.Name & "];"
                    Set rst = dbs.OpenRecordset(strSql, dbOpenDynaset)
                    'locate an updatable field (not a calculated column)
                    For Each fld In tbl.Fields
                        If fld.Type = dbText Then
                            'if there is a value in this field use this field
                            If Not IsNull(rst.Fields(fld.Name).value) Then
                                'insure that this field is an updateable type field
                                If fld.DataUpdatable = False Then
                                    'set the recordset to "edit" mode
                                    rst.Edit
                                    'update the updatabale field with the same value
                                    rst.Fields(fld.Name).value = rst.Fields(fld.Name).value
                                    'attempt to update the record.
                                    rst.Update
                                    'if the table needs to be relinked, error 3851 will occur and
                                    'our "On Error" error handling will kick in and relink the table
                                    'Only if the update was successful, move on to the next linked table
                                    GoTo GetNextLinkedTbl
                                End If
                            End If
                        End If
                    Next
                End If
            End If
        End If
    End If
GetNextLinkedTbl:
Next
***************End of Code***********

Hope it helps you.

Sunday, 26 June 2011 01:57:52 (GMT Daylight Time, UTC+01:00)  #    Comments [0]    |  Trackback
# Friday, 24 June 2011

My initial intent for this blog was that I would start by posting information about Access for beginning users. Although I still feel that new information for new users is important and something that I have a passion for, I have now come to believe that I can provide relevant information for both beginners but also for even the professional user.

Just for the record, I make my living as an Access Database Application Developer. I normally work either as an independent contractor or for various staffing organizations. I have been doing this type of work for over 15 years now. (Wow, doesn’t seem like it has been that long.)

I have worked with every version of MS Access starting with Version 2.0 thru Access 2010. My most recent and current contract assignment is with a major, world wide, very diversified company. This company has their own installation of SharePoint version 2010 and I am getting first hand experience developing Access applications for use with SharePoint lists. I will have much more about my experiences in future posts over the next few days and weeks. If you have any experiences that you would like to share with me and others about working with this relatively new tool, please provide some info here.

Friday, 24 June 2011 23:41:21 (GMT Daylight Time, UTC+01:00)  #    Comments [0]    |  Trackback
# Wednesday, 11 March 2009

After logging in, be sure to visit all the options under Configuration in the Admin Menu Bar above. There are 26 themes to choose from, and you can also create your own.

 

Wednesday, 11 March 2009 07:00:00 (GMT Standard Time, UTC+00:00)  #    Comments [0]   dasBlog  |  Trackback
Copyright © 2017 AppliTech, Inc.. All rights reserved.
DasBlog 'Portal' theme by Johnny Hughes.
Pick a theme: