Access Cookbook - Ken Getz [254]
SND_NOSTOP
16
Returns immediately with a value of FALSE without playing the requested sound if a sound is currently playing.
Normally, you'll call the sndPlaySound function to play the WAV file. If you use the SND_ASYNC or SND_LOOP flags, you'll need to call the sndPlaySound function again, passing the vbNullChar constant as the first parameter. The following code example is the simplest way to play a WAV file using the Windows API. You can try this out by loading the form frmSndPlaySound from 12-01.MDB and pressing the button on the form, which executes the following code:
Private Sub Button0_Click( )
Dim varSound As Variant
Dim intFlags As Integer
Dim intResult As Integer
Dim strWinDir As String
Dim intCount As Integer
Const conMaxLen = 255
' Find the Windows directory.
strWinDir = Space(conMaxLen)
intCount = GetWindowsDirectory(strWinDir, conMaxLen)
strWinDir = Left(strWinDir, intCount)
' Get the file name, using the common file open dialog.
varSound = acbCommonFileOpenSave(InitialDir:=strWinDir, _
Filter:=acbAddFilterItem("", "WAV Files", "*.WAV"), _
DialogTitle:="Choose a WAV File")
If Not IsNull(varSound) Then
intFlags = SND_ASYNC Or SND_NODEFAULT
intResult = sndPlaySound(varSound, intFlags)
If intResult = 0 Then
MsgBox "Unable to play sound."
End If
End If
End Sub
This example is complicated by the fact that it uses the Windows File Open dialog to request the name of the WAV file that you'd like to play (the folder named Media is a good place to look), but the heart of the routine is quite simple.
See Also
For more information on working with the Windows API, see Chapter 11.
12.2. Print an Access Report from Excel
Problem
You keep and work with your data in Excel, but you'd like to print reports using Access. You know you can use the Access Report Wizard directly from Excel, but you'd like more control over the process. Can you do this using VBA?
Solution
Access allows you to control its actions using Automation. Anything you can do directly from Access, you can also do from Excel. This solution uses Automation to link your Excel worksheet to an Access database, use that data as the data source for a report, and then remove the linked table. Because you can directly link to an Excel worksheet from Access, this process doesn't need to involve importing the data—you can use it as-is, live, in your Excel environment.
To try out the sample database, first load 12-02.XLS into Excel. This workbook includes the data (shown in Figure 12-3) and the VBA code that controls the sample. Next, click the Open Access Report button, which causes Excel to load a copy of Access and then load 12-02.MDB, link the current data to that database, and display the report in print preview mode.
Figure 12-3. Use data in Excel to print a report in Access
To use this technique in your own applications, follow these steps:
Create a database, including a report that you'd like to print. You may want to link the Excel data that's going to be the data source now, so that it's easier to create the report. You can leave it linked (in which case you'll want to modify the example code in your spreadsheet to not relink the table) or you can delete the link once you've created the report.
In Excel, create a new workbook or use an existing one. Add a new module (choose Tools Macro Visual Basic Editor, and then Insert Module) and enter the following code (or copy it from 12-02.XLS):
Option Explicit
Const conXLS = "12-02.xls"
Const conMDB = "12-02.mdb"
Const conTableName = "CustomersXLS"
Const conReportName = "Customers"
Private Sub HandleAccessReport( )
' This sample assumes that the database and
' the spreadsheet are in the same directory.
' It doesn't HAVE to be that way, of course,
' but it makes this simple example much simpler.
Dim accApp As Access.Application
Dim strPath As String
Dim strDatabase As String