In this part of the video series you’ll learn the following:
Declaring VBA Variables
Running VBA code
Different ways to use the VBA message box
(no iframe just the “embed” shortcode:)
So before you click the next link and take the quiz, we need to review certain topics:
Both Database and Form Objects have 3 things you can code:
Properties – Things that describe the object.
Methods – Things that the object can do, perform.
Events – Things that the object can respond to.
You really should use a form as an interface to a table because you can actually place some stipulations on data that gets entered into the tables. Forms you can code events, but you can’t code the actions on a raw table.
If you really want it to look like the user is interacting with the table, then build a form, and set its default view to datasheet. Then the user will feel like they are interacting with the table, but you as the developer, can set some restrictions on the handling of the data.
Form events deal with certain events that happen during the life of a form. It is loaded (before you actually see it), then it actually opens and you see it.
So a form’s Load event happens when the form is in the process of opening.
Also the form can move from record to record, so you can trap the current event
Other useful events are the BeforeUpdate event, which happens before the record actually gets committed to the database table. With the AfterUpdate event you could track users who made a change to a particular record.
The Dirty event happens while you are in the “pencil”, or edit mode of a record.
You could even code the Unload form event, and tell the form’s user what to do when they are about to close the form.
How Do You Access The Values Of Objects On Your Form
In order to access the controls on your form, it’s easiest to least the object naming convention by using the Expression Builder. The following image shows you what it looks like:
Just double click on the fields you want to add. Normally, you’ll just add 1 field, but you can really mess things up if you start adding many fields and find that the “«Expr»” text is added! You probably don’t need that, so don’t keep clicking and adding things!
This is the “bang” symbol: “!”.
It’s just an exclamation point, but it locates the controls on your form.
*** In VBA or that expression builder, you will reference a field’s content using this syntax: Forms!FormName!FormField
The . (dot) is used to reference the form object’s properties, methods, or events, like
For example, if a user was going to navigate to the next record, but a field was left blank, you would probably want to stop the navigation and flag the field that is blank so they could enter the required data and move on. Like this:
Private Sub CustomerID_BeforeUpdate(Cancel As Integer) ' Display message if CustomerID combo box is blank. Dim strMsg As String, strTitle As String Dim intStyle As Integer 'The Me keyword corresponds to the form currently in use If IsNull(Me!CustomerID) Or Me!CustomerID = "" Then strMsg = "You must pick a value from the Bill To list." strTitle = "Bill To Customer Required" intStyle = vbOKOnly MsgBox strMsg, intStyle, strTitle Me.CustomerID.BackColor = vbRed Cancel = True End If End Sub
And if you want to get the value in a text box you can do this:
Private Sub ShowName_Click() ShipCity.SetFocus MsgBox ShipCity.Text End Sub