×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Requerying a recordset

Requerying a recordset

Requerying a recordset

(OP)
Hi,
I am trying to requery a recordset so that a form changes record based on a combo box.
I thought I should be able to use a requery rather than run the whole set recordset code again.

I am using this code:

Sub SetRS(FormName As String, strSQL As String)
Set rst = New ADODB.Recordset
rst.Open strSQL, CurrentProject.Connection, , , adCmdText
Set Forms(FormName).Recordset = rst
End Sub

Private Sub Form_Open(Cancel As Integer)
Call RS.SetRS(Me.Name, "Select * from Customers
where name = '" & NameCombo.Value & "'")
End Sub

I thought I would be able to then use this:

Private Sub NameCombo_Change()
Form.Recordset.Requery
End Sub

Anybody see where I'm going wrong?

RE: Requerying a recordset

You cannot bind a form to an ADO recordset. A form can only be bound to a local table, linked table, query, or recordset to one of the preceeding.

You can bind a form to a Pass Through Query however. OR
Leave the form unbound and fill in the values on Open of the form, for example:

Private Sub Form_Open()
   Dim rst as ADODB.Recordset
   Dim strSQL as String

   strSQL = "SELECT * FROM Customers WHERE name = '" & Me.NameCombo & "'"
   Set rst = New ADODB.Recordset
   rst.Open strSQL, CurrentProject.Connection, adOpenForwardOnly, , adCmdText
  
   If rst.Recordcount > 0 Then
      With rst
         Me.Field1 = !Field1
         Me.Field2 = !Field2
         Me.Field3 = !Field3
         etc.
      End With
   End If

   rst.close
   Set rst = Nothing
End Sub


Now put a submit button on your form. When they submit changes, do the same thing, exept you would  write to the table instead of read from it.


Private Sub cmdSubmit_Click()
   Dim rst as ADODB.Recordset
   Dim strSQL as String

   strSQL = "SELECT * FROM Customers WHERE name = '" & Me.NameCombo & "'"
   Set rst = New ADODB.Recordset
   rst.Open strSQL, CurrentProject.Connection, adOpenDynamic, , adCmdText
  
   If rst.Recordcount > 0 Then
      With rst
         .Edit
         !Field1 = Me.Field1
         !Field2 = Me.Field2
         !Field3 = Me.Field3
         etc.
         .Update
      End With
   End If

   rst.close
   Set rst = Nothing
End Sub


Class modules are a good way to handle this, except it would take way to long to explain how to use them. I would check into them. There is a good example in the Access 97 Developers Handbook by Litwin, Getz, & Gilbert, Chapter 3.

Jim Lunde
compugeeks@hotmail.com
CompuGEEKS
Custom Application Development

RE: Requerying a recordset

(OP)
This line of code works:
Set Forms(FormName).Recordset = rst

binding the form to the recordset.
Perhaps you can't do this in an mdb file? I don't know.

It was just the other bit where the combo box is changed that didn't work.
I can change the recordset by sending another query. I thought that I could requery it and it would be more efficient, rather than closing the old recordset and creating a new one.

RE: Requerying a recordset

I have never seen a Recordset property for a form. I know there is a Record Source property, but Recordset is new to me.

What do you mean by "Perhaps you can't do this in an mdb file?", aren't you using an .mdb file?
Is this a read-only form? Let me explain:

In an Access database, a bound form means that the fields on the form are basically connected to the fields in the table. When you update them, the changes are automatically written to the table. I simply can't believe that you could do this with an ADO recordset. When you key information into the field on the form, the ADO recordset is not a direct link to the table, it is a set of information that you have requested from SQL Server. When you try to write information back to the table, it would require the .Edit, and .Update methods of the Recordset object. If it is a view-only form, it may work, but binding any Client-Server applications is not the best practice.

Jim Lunde
compugeeks@hotmail.com
CompuGEEKS
Custom Application Development

RE: Requerying a recordset

(OP)
Apologies!
You are correct, it wasn't bound at all.

I was using the wrong terminology.
What I should of said was that the form displays the recordset that you assign, but is NOT bound, as it can't be updated ( I had been using the form to only look at data ).
I am now using RecordSource to achieve what I want, thanks!

As far as the .mdb file bit goes, I have been using .adp (Access 2000 project file) which is just a front end to SQL server.

I don't know if you've had any experience of Access 2000 project files.

In your last sentence you say that binding forms is not a good idea in Client/Server. I assume this is because of concurrency issues. Is there any other reasons for not using bound forms. (They seem slightly faster to me)

I recently read that bound forms in A2K projects would only lock the data for the duration of the fetch, anybody know any more about this?

RE: Requerying a recordset

Unfortunately, I have not had any experience with .adp's. The job I am at now uses Oracle, so I will not be using them in the near future either.

The main reason for not binding forms in Client-Server is performance. The whole idea of having a seperate server is to let the server do the processing for you, and not the client machine. Bound forms do not allow this. I can't speak for .adp files, but I know in .mdb files, the only way to bind a form to a server is to attach the tables, then bind to the attached table. BAD Idea. It defeats the whole purpose of the Server processing.

We had a 100 user Access - Access system using bound forms, and having record counts of several hundred thousand records. The performance soon became too bad to continue using that method. So while that was going on, we converted the back end to SQL Server, and unbound everything. All data retrieval, was done with Pass through queries, and ADO. All writing was done with ADO. The performance difference when we were done was astonishing! Processes that took 15 - 25 seconds before, took less than 2 seconds.

Another reason for not binding forms is that the updates that users do to the tables are instantaneous. Data is harder to control than having them make changes, then submit them, and have them verify that they want to save them. It is also easier to create audit trail tables this way.

I am not saying that a bound application cannot be effective. Currently we have over 20 applications that use this technology on a national network. However, the performance is not what it could be if it was an unbound Client - Server application. I do accept the fact that an unbound application takes longer to develop, but I believe it is worth it in the long run.

Jim Lunde
compugeeks@hotmail.com
CompuGEEKS
Custom Application Development

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close