Replies

oh, yeah. and here i am, religiously reading the AccessWatch newsletter. i’ll have to explicitly declare DAO recordsets. duh!! welp, I’ll start in on this next week. perverse as it may sound, i’m having too much fun with DSum’s right now on a new project.

here’s what the Outlook –> Access Categories data *has* to look like to function:

Callahan, Jones, Taylor
Williamson
Brewster, Jeff_Davis, Presidio
Bastrop, Fayette, Lee
Brazos, Burleson, Grimes, Leon, Madison, Robertson, Washington

In the past, people have used / to deliminate and that will never happen if people don’t type county lists directly into the Categories text field. somebody might, somehow, do that and that’s what i’m trying to trap out. And, since we’re talking computers here, the info won’t get manipulated correctly if the commas don’t have a trailing space. and i shudder to think of what other things people might type in if they felt like it. so, it’s a potential problem. trapping anything that doesn’t conform to the correct deliminator looks like a rather hairy pattern-matching function, as you’d have to look for consecutive Capitol letters (assuming no space for instance around a deliminator character of unknown type); some counties have two names (hence 2 Capitol letters). and it’s too late in the week for me to think about it right now.

thanks for all the feedback — lounge at ya later!

i don’t see why it would be a references problem. because of Type Mismatch? well… in general, i have another module that does the whole rst.OpenRecordSet and it’s fine. perhaps i need to run the SQL in the module rather than try to launch a Query. that shouldn’t be too difficult but at the moment i am bashing out code in another access project so will not worry about this particular problem for now. like i said, this is some adhoc validation stuff that can also be replaced altogether with a little training.

but yes the Categories field is a text field.

ContainsSlash is a query (stored procedure) in the db that opens the target data and captures any record that has ‘/’ somewhere in it for the Categories field. Basically SQL:

SELECT * from tblOutlookContacts WHERE Categories LIKE “*/*”;

hmmm… According to VB Help, Instr tells you the *position* of a character in a string, so that’s not quite it.

OTOH, I am starting to think this part of the project should be left alone: what I am trying to do is idiot-proof the incoming data from Outlook, but the only way Outlook would give ‘bad’ data is if the user doesn’t use the Categories list for the Counties: by typing in a county directly, for example. This is a Bad Thing because it leads to possible misspellings and possible non-alpha order county lists. The goal, really, is to enforce best practices, not necessarily to programmically assume and correct for the lack thereof — which is tough anyway, because there’s any number of ways someone might choose to deliminate counties and the only way to account for all of them is to do some complex pattern matching such that any instance of multi-county records is force-deliminated to ‘, ‘ style. But to do this, in the case where there is no space between two or more counties and you don’t know what the deliminator is, you have to search for the second capital letter in the string and call that the next county (and some counties have two words, underscore-separated, so that has to be trapped out).

It’s something of a philosophical issue: do you want to allow bad practices to go unpunished? Bear in mind, this has nothing to do with data loss; no real harm is being done — you can enter something funky in Outlook, it’ll port to Access but it won’t make it to the final report because it’s not ‘legal’. End user can correct this be re-entering the data in the Correct Manner. Also, we have a small shop here so the training on this point is fairly minimal.

I’ve worked on another method that is also not there yet programmically: i can build a simple query in Access that tests for the ‘/’ deliminator or the ‘,’ (comma with no trailing space) deliminator and, after the main table is built, i’m trying to open and count records in the queries: if there are any a message box pops up warning that Records A-Z won’t appear in the final report because of blah blah. in this case, the queries should be run for the recordcount read but the queries themselves should not open on the screen (ideally). note this is not a globally secure method as it doesn’t test for any possible delimination not equal to ‘, ‘ but it does remind the end user that they’ve subverted the rules. I am leaning towards this solution as it will reinforce the training.

To that end I have this:

Private Sub Command10_Click()
Dim dbs As Database, rst As Recordset

‘ Return reference to current database.
Set dbs = CurrentDb
‘ Open table-type Recordset object.
Set rst = dbs.OpenRecordset(“ContainsSlash”)
Debug.Print rst.RecordCount
rst.Close
Set dbs = Nothing

End Sub

I keep getting Type Mismatch and the OpenRecordSet line is the offending one. Any clues on this?

that query worked perfectly. I had forgotten that you can specify two tables in the SELECT FROM section of the SQL. I KNEW there was an easier way to do this but was put off by the apparent obscurity of the LIKE operator in normal query grid operations. now i can go on… thanks for the quick and precise help, Hans, and for everyone else who was so responsive to my problem!

Ok here goes: I have a table like so: (these are very abbreviated examples to show the crux of the problem)

ID –> 1
Name –> Steve Skelton
Category –> CRCG
Counties –> Bastrop, Fayette, Lee

I want to produce another table with 3 rows instead of one:

ID –> 1
Name –> Steve Skelton
Category –> CRCG
Counties –> Bastrop

ID –> 2
Name –> Steve Skelton
Category –> CRCG
Counties –> Fayette

ID –> 3
Name –> Steve Skelton
Category –> CRCG
Counties –> Lee

I have a lookup table with each county:
ID –> 1
County –> Bastrop

ID –> 2
County –> Fayette

ID –> 3
County –> Lee

The SQL statement needs to do a Select [FieldNames] from Table1 where Category LIKE ‘”%Table2.County%'” (I’m using the % character as a wildcard as the ADO-style SQL string uses that to pull the data from Access; also it’s easier to pick out)

Apparently I need to generate a recordset, iterate through the records and test *each* row in Table1 against all the County fields in Table2 and write out the results in a new table (from a recordset?) combining the basic data needed from Table1 with the matching County from Table2 — also, include the county list for purposes of testing for “bad” matches (for example, Harrison county will get picked in a LIKE %HARRIS% query – there’s a few that do this and I need to make exceptions for those).

when i started working on this, naturally i went to the data grid; that doesn’t allow the LIKE operation in linking fields, alas. this makes me think i’ll have to code a module, which is no big deal; i just don’t have a handle on the syntax for the operation and the Online help hasn’t been. so, to the lounge…

Hope this makes sense. I’m pretty sure I’m not trying to do something completely strange, just never done it before.

Thanks!

DoCmd.RunSQL is something that can be run from a macro. interesting.

There’s a problem with the recommended SQL strings — they are not looking up anything from the County name look-up table but seem to be expecting an argument. I need to, basically, run the SQL for every table in the Contact table based on an iteration thru the County table. I don’t care if it’s an Append or a Make Table at this moment; really need to figure out the programming/logic on reference a look-up table for the LIKE criteria. If I could do all this in a Macro that might be a bit easier to fool with…

sorry i thought i was being specific enough not to bore with too many details. but, this is what I am working on:

1) contact records that have a particular field, ‘Categories’, with a comma-deliminated list of counties (one or more) — this is from Outlook, so it’s the Categories information.
2) a report based on a per-county list to show who covers a particular county (I can easily do a report that shows contacts with all the counties listed but i need to do a report that breaks out each contact and lists them for *each* county in the look-up table, not just in the batch as Categories has them).

Simple enough — I need to use the LIKE operator to match a county in a string of counties and page thru the contacts table and write the matching records to a new table (or an existing empty one) whenever there is a match. Now, in the case of a contact that has Bastrop, Fayette, Lee in the Categories field, the result in the target table should be

Contact, Bastrop
Contact, Fayette
Contact, Lee

where the Contact information is the same, just the county differs.

Later on, once that gets done, I’ll need to run reports detailing on a by-county basis contacts who are Chairs, Coordinators or CRCG, CRCGA or CRCGF types. That should be easy to do; it’s just finding and listing out on a per-county basis these records that is giving me problems.

once again, apologies for not showing the whole enchilada.

On the whole an Access-based solution is probably the way to go. there’s several filters I’m going to need and anyhow Access is used to support a web-based search function for the same contact information. My only problem with Access, and this is really silly, but I am puzzled by how to set up a module to loop thru the contacts list and generate a table that lists each contact by LIKE county match from another table. on the web search I have this:

SQLstring = “SELECT * FROM LocalCRCGContacts where Categories LIKE ‘%” & County & “%’ ;”

Where County is input from a drop-down. Of course, to do this on the web one has to set up a db connection (I use DNS-less) to display records. However, we’re talking about a table-to-table comparison in Access, so it would seem a connection doesn’t need to be made. The query grid doesn’t support LIKE in the joins properties, which is why I think it’ll have to be a module (or one of the more complex SQL queries) to wap out the records. In the SQL window in Access I have this:

SELECT * FROM LocalCRCGContacts WHERE LocalCRCGContacts.Categories LIKE ‘%” & CNTYCODE.County Name & “%’;

which gives me no records but no syntax error either. It looks alright in the main but there’s no looping going on, so perhaps that is why nothing results from it.

How, then, to compare the two tables and generate a new list by iterating thru one table against another table field? this should be easy but i’ve tried before and got stuck.

There’s a couple of things I can try on the automation side of things. one would be a SQL query that builds a recordset using a look-up table of county names and the LIKE operator to write the records out (LIKE can find matches in a string if you use the wildcard character) – but I haven’t figured out how to write the module in Access to do this yet. Access queries don’t support LIKE from the query grid and I’ll need to build a module that creates a recordset based on two existing tables. doesn’t sound too difficult, but I was trying to find a solution just between Outlook and Word (Excel would be ideal, btw). I’d also like to make whatever method something easy for other people to do, and porting data from Outlook to Access is a pain.

It is easy to get the data I need from an Exchange Public folder into Word and then paste the data into Excel for ‘washing’. so, I am planning on writing a macro in Excel that copies out a new record for each county. This should work for a number of reasons: it’s easy to show someone how to merge from Outlook to Word to a template and show them how to pop the data into Excel and, when ready, click on the Macro button/command to do everything else.

Outlook seems to like giving me a duplicate record for every contact with more than one county listed, but even here it is inconsistent — doesn’t always happen — so is not suitable for an auto-run macro until I can find out why some records don’t behave this way. I’d prefer having the dups as that makes it really easy to compile the by-contact information, since you’ll just have to delete and move county cells, not the rest of the contact info. well there’s a little progress at least…

I tried to find a reference to the Mater Category list as well, thinking it would be a good place to start an array-type operation but didn’t find anything applicable in the Outlook model so far. I can’t even find the event trigger on the Categories… button that brings up the Categories list.

well after receiving your (admittedly) depressing response I went exploring in the outlook object model and, well, nada. if you install Outlook Spy from this site (http://www.dimastr.com/outspy/) you get a lot of interesting info including a reference to something called ‘Keywords’ on an opened contact list that, in the Outlook Spy sw at least, loads the contegory string in a drop-down box with the proper one selected for each Categories group (the Categories title on the Group By bar in View by | Categories.) which is as close as I have gotten to seeing how Outlook parses out the Categories string for each record. Also, in vb for Outlook Help the Keywords field is mapped to the Category field (not Categories – singular).

However, adding a custom textbox to my form and tying it to the Keywords field gives me the whole list of categories. changing it to anything other than Value (well I haven’t tried all the options) doesn’t display anything. Adding a drop-down box and assigning it to Keywords likewise doesn’t work. Doing similar things with the Category field also displays no data. I suppose i should just give up on it, as, even if i managed somehow to ferret out the right combination of controls and links, there’s no guarantee *at all* that the new field will show up in a mail merge — as a matter of fact, it probably won’t as the only way to expose new fields to Word in a mail merge is to create User-defined fields and these fields I am trying to access are pre-defined and pretty well hidden.

However, I don’t want to cut-and-paste as when you do this from table format you’re not getting anything much different from a full Mail Merge from Outlook and I can get more control and automation by using the Outloook mail merge feature. I might be able to put something together in word that can wap out the records per each comma-seperated value in the Categories field, but that’s asking more than one can expect from a word-processor. I am working instead on classing this problem as a software limitation and disabuse the boss of the whole idea of getting the report requested. **Sigh**

hey, i figured it out. you mentioned that it is an option to leave the value to “ON” so I changed that to the ItemID value from the db and named the value of the Checkbox “ItemID” and now it pulls the assigned value — which makes a lot more sense. so anyway, sorry to bother you but sometimes it takes a little blathering to sort out the neurons.

I can do something like this to enumerate *all* the field values submitted via a form but is there a way to grab just one field value rather than all of them? I need to have more resolution that just being able to list everything in the collection.

for example, this will list all the field values from a SUBMIT, but I just want the *first* one, as it is my checkbox name:

‘Display the Request.Form collection

For Each checkbox In Request.Form
string = string & (checkbox) & “,”
Response.Write(checkbox) & “,”
Next

As you may already know, the word ‘checkbox’ is just a tag; it could be anything but i’d *really* like it to be the darned checkbox. I could use this method if there was a way to step out of the For Each…Next when a condition is met, but I can’t seem to find the key word to break out of this kind of loop. OTOH, it feels cludgy to set up a loop just to break it, but if there’s no way to specify the checkbox independent of other items in the From SUBMIT collection, it would at least work.

thanks for the reply… actually i managed to get what i needed after a lot of searching around. since the checkbox NAME property is dynamically assigned, I can’t hard-code some response.form stuff to capture it. but i did find a method for referring to the checkbox in VBScript that does give the name, and worked that into a SQLString to pull records matching the choices selected:

SQLString = “”

‘Display the Request.Form collection
For Each checkbox In Request.Form
sItemID = (checkbox)
SQLString = SQLString & (checkbox) & “,”
Response.Write(checkbox) & “,”
Next

Response.Write ”
”

SQLString = Left(SQLString, Len(SQLString) -1)

SQLString = “Select Title from Items where ItemID = ” & SQLString

response.write SQLString

‘myRecordSet.Open SQLstring, conn, adOpenStatic

I’m still hacking away at it, but the response.write string is:

Select Title from Items where ItemID = 8,11,6,7

which looks right to me. Now I have to get a table to display with the records found, but it’s late in the day..

thanks! that worked.