# Solved: Access forms binding



## BobFoster (Dec 5, 2008)

Hi,

I'm hoping for some advice on Access application architecture.

I'm modifying an application in Access 2003 on XP. I have a form that's bound to a (linked SQL Server) table and I want to alter the way the form stores modified data - specifically, I want it to call a stored procedure in the DB instead of automagically altering the underlying data through the binding mechanism.

I tried binding the form to a SELECT query instead of the table, hoping that this would break the binding for storing the data as well, but it did not.

So, what is the best way to change the form so that it reads the data from the table but requires action (either an UPDATE or call to a stored procedure) to update the DB? Currently, even if I comment out the "DoCmd.DoMenuItem ... acSaveRecord", on the forms save button, the record still gets updated.

Thanks in advance for any help you can give me.

I'm a reasonably experienced developer, incidentally, but this is the first time I've delved into Access.

Regards,
Bob


----------



## OBP (Mar 8, 2005)

Bob, you can't break the connection if the Form fields are bound to the SQL Query or Table and the "DoCmd.DoMenuItem ... acSaveRecord" only forces a save, Access does it anyway automatically. 
You can use use an Unbound Form and a Recordset to populate the Form instead of binding, in which case the Edited data goes nowhere unless you use the Recordset to put it there.
In future, for a quicker response to Access questions you are better off posting on the Business Programs Forum, where most of us Access types spend the majority of our time.


----------



## BobFoster (Dec 5, 2008)

Thanks for your help OBP. I'll remember to use the Business Programs forum next time!

So if I create a recordset, populate that with a query, use the data from that to display on the form and then pass the data from the recordset back to the db by explicitly calling my stored procedure, that's the best way of doing what I need?

Perhaps I'm being lazy (don't really want to repopulate all the controls if I don't have to), but is there a way to retain the individual controls' Control Source so it refers to the recordset? Or do I literally need to write VBA to copy all the values from the current record to each control and then back again to the recordset (or read from each control directly) when I call the stored procedure? I'm assuming I want to create and populate the recordset in Form_Load(), but if I do that, there's nothing in the designer to bind the form to. I suppose that's what "Unbound form" means...

Thanks,
Bob


----------



## OBP (Mar 8, 2005)

Taking the route that you want of not immediately updating the Table does mean a lot of Work, although you only need a For/Each Loop to pass the Values Back and For between the Recordset and the Form's Fields. I know and helped a guy who built a Large database and the whole thing ran on VBA controlled Forms because he had read that it gives you better control, it was pages and pages of VBA code. 
I can help you with the Code if you want, as it is fairly complex to use Loops, it is much easier to assign the values one at a time. 

Why do you feel that you need ot do this, there are other alternatives that you can use to Validate the data before Updating the Table.


----------



## OBP (Mar 8, 2005)

I am in the UK and will have to go soon as my Wife kicks me off the Computer in the Evenings.


----------



## BobFoster (Dec 5, 2008)

Me too on both counts - I'm typing on borrowed time...

The data in my form is stored in three records on two tables and there is some update logging, all of which is contained in a single transaction in the stored procedure. I'd really rather handle things this way (for this form - I'm not going to do it for the whole application).

Your offer of help is much appreciated. I can probably handle the code, but if you could give me a rough idea of the steps I ought to take (i.e. what am I iterating through in the For Each loop and what is the recommended way to identify which control matches which field in the recordset), that would certainly be of help.

Also, I'm a little confused about talking to the database. I've got an ODBC connection because of the linked tables. Can I reuse that for the recordset and to call the stored procedure? Most of the samples I have seen seem to create a new connection. This seems inefficient, but I don't know what the connection object I presumably already have is called. Can you shed any light on this for me?

Thanks again,
Bob


----------



## OBP (Mar 8, 2005)

Bob, I have never worked with a remote SQL database.
Is it the same as Linking to another Access database?
i.e. in the Tables Tab can you see the SQL tables with linking arrows?
Can you create a front end Access Query on one of those Tables?


----------



## BobFoster (Dec 5, 2008)

I think so, yes - Access behaves as if the tables were local. Not sure about the icon for linking to other Access DBs, but my tables are shown as an arrow pointing to a planet (I think). I've attached a screenshot. There are other ways of working with SQL Server (an Access Project, for example), but when I was trying to work out the best way of doing things, this seemed to tick a lot of the right boxes.

This way, you can do anything with the tables you can do with a local table (as far as I can tell), but it also means you can't do anything with them (easily) that you can't do with a local table. So you get the benefits of Access, but also the limitations: I can't use transactions or other T-SQL that Access doesn't understand (such as executing stored procedures) and so on without using pass-through queries. It seems that when I do that, I don't get the Access rapid development features such as binding forms to tables.

Bob


----------



## OBP (Mar 8, 2005)

Bob, that looks similar to the normal remote tables.
So you should be able to use Table or Query based Recordsets directly.
Do you want to try an experiment?
Create a Form based on a Table or Query with just a couple of Fields and then remove the Field's Control Sources, but leave the Form's Record Source.
In the Form's "On Current Event Procedure" put in the following VBA Code
dim rs as object
on error goto errorcatch

Set rs = Me.RecordsetClone
msgbox rs.recordcount ' this tells you how many records you would have

rs.close
set rs = nothing
exit sub
errorcatch:
MsgBox Err.Description

This will test if the Recordset is working Ok with your Form's Record Source


----------



## BobFoster (Dec 5, 2008)

This works - I get a count of the records in the table.

I wondered if I could set the form's recordset property to this clone (although I haven't yet worked out whether this is a separate view on the same underlying records or a complete, independent copy of the entire recordset).

I couldn't get this to work, however. If I use a DAO.RecordSet, assiging the clone to Me.RecordSet results in "Operation is not supported for this type of object". An AdoDb.RecordSet (which a bit of googling suggests _can _be used to assign to Me.RecordSet - http://support.microsoft.com/kb/281998), it won't even compile because you can't assign RecordSetClone to an Ado recordset.

Of course, none of this changes the RecordSource of the form, which is still the database table (which I don't think I want).

Am I barking up the wrong tree here? Presumably your test was leading somewhere - perhaps I should wait to see where...

Regards,
Bob


----------



## OBP (Mar 8, 2005)

Bob, if you got the Recordset count to work you can now remove all the Control Sources from the Fields on the Form and use the VBA recordset to populate it instead.
But any changes to the Form fields will do nothing to the Table or Recordset Values.


----------



## BobFoster (Dec 5, 2008)

Okay that sounds good... if the controls no longer have a control source, presumably the next step is to write some code in "On Current" to copy values from each of the RecordSet fields into the form controls. Correct?

What I've got (which seems to work) is:


```
Private Sub Form_Current()
    Dim fld As Field
    Dim count As Integer
    Dim rs As dao.Recordset
    
    Set rs = Me.RecordsetClone
    rs.Bookmark = Me.Recordset.Bookmark
    
    For Each fld In rs.Fields
        For count = 0 To Me.Controls.count - 1
            If fld.Name = Me.Controls(count).Name Then
                Me.Controls(count).Value = fld.Value
                Exit For
            End If
        Next
    Next
End Sub
```
This seems a bit inefficient (creating a recordset each time the record is changed - although I could use a global variable - and the nested For loops to identify fields common to controls and the records) but it's the best way I can see of doing it. At the least, I'm wondering if there is a way to create a RecordSet clone with only the current form record in it, rather than the whole lot.

As I said at the start, I'm new to Access (and I've never done much in VB / VBA before), so if I've missed anything, please point it out!

Incidentally, MSDN seems to suggest that a new RecordSet should have the same current record as the rs from which it was cloned. Despite this, I still seem to need the bookmark syncing line to achieve this.

Thanks again,
Bob


----------



## OBP (Mar 8, 2005)

Bob, you are a quick study, it took me quite a while to work out what you have done when I did it about a 6 months ago. :up:
What you have done is correct except for one thing, you don't need to keep creating the whole recordset for each record.
Don't use the Recordset Clone, instead use a VBA SQL Recordset, to do this change the Control Source of the Form's ID field to that of the Query/Table and set it's property Visible = No, so that the user does not know it is there, but it can be accessed by your VBA.
You can now use this code (which in this case updates 2 fields in the table) 
Dim rs As Object, SQL As String
SQL = "SELECT Inventory.* " & _
"FROM Inventory " & _
"WHERE TagNumber = '" & Me.TagNumber & "' "
Set rs = CurrentDb.OpenRecordset(SQL)
With rs
.Edit
!InStock = 0
!OutInField = -1
.Update
.Bookmark = .LastModified
End With
rs.Close
Set rs = Nothing

I am sure that you can suss out how to use that to do what you want. 
Replace the Table/Query name with yours and the Where statement to use your Record ID and away you go.


----------



## BobFoster (Dec 5, 2008)

Thanks OBP, I can honestly say it would have taken me a lot, lot longer without your help. 

I've got it working currently using the code I posted a couple of posts ago. I've looked at your suggested technique but I must admit, I'm a bit confused: I don't understand why creating a new Recordset from a SELECT every time the record changes is better than cloning the Recordset the form is bound to. I would have thought the Recordset is already in memory and therefore a clone of it is more likely to run quicker than a SQL query. Am I missing something? And that got me thinking... why clone the recordset at all? Since I am just copying the values out into the form fields, why not copy them from the original recordset instead of the clone? I tried this and it seems to work.

In fact, there are no navigation controls on the form and it is opened at a particular record from another form using DoCmd.OpenForm with a "where" filter to select the record, so the process is gone through actually only once per form load.

I say "once"; it is mildly interesting to note that when the form is opened like this, "On Current" is fired three times and the first time through there isn't a current record - Me.Recordset.Bookmark returns a "No Current Record" error and the ID field on the form contains nulls even though it is still bound to the table. I've used a test on Me.Recordset.AbosolutePosition (which seems to be -1 when there is no current record) to skip all the copying in this case.

Anyway - on this problem at least - I think I can make my way from here (unless you can tell me why the SQL query is better than the original Recordset or its clone). Thanks so much for all your help OBP - it's been very much appreciated!

Regards,
Bob


----------



## BobFoster (Dec 5, 2008)

Sorry OBP - I've just realised that you were answering a question I'd actually asked you: how do I create a recordset that only contains the record I'm interested in! I'd totally failed to spot the single-record nature of the recordset.

My apologies. And I was feeling chuffed with myself because of your compliment too!

In fact this technique will be doubly useful because I can make a more sophisticated query that will suit my needs a bit better.

Thanks,
Bob


----------



## OBP (Mar 8, 2005)

Bob, you are very good at this, I am sure that you you will have it working great in no time. 
Can you post a copy of the final Code and mark the thread Solved when you are finished.
Let me know if you need any other assistance, it has been great working with you. :up:


----------



## BobFoster (Dec 5, 2008)

For anyone in a similar position looking for my final solution, I've messed around with this a little bit over the last few days, but it seems to be working fine with the following code:


```
Private Sub Form_Current()
    If Me.Recordset.AbsolutePosition >= 0 Then       ' Stops difficulties of this event firing before a current record established

        Dim fld As Field
        Dim count As Integer
        
        For Each fld In Me.Recordset.Fields
            For count = 0 To Me.Controls.count - 1
                If fld.Name = Me.Controls(count).Name Then
                    Me.Controls(count).Value = fld.Value
                    Exit For
                End If
            Next
        Next
    
    End If
End Sub
```
You may note this is kind of the way I was trying to avoid doing it in the first place (i.e. using non-bound controls), but OBP pointed me to two time-savers:


Using a For Each loop to copy data into unbound fields (you have to have the same name for your controls as your fields, but if they were created bound, this is automatically the case).
Unbinding any bound records, but leaving the form itself bound to the table or query. This provides a ready made recordset from which you can copy the data (as opposed to the method discussed earlier in the thread, where a copy of the recordset or a new recordset with a single record was created).
The If statement at the start is there because the OnCurrent event seems to fire more than once when a new form is opened (I don't know whether it does this when a new record is selected for an already open form as my application doesn't allow this). The first time it is called, there is no current record defined in Me.Recordset. I would classify this as a bug in Access, given the nature of the event, but this workaround neatly sidesteps the problem

Thanks,
Bob


----------



## OBP (Mar 8, 2005)

Bob, what a lovely "Simple" piece of Code. :up: 
Is the Form in Continuous Mode, as that will cause the On Current Event to happen more than once?


----------

