Replies

Hi Graphics Guy,

I probably spent about two hours last night…not too bad. I’m currently on a remote trip, for the company I work for [think large commercial jet aircraft], in Kona, Hawaii. There may be days when I simply do not have time to reply, as my paid work must come first. I’ll send a private reply to you, and let you make the call, if you want to take it off-line.

Tom

Hi Graphics Guy,

I added a dialog form, similar (I believe) to the form that Patt described in an earlier message. This dialog form is named “fdlgAskForParameters”. This form is based on a Word document written many years ago, by Michael Hernandez. Michael is the author of Database Design for Mere Mortals, and co-author of SQL Queries for Mere Mortals. I gave a presentation to the Seattle Access Group, in January, 2008, based on this method. You can find the orginal sample, with Word document, at this site:
[/SIZE]
http://www.seattleaccess.org/downloads.htm

I hope you don’t mind, but I took the liberty of renaming some of your objects (tables, queries, forms) by using standard naming conventions. This includes not using any spaces in the names. I also renamed the Year field, to ProductionYear, so that you don’t have a field that is named with a reserved word. I added a table of locations, tblLocations, so that we can get rid of the lookup field in the main table that had 5 cities indicated. To add a new city, simply open the tblLocations table and add it there. We can even get fancier by adding “Not-in-List” code, for the combo box, to automatically display a form based on locations, if the user attempts to enter a city directly into the combo box on the form that is not already in the table. Lots of possibilities.

For the present time, I left the four Actor fields alone, but these really should be normalized by creating a table of actors, along with a join or linking table, so that you can have what is known as a “Many-to-Many” (abbreviated M:N) relationship. Your current design, for the actor’s names, is a multi-field design. It is not so flexible as far as querying, and it adds difficulty if you wanted to store 5 or more actor’s names in the future. Just something to consider for future improvement.

Along the way, I found what I believe is suspect data: you have one record that shows Olney, VA., and 24 records that show Olney, MD. The lone record for Olney, VA. is for the title “Show Me Where The Good Times Are” (October 1, 1993).

Hi Graphics Guy,

Would you be okay with posting a zipped copy of this database, so that others can provide more effective help?

Hi Sadie,

This problem indicates that the form’s recordset is returning zero records. Check the query or table that serves as the recordset for the affected form.

Field 1 (Increasing numerical entry order so she would know the total number)

There are easier ways to determine totals than having a dedicated field with a numeric series…[/SIZE]

Year (includes month), Actor 1, 2, 3, 4.

Year is a reserved word. You will always be doing yourself a favor to avoid the use of any reserved words, when giving names to objects and controls in Access. Here is a link you may want to follow, to learn more on this topic:
Problem names and reserved words in Access
http://allenbrowne.com/AppIssueBadWord.html
[/FONT][/SIZE]
Also, are you saying that you have four fields in this table for actors (Actor1, Actor2, Actor3 and Actor4)? If so, be aware that this design is not properly normalized; it could be improved, which will make future requests easier to accomodate.

[/SIZE]

Recently she sent me a copy and would like me to separate or sort the information into individual theatre print outs

I suggest creating a nice report, based on a query.[/SIZE]

She would like the final readout of a particular theatre to just include in this order: Year (starting with earliest ) & Title (of show) but with the theatre name not included in the readout. For example from The Kennedy Center entries the print out information would just include: June 1952, My Fair Lady[/SIZE]

[/FONT][/COLOR]
As others have indicated, create a query instead of trying to apply a filter. In Access 2003, select “Queries” in the database window. Click on the New button, and then on Design View. Add the Program Information table to the query, and then click on the OK button to dismiss the Show Table dialog.

You can drag fields from the table to the QBE (Query By Example) grid, in the order that you wish to display them. Alternatively, you can double-click on the field names, or you can select them in the QBE grid by clicking into the Field area, and using the dropdown. Add an ascending sort to the Year field. Note: It appears as if your Year field is a Date/Time data type (this is good), with an applied format, since the values are right justified.

Alternatively, look for the SQL indication in the upper left corner, in query design view. Click on this toolbar button to open the SQL View. If you have not yet added any fields, you should see just the word “Select” highlighted. Backspace over this to remove it. Then copy and paste the following SQL (Structured Query Language) statement:

SELECT [Year], Title FROM [Program Information] ORDER BY [Year]

Save the query with a descriptive name, preferably with a naming convention prefix such as “qry” and without any spaces or special characters in the name. For example, save as: qryProductionTitlesByYear

You can now use this query as the source for a new report. However, a report will not obey sort orders applied at the query level–for that you will need to use View | Sorting and grouping in report design view.

~~~~~~~~~~~~

Here is the SQL statement for another query, which allows you to easily count records (without having to rely on Field1):

SELECT COUNT(*) FROM [Program Information]

This query has no criteria or grouping but that is easy to add as well, if you want.

Good Luck

Hi Ian,
Try the suggested fix shown in this FMS article:

Fixing the Microsoft Windows Common Control Library (MSCOMCTL.OCX) Security Update
http://www.fmsinc.com/MicrosoftAccess/controls/mscomctl/index.htm

This article includes a statement that the fix Microsoft recommends in KB 2687441 does not work in most cases.

Good Luck

Just a quick comment on speed, regarding the use of SQL vs. VBA. I have yet to find a situation where VBA beats a SQL based alternative. Of course, in Access, your SQL options are limited to a single statement, even if it can be complex, but if what you want done can be done in SQL, it will give you the fastest option, for sure.

I agree. Running set operations with SQL is always faster versus running equivalent code in VBA.
To get around the single statement limitation, I often times create a procedure in VBA code that simply calls one query after another. This is a technique I use at work, where I’m allowed read-only access to a huge Oracle database. I have a procedure that starts by running delete queries to clear local tables out, then a series of append queries to add data back in, followed by various update queries to massage the data as required. And, in the case of this data, I also have to run a non-SQL based procedure to replace non-printing characters that come with the data.

Hi Alison,

The multiple quotes thing is simple…just use the HTML quote and end quote tags:

[*Quote*] and [*/Quote*]

Don’t include the 4 asterisks I added, before and after the square brackets. I did this so that I could show how to use this tag, without it actually taking effect.

Using an Autoexec macro to run a function to establish a global DB variable is certainly a good method. The best method, of course, is entirely dependent on your situation. For example, if you only need the global DB variable for code that only one person in 20 users is ever going to run, then you might want to delay creating such a global variable. I do recommend using a naming convention, such as gdb (or gDB), where the “g” indicates global. Likewise, use a lowercase “m” for module-level declarations, in code behind forms and reports, where once the form or report closes, the variable goes out of scope.

[Quote]
“… – other developers I know tend just to say “Can’t do that” to keep it uncomplicated…”
[/Quote]

It all depends on the situation. When users are having to pay for each request, they tend to self-regulate, and turn down the requests. On the other hand, when users do not have to pay the costs, it is not unusual to hear how a feature is a “must-have”, and, after adding it, you find out that very few people actually use it. But, I will say that that kind of attitude, in general, will get a developer in trouble. Someone else, like yourself, will be able to come in and ‘eat their lunch’.

Hi Alison,

Here’s a thought….can you append all names to the table and then use the Soundex function, with the appropriate constant, to help locate possible duplicates? You can display the possible duplicates in a form which includes a checkbox where you, or another human being [but not a computer] decides on actual duplicates. You may have to experiment with the constant to find the best value for the majority of names in your database. If memory serves me correctly, a value of 4 works pretty good for most names.

More information on Soundex, and an alternate method (Levenshtein Distance) here:

April 2005: “Close” only counts in horseshoes…and databases (467 KB)
A couple of techniques to help determine when entries are “close enough” to be considered the same

available on this page, by Access MVP Doug Steele:
http://www.accessmvp.com/djsteele/SmartAccess.html

I have used persistent connections on other databases, and anticipated doing so on this one, but will be very interested to look at your article, as I might not be doing it as well as I could.

I usually just open a form in hidden mode that is bound to a table with one record in the BE database. The user never knows this form is open. You will need to close it first, in order to compact.

I didn’t realise setting the database as a global variable would improve speed – I just assumed it would be better to clean it out of memory when I finished running the subroutine. Your recommendation makes a lot of sense in light of the number of iterations, though.

The statement Set db = CurrentDB() is fairly costly, as far as time goes. If you do this only once, when a procedure runs, no big deal. But, if this line of code is being executed for each record in a query that calls your function, the penalty can become quite noticeable, especially if you are dealing with hundreds or thousands of records.

Disambiguation looks as though it might help the problems I sometimes have when clients install a copy on a new PC that doesn’t have the correct references set up.

Disambiguation only helps with reference priority issues. It does not help at all if a required reference is not selected.

As for indexing – I live in constant fear of being found to be a complete fraud if anyone else looks at my indexing. I try to keep it tight, but as I also try to offer a lot of flexibility in the user interface, I find myself saying “They might want to sort on this field” or “They might want to select on that one” or even “They might want to sort/select on Last Name only on some occasions, but on others it will be based on Last/First” and ending up falling down the other side of the optimisation bell curve with far too many indexed fields.

Isn’t this a question that you can ask your users? If you provide them with some type of Query-by-Form (QBF), then you can easily restrict which fields they can choose as criteria or for sorting. It sounds like (?) you may be providing your users with direct access to the database window? That’s something I don’t do.

A quick look at your links tells me two things immediately: I’m not doing everything wrong; and I’m in for some good reading when I get away from work tonight…

Many thanks for your helpfull suggestions.

Great! And you’re very welcome.

Thanks – that doesn’t give any errors, but only calls up records that are unchecked, which isn’t quite what I’m after.

What I’m trying to do is identify if there are any unchecked records and, if so, pull out all records with matching names (including those that have previously been marked as checked). This is so that, each month, if a new record is imported with the same name as an existing one, the old and new can be displayed to identify if they are the same person or not; but if there are no new records, I don’t want to bother showing old records that have already been identified as two separate people.

Sorry, I know it’s a bit convoluted and therefore hard to explain clearly; so I’m not surprised if you’ve had trouble working out what I’m after!

I’ll take a closer look at your code and see if I can adapt it. Meanwhile, the VBA function is working, albeit slowly, so I do have a fall-back position.

Cheers

Alison C

Hi Alison,
Is it possible for you to post a sample database, with some dummy names, but with enough combinations of your Yes/No field checked and unchecked? Also, post the results you expect the query to deliver. It may very well be that you need to do this using VBA code.

Regarding the slowness of your procedure, how many records is your query + code working against? Are fields used for query criteria properly indexed? Is this a split application (FE/BE) with a network wire that separates you from the data? If “yes”, have you established a persistant connection to the BE database? Here is an article I wrote that provides many suggestions for getting better performance:

Implementing a Successful Multiuser Access/JET Application
http://www.accessmvp.com/TWickerath/articles/multiuser.htm

You can likely speed up your VBA procedure by declaring the database and recordset variables as global variables, and set the database variable one time, instead of setting it for each iteration. Also, I highly recommend that you “disambiguate” your recordset declaration:

Dim rst As Recordset —-> Dim rst As DAO.Recordset

This will help prevent the possiblity of a situation where your code compiles fine, yet you experience run-time error 13: Type Mismatch. More information here:

ADO and DAO Library References in Access Databases
http://www.accessmvp.com/TWickerath/articles/adodao.htm

Try this:

API: Call the standard Windows File Open/Save dialog box
http://access.mvps.org/access/api/api0001.htm

In Access it isn’t possible to do that sort of setting the value of a field based on the content of some other field when you are working at the table level.

This is a true statement for Access 2007 and all prior versions, however, Access 2010 provides support for triggers at the table level, if you are using the newer .accdb file format. I haven’t worked much with this feature, but I believe they are referred to as Data Macros. More information on Data Macros is available here:
http://blogs.office.com/b/microsoft-access/archive/2009/08/13/access-2010-data-macros-similar-to-triggers.aspx

For all other prior versions of Access, use VBA code in a form, as Wendell suggests, but, keep in mind that such updates will *only* happen if data changes are made using that form.

Hi Again –

I decided to try a Sunday morning challenge to try to help you more. If the SessionStart and SessionEnd dates fall within a single month, then you can use a query–no VBA code required. For this example, I named the table “tblTrainingSessions”. The fields are named “SessionStart” and “SessionEnd”, respectively, and are both Date/Time data type formatted as you indicated (dd/mm/yyyy). Here is some sample data that I entered into the table. TrainingID is simply an Autonumber primary key:

31280-Data

The test data includes your original dates, a set of dates that spans two months, your original dates entered backwards, and two records with nulls.

Query1 SQL Statement
SELECT SessionStart, SessionEnd, Day([SessionStart]) & “-” & Day([SessionEnd]) & ” ” & Format([SessionStart],”mmmm yyyy”) AS TrainingSession
FROM tblTrainingSessions;

Query1 Result with above test data:
31281-Q1

As you can see, the last four results have an error. You can eliminate results # 4 and 5 by adding the appropriate criteria to the query, but that still leaves the 2nd and 3rd results:

SELECT SessionStart, SessionEnd, Day([SessionStart]) & “-” & Day([SessionEnd]) & ” ” & Format([SessionStart],”mmmm yyyy”) AS TrainingSession
FROM tblTrainingSessions
WHERE SessionStart Is Not Null AND SessionEnd Is Not Null;

Query that calls VBA procedure
Create a new module and paste the following code into this module. Make sure that your new module has “Option Explicit” as the second line:

Code:

Option Compare Database
Option Explicit

Code:

[FONT font=Arial][COLOR=#000000]Public Function DetermineRange(StartDate As Variant, _
                               EndDate As Variant) As String
                               
On Error GoTo ProcError[/COLOR][/FONT]
[FONT font=Arial][COLOR=#000000]Dim intDays As Integer
   If IsNull(StartDate) = True Or IsNull(EndDate) = True Then
      DetermineRange = “”  ‘[/COLOR][COLOR=#006400]<–Return a zero length string.[/COLOR][COLOR=#000000]
   Else
      intDays = DateDiff("d", [StartDate], [EndDate]) + 1
      
[/COLOR][COLOR=#006400]      'Check for "reasonableness" of number of days in range.[/COLOR][COLOR=#000000]
      If intDays  5 Then
         DetermineRange = “Check for correct date entries.”
      Else
         [/COLOR][COLOR=#006400]’Check to see if both dates are in the same month.[/COLOR][COLOR=#000000]
         If Month([StartDate]) = Month([EndDate]) Then
            DetermineRange = Day([StartDate]) & “-” & Day([EndDate]) _
                                 & ” ” & Format([StartDate], “mmmm yyyy”)
         Else
       [/COLOR][COLOR=#006400]    ‘Session spans two months (for example 30-July to 03-August)
            ‘Note: Add an extra “m” to each format to get the full month names[/COLOR][COLOR=#000000]
            DetermineRange = Day([StartDate]) & ” ” & Format([StartDate], “mmm”) _
                           & ” to ” & Day([EndDate]) & ” ” & Format([EndDate], “mmm yyyy”)
         End If
         
      End If
      
   End If[/COLOR][/FONT]
[FONT font=Arial][COLOR=#000000]ExitProc:
   Exit Function
ProcError:
   DetermineRange = “Error ” & Err.Number & “: ” & Err.Description
   Resume Next
End Function
[/COLOR][/FONT]

SQL Statement for Query that calls VBA procedure:
SELECT SessionStart, SessionEnd, DetermineRange([SessionStart],[SessionEnd]) AS TrainingSession
FROM tblTrainingSessions;

Here is the result of this query:
31282-Q2

Hi Bill,
Isn’t it amazing how modern medicine allows doctors to replace worn out parts, like knees and hips?

You’ve likely got lots of rest and recuperation time now, so no time like the present to start working on your goal. I hope you have a few good books available for reference.

Best wishes for a speedy recovery.

Tom