Populate unbound form

[nhcaver]

Hello once again,

Ok, so I have a question (big surprise)...How do I populate an unbound form with a recordset from a table? I tried searching previous threads, but the majority of the posts were regarding either continuous forms, or populating lists, so any help here is greatly appreciated.

My form needs to be unbound since it needs to be populated with a record from the tblMain, the previous data displayed and edited. Then, when the "submit" button is clicked by the user two actions take place: The first action is that the updated record over-writes the previous record with the same Key, and then that same recordset is added to another table, and then the objects in the form are cleared in order to update the next record.

I realize that having the form based upon a query written in access is the easier way to go, but this level of control over what happens with the data is necessary.

 

[georgedwilkinson]

I know this isn't a direct answer to your question. There is a very distinct possibility that Access is not the correct tool for what you are trying to do. Once you part from the thing that Access is powerful at (bound forms) it becomes cumbersome to the point that it is easier to use some other programming language without all the overhead baggage related to bound forms.

 

[gemma-the-husky]

you see in general you probably would not do it that way in a RDBS

if you wanted to do that, what you would do is this, or some variation of it.

on entry, create a new record with the same data as the previous one - then edit the new data. have code to detect if you dont make any changes - if you dont then delete the new record. if you do, then create whatever associated records you need. effectively this method builds an archive of saved changes - its far easier it do it this way, than the way you proposed.

again however, you really ought to consider whether you need the associated records in the format you describe - it doesnt sound normalised. As soon as someone wants to do something in an anti-relational way, its a sure sign that the data isnt structured properly. most things become much easier to do with properly normalised data

 

[nhcaver]

you see in general you probably would not do it that way in a RDBS

if you wanted to do that, what you would do isthis, or sonme variation of it.

on entry, create a new record with the same data as the previous one - then edit the new data. have code to detect if you dont make any changes - if you dont then delete the new record. if you do, then create whatever associated records you need. effectively this method builds an archive of saved changes - its far easier it do it this way, than the way you proposed.

again however, you really ought to consider whether you need the associated records in the format you describe - it doesnt sound normalised. As soon as someone wants to do something in an anti-relational way, its a sure sign that the data isnt structured properly. most things become much easier to do with properly normalised data

Actually I think I should have stated it simpler instead of going into the bloody details of what I eventually wanted to get it to do. Actually, my main question is, after I build the recordset from the table, how do I get it into my unbound form?

<snickering at my own over exuberance>

I know how to stuff it into my tracking table already...its jsut getting the stuff out of there and into the objects on my form.

 

[denisc]

Did you want the records to be displayed in a 'continous' form?

AFAIK Access won't allow a 'continous' form to be unbound.

Look for a 'Grid' control, there's a very limited one available with access, tons of them available on the web.

Personally I use iGrid from
http://www.10tec.com/home/Products/ActiveX/iGrid/index.aspx
IMHO by far the best.

Regards
Denis

 

[LPurvis]

Hi all

I have to say, I don't hold to the "unbound? Use a platform other than Access" theory personally.
Certainly if you're primary data mentality is going to be unbound then you are creating a lot of work for yourself that Access is inherently designed to take.
But I know of developers who work within an "always unbound" practice and do so very well.

However having unbound forms as another tool when required is just one avenue of development in Access. For example hugely complicated forms can exist which display and perform various tasks. Going unbound makes total sense. That doesn't mean the rest of the application can't be bound, taking advantage of the RAD database abilities.

In this case it's standard unbound form with the twist of writing to more than one table. Not difficult to implement once you have the form loading.

One main Unbound example (and others in a way) in the page linked to in my sig below.

Cheers.

 

[nhcaver]

I know this isn't a direct answer to your question. There is a very distinct possibility that Access is not the correct tool for what you are trying to do. Once you part from the thing that Access is powerful at (bound forms) it becomes cumbersome to the point that it is easier to use some other programming language without all the overhead baggage related to bound forms.

Well, the explanation of why everything has to be done unbound is because I am running this with a server side and a client side with close to 500 employees accessing (no pun intended) the database. We've noticed that when you get more than 5 people or so in it (if not run server side/client side) that it has a tendency to lock up the database (and even then there are times that it will still lock up). I realize that there are other tools out there (are you kidding, I would love it if we switched to ORACLE) that would be better suited for our databases, however we have to work with what tools are available to us. So...it means bending Access to our will, and since I am the Junior most DB developer in our little group, I just raise the ambient temperature of our little corner of the office by a few more degrees (I relish the small day to day triumphs).

So, since the upcoming part of the DB will use a public function so it can be used in several forms (and thankfully do the same thing over and over again), I am going to attempt to write a class module, develop objApp and call various functions based off of the rs and see where that gets me (I probably used wrong terminology there, one of these days I'll get it).

BTW LPurvis...thanks!

 

[LPurvis]

Well, although an unbound ideology can offer more control and a little more efficiency - there's no inherent reason why 5 users would be causing problems unless there is another issue at play.
These could be many things from a dodgy network card, to an implemented practice such as loading forms with entire tables where updates are performed haphazzardly. (You'll appreciate that unbound forms can show only one row at a time - there's then little reason to have your bound forms perform differently. It's hardly fair to compare them unless you imlement your bound solutions almost exactly the same as you would an unbound one).

>> with close to 500 employees accessing (no pun intended) the database
How many of these are simultaneous?
You mention a pivot point of 5, but even if you performed a miracle - you'd never surpass 255 (the maximum number of connections). Going unbound would help you get closer to that - but you never would.
Most bound apps don't go over 20 simultaneous users - some talented developers can drag out more like 50 - but by Jet's very nature you're on shaky ground (since I presume this is Jet we're talking about as the backend not "Access" as it is so often referred).

Cheers.