Creating a customer profile - unbound form

[gi0rg0s]

Hi all,

I am building a customer profile using an unbound form and I just wanted to ask for advice on best practices for doing this. Specifically on the matters listed below:

1. Retrieving the data from the tables
   
   To do this I created a one line multiple fields recordset based on selected customer ID. An then assigned the values of the recordset to the relevant controls in the form. Is this the proper way to do this?
   
   
2. Handling fields with unknown amount of records
   
   For example I may want to list children or addresses. In these cases customers may have more than one records. I thought of presenting these in listboxes. To retrieve the data I will construct an SQL query and set the listbox rowsource to that query (through VBA).
   
   My question is whether this is inefficient. Does control.rowsource = SQLstr effectively open and close a recordset each time it is being used? Is there a better solution to get the data by opening only one recordset?

Any other tips are certainly welcome.
Thanks,
George

 

[MarkK]

1 Why not use a bound form? Set the RecordSource property of the form to your SQL statement, and set the ControlSource property of each control. Then opening the recordset and putting the values in the controls is all done automatically.

2 You can also use a subform to display the many side of a one-to-many relationship. Design the form to provide the many side of the relationship, and simply drop it on the main form in design view. With bound forms the links are made automatically.

hth
Mark

 

[gi0rg0s]

Thanks MarkK, but I would rather use unbound forms. Mainly for the following reasons.

1. I am new to both Access and programming and unbound forms are great for learning and developing problem solving skills
2. The database may need to be upgraded to an SQL server in the near future. Having unbound forms this transition will be a lot easier
3. I can't be sure of this as I am still a newbie, but I think you have better control and performance with unbound forms and code

I understand that using bound forms is easier, but since part of designing this is learning I will stick to unbound forms.

Best,
George

 

[isladogs]

In answer to your 3 points in last reply
1. Building your own house is a great way of becoming good at DIY but I'd recommend learning in small steps. Start with bound forms and only use unbound where absolutely necessary.
2. Not sure that your point about SQL server is true. Where's your evidence?
3. Not true

Apart from that I agree with everything else in your post..!

 

[ashleedawg]

I created a one line multiple fields recordset then assigned the values of the recordset to the relevant controls in the form. Is this the proper way to do this?

There's many potential "proper ways" to accomplish the same thing in Access, and different people will have different advice. (That can be awesome & annoying at the same time!) I figure, if it works, it's the proper way!

For example I may want to list children or addresses. In these cases customers may have more than one records.

So, one customerID could have multiple address records and multiple children records? Listboxes are my preference in that case, and I like the ways they can be customized, like double-clicking to add/edit etc.

To retrieve the data I will construct an SQL query and set the listbox rowsource to that query (through VBA). My question is whether this is inefficient. Does control.rowsource = SQLstr effectively open and close a recordset each time it is being used?

If I'm picturing this correctly, I figure that should be just fine. "On Open" of the record (each time it's changed) you would set the SQL for the listbox.rowsource (and requery where needed).

Re bound vs. unbound: if you're not bound to the source, remember that changes to and fields on your form will not be automatically saved. You'll need to check if anything's changed and take action appropriately.

Building your own house is a great way of becoming good at DIY but I'd recommend learning in small steps.

I agreed with ridders - might be a good idea to make sure you have a solid grasp on bound forms, what they are automatically doing for you, and how they do it while protecting your data structure, before you attempt to do it yourself.

The database may need to be upgraded to an SQL server in the near future. Having unbound forms this transition will be a lot easier

Not true, Access *is* running SQL already. I moved a sizable Access back-end onto a SQL-Server, and it was remarkably simple, basically just a matter of re-linking each source table.

I think you have better control and performance with unbound forms and code

Not true. Anything you can do with bound, you can do with unbound; some things may just have slightly different methods. Goes back to "many different proper ways to accomplish the same thing."

 

[MarkK]

An unbound form has no Datasheet view, NavigationButtons, RecordSelectors, and doesn't raise events: Dirty, BeforeUpdate, AfterUpdate, Current, to name a few. The Current event alone is a very useful tool to give up when you unbind, and what do you gain in its place?

A Listbox is primarily a selection tool. It is absolutely possible to use it to show the many side of a relationship in the same way it is absolutely possible to create an unbound data-intensive form. Feel free. Give it a go. But when you want a fully featured, event driven, design-time configurable, re-useable, fully interactive interface for your data, use a bound Form. If that form represents the many side of a relationship, use it as a subform.

I totally agree that paddling upstream is excellent for building strength, endurance, and river-reading skills. No question. But consider that paddling downstream is also excellent for building strength, endurance, and river-reading skills, AND paddling downstream is fun.


Mark

 

[gi0rg0s]

Lots of good comments here, thanks. Since everyone is recommending it, I will see how bound forms behave as well and choose accordingly.

Re my comment on better control and performance with unbound forms, I thought that each time they are loaded a recordset opens with the fields that are bound. Isn't than resource intensive? In addition doesn't that create locks on the records that are open? Records that the user may only be viewing and not intending to update.

This is why I was wondering what "Listbox.rowsource = SQLstr" does under the hood. It may be the exact same thing in terms of resources.

 

[missinglinq]

Using Unbound Forms really does away with the basic function of Access, which is to facilitate RAD (Rapid Application Development) and should only be attempted by very experienced Access developers, and then only when/if a legitimate purpose requires it, and most situations don’t! You don't need Unbound Forms to

1. Do Data Validation
2. Prevent Duplicate Records
3. Do Formatting of Data before it's Saved
4. Decide whether or not to actually Save a New or Edited Record

which are the most common reasons given. Nor are they needed for another dozen reasons I've seen people give!

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, using Unbound Forms, as it does when using Access and Bound Forms. That’s because with Bound Forms the Access Gnomes do the vast majority of the heavy lifting; with Unbound Forms the developer has to write code for everything...even the most mundane tasks!

Bottom line is…with Bound Forms you end up writing code for a few specialized situations, such as #1-#4, as listed above…and with Unbound Forms you have to write code for virtually everything that needs to be done!

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

Don't misunderstand me...there are a few, specialized situations, where an Unbound Form is preferable...but anyone who routinely uses them for everything, has simply made a bad choice in deciding to us MS Access.

Linq ;0)>

 

[isladogs]

Bound forms are not resource intensive.
They are using Access the way it was designed to be used

There are various locking options available.
The default type of record locking allows several users to view & if appropriate distinguish events so that they can appear to edit data at 'the same time'.

Forms can be based on a table, a query or a SQL statement.
All work equally well.

 

[isladogs]

Hi linq
You posted that at the same time as me
I've seen that post from you before and agree with every word.

Just out of interest when, in your view, are unbound forms preferable.

I have a few very complex bound forms for defining constants used throughout the application ....but they are a total pain to maintain