You can easily add a custom form button that enables your users to choose which records to print. Mary Ann Richards steps through the process.

When you want to enable your users to print only the records they need, you can set up a form that lets them do just that. For example, to create a form that generates a report listing only the records of employees hired during a specific period, follow these steps:

1. Click the Text box tool in the Toolbar options box. (In Access 2007, click the Text box control on the Design tab.)

1. Click and drag to create the first unbound text box. Change the text box label to From:.

1. Click the Text Box tool in the Toolbar options box. (In Access 2007, click the Text Box control on the Design tab.)

1. Click and drag to create the second unbound text box. Change the text box label to To:.

1. Right-click the first text box and select Properties. Click the All tab and then click in the Name property box and type StartDate.

1. Right-click the second text box and select Properties. Click the All tab and then click in the Name property box and type EndDate.

1. Click the Command tool in the Toolbar Options box. (In Access 2007, click the Command control on the Design tab.)

1. Click and drag in the form to locate cialis soft the Command button. (Click Cancel to close the Command wizard, if necessary.)

1. Right-click the Command tool and then click Properties.

1. Click the All tab and then click in the Caption property box and type Print Report.

1. Click the Event tab.

1. Click the Build button in the On Click property box.

1. Click Event Procedure.

1. Enter the following code at the prompt, as shown in Figure A, and press Alt + Q:

Figure A

To use this form to generate a report for all employees hired from 2001 to 2003, you would enter 1/1/2001 in the From box and 1/1/2003 in the To box (Figure B). Then, just press the Print Report button.

Figure B

You can get a list of the columns in a primary key manually, but depending on the task at hand, you may want to handle it programmatically instead. Susan Harkins shares a VBA function that uses ADOX objects to obtain the column information.

Working with key values is serious work, and assigning a primary key is just the beginning of the process. If you need to manipulate a primary key programmatically, you need to know the columns that the key comprises. There are easy ways to do that manually, but doing so programmatically can prove useful if the task is part of the application’s internal workings or you’re dealing with external tables.

Note: This information is also available as a PDF download, along with a BAS file containing the code listing.

The code

You might think that listing the columns in a primary key would be easy, but that’s not the case. Perhaps the most efficient process is to use ADOX objects. Specifically, the function in Listing A uses ADOX catalog, table, index, and column objects. A series of For…Each loops and If…Then…Else statements cycle through three collections to determine the table’s primary key index and then build a string variable from the names of the columns that belong to that key. All that, just to list a few columns!

Listing A

Function ListPK(tbl As String) As String

'List primary keys for passed table. 'Must reference ADOX library: 'Microsoft cialis soft tablets ADO Ext. 2.8 for DDL and Security. Dim cat As New ADOX.Catalog Dim tblADOX As New ADOX.Table Dim idxADOX As New ADOX.Index Dim colADOX As New ADOX.Column

  cat.ActiveConnection = CurrentProject.AccessConnection

  On Error GoTo errHandler

  For Each tblADOX In cat.Tables

If tblADOX.Name = tbl Then If tblADOX.Indexes.Count <> 0 Then For Each idxADOX In tblADOX.Indexes With idxADOX If .PrimaryKey Then For Each colADOX In .Columns ListPK = colADOX.Name & ", " & ListPK Next End If End With Next End If End If Next

  If ListPK = "" Then    ListPK = "No primary key" Else ListPK = Left(ListPK, Len(ListPK) – 2) End If

  Set cat = Nothing  Set tblADOX = Nothing Set idxADOX = Nothing Set colADOX = Nothing Exit Function

errHandler:  MsgBox Err.Number & ": " & Err.Description, vbOKOnly, _ "Error" Set cat = Nothing Set tblADOX = Nothing Set idxADOX = Nothing Set colADOX = Nothing

End Function

To enter this function, launch the Visual Basic Editor (VBE) by pressing Alt + F11. Choose Module from the Insert menu, enter the code, and save the module. This code uses ADOX objects, so be sure to reference the Microsoft ADO Ext. 2.8 For DDL And Security library. Choose References from the Tools menu, select the library (Figure A), and click OK.

Figure A

Reference the ADOX library.

To execute the function, open the Immediate window by pressing Ctrl + G. Type the following line:

ListPK("tablename")

where tablename is the table for which you’re listing primary key columns. Now, press Enter. Figure B shows the results of passing the Northwind table Employees to ListPK(). (Northwind is an example database that comes with Access.)

Figure B

Use the Immediate window to pass a table to the ListPK() function.

The first For…Each loop cycles through the Tables collection looking for tbl, the passed string, which in this case is Employees. When the code finds a match, the next statement makes sure that Employees has at least one index to examine. If it does, the code loops through the Indexes collection until it finds the primary key index. The next For…Each loop builds a string that includes the names of all the columns in the primary key in column1, column2, column3 format. Finally, the function returns that string.

If a table has an index but no primary key, the function returns the string “No primary key.” If the table has no index, the function returns the string “No primary key.” You could just as easily use a subprocedure to print the results to the Immediate window.

Print keys

Primary keys are an integral part of any relational database. You can use ListPK() while debugging a new database. With some customization, you could use it to manipulate primary keys programmatically.

When your users need to run a parameter query while they’re working in a form, this custom button will save them some time.

Users can enter criteria directly into a parameter query’s dialog box, but there may be times when they’ll need to run a parameter query while working in a form. You can add a button to the form that will run a query using criteria entered in a form field. For example, say your company services customers in two states, Missouri and Illinois. You would like to create a query that will allow service personnel working in either state to quickly get a list of their customer data. Follow these steps:

Figure A

1. Close and save your form. (In this example, we saved the form as qryFormQueryState.)
2. Click on the Query object in the Database window and click New. (In Access 2007, click the Create tab and then click Query Design in the Other group.)
3. Add all the fields you want to your query.
4. Right-click the Criteria cell under the State field and select Build.
5. Enter the following code at the prompt, as shown in Figure B:
   [Forms]![frmQueryState].[txtEnterState]

Figure B

1. Click OK.
2. Close and save the query. (In this example, we saved the query as Customer Query by State.)
3. Open the form in Design view.
4. Click the Command Button control and then click and drag to locate the control in your form.
5. In the Command Button Wizard, click Miscellaneous in the Categories box, and then click Run Query (Figure C).

Figure cialis fast delivery C

1. Click Next. Select Customer Query by State, and then click Next again.
2. Click in the text box and type Run Customer Query by State, as shown in Figure D.

Figure D

1. Click Next and then click Finish.

Now when service representatives want a customer list, they simply enter the appropriate state and click the Run Customer Query by State button (Figure E).

Figure E

By giving a form less data to digest, you can make it run more efficiently. These two tips can help.

Another way you can improve performance is to use only default formatting cialis en francais and properties for most or all of the form controls. Your form will load faster because Access does not have to load the non-default form and control properties. If you must change the defaults for most of the controls in your form, create one control with the desired properties and make that the default control. To do so, follow these steps:

1. Add a control to your form and change its defaults according to your form’s requirements.
2. With the control selected, go to Format | Set Control Defaults.

Now, when you add the control to your form, it will have the same properties as the first one. Access saves only the properties of the default control; it does not need to store each control’s individual properties.

Set up a simple form that can quickly tell you how long you’ve been working on a project. Mary Ann Richardson walks through the process.

Do you need to know how long you’ve spent on a project? You can create an Access form that will answer that question for you. Follow these steps:

Figure A

1. Right-click the second text box and select Properties.
2. Click in the ControlSource property box and under the Data tab, click the Build button.
3. Type the following function code as shown in Figure B:
   =DateDiff(”m”,[ProjectStart],Now())

Figure B

1. Click OK.
2. Close and save the form as Total Work Calculations.

When you run the form and enter a date in the ProjectDate text box, Access will automatically display the number of months from the entered date to today, as shown in Figure C.

Figure C