unbound vs bound in my form

[broecher]

Hi,

I am making a data entry form and it has a about 50 unbound boxes. I have a lot of combo boxes, validations, and a couple that are not added to the table but go through a calculation and the result is saved to the table.

As I was coding the save button I realized it is a lot of work, and maybe I should have used a wizard to make a the whole form with all the boxes bound.

It seems like I have more control over what happens if everything is unbound and only saved at the end using vba. I don't want partial records to get saved, and I don't

When I press save, I want to check another table and do some inserts or updates, and msgbox if needed before the data in the form goes anywhere.

It seemed like it was easy to copy and paste similar controls rather than fixing everything the wizard did.

A lot of the fields were optional and my insert commands were not working without everything being entered in every box on the form so I resorted to first INSERTing the the required fields and then UPDATEing the optional fields WHERE the newly inserted unique data is - one field at a time.

So my question is should I just start over with a wizard form and bound boxes, and then make my adjustments or should I continue with what I have started? Is a form that works like this very common? Is it bad to have a lot of UPDATEs running one after another?



Thanks for any advice!

[pbaldy]

There are some general thoughts here:

http://www.baldyweb.com/BoundUnbound.htm

The inserting/updating sounds odd and is probably pretty unusual. Typically I would have code behind the save button that validated the user entry and only proceeded if all required fields had been entered. Obviously I don't know your situation, but I'd be more likely to save/close the record and let the user come back to it if they need to add something later. Bottom line I guess is that if what you have is working, I'd probably stick with it.

[broecher]

wow! nice to know others struggle with this question too. Good article - looks like it was written by you??

So if the insert and update sounds funny, what would one normally do when they wanted to:

CurrentDb.Execute "INSERT INTO tbl_1 (field1,field2,field3.......field20) VALUES (" & Me.field1 & ", #" & Me.field2 & "#.......Me.field20 & ")"

and some of the fields can be empty? I get an error something about can't insert null.

[Missinglinq]

A big part of the reason to use Access for database development is the speed with which it can be created, using Bound Forms. Several developers I know, experienced in Visual Basic database development and Access development, estimate that development, using Unbound Forms, by highly experienced developers, takes two to three times as long as it does when using Access and Bound Forms.

There is nothing that you've mentioned you wanted to do that cannot be done using Bound Forms! Nothing! You're simply increasing your development time without gaining anything!

If you insist on using Unbound Forms, you'd be far better off using a straight VB or C++ front end with a SQL Server or Oracle back end.

• You can create an EXE file which gives total protection to your code/design
  
• You can distribute the db to PCs without a copy of Access being on board
  
• Your data security is far, far better than anything you can do in Access

So, once again, Why?

Linq ;0)>

[broecher]

Yes I am seeing how long it takes....

For now, I guess I will stick with unbound since I am already into it so far. I scrapped the UPDATEs and just have one INSERT with variables that contain all the possible fields, only if not null. Here is what I am trying right now:

    If Not IsNull(Me.LocationDescription) Then
        FieldList = FieldList & ", LocationDescription"
        ValuesList = ValuesList & ", '" & Me.LocationDescription & "'"   'text
    End If

    CurrentDb.Execute "INSERT INTO Captures (" & FieldList & ") VALUES (" & ValuesList & ")"

Another reason that I am sticking with it is that I have had bound forms in the past that I didn't completely understand everything Access was doing in every situation.... I feel like there will be no surprises this way.....

Also if I like the way this turns out, I may write a script that will write the vba for me...

[pkstormy]

I typically use a combination of bound and unbound forms. A lot depends on how often the form will most likely be left open by the user, how fast of a connection does the slowest user have, and how much data will be stored/updated in the underlying table. The biggest thing I have to make sure I do is send a refresh command to the opened form when using a bound form after I've updated certain fields in the same table behind the scenes. A totally unbound form gives great performance for slow connection type users but keep in mind that using any comboboxes or listboxes (unless programmatically coded) are often bound to a recordset and slow down performance with each one added onto the form. Since I never open a form based on the entire recordset (ie. I use the "where" parameter), this helps performance of the form. A good example is to create a form with all unbound text boxes (and not bound to a recordset) and watch how fast it opens. Then throw in a few comboboxes based on large recordsets and notice the performance drop. Then add in a recordset returning all records (preferably one with over 100,000 records), making all the fields bound. Then limit that to open the form based on a single record. Then repeat and test each of these scenarios using the same connection type the slowest user will have.

Otherwise, when using unbound techniques, I write some simple vba functions to "get" the data (ie. to the form) or "write" the data (from the form to the linked table) where I simply pass the autonumber value (I call it PersonID) to the function. A scheduling type form where there may be 10-20 users in it at 4 hours a time would be an example where I might make most of the form unbound. A demographics type form where they edit a single patients record and are then done I might make as a bound form. How you design the entire application using the right combination where needed is a key factor on the application being user-friendly for the users and problematic free.