Value of a specific field / ADO Loop / Select Case ??

[A441OTA]

Good morning one and all!!!

I am in a bit of a quandry of how to approach my latest issue. I have an Access database that I need to create Word Mail Merges from.

I understand how to set up the merges, attach the data source, etc. What I'm confused on is the most efficient approach to determining what Word template to use. This is determined by looking at two fields in the database... for instance:

If Response_Count = 9 AND Scale_Label is not = "--" then the template used would be a specific one, if Response_Count = 9 AND Scale_Label = "--" then a different one would be used.

I have 18 different templates (soon to be even more unfortunately but I'll deal with that later, the concept will be the same!!!).

How in the heck do you look at the recordset to determine values in ADO? I am not using controls because (as you all will agree!) they were causing problems and not flexible enough.... However, connecting and manipulating in ADO is REALLY confusing me!! I have used some DAO before and it seems a little more straight-forward, but everyone has "ordered" me to use ADO!!! (of course, I did use some DAO on another form because it was easier and that was early on!!!!!).

Please advise. I have tried searching but haven't come up with anything yet! I thought I saw something about SQL Select Case before but can't seem to find it (may not have been this forum), but even so I'm not sure how to determine values for specific fields via ADO! If I can figure this out then Select Case in VB and calling a procedure is what I'm thinking would be best??? The field value will be the same for every record in the specified data table.

Thanks,
Mary

[Killazzz]

try this

Dim cnn As ADODB.Connection
Set cnn = New ADODB.Connection
strCnn = "driver={Microsoft Access Driver (*.mdb)};dbq=c:\db1.mdb" ' this the database I'm using change it to your db
cnn.open strCnn

Dim rs As ADODB.Recordset
Set rs = New ADODB.Recordset
rs.ActiveConnection = cnn
rs.CursorType = adOpenDynamic
rs.open ("select * from db where n = 1 order by [n-1] asc") ' replace with your table
rs.movefirst 'go to the first record

'start looping the recordset

while not rs.eof
select case rs!response_count
case is = 9
if scalelabel = "-" then
'start word using template 1
end if
case is = 10
if scalelabel = "--" then
'start word using template 2

end if
end select


rs.movenext
wend

[A441OTA]

Thanks Killazzz, I will get working on it and let you know how it goes!! That seems pretty straight-forward, maybe even I can't screw it up!!!!! :-)

[NateBrei]

Based on your initial situation, I'd change:

select case rs!response_count
case is = 9
if scalelabel = "-" then
'start word using template 1
end if
case is = 10
if scalelabel = "--" then
'start word using template 2

end if
end select

to

Select Case rs!Response_Count
    Case is = 9
        If Scale_Label = "--" Then 
            'start word using template 1
        Else
            'start word using template 2
        End If
    Case Else
        MsgBox "No option defined for this Response_Count value"
End Select

Nate