Replies

There are two solutions that I am aware of for the situation you describe.

1) Open your windows as dialogs (pop-up property set to true).
2) Click the Microsoft button in the upper-left of the Access application. Then, select Access Options. In the form that opens, select Current Database. In the list on the right, six items down from the top there is a setting for Overlapping Windows. This setting provides the same functionality as Access 2003 and earlier.

I would store the data in a text field in Access. Then, to sort on the values, create a function that converts the date into a full date. For example, if the value is just 1995, convert it to 1/1/1995. If the date is 8/1995 or 8/95, convert it to 8/1/1995. The test would be to see how many slashes are in the date to determine if just the day or a month and day need to be added. With this method you would also need to add some input validation to make sure people were entering dates or you could sort anything that didn’t convert to a date to the top or bottom by having your function convert unrecognized values to 1/1/0000 or 12/31/9999.

You would call the function from within the query that retuns the data. It would be placed in the ORDER BY clause and the input parameter for the function would be the date field you want to sort on.

As far as the versions go, if you program in Access 2000 your code should work in all newer versions. With Access 2007 any menu’s you create will still be available as a ribbon and you may need to modify your code to work around some changes in that version.

In general, you can use a Select Case statement against the amount to see who to send the email to and then use the SendObject method to create the email in a MAPI client. The the list of names change you can build a lookup table to map the amount ranges to the correct name so you don’t have to change your VBA code anytime a name changes.

I’ve edited this post, in case you already looked at it before I hit the Post It button. The file you named is not on the machine. All that’s there now is the System.mdw file. However, when I was messing with the problem yesterday I did rename a file that was in that location and though I don’t recall the name I believe it was the file you mentioned. Anyway, renaming the file made no difference.

The database I was using before rebuilding the profile is split but I suspect that’s not the problem. I removed the network card from the virtual PC and that made no difference. The reports aren’t crosstabs. Once is a very simple mailing label based on a linked table. Just as slow with or without the network card and rebuilding my Windows User Profile fixed it.

I suppose it could have something to do with the printer driver, assuming something changes on the user profile over time with respect to the printer driver. I was wondering if it had to do with NTUSER.DAT or CLASS.DAT but I have no good way to test that because I’m also an administrative user.

Create a query that returns all of the data you want from the invoice table. In the WHERE clause, include a query that returns the Max(Invoice ID) from the same table where a common field is equal to the set of invoices you are querying. For example:

SELECT invoice_fields (one of them being JobID
FROM tblInvoice AS i
WHERE i.InvoiceID = (SELECT Max(i1.InvoiceID) from tblInvoice AS i1 WHERE i1.JobID = i.JobID);

Without additional parameters, this query will return the highest numbered Invoice record for each job.

InvoiceID must be unique or the sub query could return arbitrary results.

Well, I believe there’s nothing that can be done except to allow all data connections. If I find that it’s something I’ve done wrong and not a quirk in Office 2007, I’ll post the answer on this site.

Thanks for your help,

Setting Excel to ‘Enable All Data Connections’ was the answer. Setting ‘Allow Trusted Locations on my network’ had no effect. However, Excel on its own doesn’t complain about the data connection because the Excel file is in a trusted location. In fact, everything is in the trusted location, the database, the MS Query file, the Excel sheet and the Word doc.

Am I wrong in thinking that if Excel is OK with the data coming from a trusted location that using the Excel sheet from with Word should behave the same?

Though not the end of the world I would prefer not having to trust all connections. Kind of defeats the purpose of a trusted location. Any other thoughts to work around this issue?

Hi Solverback,

Here are the answers to your questions.

1) The Cancelled check box control is for use by the report to determine if the user cancelled out of the parameters form. In the open event for the report, the report should open the form in a modal state by using the acDialog parameter value in the DoCmd.OpenReport command. This stops the report code until the user is done with the parameter form. When the user click on either the OK or Cancel button the code behind those buttons make the form invisible but desn’t close the form. Closing the form is up to the report’s Open event, after the user’s selections have been extracted from the form. So, if the user clicks OK, set the value of the Cancelled check control to False so the report can read that value from the form and know that the user still wants to run the report. If the user clicks Cancel, set the value of the check box control to True so the Open event in the report can set the report’s Cancel property to True and close the report before it’s displayed on the screen.

2) Once the parameter form is open you can reference it’s controls by typing Forms(“name_of_the_parameter_form”).name_of_the_control.value but this would make for a lot of typing. Instead, dimension a variable as Access.Form; Dim frmParameters AS Access.Form. Then, in the code Open event, after the parameter form is hidden and the Open event procedure starts up again, set a reference to the parameter form with ‘Set frmParameters = Forms(“name_of_the_parameter_form”). Once you do that you can access the form’s properties and controls through frmParameters. When you are done with the form you can make this call to close it; DoCmd.Close acform, frmParameters.Name, acSaveNo. After you close the form make sure to destroy the object reference; Set frmParameters = Nothing.

3) Printing labels will always present problems. The first thing is that no vendor of labels is going to recommend you run the same sheet of labels through a printer more than once. But, if you do, one suggestion would be to create a work table that you can use as surrogate, empty labels. Create as many records as there are used up labels on the page. Then, in the query that returns the records for the labels, use a JOIN and an ORDER BY to include the empty labels and make them come out first. This issue is actually outside of your original post. I would suggest posting again on just the label issue. You may get an answer that’s better than mine.

Good luck.

Here’s another thought. Are you or can you use a bound form? Access handles appostrophe’s very gracefully within a bound form.

My appologies to you Hans. Because we are getting too far off of the original thread this is the last comment that I will make about the subject of commenting code but I want you to know that I wasn’t taking a poke at anything you specifically did as far as commenting or not commenting your code examples. I was really just trying to be funny in a sarcastic way.

Thanks for your replies.

Patrick.

Good point on the error handling routine. I didn’t think about that when I copied the code but I also assume everyone is or should be handling errors and would know what to do to change the code in order to get it to work with their application. So, I think you are just picking on me on that point I suppose there are too many comments in my code too?

I iether had a good reason at the time for not using the Replace funciton or I didn’t realize it existed at the time I wrote the procedure. At this time I see no reason not to use the Replace function so, good point. Did you test my code against the Replace funciton for speed or are you just saying that Replace performs faster because it’s built-in?

As far as the double-quotes looking strange on the screen, I agree but this code is not intended to prepare the text for viewing on the screen. My understanding of the original request was to take a text string that was hand-entered by a user, that had a quote in it and insert it into a database record by using an INSERT statement with a VALUE list. It is true that single-quotes and double-quotes have special meaning to most database engines but the user doesn’t need to know that. All of the processing goes on behind the form where the user will never see the addition of the second quote. If the record is read back into the form, the extra quote will have been properly removed by the database engine receiving it.

Run the fields that potentially have an appostrophe through this procedure. This procedure was set up for Chr(34) and I don’t remember if that’s the single or double quote but it is easly changed to fit your needs. I built the procedure because I work with database products that have different characters that cause the same problem. For example, I believe it’s the double-quote that messes with Access/Jet but the single quote that causes problems with SQL Server and other server based database systems.

Public Function AddSpecialQuotes(ByVal strSource As Variant) As Variant
‘Written by Patrick O. Headley, Linx Consulting, Inc., 3/20/03.
‘Parses a string for a single double-quote character. If any are found
‘they are turned into pairs of double-quotes so the string can be used
‘as a value in a database field or as a criteria expression, in code.

10 On Error GoTo AddSpecialQuotesError

Variables:
Dim lngQuotePosition As String
Dim strSourceCharacter As String

Start:
‘See if there is text to process.
20 If Not IsNull(strSource) Then

‘See if there are even any quotes in the string.
30 If InStr(strSource, Chr$(34)) > 0 Then

‘Parse the source string.
40 Do Until Len(strSource) = 0

‘See if the left most character in the source string is a quote.
50 If Left(strSource, 1) = Chr$(34) Then

60 AddSpecialQuotes = AddSpecialQuotes & Chr$(34) & Chr$(34)
70 Else

‘The character is a printable character so copy it straight across to the target string.
80 AddSpecialQuotes = AddSpecialQuotes & Left(strSource, 1)
90 End If

‘Remove the left most character from the source string.
100 strSource = Right(strSource, Len(strSource) – 1)
110 Loop ‘Check the next character
120 Else

‘There are no quotes in the source string so copy the entire string to the target.
130 AddSpecialQuotes = strSource
140 End If ‘For seeing if there are any quotes in the string.
150 Else

‘The source value is null so return a null.
160 AddSpecialQuotes = strSource
170 End If

Done:
180 Exit Function

AddSpecialQuotesError:
190 AddSpecialQuotes = “”
200 ErrorHandler “PublicFunctions”, “AddSpecialQuotesError:”, Erl
210 Resume Done

End Function

Good luck.

I am not seeing the same thing as you. When I press F1 the help window is separate from the main Access window. Try opening the help window and then click and drag on the title bar portion of the help window to see if you can dislodge it from the main Access window. This may be related to dislodging the Help window but under each of the headings of “HorizontalAnchor Property” I see a brief description of the help topic. One of them is “Access Object Model Reference > TabControl Object > Properties”

From your post I get the feeling that you are not enjoying the new interface that much. Please give it some time. We are all going to have to learn it at some point if we want to contue to program in Access. Good luck and keep trying.

The way I handle reporting with multiple parameters is to create an unbound form with the parameters I want users to select. In your case a multi-select list would work or a set of check box controls could also be used. Check box controls would be harder to administer if the list changed often but might be easier for user’s to understand.

In the Open event in the report, open the parameter form as a dialog with the DoCmd.OpenForm statement. Create OK and Cancel buttons in the parameter form. I also create a check control to hold the Cancelled status and make it invisible. You could also use a custom property for this. When the user selects OK, set the Cancelled status to False and make the form invisible. When the user selects Cancel, set the Cancelled status to True and make the form invisible. This way the report can be cancelled if the user didn’t really want to run it.

When the dialog form is hidden the Open Event in the report will start up again. Create an object variable that references the paramter form. Copy the user’s selections into variables and then close the parameter form. Make sure to set the object variable for the form to Nothing at the end of your code. Once you have the parameters stored in variables you can use them to construct a WHERE clause for the report’s record soruce. Wtih that constructed you can set the recordsource with the complete query, including your WHERE clause or set the Filter and Filter On properties.

This approach is very flexible and once you get it set up the first time changes usually don’t take that long. I try to use the same parameter form in as many reports as possible. This gives the user a common interface to work with. I also pass the name of the report into the parameter form using the OpenArgs property. This allows me to use the Open Event to turn on/off certain controls and set default values and to also use a validation procedure when the user clicks the OK button to make sure required values have been selected.

HTH