|
|||||||
|
|
|
|||||
|
|
|||||||
How-To-Tips
Access 2000
This article describes how to set up a macro so that you can add new values to a combo box. The article uses the Orders form in the sample database Northwind.mdb as an example.
Follow these steps to modify the Orders form so that you can add new values
to the Salesperson combo box:
CAUTION: Following the steps in this
example will modify the sample database Northwind.mdb. You may want to back up
the Northwind.mdb file and perform these steps on a copy of the
database.
Macro Name
Condition
Action
-----------------------------------------------------
NewRecord
OpenForm
OnClose [EmployeeID] Is
Not Null
RunCommand
...
SelectObject
...
ReQuery
...
SetValue
NewRecord Actions
---------------------------------------------------------
OpenForm
Form Name:
Employees
View:
Form
Data Mode:
Add
Window Mode: Normal
OnClose Actions
----------------------------------------------
RunCommand
Command:
SaveRecord
SelectObject
Object Type: Form
Object Name:
Orders
In Database Window:
No
ReQuery
ControlName:
EmployeeId
SetValue
Item:
[Forms]![Orders]![EmployeeID]
Expression:
[Forms]![Employees]![EmployeeID]
OnDblClick:
Macro1.NewRecord
StatusBarText: Double-click to Add a New
Employee
On Close: Macro1.OnCloseTo see how this works, open the Orders form in Form view, and then double-click the Salesperson combo box. Note that the Employees form opens. After you add a new record, close the Employees form. The new employee's name appears on the Orders form.
At times, you may want to animate the movements of the Office Assistant in
response to actions performed in your application. For example, you may want to
have the Assistant appear when you open a particular form. Or you may want the
Assistant to react when a certain event is triggered. This article shows you how
to do so.
The following example creates three event procedures that control the
behavior of the Clippit Assistant. The first procedure selects Clippit and makes
the Assistant visible when the Employees form is opened. The second procedure
instructs Clippit to simulate listening to the computer when a field is updated.
The third procedure closes the Assistant when the form is closed and resets some
of the properties of the Assistant.
CAUTION: Following the steps
in this example will modify the sample database Northwind.mdb. You may want to
back up the Northwind.mdb file and perform these steps on a copy of the
database.
NOTE: This example assumes you have the Clippit
Assistant (Clippit.acs) installed on your computer. By default the Assistants
are installed in the C:\Program Files\Microsoft Office\Office folder.
Private Sub Form_Open(Cancel As Integer)
With Assistant
.Filename =
"Clippit.acs" ' Returns or sets the name of
the
' active Office Assistant.
.Visible =
True
.Animation =
msoAnimationGreeting ' Simulates greeting
user.
.Sounds =
True
.SearchWhenProgramming =
True
.FeatureTips = True
End
With
End Sub
Private Sub
FirstName_AfterUpdate()
With
Assistant
.Animation =
msoAnimationListensToComputer '
Animates
' Assistant.
End With
End Sub
Private Sub Form_Close()
If
Assistant.Visible = True Then
With
Assistant
.AssistWithHelp
= False
.SearchWhenProgramming =
False
.GuessHelp =
False
.FeatureTips =
False
.Visible =
False
End With
End If
End
SubFor more information about using the Office Assistant, click Microsoft
Access Help on the Help menu, type Assistant in the Office
Assistant or the Answer Wizard, and then click Search to view the topics
returned.
To see a list of file names that correspond to the different
Office Assistants, from the Visual Basic Editor, click Microsoft Visual
Basic on the Help menu, type "Filename property" in the Office
Assistant or the Answer Wizard, and then click Search to view the topic.
This article shows you how to use an SQL pass-through query for a form's
record source. It assumes that you know how to build and use SQL pass-through
queries.
You should keep the following considerations in mind when you
use SQL pass-through queries for record sources of forms:
Forms based on SQL pass-through queries are read-only because SQL pass- through queries are read-only. The recordset returned by an SQL pass- through query is a snapshot, or read-only recordset. This behavior is by design. In order for the form to be updateable, base your form on a linked table with a unique index.
The Query Builder of the RecordSource property displays a window that
looks similar to the Design window of a query. You use this window to build the
SQL string or query for the RecordSource property. The Query Builder will
set the RecordSource property to an SQL string if the SQL string is not
saved as a query. If you save the string as a query, the name of the query will
be used as the RecordSource property.
When you are using an SQL
pass-through query created by using the Query Builder, the ODBC connect string
defined in that query will not be returned as part of the RecordSource
property SQL string. This can produce the following error message:
Couldn't find input table or query '[Table from ODBC Server]'
Without the ODBC connect string, the form will look for a local table, instead of a table on the server. If you save the SQL string as a query, the RecordSource property will contain the name of the query instead of the SQL string, and the form will be able to retrieve the remote data correctly.
The best way to use server-based data in a subform is to link the remote
table and then base the subform on the linked table.
If you base a
subform directly on an SQL pass-through query, you may receive the following
error message when you open the form:
You can't use a pass-through query or a non-fixed column crosstab query as a record source for a subform or subreport.
This error occurs if you have LinkMasterFields and LinkChildFields
defined for the subform or subreport.
For more information about SQL pass-through queries, click Microsoft
Access Help on the Help menu, type "pass-through queries" in the
Office Assistant or the Answer Wizard, and then click Search to view the
topic.
This article shows you how to reference a subtotal control on a subform from a main form.
If you try to sum a subform field by using a calculated control on a main form, you may receive an error message in the main form control. For example, the following expression in a main form control generates a "#Name?" error message:
=Sum([MySubForm].Form![ExtendedPrice]) To avoid an error, you can sum the subform field by using a calculated
control in the subform's form footer. You can then reference the calculated
control from the main form to display the summed value.
There's an
example of this method in the Orders form of the Northwind sample database.
First, a calculated control in the footer of the Orders Subform sums the
subform's Extended Price field to create a subtotal. It uses the following
syntax:
Text Box Control
----------------
Name: OrderSubtotal
ControlSource: =Sum([ExtendedPrice]) Because the subform's DefaultView property is set to Datasheet, you do not see the calculated control in the subform's form footer when you open the Orders form in Form view. However, a text box control on the main Orders form refers to the subform's calculated control and gets its value to display. The main form control is called Subtotal and references the subform's calculated control. It uses the following syntax:
Text Box Control
----------------
Name: Subtotal
ControlSource: =[Orders Subform].[Form]![OrderSubtotal] This reference allows the main form to display a subtotal of a subform control while the actual totaling of subform values is performed by using a calculated control in the subform's form footer.
For more information about referencing subform controls, click Microsoft
Access Help on the Help menu, type "calculating a total in a subform"
in the Office Assistant or the Answer Wizard, and then click Search to
view the topic.
By default, a chart sorts the X-axis fields in alphabetical or numeric order. This article describes two methods that you can use to sort the X-axis fields in a different order.
There are two methods you can use to change the ordering of fields in a
chart. The first way is to add an ORDER BY clause to the SQL statement in the
chart's RowSource property. The second way is to create a query that
orders the fields the way that you want them, and then to use the query for the
chart's RowSource property.
CAUTION: Following the steps in
this example will modify the sample database Northwind.mdb. You may want to back
up the Northwind.mdb file and perform these steps on a copy of the
database.
To create a chart, follow these steps:
The following example demonstrates how to add an ORDER BY clause to the SQL statement in a chart's RowSource property:
SELECT [CategoryName],Sum([ProductSales]) AS [SumOfProductSales] FROM [Sales by Category] GROUP BY [CategoryName];
SELECT [CategoryName],Sum([ProductSales]) AS SumOfProductSales] FROM [Sales by Category] GROUP BY [CategoryName] Order By SUM([ProductSales]) desc;
The following example describes how to create and use a query for the chart's
RowSource property:
For more information about ordering fields, click Microsoft Access
Help on the Help menu, type "ORDER BY clause" in the Office Assistant
or the Answer Wizard, and then click Search to view the topic.
This article shows you how to use a subquery to compare the values in a record with the values in another record. In order to use the method described in this article, the table that you are using must have one or more fields that can be compared by using the Greater Than (>) or Less Than (<) operator.
The following example demonstrates how to calculate miles per gallon by subtracting a recorded mileage from the current mileage, and then dividing by the number of gallons of gasoline recorded in the current record. The example is divided into two sections: one for an Access database, and the other for an Access project.
Table:
MileageRecord
--------------------
Field Name:
Date
Data Type: Date/Time
Field Name:
Mileage
Data Type: Number
Field Size:
Single
Field Name: Gallons
Date Type:
Number
Field Size: Double
Date Mileage
Gallons
-----------------------------
7/08/1999 12340 14.8
7/13/1999 12700 12.6
7/18/1999 13090 13.7
7/25/1999 13425 11.9
PrevMileage: (Select Max(Mileage) from MileageRecord _
Where Mileage < Mile1.[Mileage]) This expression is a subquery that finds the highest mileage in the
MileageRecord table that is less than the mileage of the current
record.
Elapsed: [Mileage] - [PrevMileage] This field calculates the difference between the current and previous
mileage entries.
MPG: ([Mileage] - [PrevMileage]) / Gallons This field calculates the miles per gallon.
Table: MileageRecord
--------------------
Column Name: ID
Datatype: uniqueidentifier
Allow Nulls:
no
Default Value: newid()
Column Name:
Date
Datatype: datetime
Column Name:
Mileage
Datatype: decimal
Length:
9
Precision: 18
Scale:
2
Column Name: Gallons
Datatype:
decimal
Length: 9
Precision:
18
Scale: 2
Date Mileage
Gallons
-----------------------------
7/08/1999 12340 14.8
7/13/1999 12700 12.6
7/18/1999 13090 13.7
7/25/1999 13425 11.9
CREATE PROCEDURE
"Calculate_Mileage_Proc"
AS
SELECT
MileageRecord.Date,
MileageRecord.Mileage,
(SELECT
MAX(mileage)
FROM mileagerecord
WHERE mileagerecord.mileage < mileagerecord1.mileage)
AS PrevMileage,
MileageRecord.Mileage - (SELECT
MAX(mileage)
FROM mileagerecord
WHERE mileagerecord.mileage < mileagerecord1.mileage)
AS Elapsed,
(MileageRecord.Mileage - (SELECT
MAX(mileage)
FROM mileagerecord
WHERE mileagerecord.mileage <
mileagerecord1.mileage))
/
MileageRecord.Gallons
AS MPG
FROM
MileageRecord
INNER JOIN
MileageRecord MileageRecord1
ON
MileageRecord.id = MileageRecord1.idNote that you receive the following results from the query or the stored procedure:
Date Mileage PrevMileage Elapsed MPG
------------------------------------------------------------
7/08/99 12340
7/13/99 12700 12340 360 28.5714285714286
7/18/99 13090 12700 390 28.4671532846715
7/25/99 13425 13090 335 28.1512605042017
For more information about subqueries, click Microsoft Access Help on
the Help menu, type "SQL subqueries" in the Office Assistant or the
Answer Wizard, and then click Search to view the topic.
A Microsoft Access form has a CloseButton property, which you can use to prevent a user from closing the form with either the Close Button icon or by clicking Close on the File menu. However, the user can still close the form by pressing ALT+F4. This article shows you how to prevent the user from closing a form by pressing ALT+F4.
CAUTION: Following the steps in this example will modify the sample
database Northwind.mdb. You may want to back up the Northwind.mdb file and
perform these steps on a copy of the database.
This example prevents the
user from using ALT+F4 to close a form:
Form:
Customers
-------------------------
CloseButton: No
Option Explicit
Public blnClose As Boolean
blnClose = False
Command
Button
-------------------
Name:
cmdCloseForm
Caption: &Close
blnClose = True
DoCmd.Close acForm,
"Customers", acSaveNo
Dim strMessage As String
Dim intStyle As Integer
Dim strTitle As
String
strMessage = "You are attempting to close this form
incorrectly." & _
vbCrLf & "Please
try again using the designated Close Button"
intStyle =
vbOKOnly + vbCritical
strTitle = "Closing
Customers?"
If blnClose = False
Then
MsgBox prompt:=strMessage,
buttons:=intStyle, Title:=strTitle
Cancel =
True
End If
For more information about events, in the Visual Basic Editor, click
Microsoft Visual Basic Help on the Help menu, type "events and
event properties Reference" in the Office Assistant or the Answer Wizard, and
then click Search to view the topic.
Moderate: Requires basic macro, coding, and interoperability
skills.
You may want to display scroll bars in a subform only as needed.
The following function turns the scroll bars on or off according to a preset
number. The scroll bars do not appear unless the number of records in the
subform is greater than the preset number.
The following example demonstrates how to use an event procedure in the OnCurrent property of the subform to display or hide the scroll bars:
Private Sub Form_Current()
'
If the number of records in the subform
' is greater than 4,
display the
' horizontal and vertical
scrollbars.
If Me.RecordsetClone.RecordCount > 4
Then
Me.ScrollBars = 3
Else
Me.ScrollBars = 0
End
If
End Sub
For more information about the ScrollBars property, click Microsoft Access
Help on the Help menu, type "ScrollBars property" in the Office
Assistant or the Answer Wizard, and then click Search to view the
topic.
Sometimes you may want to display a status message to inform users that they must wait for an action to be completed. For example, you may want to display the text "Please Wait" while Microsoft Access is initializing a database, running a long query, and so on. This article uses the sample database Northwind.mdb to show you how to create a "Please Wait" message.
To display a status message in Microsoft Access, do the following:
Form: PleaseWait
---------------------
Caption: TestForm
ScrollBars: Neither
Popup: Yes
Modal: Yes
RecordSelectors: No
NavigationButtons: No
Label
--------------------
Caption: Please Wait
Font Size: 18 The label caption is the message you want to display.
Macro Name Action
-----------------------------
Test1 OpenForm
RepaintObject
OpenQuery
Close
Test1 Actions
-----------------------------
OpenForm
Form Name: PLEASEWAIT
View: Form
Data Mode: Read Only
Window Mode: Normal
RepaintObject
Object Type: Form
Object Name: PLEASEWAIT
OpenQuery
Query Name: Order Details Extended
View: Datasheet
Close
Object Type: Form
Object Name: PLEASEWAIT
Save: No NOTE: The above argument for OpenQuery is just an example of what you may
want to run; this can be anything. The main macro actions in Test1 are
OpenForm, RepaintObject, and Close.
The following example demonstrates how to create and use a form to track elapsed time:
ScrollBars:
Neither
RecordSelectors: No
NavigationButtons:
No
OnTimer: [Event Procedure]
TimerInterval:
0
Name: ElapsedTime
DefaultValue:
"00:00:00:00"
Enabled: No
Locked: Yes
Name: btnStartStop
Caption: Start
OnClick: [Event Procedure]
Name:
btnReset
Caption: Reset
OnClick: [Event
Procedure]
Option
Explicit
Dim TotalElapsedMilliSec As Long
Dim StartTickCount As
Long
Private Declare Function GetTickCount Lib "kernel32" () As Long
Private Sub Form_Timer ()
Dim
Hours As String
Dim Minutes As String
Dim
Seconds As String
Dim MilliSec As String
Dim
Msg As String
Dim ElapsedMilliSec As Long
ElapsedMilliSec = (GetTickCount() - StartTickCount) +
_
TotalElapsedMilliSec
Hours = Format((ElapsedMilliSec \ 3600000), "00")
Minutes =
Format((ElapsedMilliSec \ 60000) Mod 60, "00")
Seconds =
Format((ElapsedMilliSec \ 1000) Mod 60, "00")
MilliSec =
Format((ElapsedMilliSec Mod 1000) \ 10, "00")
Me!ElapsedTime = Hours & ":" & Minutes & ":" & Seconds &
":" _
& MilliSec
End Sub
Private Sub
btnStartStop_Click()
If Me.TimerInterval = 0
Then
StartTickCount =
GetTickCount()
Me.TimerInterval =
15
Me!btnStartStop.Caption =
"Stop"
Me!btnReset.Enabled =
False
Else
TotalElapsedMilliSec = TotalElapsedMilliSec +
_
(GetTickCount() -
StartTickCount)
Me.TimerInterval =
0
Me!btnStartStop.Caption =
"Start"
Me!btnReset.Enabled =
True
End If
End Sub
Private Sub
btnReset_Click()
TotalElapsedMilliSec = 0
Me!ElapsedTime = "00:00:00:00"
End SubTo perform a Do While or Do Until loop in a Microsoft Access macro, you need to use a combination of correct macro structure and the RunMacro action with appropriate information supplied in the Repeat Expression parameter. These requirements are described in more detail later in this article.
There are several types of Do loops. Each type handles the looping procedure and conditions differently. The different types of Do loops are as follows:
To perform a Do While or Do Until loop in a macro, use a macro similar to the following examples. To create the following examples, open a new macro and click Macro Names on the View menu. Add the following macro names and actions and save this macro group as Do_Loops.
Macro Name Action
-------------------------
Do_Loop1 RunMacro
Loop1 MsgBox
Do_Loop1 Actions
--------------------------------------
RunMacro
Macro Name: Do_Loops.Loop1
Repeat Expression: <your_condition>
Loop1 Actions
-------------------
MsgBox
Message: ="Loop" In this example, the Do_Loop1 macro calls the Loop1 macro while the Repeat Expression parameter of the RunMacro action is true.
Macro Name Action
-------------------------
Do_Loop1 RunMacro
RunMacro
Loop1 MsgBox
Do_Loop1 Actions
--------------------------------------
RunMacro
Macro Name: Do_Loops.Loop1
RunMacro
Macro Name: Do_Loops.Loop1
Repeat Expression: <your_condition>
Loop1 Actions
-------------------
MsgBox
Message: ="Loop"
In this example, the Do_Loop1 macro calls the Loop1 macro once unconditionally, and then continues to call the Loop1 macro while the Repeat Expression parameter of the RunMacro action is True.
The condition used in the loop, which is supplied in the Repeat Expression parameter of the RunMacro action, can be based on a value in a field on a form, a property of a control on a form, or the value returned from a Visual Basic function. If the condition is based on a field in a form or a property of a control on a form, it will have syntax similar to:
Forms![CounterForm]![Counter]<=10
-or-
Forms![EntryForm]![InvoiceNo].Visible=True
If the condition is based on the value returned from a Visual Basic function, it will have syntax similar to:
Time()>=TimeEntry() Whether a loop is a Do While loop or a Do Until loop depends on the condition. The RunMacro action runs the macro until the condition in the Repeat Expression parameter is false. This behavior is exactly what is needed for a Do While loop. It is, however, the opposite of what is needed for a Do Until loop. Therefore, to make a condition for a Do Until loop work correctly, precede the condition in the RunMacro action with the NOT operator.
This article demonstrates two methods that you can use to create a running
totals query. A running totals query is a query in which the total for each
record is a summation of that record and any previous records. This type of
query is useful for displaying cumulative totals over a group of records (or
over a period of time) in a graph or report.
The first method uses a DSum() function and criteria in a query to
create a running sum over time. The DSum() function sums the current
record and any previous records. When the query moves to the next record, the
DSum() function runs again and updates the cumulative total.
The
following sample query uses the Orders table from the sample database
Northwind.mdb to create a running sum of the freight costs for each month in
1997. The sample data is limited to one year for performance reasons. Because
the DSum() function runs once for every record in the query, it may take
several seconds (depending on the speed of your computer) for the query to
finish processing. To create and run this query, follow these steps:
Field: AYear: DatePart("yyyy",[OrderDate])
Total: Group By
Sort: Ascending
Show: Yes The expression in the Field box displays and sorts the year portion
of the OrderDate field.
Field: AMonth: DatePart("m",[OrderDate])
Total: Group By
Sort: Ascending
Show: Yes The expression in the Field box sorts and displays the month portion
of the Order Date field as an integer value from 1 to 12.
Field: RunTot: DSum("Freight","Orders","DatePart('m', _
[OrderDate])<=" & [AMonth] & " And DatePart('yyyy', _
[OrderDate])<=" & [AYear] & "")
Total: Expression
Show: Yes The expression in the Field box uses the DSum() function to
sum the Freight field when the values in both the AMonth and the AYear fields
are less than or equal to the current record that the query is
processing.
Field: FDate: Format([OrderDate],"mmm")
Total: Group By
Sort: Ascending
Show: Yes The expression in the Field box displays each month in a textual
format, such a Jan, Feb, Mar, and so on.
Field: DatePart("yyyy",[OrderDate])
Total: Where
Criteria: 1997
Show: No The expression in the Field box filters the query's recordset to
include data from 1997 only.
AYear AMonth RunTot FDate
--------------------------------------
1997 1 2238.98 Jan
1997 2 3840.43 Feb
1997 3 5729.24 Mar
1997 4 8668.34 Apr
1997 5 12129.74 May
1997 6 13982.39 Jun
1997 7 17729.29 Jul
1997 8 22204.73 Aug
1997 9 26565.26 Sep
1997 10 32031.38 Oct
1997 11 36192.09 Nov
1997 12 42748.64 Dec The second method uses a totals query with a DSum() function to create
a running total over a group.
The following sample query uses the Orders
table to sum freight costs per employee as well as to calculate a running sum of
the freight. To create and run the query, follow these steps:
Field: EmpAlias: EmployeeID
Total: Group By
Show: Yes This field groups data by EmployeeID.
Field: Freight
Total: Sum
Show: Yes This field sums the freight data.
Field: RunTot: Format(DSum("Freight","Orders","[EmployeeID]<=" _
& [EmpAlias] & ""),"$0,000.00")
Total: Expression
Show: Yes The expression in the Field box uses a DSum() function to sum
the Freight field when the EmployeeID is less than or equal to the current
EmpAlias, and then formats the field in dollars.
Employee SumOfFreight RunTot
-------------------------------------------------
Davolio, Nancy $8,836.64 $8,836.64
Fuller, Andrew $8,696.41 $17,533.05
Leverling,Janet $10,884.74 $28,417.79
Peacock, Margaret $11,346.14 $39,763.93
Buchanan, Steven $3,918.71 $43,682.64
Suyama, Michael $3,780.47 $47,463.11
King, Robert $6,665.44 $54,128.55
Callahan, Laura $7,487.88 $61,616.43
Dodsworth, Anne $3,326.26 $64,942.69
For more information about totals queries, click Microsoft Access Help
on the Help menu, type perform calculations in a query in the
Office Assistant or the Answer Wizard, and then click Search to view the
topics returned.
This article describes how you can remove duplicate records from a table by using primary keys. By using this method, you can check for duplicate values in up to 10 fields in the table.
To remove duplicate records from a table, follow these steps:
Microsoft Access can't append all the records in the append query.
Microsoft Access set 0 field(s) to Null due to a type conversion failure, and it didn't add <number> record(s) to the table due to key violations, 0 record(s) due to lock violations, and 0 record(s) due to validation rule violations.
Do you want to run the action query anyway?
To ignore the error(s) and run the query, click Yes. For an explanation of the causes of the violations, click Help.
For more information about deleting duplicate records, click Microsoft
Access Help on the Help menu, type "Automatically delete duplicate
records from a table." in the Office Assistant or the Answer Wizard, and then
click Search to view the topic.
This article shows you how to create a Visual Basic for Applications function
to determine if a date falls on a weekend or holiday. This example is useful for
setting due dates in applications that have billing or invoicing
features.
The following example uses the WeekDay() function to determine if a specific date falls on a Saturday or Sunday. Then, it uses a DLookup() function to determine if it falls on a date stored in a user-created Holidays table.
The following example requires a table with a particular structure for storing Holiday dates. To create the table and sample records, follow these steps:
Table: Holidays
-----------------------
Field Name:
Description
Data Type: Text
Field
Name: HoliDate
Date Type: Date/Time
Description
HoliDate
--------------------------------------------
New Year's
Day
1/1/2000
Martin Luther King, Jr. Day (USA)
1/15/2000
Memorial Day
(observed-USA)
5/29/2000
Labor Day
(USA)
9/4/2000To create a function that determines if a date falls on a weekend or holiday, follow these steps:
Function OfficeClosed(TheDate) As
Integer
OfficeClosed =
False
' Test for Saturday or Sunday.
If
WeekDay(TheDate) = 1 Or WeekDay(TheDate) = 7
Then
OfficeClosed =
True
' Test for Holiday.
ElseIf Not
IsNull(DLookup("HoliDate", "Holidays", "[HoliDate]=#"
_
& TheDate & "#"))
Then
OfficeClosed =
True
End If
End Function
? OfficeClosed(#9/4/2000#)Note that this returns a True value (-1) because 9/4/2000 is listed in the Holidays table.
You can use the custom OfficeClosed() function to calculate due dates. For example, if your office or business is closed for a three-day weekend, you may want to extend your customers' grace period for their outstanding bills. Here's sample code for adding one more day to a grace period:
DueDate=OrderDate+30
Do While OfficeClosed(DueDate)
DueDate=DateDue+1
Loop
For more information about the Weekday() function, in the Visual Basic
Editor, click Microsoft Visual Basic Help on the Help menu, type
weekday function in the Office Assistant or the Answer Wizard, and then
click Search to view the topic.
This article shows you how to create and use a procedure to display the current line or row number in a subform.
The following examples demonstrate how to create and use the sample function,
GetLineNumber().
NOTE: The sample code in this article uses
Microsoft Data Access Objects. For this code to run properly, you need to
reference the Microsoft DAO 3.6 Object Library.
Option Explicit
Function GetLineNumber (F
As Form, KeyName As String, KeyValue)
Dim RS As
DAO.Recordset
Dim CountLines
On Error GoTo
Err_GetLineNumber
Set RS =
F.RecordsetClone
' Find the current
record.
Select Case
RS.Fields(KeyName).Type
' Find using numeric
data type key value.
Case dbInteger, dbLong,
dbCurrency, dbSingle, dbDouble,
dbByte
RS.FindFirst "["
& KeyName & "] = " &
KeyValue
' Find using date
data type key value.
Case
dbDate
RS.FindFirst "["
& KeyName & "] = #" & KeyValue &
"#"
' Find using text data
type key value.
Case
dbText
RS.FindFirst "["
& KeyName & "] = '" & KeyValue &
"'"
Case
Else
MsgBox "ERROR:
Invalid key field data
type!"
Exit
Function
End Select
' Loop backward,
counting the lines.
Do Until
RS.BOF
CountLines = CountLines +
1
RS.MovePrevious
Loop
Bye_GetLineNumber:
' Return the
result.
GetLineNumber = CountLines
Exit
Function
Err_GetLineNumber:
CountLines =
0
Resume Bye_GetLineNumber
End FunctionThe GetLineNumber() function requires the following three parameters:
You could use the following sample expression as the ControlSource property setting of a text box on a subform, given that the subform's underlying table has a field called ID as its unique key field:
=GetLineNumber(Form,"ID",[ID]) CAUTION: Following the steps in this example will modify the sample
database Northwind.mdb. You may want to back up the Northwind.mdb file and
perform these steps on a copy of the database.
Field Name: ID
Data Type: AutoNumber This field will serve as the required single unique field for the
table.
Name: LineNum
ControlSource: =GetLineNumber([Form], "ID", [ID])This article describes how to display only the last two digits of the
year.
The expression
=Year(Now())
displays all four digits of the current year (for example, 1999).
In
order to display only the last two digits of the year (for example, 99), use one
of the following expressions:
=Format(Now(),"yy")
-or-
=Right(Str(Year(Now())),2)
The breakdown of expression 2 is as follows:
NOTE: The right two characters are returned as a text value. If you want them to be returned as a number, use the following expression:
=Year(Now()) Mod 100
For more information about the first expression, click Microsoft Access
Help on the Help menu, type format property - date/time data
type in the Office Assistant or the Answer Wizard, and then click
Search to view the topic.
For more information about the elements
in the second expression, in the Visual Basic Editor, click Microsoft Visual
Basic Help on the Help menu, type year function, right function,
str function, or now function in the Office Assistant or the Answer Wizard,
and then click Search to view the topic.
You cannot edit the information in a Hyperlink field in a table by clicking the link because that activates the link. This article describes the different parts of a Hyperlink data type, and shows you several methods that you can use to edit a Hyperlink field.
You can use Hyperlink fields to store links to other objects in an Access database, links to other files on your hard drive or network, or links to intranet and Internet Web addresses. Each Hyperlink field can store three pieces of information about a link: the DisplayText, the Address, and the SubAddress. These three pieces are separated by number signs (#) and have the following meaning:
DisplayText#Address#SubAddress
NOTE: When you view a Hyperlink field in the Datasheet view of a table or in a bound text box on a form, all you see is the DisplayText portion of the field. If there is no DisplayText, then you see the Address portion of the field; if there is no Address, then you see the SubAddress portion of the field.
For Hyperlink fields in a table, you click in the field to activate the link. If you want to edit the DisplayText, Address, or SubAddress portions of a Hyperlink field, use one of the following methods.
This method may require some practice.
For more information about using hyperlinks, click Microsoft Access
Help on the Help menu, type "Hyperlink data type" in the Office
Assistant or the Answer Wizard, and then click Search to view the topics
returned.
This article shows you how to use code to embed a bitmap image in a report at
run time. The example uses an image control instead of an OLE object frame to
store the bitmap.
You can use Visual Basic for Applications code to embed
an OLE object into a control on a form at run time. To use this method, however,
you have to set the Enabled property of the control to Yes and the Locked
property to No. In a report, OLE object controls do not have Enabled or Locked
properties. To work around this behavior in reports, you can embed bitmaps and
other graphic images in an image control.
CAUTION: Following the steps in this example will modify the sample
database Northwind.mdb. You may want to back up the Northwind.mdb file and
perform these steps on a copy of the database.
Private Sub Detail_Format(Cancel As Integer,
FormatCount As Integer)
Me!Image0.Picture =
"C:\Windows\Circles.bmp"
End SubFor more information about the image control, click Microsoft Access
Help on the Help menu, type "image controls, creating" in the Office
Assistant or the Answer Wizard, and then click Search to view the
topic.
For more information about the Format event in reports, click
Microsoft Access Help on the Help menu, type "OnFormat property"
in the Office Assistant or the Answer Wizard, and then click Search to
view the topic.
This article describes how to create a command button on a filtered form that, when clicked, opens a report and applies the filter that is on the form to the report.
This example uses the sample database Northwind.mdb. The technique involves creating a new form and a new report. The form uses event procedures to apply a filter and to open the new report. The report uses the Filter property to apply the same filter that is used in the form.
Name: cmdOpenReport
Caption: Open Report
OnClick: [Event Procedure]
Private Sub
cmdOpenReport_Click()
If Me.Filter = ""
Then
MsgBox "Apply a filter to
the form first."
Else
DoCmd.OpenReport
"rptCustomers", acViewPreview, , Me.Filter
End If
End
Sub
Name: cmdClearFilter
Caption: Clear Filter
OnClick: [Event Procedure]
Private Sub
cmdClearFilter_Click()
Me.Filter = ""
End Sub
Name: cmdClose
Caption:
Close
OnClick: [Event Procedure]
Private Sub
cmdClose_Click()
DoCmd.Close acForm, Me.Form.Name
End
Sub
OnOpen: [Event Procedure]
OnClose: [Event Procedure]
Private Sub Form_Open(Cancel as
Integer)
Me.Filter = ""
End Sub
Private Sub Form_Close()
DoCmd.Close acReport, "rptCustomers"
End SubFor more information about the Filter property, click Microsoft
Access Help on the Help menu, type Filter Property in the
Office Assistant or the Answer Wizard, and then click Search to view the
topics returned.
For more information about Filter By Form, click
Microsoft Access Help on the Help menu, type Modify a filter in
the Filter By Form window in a table, query, or form in the Office Assistant
or the Answer Wizard, and then click Search to view the topics
returned.
For more information about Filter By Selection, click
Microsoft Access Help on the Help menu, type Filter records by
selecting values in a form or datasheet in the Office Assistant or the
Answer Wizard, and then click Search to view the topics returned.
This article applies only to a Microsoft Access database (.mdb).
This
article demonstrates how to find records in a Microsoft Jet database using
ActiveX Data Objects (ADO) and OLE DB.
What follows are two example procedures. The first, CreateJetDB, creates a
new Microsoft Jet database in the root directory of on drive C and populates it
with data. The second, CursorLocationTimed, demonstrates using the Find
method with a server side cursor and with a client side cursor.
To create
these procedures, follow these steps:
Microsoft ActiveX Data Objects 2.1 Library
Microsoft ADO Ext. 2.1 for DDL and Security
Sub
CreateJetDB()
Dim cat As New Catalog
Dim cn As
New Connection
Dim rs As New Recordset
Dim
numrecords As Long
Dim i As
Long
' Number of sample records to
create
numrecords = 250000
On
Error Resume Next
'Delete the sample database
if it already exists.
Kill "c:\findseek.mdb"
On Error GoTo 0
'Create a new Jet 4.0 database
name findseek.mdb
cat.Create
"Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data
Source=c:\findseek.mdb"
'Set the
provider, open the database,
'and create a new table called
tblSequential.
cn.Provider =
"Microsoft.Jet.OLEDB.4.0"
cn.Open "Data
Source=c:\findseek.mdb"
cn.Execute "CREATE TABLE tblSequential
(col1 long, col2 text(75));"
'Open the
new table.
rs.Open "tblSequential", cn, adOpenDynamic,
_
adLockOptimistic,
adCmdTableDirect
Add sample records to the
tblSequential table.
For i = 0 To
numrecords
rs.AddNew
rs.Fields("col1").Value =
i
rs.Fields("col2").Value = "value_" &
i
rs.Update
Next i
rs.Close
'Create a multifield Index on col1
and col2.
cn.Execute "CREATE INDEX idxSeqInt on tblSequential
(col1, col2);"
'Close the
connection
cn.Close
End Sub
Sub
CursorLocationTimed()
Dim cn As New
Connection
Dim rs As New Recordset
Dim i, j As
Long
Dim time As Variant
On
Error GoTo ErrHandler
cn.Open
"Provider=Microsoft.Jet.OLEDB.4.0;" _
& "Data
Source=c:\findseek.mdb"
' Specify how ADO
should open the recordset:
' adUseServer - use the native
provider to perform cursor
' operations
'
adUseClient - use the client cursor engine in ADO
' NOTE:
adUseServer more closely resembles DAO
' Time
opening a recordset and doing 1000 finds (Server cursor
'
engine)
'
rs.CursorLocation =
adUseServer
time = Timer
'
Open the recordset and perform serveral Finds to locate
records.
' Using the adCmdTableDirect opens a base table
against Jet, which
' is generally the fastest, most functional
way to access tables.
rs.Open "tblSequential",
cn, adOpenDynamic, adLockOptimistic, _
adCmdTableDirect
For i = 0 To
1000
rs.Find "col1=" & i
Next i
Debug.Print "Sequential Find + Open
(Server) = " & Timer - time
rs.Close
' Time opening a recordset and doing
1000 finds (Client cursor
'
engine)
rs.CursorLocation =
adUseClient
time = Timer
rs.Open "tblSequential", cn, adOpenDynamic, _
adLockOptimistic, adCmdTableDirect
For i = 0
To 1000
rs.Find "col1=" &
i
Next i
Debug.Print
"Sequential Find + Open (Client) = " & Timer - time
rs.Close
Exit
Sub
ErrHandler:
For j = 0 To cn.Errors.Count -
1
Debug.Print "Conn Err Num : ";
cn.Errors(j).Number
Debug.Print "Conn Err
Desc: "; cn.Errors(j).Description
Next
j
Resume Next
End Sub
?CreateJetDB()
?CursorLocationTimed() You should next see output similar to the following:
Sequential Find + Open (Server) = 0.28125
Sequential Find + Open (Client) = 5.28125 NOTE: The resulting numbers may differ from computer to
computer.