To experiment, Open a form and put a button on it. In the OnClick property put:

MsgBox("This message is too long so" & Chr(13) & "we added a Chr(13) function to wrap the text.")

and then click the button. Access returns a message box displaying two lines of text. Furthermore, the Chr(13) function inserts a line break between the words "so" and "we." Without this function, Access, not you, decides whether and where to wrap the text.

Two more things:

1) Instead of chr(13) you can also use vbcrlf to generate a hard return.

2) You can also vbTab to generate tabs.

So you could try the following behind this same button:

Dim Msg as string

msg = "This is a test of the hard returns and tabs:" & vbcrlf & vbcrlf

msg = msg & vbTab & "Here is a tab." & vbcrlf

msg = msg & vbtab & vbtab & "You can use these to shape your message."

msgbox msg, vbinformation+vbOk, "Tab Test"

Set ctlListBox = Me!lstYourListBox
Me!txtCity = ctlListBox.Column(1)
Me!txtState = ctlListBox.Column(2)

End Sub

Assume you have a table called "tblCustomers" with the following structure:

   CustomerID       Text (indexed as Primary Key.)
   Company          Text
   ContactPerson    Text

A form displays data from this table in Single Form view. Add a combo box to the form's header, with the following properties:

   Name             CboMoveTo
   Control Source     [leave this blank]
   Row Source Type  Table/Query
   Row Source       tblCustomers
   Column Count     3
   Column Widths    0.6 in; 1.2 in; 1.2 in
   Bound Column     1
   List Width       3.2 in
   Limit to List    Yes

Now attach this code to the AfterUpdate property of the Combo Box:

Sub CboMoveTo_AfterUpdate ()
   Dim Rst As Recordset
   Set Rst = Me.RecordsetClone
   Rst.FindFirst "[CustomerID] = '" & Me![CboMoveTo] & "'"
       ' Note: Omit single quotes is CustomerID is numeric.
   If Not Rst.NoMatch Then Me.bookmark = Rst.bookmark
End Sub

The three steps this function takes are:

• The RecordsetClone method creates a duplicate set of pointers to the records behind this form ( "Me".)
  
• The FindFirst matches the field to be searched with the contents of the combo box.
  
• The record is displayed by matching the form's BookMark to the record found in the clone recordset.

Global StatusCalled
Global CurrentStatusMsg

Sub StatusBarMsg (StatusMsg)
    If StatusMsg <> CurrentStatusMsg Then ' test if message already displayed
        Dim ss As Variant
    	ss = SysCmd(SYSCMD_SETSTATUS, StatusMsg)
        StatusCalled = True
        CurrentStatusMsg = StatusMsg
    End If
End Sub

Sub ClearStatusBarMsg ()
    If StatusCalled Then
        Dim ss As Variant
        ss = SysCmd(SYSCMD_CLEARSTATUS)
        StatusCalled = False
        CurrentStatusMsg = " "
    End If
End Sub

The following code (with different messages, of course!) is attached to the 'mouse move' event of each control. When the mouse moves across a control the appropriate message will be displayed in the status bar.

Sub CmdButton_MouseMove (Button As Integer, Shift As Integer, x As Single, Y As Single)
    StatusBarMsg "Display this message on the status bar."
End Sub

This code is attached to the "background" of each part of a form, eg detail, header, footer etc. In this way the message is removed from the status bar when the mouse is moved 'off' a control.

Sub Detail0_MouseMove (Button As Integer, Shift As Integer, X As Single, Y As Single)
    ClearStatusBarMsg
End Sub

This code is attached to the 'on timer' event of the main form of an application. This bit of code is not strictly necessary but I have found that a timer interval of 6500 leaves a message on the status bar long enough to be read, yet gets rid of it quickly enough to avoid confusion with any status bar text associated with the control which has focus.

Sub Form_Timer ()
    If StatusCalled Then
	ClearStatusBarMsg
    End If
End Sub

Sub Form_Current ()
   If [AmountDue] > 500 Then
      [AmountDue].forecolor = 255
      [AmountDue].fontbold = True
      [AmountDue].fontsize = 14
      [AmountDue].height = 400
   Else
      [AmountDue].forecolor = 0
      [AmountDue].fontbold = False
      [AmountDue].fontsize = 10
      [AmountDue].height = 300
   End If
End Sub

Some of the changes you can perform are rather radical, such as changing the record source of a form while it is running! You might do this to change the sort order, or - with astute use of SQL - to reduce network traffic from a remote server.

In addition, some controls have properties which do not appear in the "Properties" list at all, since they are available only at runtime. For example, Combo boxes have a "column()" property which refers to the data in the columns of the control. Picture a Combo box called cboClients with 3 columns: ID, Surname, Firstname. When not dropped down, only the ID is visible, so you decide to be helpful and add a read-only textbox displaying the name. DLookup() will work, but it is much more efficient to reference the data already in the combo by binding your textbox to the expression

= [cboClients].[Column](2) & " " & [cboClients].[Column](1)

• Create a table to save the record's Primary Key value between sessions;
• Use the form's Unload event to save the current record's ID;
• Use the form's Load event to find that record again.

As an example, take a form that has CustomerID as the primary key field.

Create a table with these 3 fields:

    Field Name    Type        Description
    Variable      Text, 20    Holds the variable name. Mark as primary key.
    Value         Text, 80    Holds the value to be returned.
    Description   Text, 255   What this variable is used for/by.

Save this table with the name "tblSys". In Access 95 or 97, you may care to mark this as a hidden table.

Set the form's UnLoad property to "[Event Procedure]", and add the following code. It finds (or creates) a record in tblSys where the field Variable contains "CustomerIDLast", and stores the current CustomerID in the field called Value.

Sub Form_Unload (Cancel As Integer)
    Dim db As Database, rst As Recordset
    If IsNull(Me![CustomerID]) Then Exit Sub
    Set db = DBEngine(0)(0)
    Set rst = db.OpenRecordset("tblSys")
    rst.index = "PrimaryKey"
    rst.Seek "=", "CustomerIDLast"
    If rst.NoMatch Then
        rst.AddNew          'Create the entry if not found.
            rst![Variable] = "CustomerIDLast"
            rst![Value] = Me![CustomerID]
            rst![Description] = "Last customerID value, to restore in form " & Me.Name
        rst.Update
    Else
        rst.Edit            'Save the current record's primary key.
            rst![Value] = Me![CustomerID]
        rst.Update
    End If
    rst.Close
End Sub

Set the form's Load property to "[Event Procedure]", and add the following code. It performs these steps:

• locates the record in tblSys where the Variable field contains "CustomerIDLast";
• gets the last stored CustomerID from the Value field;
• creates a RecordsetClone of the records in the form;
• finds that CustomerID in the clone set;
• moves to that record by setting the form's BookMark.

  Sub Form_Load ()
      Dim db As Database, rst As Recordset, rstFrm As Recordset
      Set db = DBEngine(0)(0)
      Set rst = db.OpenRecordset("tblSys")
      rst.index = "PrimaryKey"
      rst.Seek "=", "CustomerIDLast"
      If Not rst.NoMatch Then
          If Not IsNull(rst![Value]) Then
              Set rstFrm = Me.RecordsetClone
              rstFrm.FindFirst "[CustomerID] = " & rst![Value]
              If Not rstFrm.NoMatch Then
                  Me.BookMark = rstFrm.BookMark
              End If
              rstFrm.Close
          End If
      End If
      rst.Close
  End Sub
  

That's it!

Incidentally, once you have a tblSys, you will find numerous uses for it. Instead of hard coding your company name into perhaps a dozen reports, add another record to tblSys where the field Variable contains "CompanyName", and the field Value contains the actual company name. A text box at the head of any report can then look up this value by setting its ControlSource to:

     =DLookup("Value", "tblSys", "Variable = 'CompanyName'")

It can be made to add new records, change existing search fields, and so on. Your first line of code should be

   DoCmd DoMenuItem A_FORMBAR, A_EDIT, A_UNDOFIELD, , A_MENU_VER20

You can then do what you like, because you have the new value that the user typed in to the NewData parameter and the original data is still available in the field. I generally open a modal form asking if the user wishes to add a new record or change the existing one, and then take appropriate action in the recordset. The only trick is to end the NotInList subroutine with

   Response = DATA_ERRCONTINUE

This forces Access to ignore the error condition which originated the trigger for the NotInList subroutine.

To provide this functionality in Access, attach this Event Procedure to the KeyPress event of your control.

• Select Case KeyAscii
      Case 43                                 ' Plus key
          KeyAscii = 0
          Screen.ActiveControl = Screen.ActiveControl + 1
      Case 45                                 ' Minus key
          KeyAscii = 0
          Screen.ActiveControl = Screen.ActiveControl - 1
  End Select

When any alphanumeric key is pressed, Access passes its ASCII value to your event procedure in the variable KeyAscii. The code examines this value, and acts only if the value is 43 (plus) or 45 (minus.) It destroys the keystroke (so it is not displayed) by setting the value of KeyAscii to zero. The active control is then incremented or decremented.

This idea is not limited to dates, or even textboxes. The code can be adapted for other keystrokes as required. Use the KeyDown event to distinguish between the two plus keys (top row and numeric keypad), or to trap control keys such as {Esc}. Anyone feel like reprogramming an entire keyboard?