Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > Microsoft Office > Access and Access VBA > Access VBA
Password Reminder
Register
| FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read
Access VBA Discuss using VBA for Access programming.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access VBA section of the Wrox Programmer to Programmer discussions. This is a community of tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old October 9th, 2003, 06:06 AM
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
  #2 (permalink)  
Old October 9th, 2003, 01:59 PM
Friend of Wrox
 
Join Date: Jun 2003
Location: , , USA.
Posts: 1,093
Thanks: 1
Thanked 12 Times in 11 Posts
Default

Quote:
quote:
How do I get each recordset to populate each record on the form?
Unfortunately you can't. There isn't any way to assign unbound control values from a Recordset object if the form is displayed in continuous or datasheet view.

If you were using a .mdb file, you could cache the recordset in a temp table, then bind to the temp table. Since you're using a .adp file, though, you don't have the local storage option. What I have seen done ("Microsoft Access Developer's Guide to SQl Server") is the use of a local XML file to cache local data, with code like the following:

'Create a client-side recordset.
  Set rst = New ADODB.Recordset
  rst.CursorLocation = adUseClient
  rst.CursorType = adOpenStatic
  rst.LockType = adLockReadOnly
  'Find or create the XML file,
  ' and open the recordset.
  If Len(Dir(strFile)) > 0 Then
      'File was found. Open recordset from the file.
      rst.Open Source:=strFile, Options:=adCmdFile
  Else
      'File wasn't found.
      'Open recordset using the Row Source,
      ' and create the XML file.
      rst.ActiveConnection = _
        Forms!frmLogin.OLEDBConnect
      rst.Open Source:=ctl.RowSource
      rst.Save strFile, adPersistXML
      Set rst.ActiveConnection = Nothing
  End If

The source code for the book I cited above is available from the Sam's Publishing web site if you want to look into this technique a little further. It works well, but isn't exactly trivial to implement.

HTH,

Bob

Reply With Quote
  #3 (permalink)  
Old October 9th, 2003, 04:04 PM
Friend of Wrox
 
Join Date: Jun 2003
Location: , , USA.
Posts: 1,093
Thanks: 1
Thanked 12 Times in 11 Posts
Default

If you decide to download the code, you're interested in the chapter 11 folder. It contains two unbound Access front-ends to the SQL Server version of Northwind. One app is a .mdb which executes stored procedures using pass-through queries and caches the data in local Jet tables; the other is an .adp which caches local data in XML files on your file system in the directory where the app is sotred. There is also a script with the download that you need to run to create the stored procedures. There are quite a few. Both apps are reasonably complete: login screens, search functionality, ability to fetch a limited number of records, all your standard DML functions, input validation, etc...

Reply With Quote
  #4 (permalink)  
Old September 7th, 2004, 09:53 PM
Von Von is offline
Registered User
 
Join Date: Sep 2004
Location: , , .
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts
Default

I have been working the same issue and here is the solution I came up with. My code is almost the same as yours with the following exceptions:

Remove the following loop code:
While Not rst.EOF
With rst
Me.txtItemType = !ITEM_TYPE
Me.txtBigNotes = !BIG_NOTES
Me.txtLocationUse = !LOCATION_USE
rst.MoveNext
End With
Wend

Then on your form bind the field text box controls (not the form) by putting each table field name in the field text box control source property.
Leave the form recordsource unbound.

This populated my continuous form correctly. Of course since it is an ADO recordset assigned to a form it is not updateable in the form. In my form the only field that needs to updated is a supervisor's approval checkbox. I put a command button that executes a parameterized update query based on the primary key field in the record that has the focus in the continous form.
Let me know if this works for you.
Von Kettler

Reply With Quote
  #5 (permalink)  
Old December 18th, 2005, 02:27 AM
sfx sfx is offline
Registered User
 
Join Date: Dec 2005
Location: , , .
Posts: 6
Thanks: 0
Thanked 0 Times in 0 Posts
Default

This reply is somewhat belated, but I have only just read this series of posts after being confronted with the same problem that Bobby has had.

My question is directed to Von Kettler. Could you provide more detail concerning how you managed to get an ADO recordset to populate a continuous form? I am particularly interested in the form and control work/syntax that you used to solve your problem.

Cheers,

sfx
Reply With Quote
  #6 (permalink)  
Old December 18th, 2005, 04:53 AM
Friend of Wrox
 
Join Date: Jun 2003
Location: , , USA.
Posts: 1,093
Thanks: 1
Thanked 12 Times in 11 Posts
Default

Hi sfx,

Since this appears to be Von’s only post to this forum ever (gem that it is), mind if I share my two cents? I was under the impression that this couldn’t be done, so I found it quite interesting. Of course, it turns out to be quite simple.

I created a table (in a .mdb) with two fields, DataID and DataItem, and added a few records. Then created a form and placed two text box controls (Text1 and Text2) on it. I set the form’s Default View property to Continuous Form, and left all other form properties alone. Then I typed DataID in Textbox1’s Control Source property, and DataItem in Textbox2’s Control Source property, leaving all other textbox properties alone. Then I placed the following in the form’s Load Event:

Private Sub Form_Load()
    Dim cnn As ADODB.Connection
    Dim rst As ADODB.Recordset

    Set cnn = CurrentProject.Connection
    Set rst = New ADODB.Recordset

    Dim strSQL As String
    strSQL = "SELECT * FROM tblData"

    rst.Open strSQL, cnn, adOpenKeyset, adLockOptimistic, adCmdText

    Set Me.Form.Recordset = rst

End Sub

The continuos form populates as advertised.

The only observation I might add is that this only works with a Keyset or Static cursor, which suggests that the recordset needs to be able to report exactly how many records it contains. Dynamic or ForwardOnly cursors return a run-time error.

Thanks for revisiting this.

Bob


Reply With Quote
  #7 (permalink)  
Old December 18th, 2005, 08:31 AM
sfx sfx is offline
Registered User
 
Join Date: Dec 2005
Location: , , .
Posts: 6
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Hi Bob,

No, thank you for revisiting this. :)

Your information has proven most helpful. It has been some time since you last broached this subject, and no doubt your skills have greatly improved since then. I am but a nascent Access developer, so thank you for responding so swiftly to my post and sharing your insight with me.

Cheers,

sfx
Reply With Quote
  #8 (permalink)  
Old May 11th, 2007, 06:05 PM
Registered User
 
Join Date: Feb 2006
Location: , , .
Posts: 5
Thanks: 0
Thanked 0 Times in 0 Posts
Default

The code provide by Bob Bedell in his 12/18/2005 posting is very interesting. But what do I do if besides the actual field values I also need to include some action buttons per each record in the recordset?
Also, suppose the user clicks on the button that corresponds to a particular record shown. How do I refer to that record in the VBA behind the button, in a continuous form?

Thank you,
Vlad

Reply With Quote
  #9 (permalink)  
Old May 13th, 2007, 03:28 AM
Authorized User
 
Join Date: Mar 2007
Location: , , United Kingdom.
Posts: 21
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Hi Vlad

The answer is simple - just treat the continuous form as if it were a normal form. For example, if the user clicks a command button on a particular record in the continous form, then Access will treat all code as if the form displayed just that one record. So references to controls such as Me!txtValue will give you the current value for that record.

As for the per record actions, just add the command buttons to the continuous form - as I said above, each command button will run in the contect of the record it is displayed against.

Hope that helps!

Richard

Reply With Quote
Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Conditional Formatting in Continuous Forms Balasarius Access 3 September 19th, 2008 07:06 AM
continuous forms question hamffjs Access 3 January 29th, 2007 01:33 PM
Checkboxes and continuous forms mdProgrammer Access VBA 4 August 3rd, 2006 09:47 AM
Continuous Forms Issues strikeuk Access 1 January 2nd, 2006 11:59 AM
Continuous Forms Ben Access VBA 2 February 17th, 2004 10:34 AM



All times are GMT -4. The time now is 12:07 PM.


Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.