Do not save form/subform data. Can it be done? 3

[Stevehewitt]

Hey,

I have looked this up on Google Groups as well as the Advanced Search tool on TT but nothing that seems to work.

Basically I need to find a line of code that ensures that the data entered into the form is not saved.

I have a form and subform joined using a order number. At the moment if the payment terms or gross profit percent are below a specificed amount then a supervisor password is required. The password is asked for at the 'save' button. If the data is OK (Terms are 30 days or under and the GP% is above the set level) then the record is saved with a popup. If the data has not met the criteria then a input box requests a password. If incorrect then the data is still not saved. If its correct then its saved with a message box.

I also have a close buttton where if the record has not been saved then its not recorded. (So either it must meet the sales criteria or have a password).

Sorry for the lengthy post! Can anyone help?

Cheers,


Steve.

 

[Rolliee]

Steve,

I know of only one way to do this. Make your form 'unbound' If could be just like another bound form but to unbind, do an Edit Copy Paste and make a new form. Remove the Control Source( read the tie to the table) and
change the name of the control just a little. I add txt in front of it. Then when you are ready to save the data in the record, you simply do so with a cmd button and

add the data to the basic tabale(s).

rollie@bwsys.net

 

[Stevehewitt]

Crikey!

Thanks for the reply. That is quite shocking. So there is no way to stop Access from saving forms by default when they are closed other than to remove the links in the form/subform? That is madness!!!

Cheers for the reply again. Anyone else know of another way or a way around this problem?

Thanks,


Steve.

 

[steve02124]

Many possibilities:

1) In the beforeupdate event in the subform, check for the values you need to validate with code in an if statement and set the Cancel property to True:

Cancel = True

Cancel is a variable passed into the function. You can see this when you open the beforeupdate event.

2) You could use the before update for the last control on the form to validate all the info you need to check. If it isn't right you could use sendkeys to programatically hit the escape key twice and that will cancel out everything that had been typed in the record:

SendKeys "{esc}{esc}"

3) If there is alot of typing to enter the record, I'd probably recommend, however, that you go ahead and let the user save the record with a MsgBox popup that there is a problem and add a yes/no type field to the table called RequiresSupervisorAuthorization. When the code detects that there is a problem, set the RequiresSupervisorAuthorization to true for the record. I would then have the code email a supervisor that there is a problem. I might have a report that I could run every day to see which employees and records were problematic and go in a review with employee and fix them. Just makes it a little better for the user to not have to re-enter everything. Users who like applications are more likely to continue to use them. Just make sure that any systems that might generate bills or shipping labels have a criteria that RequiresSupervisorsAuthorization must be false.

 

[Stevehewitt]

Hey,


Thank You. I'm going to play with the SendKey idea. I'm not sure if it will work as there is a subform on the form, so the main form will be saved immediatly if I remember correctly.

I'll post back ASAP.

Thanks again,


Steve.

 

[JeremyNYC]

I don't know if sendKeys is still buggy in later version of Access, but in 97 it definitely was. I've grabbed code from Dev Ashish's site to replace the SendKeys command and it works a treat.

On the other hand, you could use me.undo instead of the two escapes.

But the problem with this method is that there are so many ways for the user to save the record before this point:
ctrl s
hit the record selector (grey bar to the left of the form)
menu
move to a new record.

The previous two posters hit on the only two ways I could imagine doing this for a client of mine: use an unbound form (quite powerful, you're in full control, takes a bit of work) or use a flag field and treat some records different from others.

Jeremy

==
Jeremy Wallace
AlphaBet City Dataworks

http://www.ABCDataworks.com

Access Databases for Non-Profit Organizations

Please post in the appropriate forum with a descriptive subject; code and SQL, if referenced; and expected results. See thread181-473997 for more pointers.

 

[Rolliee]

I might ask why one would want to put data in a table only to take it out. Why package soomething only to unpackage it. When you enter one field in a new rec on a form, it is written to table - then.

You can play with the undo command but it is flackey.


Rollie E

 

[JeremyNYC]

Rollie,

In what way is the "Undo" command flaky? I've not used it much, but I've never had, nor heard, any problems with it.

Jeremy

==
Jeremy Wallace
AlphaBet City Dataworks

http://www.ABCDataworks.com

Access Databases for Non-Profit Organizations

Please post in the appropriate forum with a descriptive subject; code and SQL, if referenced; and expected results. See thread181-473997 for more pointers.

 

[Stevehewitt]

Hey,

Thanks for the replies. They are really apreciated. I have been playing with the SendKeys as its the only one I really understand.

Rollie
The data should only be saved if the data meets the criteria I have set in VBA Code. If It doesn't meet it then you must have password. Password is correct it will save. If not then the user should not be able to save when they click on the 'close' button.

All
SendKeys seem to work fine, but the still saves when the user goes from the masterform to the subform. :-(

Anyone know of a good starting point for unbound forms?! I haven't really got a clue! Sorry.

Thank You,


Steve.

 

[JeremyNYC]

Steve,

If you're going to get into unbound forms, which sounds appropriate to me, I'd _strongly_ suggest you run out and buy the Access [version number] Developer's Handbook. It's expensive and thick, but it will pay you back for both your time and your money in very short order. It's simply unbeatable, as far as I'm concerned.

Short of that, you'll have to dig through the help files a good bit, concentrating on recordsets. I'd suggest using DAO not ADO. Check into help for those two as well.

The basic idea is that you make a recordset of the data for the record. A recordset is a huge variable that holds data for each of the fields you need. Then you fill the controls on the form from that. If you want to save the data, you can either create another recordset and update it with the data from the form or, more simply, create an SQL statement that will update each appropriate table and execute that (look for execute in the help files).

It will be a bit of work, but it's a very powerful tool that you'll have learned to use.

Jeremy

==
Jeremy Wallace
AlphaBet City Dataworks

http://www.ABCDataworks.com

Access Databases for Non-Profit Organizations

Please post in the appropriate forum with a descriptive subject; code and SQL, if referenced; and expected results. See thread181-473997 for more pointers.

 

[Kherozen]

Why not asking the password before giving access to the form?!? so that way you always want to save at the end ...
or maybe I didn't understand the problem correctly




jul ^_^
"Computer Science is no more about computers than astronomy is about telescopes"
E. W. Dijkstra.

 

[Stevehewitt]

There is only a need for a password if the order is below our minimum gross profit or over our normal credit terms.

 

[JeremyNYC]

Well, there have been two good suggestions made that might make it unnecessary to use an unbound form. The first one seems to be great, the second one quite good.

If, when a record doesn't meet the profit threshold, you save the record but don't process it as a sale, then you could create, in effect, a bucket of records for management to review. They could go over these at their leisure, approving and disapproving the ones that make sense to them. This would require only a single yes/no field that would never be seen by your users--it would get filled out by code when the order taker hits save, and cleared if the manager decides to go ahead with it. If you created another yes/no field, to be set to yes if the manager disapproves the sale, then you would also create a bucket of records that never get approved. These fields would give you a great set of tools for analyzing what orders have come in under the threshold and were approved, becasuse you would no longer be throwing away data.

The other way to go about this, again, mentioned by another poster, is that you could have people log in beforehand (hopefully you're using the built-in user-level security, so this is not an issue). Depending on a user's security level, you could change the reaction to a low-profit order. For all users it would let you know that the profit level is too low. For normal order takers it would stop the process, hopefully doing as above. For managers, it would give you the ability to override it right there.

Think about the bigger picture, not just replicating exactly what was asked for. Programmers become developers when they help their clients see advantages to be gained through approaching their problems differently.

Jeremy

==
Jeremy Wallace
AlphaBet City Dataworks

http://www.ABCDataworks.com

Access Databases for Non-Profit Organizations

Please post in the appropriate forum with a descriptive subject; code and SQL, if referenced; and expected results. See thread181-473997 for more pointers.

 

[Stevehewitt]

Hi.

Thanks for the reply. I have done one of the suggestions. I have created a "Authorised" checkbox where using VBA, if the criteria is met its ticked.

Otherwise its saved but not authorised. Any report or form that wants to lookup sales will only lookup authorised sales.

Thanks very much for your help people. If I could give more stars then I would!

Much appreciated,


Steve.