Using a relatively unknown feature of a combobox, I have added a year selector to the Nifty Date Picker. It would be fairly simple to add a list of years into the combobox as a value list, but you would be stuck with a hard coded range of years. If you had an application where you were entering dates in the distant past or future, then it would be restrictive.
Overtime the value list of dates would become outdated and need to be updated to make it relevant again.
Many years ago I came across the “Call Back Function” the example I saw was used to to display a list of years, exactly as I have duplicated here in my “Nifty Date Picker” I lost this example, and it only recently since seeing a YouTube by Crystal Long that I thought I should look at it again.
In Video 1 Below, I demonstrate both the “Year Picker” and the “Month Picker” both of which are based on “Call Back Functions”
In a future video I will explain the callback function how it works.
Often, I see examples where people pass information between Forms and Reports using Opening Arguments “openArgs” … I did this myself until I happened upon an article, I think it was from “Smart Access” which explained a very satisfying way of Passing information around.
The method still employs Opening Arguments “openArgs” but instead of using the “openArgs” to pass data, it is used to pass the Form Name, the name of the Form from where the data originates, and of course it will also be the form Opening the Report. With the Form name now in the Report, the Report knows where to look for the information it needs.
Pass Info In to a Report
Pass Info In to a Report
Video 1 (3:41)
The open-args method, the method of passing lots of information in a string, separated by delimiters and passing them through with the openargs, it’s quite a challenging method of handling data.
The method explained in this video is far superior. This method makes the data available in the “Calling Form” the Form that is Opening the Report. The Report is directed to extract the necessary information from the “Calling Form”.
With this method you have a greater degree of control over the information. It’s logical, it’s easy, especially if you need to add or remove information.
'======================== this code in the Form ======================
Option Compare Database
Option Explicit
Private mstrRptHeaderLabel As String
Private mstrRptSQL As String
'<<<<<<<<<<<<<< --- PROPERTY STATEMENTS --- >>>>>>>>>
Property Let prpRptHeaderLabel(strHeader As String)
mstrRptHeaderLabel = strHeader
End Property 'prpRptHeaderLabel Let
Property Get prpRptHeaderLabel() As String
prpRptHeaderLabel = mstrRptHeaderLabel
End Property 'prpRptHeaderLabel Get
Property Get prpRptSQL() As String
prpRptSQL = mstrRptSQL
End Property 'prpRptSQL Get
Property Let prpRptSQL(strHeader As String)
mstrRptSQL = strHeader
End Property 'prpRptSQL Let
Private Sub btnOpenRport_Click()
Me.prpRptSQL = "SELECT ID, T1, T2, T3, T4 FROM Table1 WHERE (((T3)='ww'))"
Me.prpRptHeaderLabel = "REPORT HEADER FROM FORM"
Dim strRptName As String
strRptName = "rptTable1"
DoCmd.OpenReport strRptName, acViewReport, , , , Me.Name
With Reports(strRptName)
.Caption = "I CAN CHANGE THE CAPTION"
End With
End Sub
'======================== this code in the Form ======================
Passing Information to a Report - The REPORT CODE
'======================== this code in the Report ======================
Option Compare Database
Option Explicit
Private Sub Report_Open(Cancel As Integer)
If Len(Me.OpenArgs) > 0 Then
With Forms(Me.OpenArgs)
Me.RecordSource = .prpRptSQL
Me.lblRptHeader.Caption = .prpRptHeaderLabel
Me.lblShowSQL.Caption = .prpRptSQL
End With
End If
End Sub
'======================== this code in the Report ======================
The “Nifty Container” offers you a graphical way of managing the controls on your form. All you do is surround a group of controls with a box, choose one of the coding examples, or write your own, and there you go, you have a simple and easy-to-use method, which avoids using your Control(s) Tag Property, (the usual method of doing this) which you might well want to use for something else.
There are many ways to identify controls on your form for particular attention. This “Nifty Container” idea I have developed is unique, I haven’t come across it anywhere else. All you do is draw a box around the group of controls you want to handle. You might want to make sure that the controls have text entries, you might want to make sure that at least one of the controls has an entry, anything you want to do to a group of controls can be done with the “Nifty Container” system, and at the same time avoids using the controls Tag property…
Nifty Container - Demo
Nifty Container - Demo
Video 1 (5:08)
Well here it is! Another drop in component from Nifty Access. The “Nifty Container” provides an alternative way to handle a “Block of Controls” on your form. Instead of using the “Tag Property”. This method utilizes a simple rectangle as a Container to surround a bunch of controls. Call the code and it will cycle through the objects within the rectangle and alter them to your specific programming requirements.
There are many ingenious ways to offer help to your MS Access Application user. One of the simplest ways is to store the help information in the table. But it’s not so simple because every place that you want to provide help, you will have to add the code to open the help form, and also provide the individual ID reference to the help record associated with the control/place on your form that your user will benefit from some help. It’s a nightmare to run a help system like this, and if you have several forms, switching back and to, to the table to find out which is the next available help ID number, well it gets a bit tiresome. However this here is an easy solution to the problem.
Help System For MS Access
Help System For MS Access
Video 1 (3:25)
Here is a run through of the Help System in operation. the “Nifty Access Help System” uses an MS Access Table, an MS Access Form and utilises the “Nifty CallCalled Class Module” to extract information from the Calling Form.
In this video I demonstrate a useful trick where you can Call the same Function from lots of controls in your MS Access Database. The beauty of this method is you can wire up numerous controls within seconds. It’s a very useful – Nifty – MS Access technique…
Video 2 (3:21)
https://www.youtube.com/watch?v=3FdRkYuUsxU
… …
Access Help System – SETUP!
Nifty Access Help System – SETUP!
Video 3 (7:45)
A matter of “10-minutes” – that’s all it takes to setup this “Nifty Help System!” Now, you’ve got to enter in all the help information – that’s not going to take 10 minutes is it! Best of Luck!
Video 3 (7:45)
https://www.youtube.com/watch?v=4Y4NaFLzcY4
… …
MS Access HELP – PopUp on MouseOver
Help System – PopUp on MouseOver
Video 4 (1:38)
FarZ recently made an excellent observation about the Nifty Access – Help System. The problem for FarZ was that he already uses the double click event for something else. He wondered if it would be possible to pop-up the help with a hotkey and and MouseOver… I searched the internet for ideas and I found these two blogs which provided some excellent information:- How to code “Mouse Over” effects for MS Access <<< And >>> Form.KeyDown event (Access) With this information I was able to to create a solution for FarZ. I have Incorporated the solution into the the Nifty Access – Help System… Which can be downloaded HERE:- Help System for MS Access If you prefer the free option, then the above links should give you enough information for you to be able to recreate this solution for yourself.
Run-time error 438 – Object doesn't support this property or method. – (Microsoft Access)This problem often occurs when you Loop through a set of controls with a For Next Loop. It happens because you are examining the properties of a control. However not all controls have the same
properties.
Different Controls, different Properties
If you open a property sheet for a text-box and look under the data tab there are a number of properties including “Enabled” & “Locked”.. However if you open a property sheet for a label and look under the data tab you will see that there are no properties listed. In other words a “Label” does not have the properties”Enabled” & “Locked”. If you try and access a property that does not exist for any particular control then you will generate an error and your code will cease to run.
Text Box Properties
Label Properties
… …
A Technique to Exclude Objects
A Technique I often use, is to exclude certain types of controls from the For Next Loop. This technique prevents the code from attempting to examine an object that does not have the properties you are testing. To do this you add an extra step to your loop, a step that tests for the type of control. If the control is a label, commonly a type of control that serves no purpose from a coding point of view, you can exclude Label Controls from any further processing.
… …
A Couple of VBA Examples
Code Snippet 1 below, examines the control type in the Select Case Statement, you can easily add or remove different types of control from your test routine by un-commenting the particular control type from the commented out part of the Select Case Statement…
This code is designed to remove the commonly used naming convention, a three digit prefix from the control name, then check to see if what’s left constitutes a “duplicate name”. For example if you had a combobox named:- cboDate and you had a text box named:- txtDate then the code would return:- “Date in the variable:- “strFoundControlName”. This is a handy piece of code if you want to associate two controls together via the Controls’ Names.
… …
'Code Snippet 1
Dim strFoundControlName As String
Dim strNamePart As String
Dim Ctrl As Control
Dim X As Integer
For Each Ctrl In Me.Controls
Select Case Ctrl.ControlType
Case acComboBox ,acTextBox ', acListBox, acOptionButton, acOptionGroup, acToggleButton, acLabel, acCheckBox
If Right(Ctrl.Name, Len(Ctrl.Name) - 3) = strNamePart Then
X = X + 1
strFoundControlName = Ctrl.Name
End If
End Select
Next Ctrl
Code Snippet 2 below, uses “TypeOf” in an IF Statement instead of “Ctrl.ControlType” as demonstrated above. This example only selects one particular type of control and in this case will only check the checkboxes on your form:-
'Code Snippet 2
Dim Ctrl As Control
For Each Ctrl In Me.Controls
If TypeOf Ctrl Is CheckBox Then
If Ctrl = False Then Form_frmQC.sFrmWinTblFaults.Visible = True
End If
Next Ctrl
There’s a fantastic, useful, clever feature of MS Access (actually I think it’s in all of the VBA products) where you can store code snippets in the normal file structure of Windows. Basically you have a folder which is accessed quickly and easily through the — >>>Insert >>>File — menu. This menu is shown when you are in the VBA interface. When you open it, you have access to this folder. You can have subfolders in any/all of those folders. You can have text files containing your code, Code Modules, Code Snippets, Class Modules Anything Textual you Want! Clicking on any of the text files automatically copies the contents of that text file straight into your Code/Class Module. But here’s the thing, you can also access any of those code snippets stored in the text files and edit them. You can simply and easily keep your stock of code available, ready and updated. And to top it all, it’s immediately available to any of your MS Access database projects.
"Insert File" - Demonstration
"Insert File" - Demonstration
Video 1 (Less than a Minute!)
Insert File Demonstration. In this video I show you how to quickly and easily insert a code snippet into your MS Access form Code Module using “file – Insert” menu option. In particular, I demonstrate inserting a “Custom Property” which, in this particular case is designed to hold an “Object Variable”…
Video 1 (0:38)
… …
Add to "Insert File"
Add to "Insert File"
Video 2 (Less than a Minute!)
In this 40 second video clip I demonstrate how easy it is to add and modify code in the “File Insert” section of “MS Access”… It’s one of the little used features of MS Access. It’s so helpful and can speed up your development process no end. If you are not currently using it I highly recommend you get conversant with it, start adding your favourite code snippets in there. I find the biggest problem is “Categorising my Code Snippets” I never know whether to categorise it by objects like, Form, Report, Module — or by what the code does! If you’ve got any ideas, then I’m all ears!
I’ve also made a note of this Tip in the Nifty Tips section of my website…
Combo boxes are one of the most valuable controls you can have on a form, they are so versatile and give you such an extra range of functions for your form that you should really consider understanding how to use them. Also with a combobox as your foundation, you can start to take your first steps into VBA programming!
Display Data from a Combo Box in a Text Box
Display Data from a Combo Box in a Text Box
Video 1 (5:50)
One of the most useful things you can do with a combobox is link it to related data in another table.
Let’s say you have an invoice and you want to choose the customer. You also want to display the customer address. You could link your invoice table to the customer table with a query and use that as the basis for your invoicing form. However it’s much simpler to utilise the customer ID. From the “CustID” you can display all of the customer information you require, customer name, address1. Addr2, Town, etc. Just add a set of unbound text boxes and have them filled with the values from the selected row in the combobox.
It’s an excellent for displaying “Addresses” straight from a Combobox on your form. Makes for a very Tidy interface…
More - ComboBox Info HERE:-
Video 1 (5:50)
… …
Form with Combo Box Lookup
Form with Combo Box Lookup
Video 2 (4:56)
This is another example of how you can set up a Combo Box and have it display information from the combo-box’s row in text-box’s on the form.
You might think getting the caption of Textbox Label would be just as simple as getting the caption of a Command Button. However it’s a little bit more involved. I run through the process in this short video… This is the VBA code to get the caption of the textbox….
“Me.ControlName.Controls(0).Caption”
Finding the caption on a textbox is not quite a straightforward as you might think. This is because the caption is actually contained in the label attached to the textbox. This also causes an issue if you don’t have a label for your text box because if you have deleted the label, then the code to extract the caption will not run it creates an error
Video 1 (4:23)
Video 1 Index
00:10 linked to earlier video – See Video 4 Here:- Lock, Unlock Controls… 00:13 two buttons to perform one function not good! 00:22 command button locks the form so that you cannot type in the text boxes 00:25 the other button unlocks so you can type text in the text field on the form 00:42 you can change two buttons into a single button 00:55 and this is a look at the VBA code for Locking/Unlocking 00:57 the code checks the caption property of the command button, looking to see if the caption
contains "Lock" or "Unlock" and then processing the code depending 01:14 however a text-box is different from a command button in that
it doesn't have a caption property 01:22 a textbox has a label and the label gives it the text box a caption 01:33 this is the VBA code for capturing a command buttons caption 01:50 however if you try and extract the caption from a textbox
in the same way as you do from a command button you generate an error "Method or data member not found (Error 461)" 02:00 this is because a textbox
does not have a caption, the label attached to the textbox contains the caption 02:16 demo of the code for getting the caption from a textbox label 02:36 the VBA code to extract the Caption from the textbox label is:- Me.txtGetMyCaption.Controls(0).Caption 02:48 you are effectively looking through
the collection of controls related to the text-box and as far as I'm aware there's only one control! 03:30 you can also get the label name in
the same manner just by altering the code slightly Me.txtGetMyCaption.Controls(0).Name 03:38 notice how when you type in "name" in lower case
MS Access automatically makes the first letter uppercase. "Name" This is a good indication that "name" is a valid property and the will most likely work 03:50 it's always a good idea to test for conditions that might be prevalent in the future, like a missing label. When you delete the label the code fails reporting error:- Run-time error 2467 – The expression
you entered refers to an object that is closed or doesn't exist
Often, you will want to add some Custom Functionality to your MS Access Form by creating a Command Button. Once you have created the Command Button you will more than likely add some code to the onClick event of the command button.
You would do this by opening the property sheet and finding the Button “on click event”, press on the ellipsis and this will automatically generate a code stub in the Code Module behind the Form, ready for your custom code. If your Code Module already has lots of code in it, then the button onClick event could be created anywhere in the code list.
Luckily the cursor will be flashing at the code stub and give you an indication of where You need to enter your custom code. However because of the seemingly random placement of the Code Stub by MS Access, you may have difficulty finding the stub subsequently.
A good practice to get into is to copy the Code Stub to the top of your form, place it under the declarations and properties (if you have them). Now you will be able to find it easily when you come back to this Form…
Bring Latest Code to the Top
Bring Latest Code to the Top
Video 1 (09:26)
In this video I demonstrate a a very simple but very useful technique. All you do is bring the latest code you are working on in a form or a module to the top, just below the declarations. Now when you return to your project a few days, weeks or month’s later, you automatically know the last piece of code you were working on… Sort of like my favourite subject “Self Documenting Code”
There are many ingenious ways to offer help to your MS Access Application user. In a help table in a Text Box. There is a YouTube video by way of explanation here, and a downloadable example database.
Display Message inside TextBox
Display a Message for the User
Video 1 (1:24)
An excellent space saving tip here from Mark. You can display instructions for the user actually inside a text box. There’s a downloadable sample Microsoft Access Database available on the Access World Forums website. Follow the Link Provided…
Here is a run through of the Help System in operation. the “Nifty Access Help System” uses an MS Access Table, an MS Access Form and utilises the “Nifty CallCalled Class Module” to extract information from the Calling Form.
This is one of the simplest ways is to store the help information in the table. But it’s not so simple because every place that you want to provide help, you will have to add the code to open the help form, and also provide the individual ID reference to the help record associated with the control/place on your form that your user will benefit from some help. It’s a nightmare to run a help system like this, and if you have several forms, switching back and to, to the table to find out which is the next available help ID number, well it gets a bit tiresome. However this here is an easy solution to the problem.
This website uses third-party software - WordPress Add-Ins to be exact. I don't know what any individual add-in does, but I'm sure that many of them collect information about you. So be aware, if you continue using this site, then you are likely to be sharing your information. I don't know how to disable this sharing for any, or all of the plugins for individual users. So I can't stop the sharing of information. If this worries you then please do not use this site... If you continue to use this site I will assume that you are happy with it.
