avoid saving data upon losing focus 1

[xezekielx]

Is it possible to avoid saving data when a subform loses focus? The reason I want to do this is because I want to have a "cancel" or "undo" button on my main form, not on the subform itself (because the "form" within the subform is changed with the click of other buttons), but from what I read, as soon as the subform loses focus, the record is saved.

Thanks in advance!

 

[GingerR]

That is true. An option is to have a 'temporary' table for your subform, which is loaded with any existing related data when the form is opened. Then the user has to hit a SAVE button to actually save the data (you'd put code behind the button to write the data to the "real" table).

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[xezekielx]

Wow! That's a great idea! Could you just help me a little bit to create a temp table? I don't really know how to do this... Thanks again for your great answer!

 

[GingerR]

Sure. Some questions first:

what do you mean by the subform is updated with other buttons?

Is the main form your are on only used for data entry? Could there be multiple people entering data for the same main record?

Please list your table structure (i.e. Table Names, pertinent Field Names). Also your form/subform names.

Do you also want the Main Form data handled in the same way? What I mean is that the main form data does not go directly into the table until you hit a button that says SAVE?

Thanks--g

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[xezekielx]

what do you mean by the subform is updated with other buttons?

I have a menu made with buttons on my main form that will switch which form is displayed in the subform. A button will display the customer's personal info, another button will display its asset, etc, each "section" being a form loaded in the subform control.

Is the main form your are on only used for data entry?

The main form isn't really a data entry form. You could compare the interface with a webpage: the main form is the frameset page. The is a small menu with buttons to change the content of the other frame (read: the subform). Of course if you know a better way to have a similar looking interface, let me know

Could there be multiple people entering data for the same main record?

No.

Please list your table structure (i.e. Table Names, pertinent Field Names). Also your form/subform names.

Main form's name: frmMain
Subform's name: frmAsset
Tables:
- tCustomer (CustomerID being the Primary Key)
- tAsset (CustomerID being a Foreign Key)
There are more tables but I'll be able to do it for the other tables with an example for these two.
(the tables are all linked with a 1/1 relationship - it could have been one huge table but it was kinda pointless)

Do you also want the Main Form data handled in the same way?

No, since there isn't really any data updated on the main form.

Thanks in advance!

 

[TheAceMan1]

How are ya xezekielx . . . . .

Yes you can! . . . but instead of the [blue]LostFocus[/blue] event of the subform, [blue]you need to perform some validation in the BeforeUpdate event of the subform to determine if the record should be saved.[/blue] Something like this:

[blue]   Dim Msg as String, Style as Integer, Title as String  
   
   If [purple][b]My_Validation_Fails[/b][/purple] Then
      Msg = "YourMessage"
      Style = "vbInformation + vbOKOnly
      Title = "Validation Failed! . . ."
      MsgBox Msg, Style, Title
      
      [purple]Cancel = True
      Me.Undo[/purple]
   
   End If[/blue]

What ever you do, you have to perform some validation so [blue]access knows what to do![/blue] The only problem with this method is that it will take two clicks . . . one to close the MsgBox and another to go where ever . . .

Your thoughts?

See Ya! . . . . . .

 

[xezekielx]

Thanks for your answer TheAceMan1, but I don't really want to 'ask' the user if he wants to undo the changes before he presses the undo/save button... Plus, I don't really know what kind of validation I could use anyway

 

[TheAceMan1]

xezekielx . . . . .

Its you call . . . but somehow you have to tell access wether to save or not!

See Ya! . . . . . .

 

[xezekielx]

yeah I know... but I want that the only way to save/undo is to press the save button or the undo button placed on the main form. the reason why they are on the main form and not on the subform is because, like I said earlier, the subform control is used to display several forms and putting the buttons on each subform would be long, repetitive and I would have to position the buttons at exactly the same place on each subform to give the illusion that they are always there (i.e. on the main form)

 

[GingerR]

So does the main form have a recordsource? I assume so since each subform displays information about a customer?

If this information is all one-to-one, how about using a tab control instead of subforms?

I think you should put all fields in to one table.

That being said, how are you using this main form? Can you search for an existing record as well as add a new record? Do you search with a combo box? or just with navigation buttons? The gist is that OnCurrent or on some other event, we'll add records from the "real" tables to the "temp" tables so that data will be displayed in the subforms. Then when a user clicks a button to SAVE, we'll write the "temp" data to the "real" tables. If the user hits UNDO, well then we do nothing but close the form.



Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[xezekielx]

The main form has a record source, it's the table tCustomer (it's only used to be able to 'link' the main form and the subforms).

I have an 'Open customer file' form which is kind of like the 'File | Open' dialog box in standard Windows apps. There is also a 'New customer file' to create a new customer.

Thanks a lot for helping me out GingerR... I know I'm terribly annoying

 

[GingerR]

It's not annoying at all. I'm just trying to get my bearings cause it affects what I tell you.

Are you using the same form for both both opening an existing file as well as entering a new one?



Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[xezekielx]

no, there's an 'Open customer' form and a 'Create new customer' form. Thanks again!

 

[GingerR]

why two forms? do they look pretty much the same? Or does the Create New Customer form just create the new basic customer information like Customer ID, etc, for the Customer table? Is frmMain yet a third form? sorry for all the questions but I'm trying to understand. Maybe you can just write out a few sentences so I understand how your db operates from a user perspective. Thanks.

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[xezekielx]

OK so there's the main form, with two buttons; one to create a new customer, the other one to open an existing customer.

The 'New customer' button opens the 'New customer' form which allows the user to input the customer's personal info (name, phone number, address, etc). After the user is created, you can enter the data for this user (asset, liability, etc) in the subforms that is now visible on the main form (it's hidden upon loading the main form).

The 'Open customer' button opens a small form containing a listbox with the name of every customer in the database. The user then presses the 'OK' button (or the 'Cancel' button to close the form without 'selecting' a customer). Once the user has selected the desired customer, the subform on the main form is shown, just like it is after a new customer is created. The buttons menu to select which form will be displayed the the subform control is shown as well, so that the user can navigate throughout the different data input forms.

 

[GingerR]

I'm sorry but I'm still confused. There is only one place where these subforms reside, and it's on what you call the "main form" which is also the one with the two buttons on it to "create a new customer" and "open existing customer"?

The "New Customer" form has no subforms? but some how you return to the "main form" after you add a new customer and then the "main form" has the new customer information displayed in its subforms?

Sorry I'm having a hard time wrapping my arms around this--it's not a typical set up so I'm trying to understand.

In any case, you'd make duplicate tables of your table structures (don't include the data) for any tables which are used in subforms. These temp tables would become the recordsources of your forms/subforms. Make some sample forms/tables for this to try it out first.

So say someone is entering a new record. You'd make an unbound form, which essentially is the same as your current "New Customer Form" but take out the recordsource and remove the control sources of all the controls. Put a button that says SAVE on it on the form. In the OnClick code, you'd put something like:

'Confirm user wants to add data
if msgbox("Are you sure you wish to save this data?",vbYesNo,"Confirm Addition") = vbNo then exit sub

'Add record to table
Dim rs as dao.recordset
set rs = currentdb.openrecordset ("Select * from tCustomer")
rs.addnew
rs!CustomerName = me.txtCustomerName
rs!CustomerAddress = me.txtCustomerAddress
rs!CustomerPhone = me.txtCustomerPhone
rs.update
set rs = nothing
msgbox "Done adding " & me.txtCustomerName & "!"

'Clear all boxes by opening the form in ADD mode
Docmd.OpenForm "frmMain",,,,acFormAdd

Then say you are on your form where you are looking at an existing record and the related subforms. The subforms have recordsources of the temp tables. The Main form though is unbound (no recordsource) and the fields get filled up in the OnOpen event of the main form. In the button on the Open Customer form, don't put any where clause. Just say to open this form. The first bit of code goes to the tCustomer and fills the form with data for the customer you've chosen.

dim intCustomerID as integer
intCustomerID = Forms![Open Customer]![CustomerID]
dim rs, rsAsset, rsAssetTemp, rsOther, rsOtherTemp as dao.recordset
set rs = currentdb.openrecordset ("Select * from tCustomer where CustomerID = " & intCustomerID

me.txtCustomerName = rs!CustomerName
me.txtCustomerAddress = rs!CustomerAddress
me.txtCustomerPhone = rs!CustomerPhone
set rs = nothing

'Fill temp tables with related data; first delete any related data that might be left in them

currentdb.execute ("Delete * from tAssetTemp where CustomerID = " & intCustomerID)
currentdb.execute ("Delete * from tOtherTemp where CustomerID = " & intCustomerID)

'-----------------
set rsAsset = currentdb.openrecordset ("Select * from tAsset where CustomerID = " & intCustomerID
set rsAssetTemp = currentdb.openrecordset ("Select * from tAssetTemp")
rsAssetTemp!CustomerID = intCustomerID
rsAssetTemp!Asset = rsAsset!Asset
rsAssetTemp!Field2 = rsAsset!Field2
rsAssetTemp!Field3 = rsAsset!Field3

set rsAsset = nothing
set rsAssetTemp = nothing

'-----------------
set rsOther = currentdb.openrecordset ("Select * from tOther where CustomerID = " & intCustomerID
set rsOtherTemp = currentdb.openrecordset ("Select * from tOtherTemp")
rsOtherTemp!CustomerID = intCustomerID
rsOtherTemp!Field1 = rsOther!Field1
rsOtherTemp!Field2 = rsOther!Field2
rsOtherTemp!Field3 = rsOther!Field3

set rsOther = nothing
set rsOtherTemp = nothing

In the VBA code window, from the menu you have to pick TOOLS+REFERENCES and make sure that Microsoft DAO is picked. Choose the highest version (probabaly 3.6). I didn't test the code above, but take a look and see if you can decipher it.



Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[GingerR]

oh-hey--you can probably forget about making the temp tables. I'm used to using subforms to display data that has a one-to-many relationship with the 'main' form. Since yours does not, just fill the subforms with the rsAsset data like the main form fills up.

Again, though, I suggest putting all of your fields into one table (to adhere to db normalization rules). If you do that, you don't need subforms. You can use a tab control.

Ginger

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244