Requerying a recordset 1

ColinM · Jul 20, 2000

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 & "'&quot

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?

jimmythegeek · Jul 20, 2000

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 <a href=mailto:compugeeks@hotmail.com>compugeeks@hotmail.com</a> <a href=

http://www.cgeek.com>CompuGEEKS</a> Custom

Application Development

ColinM · Jul 20, 2000

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.

jimmythegeek · Jul 20, 2000

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 <a href=mailto:compugeeks@hotmail.com>compugeeks@hotmail.com</a> <a href=

http://www.cgeek.com>CompuGEEKS</a> Custom

Application Development

ColinM · Jul 21, 2000

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?

jimmythegeek · Jul 21, 2000

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 <a href=mailto:compugeeks@hotmail.com>compugeeks@hotmail.com</a> <a href=

http://www.cgeek.com>CompuGEEKS</a> Custom

Application Development

Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Requerying a recordset 1

ColinM

Programmer

jimmythegeek

Programmer

ColinM

Programmer

jimmythegeek

Programmer

ColinM

Programmer

jimmythegeek

Programmer

Similar threads

Part and Inventory Search

Sponsor