while loop eof with ADP and stored procedures

[belovedcej]

back end is SQL server - front is adp.

we have multiple claimants per claim and multiple phone numbers per claimant.

On my claim form I am trying to fill an unbound text box with claimant address and phone information. I cannot use a subform because I would actually need to put a subform in a continuous form (phone numbers into claimants) and that won't work.

So I'm trying my hand at a loop.

Loop seems okay - the real problem is making the connection and correct syntax for assigning the information from the recordsets into the field.

The following code compiles but when run I get an "invalid use of property" on the rst1.filter.

Could someone advice me?

Thanks!

Dim rst1 As New ADODB.Recordset
Dim intClaimID As Integer
Dim strClaimants As String

intClaimID = Me.txtClaimID

Set rst1 = CurrentProject.Connection.Execute("CSF_Claimants_Per_Claim_V")
rst1.Filter ("Claim_ID = " & intClaimID)


Do While Not (rst1.EOF)

strClaimants = strClaimants & rst1.Fields("Attorney_Name") & Chr(13) + Chr(10) + _
                rst1.Fields("Line_1_VC30") & Chr(13) + Chr(10) + rst1.Fields("Line_2_VC30") _
                & Chr(13) + Chr(10) + rst1.Fields("Line_3_VC30") & Chr(13) + Chr(10) + _
                rst1.Fields("City_VC30") & ", " & rst1.Fields("State_C2") & "  " & rst1.Fields("zip_VC10") & Chr(13) + Chr(10) + ""
        
Dim rst2 As New ADODB.Recordset
Dim intPerson As Integer
intPerson = rst1.Fields("Person_ID")

Set rst2 = CurrentProject.Connection.Execute("CSF_phone_Numbers_V")
rst2.Filter ("Person_ID = " & intPerson)

    Do While Not (rst2.EOF)
    strClaimants = strClaimants & rst2.Fields("Description_VC240") & ":  " _
    & rst2.Fields("number_vc60") & " (" & rst2.Fields("Description_VC240") & ")"
    
    Loop

Loop

Me.txtClaimants = strClaimants

 

[RoyVidar]

The invalid use of property, is probably because the filter is assigned through

[tt]rst1.Filter = "Claim_ID = " & intClaimID[/tt]

But I don't know how it works on a forwardonly, readonly recordset based on an SP, as you're doing here.

Roy-Vidar

 

[belovedcej]

Thanks - I'm wondering if maybe you can't filter using a variable? I could create a stored procedure and set the variable that way (instead of using a view) - but I was hoping to reuse what's already on the server instead of creating all this duplicate stuff.

(I also missed the moveNext in the loops - it never would have worked anyway! )

 

[PHV]

instead of using a view
If CSF_Claimants_Per_Claim_V is a view, why not simply open a recordset based on the following SQL:
"SELECT * FROM CSF_Claimants_Per_Claim_V WHERE Claim_ID=" & intClaimID


Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[belovedcej]

Because I always try the "hard way" first.

Okay, I've given this a shot - I would think it would work better - but it tells me type mismtach at the ampersand.

Set rst1 = "SELECT * FROM CSF_Claimants_Per_Claim_V WHERE Claim_ID=" & intClaimID


I changed my declaration if intClaimID to a Long, but that didn't help. Any ideas on that? Thanks!

 

[RoyVidar]

I think what PHV meant was;

[tt]Set rst1 = CurrentProject.Connection.Execute("SELECT * FROM CSF_Claimants_Per_Claim_V WHERE Claim_ID=" & intClaimID,,adcmdtext)[/tt]

Roy-Vidar

 

[belovedcej]

Beautiful! Thanks - I'm still new at this and miss the obvious sometimes.

Now I just have to work on making it look "pretty" - the chars aren't in the right spots. Fun-fun!