[Resolved] efficient approach... Word Mail Merge / Access db

[A441OTA]

Thanks Vince -- you were on the right track of how this was accomplished.


This is what worked if for some reason anyone needs it in the future!

Dim ConvertBarValue As Integer
Dim OldBarFile As String 'name of existing bar chart file
Dim NewBarFile As String 'temp name based on value
Dim BarChartFile As String 'name to set in field

With rsSurvey
.MoveFirst

Do Until rsSurvey.EOF
If (rsSurvey!Bar_Value <> "") Then

If (rsSurvey!Bar_Value < 0) Then
OldBarFile = App.Path & "\BarCharts\Bar_0.bmp"
NewBarFile = Replace(OldBarFile, "\", "\\")
rsSurvey!BarChartFile = NewBarFile
.Update

ElseIf (rsSurvey!Bar_Value = 0) Then
OldBarFile = App.Path & "\BarCharts\Bar_0.bmp"
NewBarFile = Replace(OldBarFile, "\", "\\")
rsSurvey!BarChartFile = NewBarFile
.Update

ElseIf (rsSurvey!Bar_Value > 0) Then
ConvertBarValue = Int(rsSurvey!Bar_Value * 100)
OldBarFile = App.Path & "\BarCharts\Bar_"
&ConvertBarValue & ".bmp"
NewBarFile = Replace(OldBarFile, "\", "\\")
rsSurvey!BarChartFile = NewBarFile
.Update

End If

End If

rsSurvey.MoveNext 'go to next record

Loop 'loop until end of file

End With




********** Original Post *****************************

Good morning everyone,

My brain is still a bit fuzzy from vacation (I'm at work physically but my brain is still in the Bahamas!!!). I have a rather complicated issue to contend with and I'm not sure the most efficient approach. I suspect that arrays will be involved but I've never used an array and don't know the best way to set it up.

I have a field in a database that has a value 0.0 to 1.0 -- it should never be less 0 and no more than 1.0 (it equates to a percentage). I have another field for a filename that is blank. I need to get the value from the first field and insert the appropriate filename in the filename field. These files are .bmp files (it is a bar that corresponds to the percentage). These files will be stored in a subdirectory called Bar Charts but will be in the application path on the user's machine.

For instance:

field value = 1.0 set filename = app.path &"\Bar Charts\Bar_100"
or
field value = .1 set filename = app.path &"\Bar Charts\Bar_10"

Should I use Select Case and set up 100 cases? or an array? Or are both about the same?

One other complication is that in order to use this file name in the Word mailmerge to include the picture the file has to have double backslashes instead of single..... anyone have a suggestion how to do this when using app.path?? For instance, if the filename should be set to D:\UserData\Bar Charts\Bar_10 -- it has to read "D:\\UserData\\Bar Charts\\Bar_10" in order for Word to process it with the IncludePicture field.

If anyone can help I would greatly appreciate it!!! In the meantime I will continue searching.... I have to run a test for the client tomorrow afternoon so I'm trying to get this portion automated before then -- if not I will have to manually insert these pictures (and there are tons of them!!!!).

Thanks,
Mary

I'm in VB 6 using ADO, Windows 2000, and Office XP