More Access Tips & Tricks

Using pop-up forms

If PopUp = YES it allows the form to be displayed on top of the other windows as a pop-up dialog box.

There are two kinds of pop-up forms: A modal pop-up form, which forces a user to make a choice or attend to a task before he or she can continue to work and a modeless pop-up form which displays additional information but allows access other objects and menu commands while it's open.

Coloring Alternate records in reports

Enter the design mode for the report and add a new unbound text box to the detail section. Right click on the box and choose properties. On the format tab set the visible property to 'No' on Data tab set control source to =1 and the running sum property to 'Over All' on Other Tab set the name property to RunSum and close the property box.

From the menu choose View -> Code and type the following in.

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
If [RunSum] Mod 2 = 1 Then
Me.Section(0).BackColor = vbYellow
Else
Me.Section(0).BackColor = vbWhite
End If
End Sub

DCount counts the number of records that meet criteria specified in the criteria:

Count("[ContactName]","Customers","[ContactName] Like 'S*'")

Counts contact names that have S as the first letter

DSum sums the field of records that meet criteria specified in the criteria:

DSum("[Quantity]*[UnitPrice]","Order Details","[ProductID]=" & [ProductID])

Where the Product IDs in the current table match the Product IDs in the Order Details table, updates sales totals based on the product of Quantity and UnitPrice

DLookup returns the first record that meet criteria specified

DLookUp("[InvoiceTotal]","CustomersInvoices","[InvoiceDate] = #21/1/2000# AND [CustomerID] = 7")

Returns the first InvoiceTotal from customerinvoices where the invoice date = 1/21/00 and the customerid = 7

To Open a Combo Box automatically when user tabs or selects field: Set the combos box’s OnGotFocus porperty to the following event procedure: (replacing FieldName with your actual field’s name)

           Private Sub FieldName_GotFocus()

          Me!ComboBoxName.Dropdown

             End Sub

When your form is in Design View switch to Form View by pressing [F5].
To embed a form as a subform, just drag it from the Forms list to the main form in design view.
You can place a datasheet on a report by creating a datasheet form and dragging it to a report, where it will become a subreport.
Use Shift [F2] to Zoom field or property information

Joins - An inner join selects only equal fields from both tables.

A outer joins select all records from one table (even if there is no matching data)and matching records from the second. You would use a right outer join to show all records from the right table, but only matching records on the left side. A left outer join shows all on left, but only matching from right.

In a Visual Basic for Applications procedure, you can use the Me keyword rather than the full identifier to refer to a control on the current form or report. For example, to assign the sum of the values in the Subtotal and Freight controls on a form to the variable OrderTotal in one of the form's event procedures, add the following statement to the event procedure:

OrderTotal = Me![Subtotal] + Me![Freight]

Converting to Proper Case - Create an Update Query with the expressionn StrConv([FieldName],3) entered in the “Update To” box.