Logo

MS Access FAQ - Forms

 Logo

Table of Contents (12 Questions)

Bullet Waiting for form to Close
Bullet Refrencing Subform Objects
Bullet Record X of Y
Bullet Force Upper Case
Bullet Forecolor propery limited to 16 colours
Bullet Tabbing past end of form
Bullet Making combo boxes faster #1
Bullet Making combo boxes faster #2
Bullet Proper Case
Bullet Open drop down of a combo box
Bullet Control Name under Mouse Pointer
Bullet Bold On the Fly

Q How can I open a form or report in code and make the code wait until that form or report has been closed..
Back to Top
A The simple answer for forms is to open the form as a modal dialog box, like this:

DoCmd OpenForm "frmFormName", , , , , A_DIALOG

Note the commas, the A_DIALOG parameter is the fifth parameter.

Of course, this will not work for reports in preview mode, and may not be the desired result for a particular form, some polling will be called for, e.g.
DoCmd OpenForm "frmFormName"
Do While IsFormLoaded("frmFormName")
  DoEvents
Loop

DoCmd OpenReport "rptReportName"
Do While IsReportLoaded("rptReportName")
  DoEvents
Loop

Then in a global module, the following functions will test if a particular form or report is loaded:

Function IsFormLoaded(pstrFormName As String) As Integer
  IsFormLoaded = SysCmd(SYSCMD_GETOBJECTSTATE, A_FORM, pstrFormName)
End Function

Function IsReportLoaded(pstrReportName As String) As Integer
  IsReportLoaded = SysCmd(SYSCMD_GETOBJECTSTATE, A_REPORT, pstrReportName)
End Function

Updated: 04/11/1996 - Trevor Best

Back to Top
Q How do I access the recordset of a SUBform. I know how to clone the set of a form, but this doesn't work for subforms.
Back to Top
A All Versions: Let me guess, you tried: forms!myform!MySubForm.RecordsetClone

This will always fail as MySubForm points to a control on the form, you need to access it's Form propery, thus:

forms!myform!MySubForm.Form.RecordsetClone

Should work OK.

Updated: 21/12/1997 - Trevor Best

Back to Top
Q How do I emulate the "Record X of Y" that Access displays in the navigation buttons
Back to Top
A Access 2.0: The following function will do this for a form, just send any string and the form object as the parameters,
e.g. in a ControlSource use

=RecordNumber("Item",[Form])

For code, use:
strVariable = RecordNumber("Item",Me)
or
strVariable = RecordNumber("Item",Forms!MyForm)

This will return something like "Item 4 of 899", if the form is on a new record it will return the string "New Record".
------- begin cut here -----------
Function RecordNumber (pstrPreFix As String, pfrm As Form) As String
    On Error GoTo RecordNumber_Err
    Dim rst As Recordset
    Dim lngNumRecords As Long
    Dim lngCurrentRecord As Long
    Dim strTmp As String
    
    Set rst = pfrm.recordsetclone
    rst.MoveLast
    rst.bookmark = pfrm.bookmark
    lngNumRecords = rst.recordcount
    lngCurrentRecord = rst.absoluteposition + 1
    strTmp = pstrPreFix & " " & lngCurrentRecord & " of " & lngNumRecords
RecordNumber_Exit:
    On Error Resume Next
    RecordNumber = strTmp
    rst.Close
    Set rst = Nothing
    Exit Function
RecordNumber_Err:
    Select Case Err
        Case 3021
            strTmp = "New Record"
            Resume RecordNumber_Exit
        Case Else
            strTmp = "#" & Error
            Resume RecordNumber_Exit
    End Select
End Function

------- end cut here -----------

Access 97: Same but for the fact that the trap for new record doesn't work so it displays "Record 0 of xxx", I haven't investigated this yet but I would assume that checking for rst.AbsolutePosition returning -1 could prove fruitful, then again you might like the Record 0 approach.

Updated: 21/12/1997 - Trevor Best

Back to Top
Q How can I Force input to upper case in a text box?
Back to Top
A Several ways:

You can use an input mask, e.g. ">AAAAAAAA"

You can use a OnKeyPress Event Procedure, e.g.

Sub Field_KeyPress(KeyAscii As Integer)
  KeyAscii = Asc(Ucase(Chr(KeyAscii)))
End Sub

Or use an AfterUpdate event procedure, e.g.
Sub Field_AfterUpdate()
  Field = Ucase(Field)
End Sub

Updated: 26/08/1996 - Trevor Best

Back to Top
Q I change the forecolor of a control using the color builder and I do not get the text color I selected. Why not?
Back to Top
A No matter what color you select, the forecolor will always default to one of the original 16 in the palette. This is a know limitation of Windows and is not caused by Access.

Updated: 17/08/1996 - Richard Laughinghorse

Back to Top
Q When I Tab past the last control on a form, how can I keep Access from going to the next record (or backwards when I Shift+Tab back from the first control)?
Back to Top
A Access 2.0
A) Create two small, transparent buttons (set their Transparent property to Yes). Be careful that you don't make them invisible by setting their Visible properties to No.
B) place one of the buttons just to the left of the first user-editable control on the form, and place the other just to the right of the last user-editable control.
C) Create two macros that you will assign to the buttons' OnEnter events:
i) For the first button, use GotoControl to go to the FIRST control on your form.
ii) For the other button, use GotoControl to go to the LAST control on your form.
D) Choose the Tab Order command from the Edit menu and place the the button at the bottom of the tab order; place the last button at the top of the tab order.

HOW IT WORKS: As you tab through the controls and reach the last editable control, pressing Tab again enters the new, transparent button. Since its OnEnter event triggers a macro that sends you back to the first editable control, it has the effect of keeping you on the same record. Pressing Shift+Tab from the first control has the opposite effect.

See Also MSKB #Q112064

Access 97 Check out the "Cycle" property of the form, neat and about time too Microsoft.

Updated: 22/12/1997 - Ken Getz

Back to Top
Q How can I make my combo and list boxes faster? #1
Back to Top
A
- Include only fields from the record source that are absolutely
necessary. Extra fields can decrease combo or list box performance.

- Index the first field that is displayed in the combo or list box.

- Index any other fields used for criteria (such as when a combo or list
box is based on a criteria query).

- In combo boxes, set the AutoExpand property to No if it is not needed.

- Create a default value for combo boxes. Combo boxes try to match
whatever is entered in them, so if there is no default value for the
combo box, the combo box tries to match a null value when it is first
opened. An example of a default value for a combo box is:

=[combobox].ItemData(0)

In the example above, [combobox] is the name of the combo box.

- Use unbound subforms to display data when there is a large number of
records.

Updated: 08/11/1996 - Jim Hance

Back to Top
Q How can I make my combo and list boxes faster? #2
Back to Top
A Combo boxes in Access are very inefficient when large amounts of records are dealt with, the single best thing you can do is to restrict the number of records that are returned in the query. What I generally do is to place a text box on top of the combo box so that only the drop down button is visible. The idea is that in most cases, the user drops the box and starts typing away to get down the records he/she wants, my way just reverses the order of things, the user types the first part of what they want then drops the list. The OnEnter event will change the RowSource of the combo box to return a few records depending on what the user typed in, e.g.

Sub cbo_Enter()
cbo.RowSource="select id, desc from tbl where desc like '" & txtTextBox & "'"
End Sub

I generally have a similar procedure tagged onto the NotInList event of the combo box just in case the user finds nothing and starts typing in something that isn't there (as they do).

Updated: 08/11/1996 - Trevor Best

Back to Top
Q How do I captitalise the first letter of each word (proper case)
Back to Top
A 
Function Proper (var As Variant) As Variant
' Purpose: Convert the case of var so that the first letter of each word capitalized.

   Dim strV As String, intChar As Integer, i As Integer
   Dim fWasSpace As Integer    'Flag: was previous char a space?
   
   If IsNull(var) Then Exit Function
   strV = var
   fWasSpace = True              'Initialize to capitalize first letter.
   For i = 1 To Len(strV)
      intChar = Asc(Mid$(strV, i, 1))
      Select Case intChar
      Case 65 To 90              ' A to Z
         If Not fWasSpace Then Mid$(strV, i, 1) = Chr$(intChar Or &H20)
      Case 97 To 122             ' a to z
         If fWasSpace Then Mid$(strV, i, 1) = Chr$(intChar And &HDF)
      End Select
      fWasSpace = (intChar = 32)
   Next
   Proper = strV
End Function

Updated: 04/11/1996 - Allen Browne

Back to Top
Q How do I make a combo box drop down as soon as the user puts the cursor in there.
Back to Top
A Access 2.0 Use an On Enter event procedure like:


Sub cboSupplier_OnEnter()
Sendkeys "{F4}"
' or you can use Sendkeys "%{Down}"
End Sub


Beware when doing this OnEnter, as if the users first action on this combo before entering it is to click the down arrow with the mouse then your code will actually have the opposite effect as the F4 or Alt+Down is a toggle.

Access 97 Combo Boxes have a .Dropdown method.

Updated: 22/12/1997 - Trevor Best

Back to Top
Q How Can I get the name of the control that the mouse pointer passes over, Screen.ActiveControl doesn't work as users have a habit of moving the mouse over other objects on the screen apart from the active one (the awkward so and so's :-)
Back to Top
A This'll take some coding, you'll need two procedures, one to get the name of the control, and one to blank it out when the mouse moves over a blank part of the screen. In a global module, create a global variable and....

(Declarations)
Option Explicit

Global gctlUnderMouse As Control

Function SetCtlUnderMouse(pctl As Control)
  Set gctlUnderMouse = pctl
End Function


Now, in the form, you need to set the MouseMove property of each control, for the background (Form, detail, header, footer) use:

=SetCtlUnderMouse(Nothing)

This will set the global variable to nothing, indicating that the mouse is not hoverig over a control that you're intrested in.

For Controls that you're intrested in use:

=SetCtlUnderMouse([ControlName])

Where controlName is the name of the control, of course this would be ultra tedious doing this in one form, let alone every form in the database so here's a bit of code that will do it for you, just bring up the form in design mode, select the objects you want to have this done to using [Shift] + Click, then open the code window, and in the immediate window type:

CreateSetCtlUnderMouse Me

Here's the code that will run (in a global module):
Sub CreateSetCtlUnderMouse(pfrm As Form)
  Dim ctl As Control, i As Integer
  For i = 0 to pfrm.Count - 1
    Set ctl = pfrm(i)
    If ctl.InSelection Then
      ctl.OnMouseMove = "=SetCtlUnderMouse([" & ctl.Name & "])"
    End If
    Set ctl = Nothing
  Next
End Sub


Updated: 16/11/1997 - Trevor Best
Back to Top
Q How can I make text on a form or report appear bold based on a condition.
Back to Top
A Assuming you know your condition (I don't, could be anything), the code would go like:

If <bold-condition> Then
  Control.FontWeight = 700
Else
  Control.FontWeight = 400
End If

Updated: 16/11/1997 - Trevor Best

Back to Top