If, Then, Else..............HELP

[BigD]

I am pretty new to VBA programming and having problems with the following syntax.

Function CTProvidersB_PopulateRTC()

Set db = CurrentDb()
Set rs = db.OpenRecordSet("CT-Providers")
Set fld1 = rs("Primary_Specialty")
Set fld1 = rs("Secondary_Specialty")
Set fld3 = rs("RTC")

rs.MoveFirst

Do

rs.Edit

If fld1 = "Insurance Medicine" And fld2 = "Occupational Medicine" Then fld3 = "PH"
ElseIf fld1 = "Insurance Medicine" And fld2 = "Psychiatry" Then fld3 = "BP"

rs.Update

rs.MoveNext

Loop Until rs.EOF

MsgBox "Complete.", vbInformation, "MCIS Update"

rs.Close

End Function

Any help would be appreciated.

 

[mtc67]

i am not very good at vb coding but it seems like you are missing your "end if" comment.

there might be other problems, i'm not sure, but that stands out to me.

It should look like:

If fld1 = "Insurance Medicine" And fld2 = "Occupational Medicine" Then fld3 = "PH"

ElseIf fld1 = "Insurance Medicine" And fld2 = "Psychiatry" Then fld3 = "BP"

END IF


hope that helps.


[This message has been edited by mtc67 (edited 06-21-2001).]

 

[KDg]

the above is correct but you'll also need to split out the first then statement :
If fld1 = "Insurance Medicine" And fld2 = "Occupational Medicine" Then
fld3 = "PH"
ElseIf fld1 = "Insurance Medicine" And fld2 = "Psychiatry" Then fld3 = "BP"
End If

otherwise the first if is terminated on the first line and the elseif will throw you an error. At that point it's probably neater & easier to read if you split it fully into

If fld1 = "Insurance Medicine" And fld2 = "Occupational Medicine" Then
    fld3 = "PH"
ElseIf fld1 = "Insurance Medicine" And fld2 = "Psychiatry" Then
    fld3 = "BP"
End If

You may also want to move the loop test to the top to be:

Do Until rs.EOF
...
Loop

that way if no records are found you will step over the loop rather than having to run through it once and generating an error.

HTH

Drew

[This message has been edited by KDg (edited 06-21-2001).]