Printing (Access 2000/ Windows XP)

[frippyman1]

Hello all,
I have a form and on this form is a combo box which selects an assembly and then with a click on a command button the information is printed with work instructions for that assembly and this is done obviously through automation. Now my question is: I would like to print out other documents that are associated with the assemblies. This would be very easy if all the assemblies had the same amount of documents but each assembly has a different amount of documents. SO, what would be the easiest way to accomplish this task. Through code or some other method? I am sorry for the vagarious nature of my question but it is a start. Any help will be very much appreciated.

[HansV]

My reply will be general, since your question is - as you indicate yourself - rather vague.

I assume the "assembly" has some kind of identifier.
You could create a table with 2 fields: a field linked to the assembly identifier, and a text field containing the path/filename of a document associated with the assembly.
The table would contain a separate record for each document, so if there are 5 documents associated with a particular assembly, there would be 5 records with the assembly identifier, each with a different document.
The On Click event procedure would open a recordset with the records for the selected assembly, and loop through its records, printing each document in turn.

[frippyman1]

Hans,
As Always thanks for the reply. I thought something like was a way to go. Just really wanted to check to see if my thinking was flawed. I have been out of database development for a while and thought I'd pick your brain. Thanks again for your reply.

[MickeyShmack]

Hans,
Its been a while. Sorry about the latency in my reply. Regarding your last answer, you are correct there is an autonumber field which supplies a unique identifier for each assembly. As far as the code on the command button, could you point me to an example. Its been a while since ive been into access so any help is very much appreciated.

[HansV]

Copy the following code into a standard module (the kind you create by clicking New in the Modules section of the database window):

<img src=/w3timages/blueline.gif width=33% height=2>

Public Declare Function ShellExecute Lib "shell32.dll" Alias "ShellExecuteA" ( _
ByVal hwnd As Long, _
ByVal lpOperation As String, _
ByVal lpFile As String, _
ByVal lpParameters As String, _
ByVal lpDirectory As String, _
ByVal nShowCmd As Long) As Long

Public Const SW_SHOWNORMAL = 1

<img src=/w3timages/blueline.gif width=33% height=2>

Here is possible code for the command button. Comment follows after the code.

<img src=/w3timages/blueline.gif width=33% height=2>

Private Sub cmdPrint_Click()
Const strPath = "CocFolder"
Dim strFile As String
Dim strSQL As String
Dim cnn As ADODB.Connection
Dim rst As New ADODB.Recordset
Dim lngResult As Long

On Error GoTo ErrHandler

Set cnn = CurrentProject.Connection
strSQL = "SELECT * FROM tblDocuments WHERE ID = " & Me.cboSomething
rst.Open strSQL, cnn, adOpenForwardOnly, adLockOptimistic, adCmdText
Do While Not rst.EOF
strFile = rst!FileName
lngResult = ShellExecute(hWndAccessApp, "Print", _
strPath & strFile, 0&, 0&, SW_SHOWNORMAL)
If lngResult <= 32 Then
MsgBox "Can't print " & strFile, vbExclamation
End If
rst.MoveNext
Loop

ExitHandler:
On Error Resume Next
rst.Close
Set rst = Nothing
Set cnn = Nothing
Exit Sub

ErrHandler:
MsgBox Err.Description, vbExclamation
Resume ExitHandler
End Sub

<img src=/w3timages/blueline.gif width=33% height=2>

Comments:
<UL><LI>cmdPrint is the name of the command button.
<LI>strPath contains the path of the folder containing the documents (I have assumed they're all in the same folder). Modify as needed, but keep the trailing backslash.
<LI>tblDocuments is the name of the table containing the document names; it has two fields:
<UL><LI>ID (numeric, corresponding to the ID from the combo box)
<LI>Filename (string, contains the document name)[/list]<LI>cboSomething is the name of the combo box.[/list]You should change all these to the appropriate values for your situation.

[MickeyShmack]

Thanks Hans as always you are of great help.