Facebook Linkedin Youtube Twitter

Controls

TEST123

by






Nifty Search Form – Nifty Access Reproduction


Nifty Search Form

This Advanced but simple to use Search Form is unbelievably easy to set-up. Just create a query and a subform. Following the comprehensive Video Instructions you can integrate the Search Form into your own database. The Nifty Search Form is one of my “Nifty Access Drop in ‘Nifty Components'” – use it to elevate yourself to “Power User Level” in your organisation!

Video 1 (1:03)

Demonstrates the search form’s features, specifically the option to search for text formatting characters at the beginning, middle, and end.

Video 2 (8:30)

Shows how to change the form to display products or customers, including steps and potential pitfalls.

Video 3 (3:58)

Explains how to search another field (e.g., customer reference). Notes that the form is designed only for text searches, not numbers or dates.

Video 4 (4:12)

Provides a quick setup demonstration of the “Nifty Search Form” from scratch, with minimal narration except for mouse clicks.

Video 5 (00:57)

Demonstrates solving a search problem from Access World Forums using the Nifty Search Form. References a forum thread and includes a YouTube video answer.

Video 6 (2:42)

Examines the code behind the Search Form, focusing on modifications for exact matches and null values. Intended for users who want to alter the code.

Video 7 (4:12)

Continues the code discussion from Video 6, covering updates for exact matches and null values in response to a customer request.



Nifty Container

by

Nifty Container

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.

Product Details and Download

Video 1 (5:08)

Nifty Access YouTube Thumb Nail

… …

More Useful Stuff HERE:-

Sign Up to Get Nifty News

Run-time error 438

by

Run-time error 438

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

Textbox Property Sheet

Label Properties

Label Property Sheet

… …

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

Links

… …

More Useful Stuff HERE:-

Sign Up to Get Nifty News

Display Data from a Combo Box in a Text Box

by

Display Data from a Combo Box

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)

Nifty Access YouTube Thumb Nail

… …

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.

Video 2 (4:56)

Nifty Access YouTube Thumb Nail

… …

More Useful Stuff HERE:-

Sign Up to Get Nifty News

Find The Caption of a Label Attached to a Textbox

by

Getting the Caption of a Textbox Label

Getting the Caption of a Textbox Label

Caption of a Label Attached to a Textbox

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

… …

More Useful Stuff HERE:-

Sign Up to Get Nifty News

Value

by

Use "Value" or Not?

I’ve noticed a couple of minor glitches caused by the default of a ‘textbox’, the value; not being assigned explicitly. It’s not a big problem and I don’t think it will affect many people, possibly nobody! However I think there’s a good blog in it for someone, possibly me, when I’ve got the time, or someone else might pick it up and carry it…

Not done any research on this. I’m just recording instances where I notice a problem. Hopefully I, or someone else will have time to investigate more thoroughly. The first time I noticed the problem was when converting a macro into VBA code.  See my Blog about it HERE:-

TempVars Value Error

I think it was the macro from the sample Northwind database, and in particular it was in the login form. If my recollection is correct, the conversion assigned a textbox value to a TempVar. The problem I noticed was that it tried to assign the textbox as control, therefore it did not automatically extract the value from the textbox it tried to assign the textbox as a control which caused an error.

I considered this a minor glitch and would very seldom cause anyone a problem and put it to the back of my mind.

If you’re not familiar with the Northwind Sample Database, then I’ve done a quick video demonstrating how to install and use it here:-    Setup the Northwind dB 

However whilst researching

“DoCmd.RunCommand acCmdSaveAsOutlookContact”

which I also found in the Sample Northwind Database.

I took a particular interest in it because it was a command I hadn’t come across before.

Whilst researching it, I ran across this thread:-   Save as Outlook Contact Problem 

Which harkens back to a similar error with the value of a textbox. Hence I’m putting two and two together and possibly making Five> I note some similarity between my two observations and thought it’s worth recording…

My thinking is if there is any possibility of your textbox value being assigned to a control variable, then you must use value explicitly. In some ways this sentence doesn’t make much sense! I think it’s just my paranoia…. possibly… who said that?

… …

More Useful Stuff HERE:-

Sign Up to Get Nifty News

Attach a Label

by

Attach a Label

Attach a missing label – Nifty Access – The problem you are facing is, you’ve got a Control and it’s missing its Label. To duplicate the situation, first create a Textbox on an MS Access Form. Created a text box with a label. When you move the text box, the Label moves with it.

Attach a Missing Label

You Deleted the Label - And Now Want it Back!

Video 1 (0:52)

However for some reason you decide you no longer need the label and you delete it. A few hours later you realise you made a mistake and think to yourself “Damn! I wish I hadn’t done that” You try and fix the problem by creating a new label, however The first problem is the label won’t stay there! It vanishes! You can solve that problem by entering the label and pressing a dot, or any text really. You’ve added a caption in the label and it will stay. But although the New Label is adjacent to your text box, it won’t move with the Textbox, it’s not fixed, not attached to it. What you do is you click on the label do:- “Cut” then click on the text box and do “Paste”. Now your label is reattached to your text box…..

Video 1 (0:52)

Nifty Access YouTube Thumb Nail

See the video Above:- Video 1 (0:52) for a demonstration of the problem and how to fix it.

More Label Posts

Vertical Text in a Label

Explanation of how to display text vertically in a label and textbox here:-

Vertical Text in Label and TextBox

Find Caption of Label

Getting the caption of a text box label is not straightforward, I explain it here

Find The Caption of a Label Attached to a Textbox

… …

More Useful Stuff HERE:-

Sign Up to Get Nifty News

List Box Default Selection

by

List Box Default

Please be aware that while upgrading the Nifty Access Website I discovered this post. I tried to link it back to the original post on Access World Forums, however I couldn’t I find the post. I found one that looks similar, but I don’t think it is the correct one! I’m going to leave this post like it is for now and get back to it at a later date. Nothings wrong with the post other than the links back to the forum might not be correct… cheers Tony.

List Box Highlight a Default Selection 

List Box Highlight a Default Selection 

Video 1 (2:0)

This video is In answer to a specific question asked by Frank.

Frank Asked:- I need to select (highlight) the first item in a llstbox in order to ensure that a piece of code operates, but I cannot remember how to do this in code on the opening of the form obtaining the listbox. The form is called frmListRecd and the listbox is called lstRecd. Any help would be greatly appreciated.

Set the listbox controls default value so that the list highlights the same row …

Video 1 (2:0)

Nifty Access YouTube Thumb Nail

Access World Forums Post HERE:-

Highlight a Default Selection - Bullet Points

00:27 go to the list box properties
00:45 put something in the default value
00:47 a “2”
01:12 and you can see that item “2” is selected
01:26 the list box needs to be linked to a field in the underlying table
01:35 it won’t work if it’s not linked to a table because the value is not recorded (although I’m not sure that’s right!)

… …

More Useful Stuff HERE:-

Sign Up to Get Nifty News

Control Wizard (Access 2007)

by

Control Wizard

The Control Wizard is a very handy feature of MS Access. It provides built-in wizards, which simplify many of the processes you have to partake in to build your MS Access database.

Button Wizard (Access 2007)

Button Wizard (Access 2007)

Video 1 (2:09)

Demonstrate how to use the MS Access (2007) command button wizard to open another form and filter it to display the same record on the second form as you were looking at in the first form.

Video 1 (2:09)

Nifty Access YouTube Thumb Nail

… …

More Useful Stuff HERE:-

Sign Up to Get Nifty News

Enable or Disable a Text box

by

Enable or Disable by Command

In this video I demonstrate how to make a control appear greyed out. In other words, you can’t do anything with it. I demonstrate the process by using two separate command buttons, however it is possible to do it with a single command button. I have a video demonstrating this, however I couldn’t find it! Will post it here when I find it! If you need it, contact me and I will definitely find it for you!

Enable or Disable a Text box

Grey out a Text box in MS Access 

Video 1  (1:57)

I’ve done a quick and dirty video here showing how to enable or disable a text box just using a couple of command buttons.

Video 1  (1:57)

…

More Useful Stuff HERE:-

Sign Up to Get Nifty News

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.

Accept & Close
Decline

Do you need a hand in the right direction?

You are in the right place.

Take me home!
go to projects