Results 1 to 6 of 6
  1. #1
    broecher is offline Advanced Beginner
    Windows XP Access 2003
    Join Date
    Sep 2010
    Posts
    38

    unbound vs bound in my form

    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!

  2. #2
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    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.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  3. #3
    broecher is offline Advanced Beginner
    Windows XP Access 2003
    Join Date
    Sep 2010
    Posts
    38
    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:

    Code:
    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.

  4. #4
    Missinglinq's Avatar
    Missinglinq is offline VIP
    Windows 7 64bit Access 2007
    Join Date
    May 2012
    Location
    Richmond (Virginia, not North Yorkshire!)
    Posts
    3,016
    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)>
    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  5. #5
    broecher is offline Advanced Beginner
    Windows XP Access 2003
    Join Date
    Sep 2010
    Posts
    38
    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:


    Code:
        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...

  6. #6
    pkstormy's Avatar
    pkstormy is offline Access/SQL Server Expert
    Windows XP Access 2003
    Join Date
    Mar 2010
    Location
    Madison
    Posts
    682
    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.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Unbound textbox in bound form
    By Evilferret in forum Forms
    Replies: 5
    Last Post: 08-15-2012, 01:26 PM
  2. Replies: 8
    Last Post: 08-02-2012, 10:50 AM
  3. Concatenating (2x unbound into bound text box)
    By justinwright in forum Programming
    Replies: 1
    Last Post: 08-19-2010, 08:11 AM
  4. Replies: 0
    Last Post: 05-09-2010, 08:43 AM
  5. Link unbound form to bound form
    By Papote in forum Forms
    Replies: 0
    Last Post: 09-25-2008, 07:42 PM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums