Check if a form has changed since it was last saved - 17318

I am developing an application using an ms access front end and a
visual foxpro 8 backend. The application uses adodb connections to add/
update/edit data in the back end database.  When a user clicks the
save button on main data capture form the user input is
programmatically inserted into a new record or an existing record is
updated by creating SQL INSERT INTO or UPDATE statements on the fly.

I was wondering if anyone knew of an efficient way of coding a
mechanism to check if the form had changed since the save button was
last pressed.  I guess this can be achieved by capturing the On Change
event of each control on the form but I was hoping there was an easier
way?? (50+ controls on form) I have tried the forms After Update event
but this only seems to occur if the form is bound directly to a
recordset.

I am self taught in vba for you'll have to excuse me if this is a
stupid question.

Thanks

Mike

p.s I have just realised by the time I have written this I could have
copied and pasted some code behind the On Change event of each
control....maybe the answer to this question save someone else some
work!?

0
bunter
9/27/2007 6:49:01 PM
access.formscoding 7493 articles. 0 followers. Follow

4 Replies
643 Views

Similar Articles

[PageSpeed] 7

<bunter@msn.com> wrote in message 
news:1190918941.640453.16270@d55g2000hsg.googlegroups.com...
>I am developing an application using an ms access front end and a
> visual foxpro 8 backend. The application uses adodb connections to add/
> update/edit data in the back end database.  When a user clicks the
> save button on main data capture form the user input is
> programmatically inserted into a new record or an existing record is
> updated by creating SQL INSERT INTO or UPDATE statements on the fly.

The above tells me you using the wrong tool. The ms-access object model
REALLY REALLY REALLY falls down when you using un-bound forms. You
would be MUCH better to use vb here, as it has TONS of wizards, and
data controls that allow you to build forms with a data connections.

in ms-access, you don't have all those cool wizards and development aids
because you DO NOT need them when you use bounds forms.

The fact that your asking for a way to know if the form been updated shows 
exactly this point.

if you use a bound form, then the dirty flag will be set = true.

In code, you can simply go:

if me.Dirty = True then
...... form been updated...

> I guess this can be achieved by capturing the On Change
> event of each control on the form

No, even if using bound forms, you want to use the controls after update 
event. The on-change fires for every single keypress, and you don't know at 
that point the user actually changed the value. In fact, they might go 
edit->undo. You REALLY want to use the after update event of the control, 
since it will ONLY fire when value is actually changed and the value is 
going to committed to the table (however, you can still do verification, and 
these values are NOT yet written to the table). So, use after update, and it 
will ONLY fire when the user is done editing the control. On-change the 
WRONG event. (and, father it means you likely have to use the .text property 
of a control, and again, you don't want to do that either....use the .value 
property of controls).

> but I was hoping there was an easier
> way??

Well, is there any reason why you can't using linked tables to the back end? 
I would just link the form to the tables direct, and you done!!! It would 
take zero code here. If you do use a linked odbc table, and bind the form to 
that table, then you not need to write ONE line of code,or bother checking 
if a control had its value changed.

>
> I am self taught in vba for you'll have to excuse me if this is a
> stupid question.

No, not stupid question at all. However, you can't cut steak with a fork. 
You can eat the steak, but the approach is just wrong.

Ms-access is very much the wrong tool, and thus there not a trivial, or 
simple answer to your approach. You CAN cut a steak with a fork, but usually 
really a bad process....

You can avoid having to put in code for each controls after update event by 
using a public function, and placing it in the actual property sheet for the 
control (and, if you highlight all 50 controls,...you only have to type the 
function name once -- however, this kind advice I given you is really only 
giving you more rope in which you hang yourself around the un-bound form 
apporach that don't work well at all in ms-access).


-- 
Albert D. Kallal    (Access MVP)
Edmonton, Alberta Canada
pleaseNOOSpamKallal@msn.com 


0
Albert
9/28/2007 12:04:19 AM
> The above tells me you using the wrong tool. The ms-access object model
> REALLY REALLY REALLY falls down when you using un-bound forms. You
> would be MUCH better to use vb here, as it has TONS of wizards, and
> data controls that allow you to build forms with a data connections.

> in ms-access, you don't have all those cool wizards and development aids
> because you DO NOT need them when you use bounds forms.
>
> The fact that your asking for a way to know if the form been updated shows
> exactly this point.
>
> if you use a bound form, then the dirty flag will be set = true.
>
> In code, you can simply go:
>
> if me.Dirty = True then
> ..... form been updated...

I totally agree this is not an ideal solution, unfortunately the
organisation I work for will not allow me to develop applications on
anything other than the Microsoft office type environment.  The
application I am developing is to replace the current database which
is ms access front end and back end using link tables  - this database
is extremely slow even and unstable though I think it is quite well
designed and is compacted regularly.  It seems access is unable to
support the 30-50 users this application will be used co-currently by,
having used Fox Pro databases in the past I am more confident of its
ability to hold up with this many users (many more than the database
was originally intended)

In an ideal world I would have Web based application served by a SQL
server, however what I can only describe as "red tape" means this
would have to be done by external suppliers who have an awful habit of
charging significant amounts of money!

I am really using the development as an opportunity to demonstrate
what is possible, I am hoping that the organisation will realise the
benefits and will agree to fund a scalable/secure/complete solution.

Happy to take any board any suggestions for alternative development
tools, given the current constraints.

> > I guess this can be achieved by capturing the On Change
> > event of each control on the form
>
> No, even if using bound forms, you want to use the controls after update
> event. The on-change fires for every single keypress, and you don't know at
> that point the user actually changed the value. In fact, they might go
> edit->undo. You REALLY want to use the after update event of the control,
> since it will ONLY fire when value is actually changed and the value is
> going to committed to the table (however, you can still do verification, and
> these values are NOT yet written to the table). So, use after update, and it
> will ONLY fire when the user is done editing the control. On-change the
> WRONG event. (and, father it means you likely have to use the .text property
> of a control, and again, you don't want to do that either....use the .value
> property of controls).

Understood, thanks

> > but I was hoping there was an easier
> > way??
>
> Well, is there any reason why you can't using linked tables to the back end?
> I would just link the form to the tables direct, and you done!!! It would
> take zero code here. If you do use a linked odbc table, and bind the form to
> that table, then you not need to write ONE line of code,or bother checking
> if a control had its value changed.

There were two reasons I did not consider odbc:

1) As I understood it the visual foxpro odbc drivers have not been
updated since foxpro 6, version 8 contains new features which the obdc
driver does not recognise (such as auto-increment).  If anyone know
differently or knows of a driver that will work with vfp 8 features
please let me know.

2) In terms of performance I always thought that using adodb
connections would be more efficient than odbc connections.  Not quite
sure why I thought this....How does adodb compare with odbc in terms
of performance?

Many thanks for taking the time to respond, your help is much
appreciated.

Regards

Mike


0
mikeh
9/28/2007 7:03:30 PM
> support the 30-50 users this application will be used co-currently by,
> having used Fox Pro databases in the past I am more confident of its
> ability to hold up with this many users (many more than the database
> was originally intended)

I *really* doubt that dropping native access to a mdb back end
and going to a fox back end is going to be more reliable. You
adding a complex ADO + fox driver "software" layer here that
you did not have before. and, ms-access is still going to be
loading the native "JET" data engine it needs anyway.

So, you adding layers of software here.

If anything, you wind up with a LESS stable
system. Now, your system will have to open table, read table (perhaps
close table at this point). Put data into form. After we edit, we
then take data from form, re-open table, re-execute sql to write back
to that table with sql updates. You more then doubled the steps and
work then just letting access update the table direct like it does
now.

Further, this approach could wind up using MORE bandwidth then
letting ms-access do a NATIVE update to a mdb file. Remember, in the case of
letting ms-access do the update, there is NO odbc, no
extra ADO layer, and I am quite sure that NO sql statements need be
parsed to make the update.  Note that just to parse the sql, the sql
has to be *checked* against legal fields in the back end.

So, you likely use LESS bandwidth by leaving the tables open and
using native mdb file for the back end. All of your open/close,
read/load data into form, and then write back out + open/close
connection stuff can actually cause MORE bandwidth to be used
then just letting ms-access update a record it just read
into a form.

Ms-access is able to raw update that table, and,
access is NOT using odbc to connect to the
back end...it a native update system.

I do not see ANY reasonable argument here that moving the back end
to a Fox db, adding additional layers of code and ado.
I don't think you gain anything here. And, the argument that a
Fox back end is going to be more stable is weak.
(all those who developed in FoxPro, raise
your had if you ever had a corrupted index (everyone in the room will
raise their hand)).

So, I really doubt you going to gain additional
stability, and I doubt you gain ANY performance here. The ONLY reason
why you might gain performance is because your FORCING your design
to only load the one record to the form, and you should do that
in ms-access anyway.

With bound tables in ms-access, you don't need to write any update
code, and you seem VERY sensitive to time + cost issues here, and yet
are using up large amounts of developer time to write a bunch of update
code? You could use that time to migrate to sql server.

You not going to gain any more stability or performance here in my IMHO.

> In an ideal world I would have Web based application served by a SQL
> server, however what I can only describe as "red tape" means this
> would have to be done by external suppliers who have an awful habit of
> charging significant amounts of money!

You do realize there is at least 2, perhaps 4 free versions of sql server
available from Microsoft? The time and cost to re-write this stuff
to use ado, fox is likely the same for migration to sql server.

>
> Happy to take any board any suggestions for alternative development
> tools, given the current constraints.

I would first check if your existing application is correctly setup.

As long as you do the following, you should get good performance, and
stability:


1) split the databae (that likely a given on your part)

2) always distribute a mde to users.

3) NEVER open up a form bound direct to a table unless you use a where 
clause.
   (so, bound forms are ok, but you MUST restrict the records loaded. With
a small table of 100,000 records, the time to load the form will be same for
a table of 1 record *if* you respect this approach. Here is quick example of
a typical way to "restrict" records (eg: ask the user BEFORE you load the 
form

http://www.members.shaw.ca/AlbertKallal/Search/index.html

This also means that as a general rule you not need, nor allow the 
navigation buttons
in he form because you load only one record.

Further, once you split the database, and reduced the number of records that 
flow to a form, then you 90% of the way to having a application is VERY 
close to a good setup for running the data from sql server. So, assuming you 
do eventually get sql server, then you simply move your back end data from 
the mdb file to sql server, and then link your front end tables to sql 
server. At this point, 90%, or more of your code and forms will work "as is" 
WITHOUT having to write ANY code. so, you simply tweak that last 10% to work 
with sql server...

> There were two reasons I did not consider odbc:
>
> 1) As I understood it the visual foxpro odbc drivers have not been
> updated since foxpro 6, version 8 contains new features which the obdc
> driver does not recognise (such as auto-increment).  If anyone know
> differently or knows of a driver that will work with vfp 8 features
> please let me know.

I not heard the above. I would assume that the latest drivers for
fox would support the basic functions you speak of. Further, any
reason why you can't used a supported version of fox for the back
end.


>
> 2) In terms of performance I always thought that using adodb
> connections would be more efficient than odbc connections.  Not quite
> sure why I thought this....How does adodb compare with odbc in terms
> of performance?

In general, there not much difference. This is *especially* so when using
a file share, and NOT a server (and, that is our case until you spring
for sql server). However, with ms-access, you not using
a odbc layer, you not using a ADO layer, you using native JET to the mdb.
Now, your proposing to throw in ado layer that you did have before, and
also the FoxPro driver which also has to be loaded and run in addition to
ms-access. Lots and lots of more
layers of software you adding here.

I don't think you gain a thing in terms of stability, or performance
if you have a good desing now...


-- 
Albert D. Kallal    (Access MVP)
Edmonton, Alberta Canada
pleaseNOOSpamKallal@msn.com 


0
Albert
9/29/2007 8:26:11 PM
I going to add one more comment:

Looking to run 40, or 50 is pushing ms-access.

There are reports that when everything is JUST right, some have manage to 
push ms-access to 100 users, but that is in a absolute "ideal" case. And, it 
was in a era when we did not load up pc's with a zillion other applications.

And, it should be pointed out that even Microsoft rated the JET database 
engine for 50 users (but, that was *before* they had sql server in their 
stable (and, before Unicode was used in JET).

Ms-access can be run with 50 users, but I think 50 is really pushing it....

30 users? Hum, sure ok, but only if you have a VERY stable environment (no 
network problems, and NO pc's that freeze or get rebooted by users).

And, yes, I do think the FoxPro database format is somewhat more robust then 
is ms-access (but, not by much, and it not going to get you a lot more users 
IMHO).

Given that versions of sql server are available for free that can easily 
handle 50 users, I don't see the cost benefit to try and use a file share 
with so many users.


-- 
Albert D. Kallal    (Access MVP)
Edmonton, Alberta Canada
pleaseNOOSpamKallal@msn.com


0
Albert
9/30/2007 3:10:16 AM
Reply:

Similar Artilces:

Stale dated checks
We have a number of checks issued in 2007 to various vendors that were never cashed. What is the proper way to handle this so that they do not remain Outstanding Transactions in Bank Reconciliation? Should we void the check in the Payables module and then enter a credit memo to apply to the invoices the check was originally applied to? Or, should you just clear them in Bank Recon and make a reconciling adjustment to balance to the bank statement? Any suggestions would be appreciated. -- Rosalyn Rosalyn, I would do exactly as you suggested - void the checks and enter credit memos ...

Paste Special Changes Content
Greetings - I am attempting to copy and link, via Past Special, a group of rows within the same workbook. When I do, all the empty cells are populated with zeros. I am using Windows 200 and Office XP. Thanks in advance, Bill When you paste special|Links, you end up with a formula like: =x99 If x99 is empty, then excel will show a 0. But you can change your formula to look like this: =if(x99="","",x99) (if x99 is empty, then make the cell look empty, else just show what's in x99.) Bill wrote: > > Greetings - I am attempting to copy and link, via Pas...

format changes when copying from one cell to another
I'm sure this is simple, but it is not clear to me... I am copying the contents of a group of cells that exist on one worksheet to another. In Sheet2, I tell cell A1 that it should equal Sheet1's A1 value. That works fine. But if the cell is blank on Sheet1, it comes up as "0" on Sheet2. I figured it was probably assuming numeric formatting, but when I right-click > format cell on both Sheet1 and Sheet2, both show up as "general", so why would Sheet2 take a blank value and populate it with "0" ?? I gather than you are putting a formula in one...

when i save a file that i have worked on, excel7 closes down work.
i have recently purchased office pro 2007. Excel 2007, when i save a filethat i have worked on, excel closes down and work is lost, done all updates, re installed from disc, please help i have lost 2 hours of work this moring that i need for a report a few questions: 1. what happens with save as? 2. what happens in safe mode? (hold shift key while starting excel) 3. do you have add-ins? 4. what operating system? x64? -- Gary Keramidas Excel 2003 "rob" <rob@discussions.microsoft.com> wrote in message news:0483F09B-3289-42B4-8849-0C0A61380B11@mic...

How to change .pst file location on hard drive(s)?
I want to move my .PST file c/w archives to a partition other than C:\. How can I do this with Win Live Mail? Thanks, John. "Machinist60" <e_john_wilson.no.spam.@hotmail.com> wrote in message news:hj5s5l$q8r$1@news.eternal-september.org... > I want to move my .PST file c/w archives to a partition other than C:\. > How can I do this with Win Live Mail? > > Thanks, John. I didn't realize WLM utilized the .pst file. I had to convert my .pst file data into the Windows Mail format and then import the data into WLM. Please explain what you...

Just saved file, cannot be found. ?
Even Global search ? Win 7 ... Whatz up? if you saved it, it should be in the mru list? what version of excel are you using? -- Gary Keramidas Excel 2003 "Boswell" <Boswell@discussions.microsoft.com> wrote in message news:E9D6ED74-ABC5-469D-9572-2D8AE93F36CD@microsoft.com... > Even Global search ? > > Win 7 ... > > Whatz up? one other question, how did you open it, outlook email? if that's the case, search will never find it. it is probably here: C:\Users\Your user name\AppData\Local\Microsoft\Windows\Temporary Intern...

Transferring Field from Existing Table/limitations and change of d
Thank you in advance for your help! I have two Excel spreadsheets that I successfully imported into Access 2003 and created tables for. I need to add the field from one table to the other, but there is not a direct match in the relationship. The large table uses the Employee ID as the primary key. The smaller table contains one field that lists a subset of these Employee ID numbers (a selection of certain employees). I need to transfer this field to the larger table, but I do not know how to tell Access to match up the corresponding numbers (i.e., the large table lists all employees, bu...

no change pivot item.
i want to can not change pivotitems in the pivotfields. namely pivotitems restircted. How can I do in VBA? *** Sent via Developersdex http://www.developersdex.com *** ...

Data changes when Analyzed with Excel
I have a query with 3 tables that represent master item list, count qty and as qty. When I look at the results in Query, all is well and accurate yet when I select Analyze with Excel, values change. For example, if a record shows zero qty in the as of field and zero quantity in the count field, I get a value in one or both of the fields that origianlly were zero. Other than linking the tables and grouping by master item list (to show all parts regardless of qty's), there are no formulas or expressions in this query. One of the tables is linked to a FoxPro table via ODBC driver. ...

Change Source Campaign on Invoice and Order
I need to change the Source Campaign on an Invoice and an Order but the field is locked. I tried to enable the field on the form but still cannot make changes. Any ideas beyond direct edit of DB? ...

Excel 2000/Change Default Opening Location?
I'm using Excel 2000. When I open Excel, and the click on the Open button to open worksheet, it goes to a default location I'd like to change. How can I change that default location? I have searched under Tools > Options, but find no means there. Thanks. -- ---------- CWLee Former slayer of dragons; practice now limited to sacred cows. Believing we should hire for quality, not quotas, and promote for performance, not preferences. Tools|Options|General Tab change the entry in the Default File Location box CWLee wrote: > > I'm using Excel 2000. When I open...

Radio Button on opening the form
Hello: I have know how to open the form using command button, but now I want to use Radio Option button to open the form consists of: 1. Show all supplier invoices 2. Show only outstanding invoices 3. Show only paid invoices I have created 3 types of Form using 3 types of query, and now I want to open it by using radio button with the button OK and cancel to open the form. Is there any website providing the sample of Radio button to opent the form. I want to studdy how to write the VBA for that. Thanks in advance. Frank You sure you want the form to open when the radio button is sel...

POP server always changes to localhost
Hello, My outlook is having a wierd issue. I have three POP3 accounts set up to check email in Outlook. After some point in time, all three will stop functioning because in the E-mail accounts settings dialog my incoming mail server will get changed to localhost, and it fails to log in to box to pull my mail. It appends the POP3 server name to my user name which seems odd. So, to summarize, my Incoming mail server (POP3) field changes from it's usual value (something like mail.server.com) to localhost, and my mail server gets appended to my user name (something like username...

Pop-Up Subform not linking to Main Form
I have a subform - frmLawEnforcement - that is accessed on the main form - frmCorporateSecurity - by clicking on a command button from the main form. The two forms are linked by the CaseIDNumber field. If I place the subform as just an entry from with the main form, the information shows as linking by the CaseIDNumber. When I enter the information into the subform using the command button, the information does not link to the case number. I am not sure what I could be doing wrong. I am an intermediate Access user but am fairly limited on VBA. ABradley, It would help...

Custom Forms
Hello, A fellow administrator recently installed a custom form on our 2003 exchange server. One thing we noticed is that within Outlook 2003 the new form works just great, but we don't see it when logging into exchange via the web interface. Is there a way to get the custom for to be seen there? thanks! Sean no. 6-bit or 32-bit eforms won't display in OWA That isn't to say that you can't customize OWA, nor am I saying that you can't build all sorts of powerful apps on top of the Exchange store (i.e. non-mapi top level hierarchies and custom app-specific schemas,et...

Custom Forms 11-04-04
Hi All, When customizing a form in CRM 1.2 I cant find the way to edit the part of the forms that appear when you save a newly opened case. ie. When you save a new case it goes from having just the "information" button on the left hand side to having the activites and notes buttons. I'd like to be able to customise those areas. Is this possible through the CRM interface? If not how do you do it? Thanks All Tom You can add tabs to the left part of the screen via the ISV.Config file. For more information on that, refere to the SDK online. Matt Parks MVP - Microsoft CRM ---...

Multiple Filters in a Form
Hi, I need some help. I have a form based on a query which lists wine in a cellar. I want to allow the users to filter the content on the form by using multiple filters. The following code works well. Private Sub CmdApplyFilter_Click() Dim strWhere As String strWhere = "" Dim strNonZero As String Dim strType As String Dim strYear As String strNonZero = "" strType = "" strYear = "" ' from a check box to show either only current wine in cellar or all wine included that which has been drunk - no bottles in cellar If CheckNonZero = True Then ...

Checking mails with MS Outlook closed?!
Hi, is it possible to have MS Outlook notify me of new accounts in a minimized status. i.e. i have a low memory and i don't want extra programs to be opened on my computer. i want to have outlook closed still i want it to to checkup my imcoming mails and provided i should have new mails it would popup and notify me accordingly. is that possible in outlook? if not, is there any program that could do that? regards, eqbal Outlook can't do anything if it's not opened. Is the mailbox you're checking a POP3 mailbox? If so, there are a number of very small programs that c...

Change bars color
Hello, I have a dinamic chart to show the cash flow position, a scrool bar chagne the data to the past and to the future (estimate), what I am looking for is, to have an easy identification of the estimated period I would like to chage the color of the bar. I am using an Stacked Column chart with two variables. any Ideias? -- regards from Brazil Thanks in advance for your feedback. Marcelo What I usually do when I want to show a column chart with one color within a certain range and another color elsewhere, is create two series, one each color, and use formulas to change on series ...

FORM Calculated Field
I am trying to attempt a simple percentage calculation to provide our sales team for the Opportunity Entity. I have 3 fields 1. estimatedvalue (Est. Revenue)- money 2. closeprobability (Probability) - int 3. dsi_weightedrevenue (Weighted Revenue) - int I need the following calculation to populate my Weighted Revenue (int) field as a percent estimatedvalue X closeprobability = dsi_weightedrevenue I have tried the following script adding it to the OnLoad event of the form being sure to select the "Event is enabled" check box there as well. But after publishing I get nothing but a...

Auditing form on record change
HI, I have a form with multiple fields that I want to track changes to. I was able to create a auditing trail when users click on particular buttons however if they dont click on the buttons the auditing does not occur. What I am wanting to do is to run the audit when a user changes to a new record. I have tried the "on current" and "before update" but they didnt work. Any suggestions? Take a look at what Allen Browne has at http://www.allenbrowne.com/AppAudit.html -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no private e-mails, please) "...

Opens to Personel Folder by default...How to Change?
I cant seem to find a setting anywhere that will let me change Outlook 2003 to open right up to the Inbox. It keeps going to the Personel Folder....Even right after a clean install....Anyone I know that uses Outlook never had to set anything and it always went straight to the Inbox. Any one know how to change this?? THANKS! Darren, Outlook toolbar: Tools/Options/Further/extended options/Folder with program start Do you search this setting? -- Oliver Vukovics Outlook Network without exchange: Public OutLook Share your Outlook PST with several users! http://www.outlookstore.com &...

How can I change order of pages in a fold brochure in publisher
When I work on the pages on the screen it shows pages 2 & 3 and the page preview shows 2 & 3 but when I go to print page 3 comes out as page 5. Is your publication setup as a booklet? When you go to print are you checking current page or typing the page number? What version Publisher are you using? "JudieM" wrote: > When I work on the pages on the screen it shows pages 2 & 3 and the page > preview shows 2 & 3 but when I go to print page 3 comes out as page 5. "Mary Sauer" wrote: > Is your publication setup as a booklet? When you go to prin...

check for workstation availability on local network
Hi can any one tell me how can I check for availability of different computers on my local network? I want to have my MFC application to monitor my home network to check which pc's are turned on and connected to network.please guide me to classes which can help. thanks alot Don't expect to find predefined classes for this. There are a lot of network APIs, however, look for WNetOpenEnum and associated APIs. joe On Wed, 22 Oct 2008 22:28:23 +0330, "HASAN" <hasan.musavi@gmail.com> wrote: >Hi >can any one tell me how can I check for availability of diffe...

saving/moving outlook files
I need to re-format my hard drive and re-install my OS after being infected with a trojan. How do I download my Outlook files to a disk and then move them back to my hard drive? ...