Making a phone list (2000(SP3))

Topic

New Reply

I have code that outputs information for an arts directory and the book designer wanted to know if it was possible to always have a phone number, any phone number in a specific line. In essence, we will prioritize home, work and cell numbers and one appears in the output beside each of the 3 address lines like so:
address 1 tab phone 1
address 2 tab phone 2
address 3 tab phone 3

Phone 1 is not a specific assignement (work, cell) but rather the first available in a prioritized list so if there is home and work, they will take phone 1 & 2, if there is home and cell, *they* will take phone 1 & 2 and if there is work and cell, *they* will take phone 1 & 2.
What I’m thinking is to simply use a query to grab all the available numbers for an existing record and prioritize them using an IIF statement in a calculated field to assign the number 1, 2 & 3. I would then use that query in the code to decide which number goes in which space in the directory.
I can figure out how to do it with a union query but that would take quite a few queries to produce. Is there a simpler way? Am I smarter to do it all at runtime with a recordset?
All the phone numbers would be in the same record together in separate fields. If I’d known they wanted to do this, I might have used subrecords for phone numbers, then the query would be easy.

Hopefully this makes sense as I’m just thinking on my feet right now and thought I might as well ask some clever people how they would do it.

Reply | Quote

Replies

How would the IIf statement work? Do you have a fixed prioritizing algorithm, or are you going to add 3 priority fields to the table (one for each phone number)?

Reply | Quote

There would be a fixed algorithm for prioritizing and then sort ascending. So home would be 1, work 2, cell 3. Then in the VBA output, I suppose I would find the lowest value and stick it in the first place and go through until there are none left. I haven’t really thought about that yet. Am I wandering into a minefield?

I wasn’t thinking about adding anything into the table since the priority is (or can be) fixed and hence calculated on the fly.

I was thinking the IIf would be along the lines:
Priority: IIf(homephone, 1, IIf(workphone, 2,3))

This assumes I can get all three of these fields to show up in a single query that gives me:

Client1	home#	1
Client1	work#	2
Client2	work#	2
Client2	cell#	3
Client3	home#	1
Client3	work#	2
Client3	cell#	3

Reply | Quote

If I were you, I would create the subtable for telephone numbers and use code to populate it. You can find several examples of such code in this forum (search for normalize).

Reply | Quote

OK. I’ll take a look. My concern is not creating the subtable, it’s all the re-writing of the VBA to output the directory.

Thanks

Reply | Quote

You’ll have to write extra code anyway, whether you change the table structure or not. In the end, muddling on with the existing structure would probably “cost” more.

Reply | Quote

Sounds fair. Now in the VBA itself, assuming I have normalized the table and created a phone numbers subtable what would be the simplest way to fill the phone numbers into the spaces available? Could this be done with 1 universal function or do I need one tailored specifically for each space available to fill?
The go fetch part of matching the client ID is pretty straightforward. Assuming I put into the table the priority number as well as the type of phone e.g. a record might be:
ClientA homephone 555-5555 priority 1
ClientA cellphone 666-7777 priority 3

I would want the first number in the first space available and the cell in the second space.

In other words, what sort of code do I need to find the second lowest priority number or the third lowest priority number in a set?

Reply | Quote

I don’t think you need to specify both the phone type (home, work, cell) and the priority. If you use 1 for home, 2 for work and 3 for cell, this will automatically specify both. And it doesn’t really matter in which order you add them to the subtable, you’d use the Sorting and Grouping dialog of the report to specify the correct order.

Reply | Quote

Sorry. I didn’t make part of what I need to do clear. The output is happening in VBA because it is compiling the info into a Word document, not using a report. I have to be able to take the first (lowest) priority number and insert it beside Address 1, then the second goes on the next line beside address2, etc. I have to be able to choose them 1 at a time as there are up to 5 phone numbers but any combination of the 5 is possible to be included. If there is only 1 number, we always want it beside Address 1. If there are 2 they will go in ascending order of priority beside address 1 and then Address2.

Here’s a little chunk of what is going on in a module that uses the Word object to output the information:

If Not IsNull(recClient(“Address2”)) Then
Selection.TypeText recClient(“Address1”)
Selection.TypeText Text:=vbTab
If Not IsNull(recClient(“homephone”)) Then
Selection.TypeText “(” & Left((recClient(“homephone”)), 3) & “) ” & Mid((recClient(“homephone”)), 4, 3) & “-” & Mid((recClient(“homephone”)), 7, 4)
Selection.TypeText Text:=” h”
End If
Selection.TypeText Text:=vbTab
If Not IsNull(recClient(“email”)) Then
Selection.TypeText recClient(“email”)
End If
Selection.TypeParagraph

Selection.TypeText recClient(“Address2”)
Selection.TypeText Text:=vbTab
If Not IsNull(recClient(“DayPhone”)) Then
Selection.TypeText “(” & Left((recClient(“DayPhone”)), 3) & “) ” & Mid((recClient(“DayPhone”)), 4, 3) & “-” & Mid((recClient(“DayPhone”)), 7, 4)
Debug.Print recClient(“DayPhone”)
If Len(recClient(“DayPhone”)) > 10 Then
Selection.TypeText ” x” & Mid((recClient(“DayPhone”)), 11)
End If
Selection.TypeText Text:=” w”
End If
Selection.TypeText Text:=vbTab
If Not IsNull(recClient(“WebAddress”)) Then
Selection.TypeText recClient(“WebAddress”)
End If
Selection.TypeParagraph

Else …

********

I will need to modify this to pull in the first available phone number regardless of which one it is rather than the way it is now where homephone only gets put beside address1. My thinking is to assign these to string variables and do all the formatting stuff that is happening on the fly in a function for first phone number, then second etc.
So the first few lines might be modified to the following

Selection.TypeText recClient(“Address1”)
Selection.TypeText Text:=vbTab
If Not IsNull(strPhone1) Then
Selection.TypeText strPhone1
End If

Where strPhone1 has already had a value set by calling a function that finds the phone number with the lowest priority.

The part I’m not sure about is how to choose the second record or the third record from a recordset. FindFirst will, I assume find the first record I need if I sort the recordset ascending. Can I do something as simple as FindNext for the 2nd and FindNext again for the 3rd? There are only a maximum of 5 records possible in any given set so in a worst case, I’d do findnext 4 times!

I understand what you are saying about assigning numbers rather than names to the phone types and allowing that to take care of the priority issue.

Reply | Quote

What if there are more addresses than phone numbers?
What if there are more phone numbers than addresses?

Reply | Quote

There will be five specified places in the output to cover the unlikely situation that all 5 numbers are actually used. In most instances, however, there are only 2. I only gave you a very small portion of it. The first 3 will go beside the 3 Address lines. The other two will go on the next line down. The book designer has prepared a template. My job is to output the info following that template.

The idea is to make her application of style sheets as efficient as possible and to reduce the amount of hand editing to a minimum.

Reply | Quote

Just a followup for anyone who might be interested. I got it to work. After my discussions with Hans, I decided to investigate arrays and ended up using the getrows method. After I figured out how to actually pull a value out of the array – my various books all show you lovely ways to print out every value in the array, but not specific ones, so I had to think for a while. Star Trek helped. You have to specify the exact coordinate that you want. It’s obvious when you know, but from the syntax, not terribly obvious to figure out.

Then I had to figure out how to deal with null values in the recordset of phone numbers. The IsArray function and skipping creating the array if there are no phone numbers at all solved that one.

Thanks Hans for pointing me in some right directions. Attached is the complete code. It’s not beautifully formatted yet… The function at the end adds in some text formatting at the end of each phone number: H for home, W for work, etc.

Reply | Quote

Thanks for sharing your solution.

Reply | Quote

I discovered a bug in the version I posted previously. Using the IsArray function and testing for a null when you get past the first number is not sufficient. In fact, I suspect that testing for nulls is redundant but didn’t have the time to test what happens if I remove that stuff and figured it wasn’t hurting anything.

If there is not a phone number after the first row, you get error 9. To solve this, I test for the minimum number of rows I need at each juncture (i.e. if there are only 2 rows and we are at the third place for a phone number then the code won’t try to include information for that place.) Since there were only a maximum of 5 phone numbers, this hard-coded solution works fine.

Reply | Quote