Recordset Problem..please help

[Tinku]

I am having problems assigning the recordset values to a tabular form with unbound boxes. I have assigned the values eg. frm1.txt_Operator(X) = rst1(0) and put this in a loop as below..it give me a error 451 and if I put it outside a loop eg. frm1.txt_Operator = rst1(0)
then it shows only the last record.. How can I assign all the values of the recordset from the loop to the textboxes


Option Compare Database

Private Sub Search_Click()

Dim int1 As Integer
Dim int2 As Long
Dim int3 As Long
Dim cnn1 As ADODB.Connection
Dim rst1 As Recordset
Dim str1 As String
Dim str_op As String
Dim str_cent As String
Dim str_adv As String
Dim X As Integer

X = 1

Set cnn1 = New ADODB.Connection
cnn1.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\Documents and Settings\Administrator\My Documents\Skills Assessment file" & _
"\SAR.mdb;"

Set rst1 = New ADODB.Recordset
rst1.CursorType = adOpenKeyset
rst1.LockType = adLockOptimistic
rst1.CursorLocation = adUseClient

'Open recordset based on orders in 1998 for
'frmSimpleUnboundForm
rst1.Open "SELECT a.Operator, a.Center, " & _
"a.Photoshop FROM [Main-TG] as a where a.photoshop Is Not Null ;", cnn1

'Print record with revised Quantity field
Debug.Print rst1(0), rst1(1), rst1(2),

str1 = "frm_skill_tz"
DoCmd.OpenForm str1
Set frm1 = Forms(str1)
Set frm1.Recordset = rst1

Do While Not rst1.EOF
frm1.txt_Operator(X) = rst1(0)
frm1.txt_Center(X) = rst1(1)
frm1.txt_adv(X) = rst1(2)
Debug.Print rst1(0), rst1(1), rst1(2)
rst1.MoveNext
X = X + 1
Loop

'Clean up objects
rst1.Close
cnn1.Close
Set rst1 = Nothing
Set cnn1 = Nothing

End Sub

Regards
Tinku

 

[Adrianna]

I see that the textboxes are in sets of three, but how many unbound textboxes do you have on that form? Do you know how many records will be in the recordset?

Have you debugged and stepped through the process to see how the records on your form are changing?

 

[Tinku]

Hi Adrianna

This doesnot work
frm1.txt_Operator(X) = rst1(0) ' gives me a error'

This works
frm1.txt_Operator= rst1(0) ' without (X)'

But as it is outside the loop it gives me only the last value of the recordset.
But I can see there are 41 rows selected in the recordset. My problem is how
do I assign those 3 unbound textboxes to these rows of the recordset.

Hope this is helpful.

Regards
Tinku

 

[ChrisO]

G’day Tinku

A Tabular form is a Continuous Form and as such only has one row of controls.

You can see this in design view, only one row of controls and hence only one control by each name.

The reason you only get the last data is because each pass through the recordset would overwrite the previous data.

Not much help I’m afraid.

Regards,
Chris.

 

[ChrisO]

Give something like this a try.

Obviously you will need to change the names and path to suite your circumstances.
You will also need to bind the controls to the record fields.

Option Explicit
Option Compare Text


Private Sub Form_Load()

    Me.RecordSource = " SELECT tblNames.*," & _
                             " tblNames.FirstName" & _
                      " FROM tblNames IN 'C:\db1.mdb'" & _
                      " WHERE tblNames.FirstName Is Not Null;"

End Sub

Hope that helps or gets you started.

Regards,
Chris.

 

[ChrisO]

I don’t think my brain is switched on today.

Another simpler option might be to link to the table or query and build the form on that.

Regards,
Chris.