Noob Needs Assistance

[tigers08]

Hey All,

I'm using a Form Designer (FormFlow99) to design a form. I'm using ADO to connect to an Oracle Database - that part is working just fine.
My issue is that I have a fixed number of textboxes drawn onto the form and a varying amout of records being returned. If there are less records being returned than textboxes on the form it errors:
Microsoft VBScript Runtime Error
Script out of Range: '[number:6]'

Here is the code after the connection and recordset have been established:

If Not objRecordset.EOF Then
objResultSet = objRecordset.GetRows()
End If

Set objRecordset = Nothing

txtSSN1.Value = (objResultSet(0,0))
txtSSN2.Value = (objResultSet(0,1))
txtSSN3.Value = (objResultSet(0,2))
txtSSN4.Value = (objResultSet(0,3))
txtSSN5.Value = (objResultSet(0,4))
txtSSN6.Value = (objResultSet(0,5))
txtSSN7.Value = (objResultSet(0,6))

I know that the connection and recordset are good becuse it will write all reords to the form, but if there are less records than textboxes it gives the error above.

Any siggestions would be breatly appreciated.

 

[PHV]

Have a look at the UBound function.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[tigers08]

Thanks PHV,

I'm going to have a large number of fields for SSN and the records being returned could be anywhere from 1 to 50.

I'm thinking ubound in some sort of Loop might work but not exactly sure how to accomplish this.

 

[PHV]

In order to avoid the Script out of Range error:
One way is to test the UBound return value
Another way is to test the RecordCount property of your RecordSet object

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[tsuji]

quote documentation
[tt]The GetRows method is used to copy records from a recordset into a two-dimensional array. The first subscript of the array identifies the field and the second array subscript identifies the record number.[/tt]
unquote

[1] Make sure you want txtSSN1.Value = (objResultSet(0,0)) etc from the field 0 of successive record (not otherwise, successive fields of the first record).

[2] Then you control the number of records returned.
[tt]
if ubound(objResultSet,2)>=6 then
txtSSN1.Value = (objResultSet(0,0))
txtSSN2.Value = (objResultSet(0,1))
txtSSN3.Value = (objResultSet(0,2))
txtSSN4.Value = (objResultSet(0,3))
txtSSN5.Value = (objResultSet(0,4))
txtSSN6.Value = (objResultSet(0,5))
txtSSN7.Value = (objResultSet(0,6))
else
'decide what you want to do with less records returned than the number of textbox
end if
[/tt]

 

[tigers08]

Thanks for the replies. My only concern with the IF Then statement using the Ubound function is that I plan to have up to 50 fields on the form (SSN1-SSN50) and the records being returned could vary from 0-50. I would have to write many IF Then statements to cover all the possibilites.

I was hoping I could do some sort of Do Loop with a counter...then break out of the loop when the recordset = 0. I haven't been able to make it work, but something similar to this...

objResultCount = objRecordset.RecordCount

Do Until objResultCount = 0
If objResultCount = 0 Then Exit Do
txtSSN1.Value = (objResultSet(0,0))
objResultCount = objResultCount -1
txtSSN2.Value = (objResultSet(0,1))
objResultCount = objResultCount -1
txtSSN3.Value = (objResultSet(0,2))
objResultCount = objResultCount -1
txtSSN4.Value = (objResultSet(0,3))
objResultCount = objResultCount -1
txtSSN5.Value = (objResultSet(0,4))
objResultCount = objResultCount -1
txtSSN6.Value = (objResultSet(0,5))
objResultCount = objResultCount -1
txtSSN7.Value = (objResultSet(0,6))
objResultCount = objResultCount -1
txtSSN8.Value = (objResultSet(0,7))
Loop

 

[tsuji]

[0] You seem to have no idea ubound() acturally doing on your objResultSet which is a 2-dim array. You know exactly number of record returns.

[1] I have no idea of formflow and your formflow form in particular. But let's say pdfformfields be the form fields reference, and setfield(fieldname,value) be the method to set field value. (The idea is that the form must be capable to reference to fields by variable name.) You ahve to read the manual for the member methods. This is how to assign the returned result set to the fields, figuratively.
[tt]
on error resume next
for i=0 to ubound(objResultSet,2)
opdfformfields.setField "txtSSN" & (i+1), objResultSet(0,i)
if err.number<>0 then
err.clear
exit for
end if
next
on error goto 0
[/tt]
[2] The use of on error directive supposes the naming is consecutive in txtSSN1,.... If you have more precise info on the methods etc you can use the info to avoid the on error directive. That would be the general layout.