Replies

Thank you for your help. I have built in a function based on the code you referenced. I can tell it is opening our remote location. I am having the button on the form export the tables as text files and referencing the text file names for the function. Do I need the entire path name of the text files or is the current path assumed?

Thanks Charlotte. Works like a carm.

Thanks Pat. These are text fields so I had to enclose them in Chr(34) but it works just fine.

Pat,
Can you tell me the appropriate syntax for the “In” operator? Right now I have a function that returns a string like this:

strItems = “10408W” Or “10001C”

The SQL statement looks like this:
strSQL = “INSERT INTO [ClientNoMatch] ([Client No], [Client Name]) ” & _
“SELECT [Client Master].[Client No], [Client Master].[Client Name] FROM [Client Master]” & _
“WHERE [Client Master].[Client No] In & strItems ”

I get the following error message:
Run time error ‘3075’:
In operator without () in query expression ‘[Client Master].[Client No] In & strItems’.

First, should strItems be enclosed in ( parenthesis )?
Second, should the list of client numbers in strItems be an “Or” statement?

Thanks

good point. This was a SQL statement copied from the SQL View of the Query in Access. I think Access automatically puts that last line it there. I removed it and the query works just fine.
Thanks,

works perfectly. Thanks.

Hans,
Thanks. This works fine for the subreports and prints the correct quantities. The only problem now is that we need the Name Section to print before each of the copies. For example, when we come to a customer with 2 CC names, we get:

Section Header with Company Name, address, City, St, Zip, CCs
Copy one of subreport 1
Copy one of subreport2
Copy two of subreport 1
copy two of subreport 2
copy three of subreport 1
copy three of subreport 2

So, we are going to explore adding another section heading. Any suggestions?
thanks,

Pat,
The quantity (intCopyCount) is calculated in the query that populates the report. Would I be able to set the loop in the form not knowing the value of intCopyCount first?

However, the form has a multiselect listbox where the client(s) are selected. If no clients are selected, all reports are printed. I could attach a quantity to each client in the listbox by adjusting the query in the rowsource property, I suppose. Or do you think there is an easier way?
Best regards,

We would like to make one print run but we want to print multiple copies of the report for those clients who have multiple CC: names. Example – Client ABC has no CC: names therefore only one copy but client DEF has 2 CC: and needs three copies of the their report.
I have this code in the page header section but it does not repeat when needed.

Private Sub PageHeaderSection_Print(Cancel As Integer, PrintCount As Integer)
Dim intCopyCount As Integer ‘qty count from query
Dim I As Integer ‘counter
‘qty 0 means one copy to be printed
‘qty greater than 0 means qty value to be printed.

intCopyCount = Me.Qty
If intCopyCount > 0 Then
For I = 1 To intCopyCount + 1
Me.NextRecord = False
Next I
Else
Me.NextRecord = True
End If
End Sub

The report looks like this:
======================
Page header – contains nothing Height = 0
NameHeader – contains the client name, address, date and cc: lines
ABC Company
123 Anystreet
Anytown, ST, 12345-5678

Date

cc: =IIf(IsNull([CC]),””,”cc: ” & [CC1] & Chr(13) & Chr(10) & IIf(IsNull([CC 2]),””,” ” & [CC2]))

Some standard text. ” Attached please find your monthly reports….blah blah”

Client Number Header – two sub reports separated by a PageBreak control
SubReport A – Active accounts
SubReport B – Closed Accounts

Detail section – contains nothing – Height = 0

Page footer Section- contains nothing – Height = 0
Hope this helps.

Wendell and Mark,
Thanks. It turns out that I have reason to use both of your solutions. The continuous form view works for a human resources application that we have. When you say “double rows”, Wendell, I assume that you mean that I put the first 5 fields on one “row” and then the last 4 directly under while in form design view. Each form is really only about 1/2 inch in height and spans the width of an 800×600 display with no scrolling.

The code solution for works for the other db. In this one, we want the users to be able to see something that looks as much like an Excel spreadsheet as we can.

Thanks,

Go to the Visual Basic Editor and click on Tools|References. To get to the Visual Basic Editor, select any form or report or module in the database window and click View|Code.

I usually get help from this forum but once in a while I know an answer. I hope this helps.
aap2

Thanks. Front-ends are located on workstations, mostly WindowsXP with OfficeXP. Tables are indexed but I am curious about what gets pulled to the user’s front end when they open a report based on a query. Is the recordset made on the backend and then just the results sent over the lines to the front-end or do entire tables come accross for processing on the user desktop?

Also, did SQL Server improve performance?

Thanks,

Thanks for the tip. I’ll check it out.
aap2

Wendell, Mark,Pat,

Thanks for the guidance. For the moment, a batch file that automatically copies the front end to the users pc is fine. Another deployment question has come up. We have multiple users, some with windows NT some with windows xp some with Office 97 some with office xp and some without office. The “run-time” version of the db front-end has been a good solution here. However, I put the setup.exe file and all the supporting files on a network location that all have access to and tell users to navigate to that location on the network in windows explorer and run the setup.exe. Is there a good reason to make some installation CDs and pass them around instead?
Thanks again,

Mark,
Thanks for your help. So, if I am understanding you correctly, all I have to do is go into the “Support” folder on their PC and replace the “db.mdb” with “revised_db.mdb” (I keep the same db name) and when they run the application next time they should see the revised form and query? Is this the standard way to revise deployed run-time applications? If so, is there a way to make this a little more automatic? Can I build some utility into it to check for the latest version or something like that?
Thanks,