Replies

That seems to work! Not sure why I didn’t consider the before update event in the first place..:confused:

Yes, but…
There’s only one field. No second field to move control to. The form is set up to display an image (a picture stored elsewhere) by entering the name of the form (they actually cut-and-paste the name into the field from another application). When they hit Enter, they want to see the image. That’s all the form does. Should be simple, right? I suppose I could set up another field with the sole purpose of giving them somewhere to go when they hit Enter. I’d be concerned that it would confuse them, but I could move focus back to the original control as soon as it’s placed on the second control. I’ll give that a try.

Thanks for suggesting a union query. I don’t usually use them, so I never considered it. When I tried your example, I was still missing those individuals who took CourseA or CourseB, so I added one additional UNION query to catch everyone else:
UNION

SELECT tblCoursesTaken.StudentID, tblCourses.CourseName
FROM tblCoursesTaken INNER JOIN tblCourses ON tblCoursesTaken.CourseID = tblCourses.CourseID
WHERE ((Not (tblCoursesTaken.CourseID)=10 And Not (tblCoursesTaken.CourseID)=11));

That seems to have done the trick. Thanks!

The initial query is simple:
SELECT tblStudents.StudentID, tblCourses.CourseName
FROM (tblStudents INNER JOIN tblCoursesTaken ON tblStudents.StudentID = tblCoursesTaken.StudentID) INNER JOIN tblCourses ON tblCoursesTaken.CourseID = tblCourses.CourseID;

To determine which students took Course I, it’s simply a matter of using that as a criteria:
SELECT tblCoursesTaken.StudentID, tblCourses.CourseName
FROM tblCourses INNER JOIN tblCoursesTaken ON tblCourses.CourseID = tblCoursesTaken.CourseID
WHERE (((tblCourses.CourseName)=”Course I”));

or, more simply, if I look only for the courseID (assuming I know it),

SELECT tblCoursesTaken.StudentID, tblCoursesTaken.CourseID
FROM tblCoursesTaken
WHERE (((tblCoursesTaken.CourseID)=10));

Same thing to look for Course II, of course. And then I can easily determine which students took both. The issue is that I need to EXCLUDE Course 2 (CourseID 11, in my case) only for those students who took Course I. However, if that student also took CourseID 1 to 9 or 12 onwards, I still need to include that student in the query. So I can’t exclude the student; I can only exclude the course. I don’t want to delete history, so the fact that they took the “introductory” course remains in their student record. The client just doesn’t want to see it in the report.

Yes, but I can’t exclude the student because he/she may also have taken Course A and Course B. I just need to exclude Course I from the listing.

John:
In my parsing, I looked for single invalid characters, but an instance of two dots next to each other, for example, would not be caught since a single dot on its own is not invalid. I had considered revising my parsing routine to store the previous character to allow it to look for duplicate spaces or dots, but I’ve actually replaced my parsing routine with this short “resolve” edit since it’s much faster to pass the email address to Outlook and ask it to resolve than parsing through each individual character of an email address looking for invalid characters.

Well, I solved this myself.
I added code when parsing the email addresses into the string to have Outlook check each email address individually. I found this link helpful:
http://www.outlookforums.com/threads/8311-recipient-email-address-is-invalid-but-resolved-property-is-true
Essentially, I created a new variable called rcpRecipient which I defined as an Outlook.recipient. I then used the following lines to assign a value to that variable and have Outlook check its validity by using the rcpRecipient.Resolve command which allowed me to check the status:
Set rcpRecipient = appOutlook.CreateItem(olMailItem).Recipients.Add(strEmail)
rcpRecipient.Resolve
If rcpRecipient.Resolved = True Then
strList = strList & “;” & strEmail
If rcpRecipient.Resolved = false, I add the email address (strEmail) to an error log that the user can access and check later. Works perfectly.

I’m using Access 2007, in case anyone needs to duplicate this function.

Good suggestion, kreaves. What I’m doing is creating two tables: One with the headings required in the report; another with the data organized in the required columns. I’ve bound the report to the table with the headings and added a subreport which shows the data contained in the data table. I’ve also created a form to display this information in the same format. Both tables are created using VBA (SQL commands, actually). This has the added bonus of resulting in a data table that can be exported to Excel. That functionality is actually built into the system, of course, even before you suggested it! The difficulty was ensuring the data was added to the table in the proper order, but that was accomplished with a series of For-Next and DO loops.
The down-side to manipulating the data like this is that the report layouts need to be pre-defined (for example, I need to know that for the first 2 years of any selected period, they want individual months; after that Quarters for 2 years and any additional data will be summarized by year). They can’t decide to change this up unless they want to pay me to change the VBA code! They can still use standard Access Crosstab reports, but they’re less elegant and not as flexible. I always tell my customers to create queries in Access and Pivots in Excel because data should always be stored in Access, but analysis should always occur in Excel.

Thanks for the feedback.

That’s really helpful, Larry! I’ll be using a seek as often as possible from now on! You’re right about the network, though. I’ll have to talk to the hardware guys and see what can be done about getting some more speed or bandwidth or something.
Mark: Thanks for clarifying that records aren’t locked when a record is only viewed.

Mark – thanks for clarifying what happens with multiple recordsets open. It makes sense that it uses memory to leave it open, and it also makes sense that it would be faster if it was already open! There’s no issue with selecting a particular record when opening the recordset? Will Access lock that record until the recordset is closed again?
Larry – I would be interested in a comparison between the seek and the regular query (since it sounds to me that you’d like to test that! ) I have a lot of work to do with datasets in this project, so I want to optimize my interaction with the data. The data has to be split for security reasons – I just wish I had a faster network.

Thanks for everyone’s feedback. I don’t have 4 million records – more like 500,000 (and growing) – but I don’t have a “robust server”. This is a front-end/back-end situation, but I’m only using Access tables as my backend not SQL server, so I’m hampered by speed this way and the fact that the network is slow. I’ll re-write the code to open a recordset directly as suggested by Mark and see if that speeds things up. Thanks for your help.

As an aside, Mark recommends “Define the recordset as Public”…..is there an issue with keeping a recordset open? In the recesses of my mind, I remember being told “get in, get out” when dealing with data sets. “open the database, get your data and close the connection”. Am I locking the records and leaving the recordset open by defining it as public? Will this be setting myself up for corruption or frustration by other users trying to access the same records moments later? I’ll admit my schooling was several years ago and maybe things have changed….

Both tables are indexed by CustomerID. Any other suggestions?

I’m comparing Customer Numbers, since that’s a “known entity”. The system assigns the autonumbers, so the individual generating the address change file that I receive has no idea what the CustomerID might be. So really, when I’m looking for duplicates, I’m only concerned with the Customer Numbers, and that’s what I compare. The file being appended doesn’t contain any CustomerID numbers – I’m letting the system generate them on its own as part of the Append process. That seems to be what’s causing the problem. Will it hurt to run the append 8 times? Am I going to have CustomerID numbers added to the new records that match records that were deleted previously? They couldn’t have been deleted unless the related records were deleted first, so it shouldn’t be an issue as far as data integrity. I’m just surprised that the CustomerIDs are assigned within the range of existing CustomerIDs.

There’s definitely SOMETHING going on here that I don’t understand because the system seems to be choosing an autonumber value that is within the range of the existing records. I haven’t tested this theory exhaustively, but it’s the only thing I can think of that would cause this type of behaviour. I have two indexed fields in this table – an autonumber CustomerID (the primary key) and the Customer Number itself. Customer numbers can be changed (in case of a merger, for example), so I didn’t make it the key to the table. Perhaps I should have….

Andrew is correct. You need to have some indicator in your Resident table to show what church they belong to. That indicator would reference (as a foreign key) another table in which you list the churches they might belong to – including, of course, “none”. You can then build a relationship between the tables which will allow you to generate the required report using a query.