View Single Post
  #1 (permalink)  
Old October 9th, 2003, 06:06 AM
Bobbyworld Bobbyworld is offline
Registered User
 
Join Date: Oct 2003
Location: , , .
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts
Default Populating Continuous Forms

Hello,
I have a problem that's really bugging me.
I'm trying to populate a form in an Access Project using an ADO recordset.
The form is set to Continuous forms.
The following code is run after a user selects an item from a combo box. There can be as may as 30 odd records being brought back.
The problem is when populating the form all the records in the form are populated with the last recordsets data. How do I get each recordset to populate each record on the form?
(The solution to set the recocrdsource in access itself will not suffice,as I need to be able to type in data without using a recordset as well)
The code I'm using is as follows;

Private Sub cboSchedule_AfterUpdate()

'This procedure transfers the reocrdset to the form based on the
'project/schedule chosen by the user

'declare connections and recordsets
Dim cnn As ADODB.Connection
Dim rst As ADODB.Recordset
Set rst = New ADODB.Recordset

Dim strSQL As String

'SQL string
strSQL = _
"SELECT * FROM luminaire_schedule_items WHERE doc_number ='" & Me.cboSchedule & "'"

On Error GoTo HandleErr

If ConnectTolager(cnn) Then

      rst.Open _
      Source:=strSQL, _
      ActiveConnection:=cnn, _
      CursorType:=adOpenKeyset, _
      LockType:=adLockBatchOptimistic, _
      options:=adCmdText

Set rst.ActiveConnection = Nothing
CloseAndReleaseConnection cnn

'If no records then quit
If rst.EOF Then
MsgBox "No records found"
GoTo ExitHere
End If

'transfer recordset to form
Set Me.Form.Recordset = rst
While Not rst.EOF
With rst
Me.txtItemType = !ITEM_TYPE
Me.txtBigNotes = !BIG_NOTES
Me.txtLocationUse = !LOCATION_USE
rst.MoveNext
End With
Wend

ExitHere:
'close connection
  CloseAndReleaseConnection cnn
  Set rst = Nothing
  Exit Sub

HandleErr:
  MsgBox Err & ": " & Err.DESCRIPTION, , _
    "Error"
  Resume ExitHere
  Resume

End If
End Sub

Reply With Quote