Access Form Navigation Control

Topic

New Reply

Is there any way determine if this control was used to move between records? I have a form and sub form, with some data filled on the main form with code when adding a new record, but don’t want this record saved if the subform is left blank.

Hope this makes sense. TIA.
Neville

Reply | Quote

Replies

A brute force technique would be to loop thru the controls on the form testing for null or empty string. If all controls report true, don’t save. Or you could put some code in the initialize event of the form: if the user opened the form, set some global var or ini setting. But opening the form and not entering data are two different things. Or again, use the query_close event on the sub form to test the control values. Close the form no matter what, but report the presence of valid entries to the main form and save accordingly.

Reply | Quote

What I’m looking for is an event (eg when user clicks the record navigation control to move to previous or first record). The only event I’m able to find that is activated under these conditions is the forms on current event. Trouble is, the record pointer has already been moved to the previous or first record.
I had hoped that the record navigation control on an Access form was subject to events just as other controls are.

Reply | Quote

Are you talking about navigation buttons on the subform or the main form? Moving between records doesn’t tell you whether any data was entered in a subform unless, possibly, you’re talking about moving between subform records. Depending on how you’ve set up your subform and the underlying data sources, you could even save an empty record, which isn’t what you want to do.

When you move from the main form to the subform, your main form record will be saved, regardless, because the focus has shifted to another form. You can’t move off of a new record unless you save the new record. If there are any required fields in your subform’s datasource, it shouldn’t be possible to move to a new record unless you had already populated the current one.

You could use the OnExit event of the subform control to test the recordcount of the subform’s recordsetclone to see if there were any records in the subform. If there weren’t, you could either scold the user into putting some in, or you could delete the record in the main form.

Reply | Quote

Main Form Navigation: If a user clicks the Navigaton control to add a record by mistake and then immediately clicks the control to move to the previous record, I’m attempting to undo the consequent saved record. (I’m using code to place data from a variable on the form. I also need to reset the value of the variable)
I also have a ‘close form’ control button on the main form, and have been uble to use the on click event to undo the saved record and reset the variable.
I know I’m able to insert indivifual record navigation contols and use their ‘on click’ events, but to me this is overkill(5 controls instead of one).
I trust this makes things a little clearer.
Neville

Reply | Quote

I’d say it’s better practice to NOT add records until the user clicks the Save or Close button on a form in which they have entered data. You can still validate data against the database, etc. But keep everything in form controls unitl they click Save. At that point do the record add, populate the row(s), etc. That way if they open the form to add a record then change their minds, you have only closed a form — no need to remove an added blank row.

Reply | Quote

Well I’ll take your word that the convenience of using the forms ‘Navigation Buttons’ set to yes is inefficient and look at inserting individual controls to ‘Move first’ etc on my forms. It does seem to be the only way I can use an event to check if I want the saved record kept or deleted.
Thanks for the assistance.
Neville

Reply | Quote

Since this is the main form, there are a couple of other things you can do. One is, as I believe Kevin suggested, to not fill in your key fields until something else has been added to the record. Just moving to a new record and then backing up doesn’t create a record if you don’t fill in any fields. Another is to test the dirty property of the form, which tells you there’s either a new record or a change to the existing one. Then too, you can test the NewRecord property to see if the current record is a new one, and respond appropriately. I’d say what you need is a validation test in the BeforeUpdate event of the main form itself. In that routine, you check to see if any critical fields are blank, and if they are, you cancel the update and use and Undo to back the record out.

Reply | Quote

The problem is that data entered on the main form will be valid, what will make the whole main form record invalid will be if nothing is entered on the subform. (I only need to check one field on the subform). The main form fields are filled in with default values using code which invarialbly will be ok (but not always, and can be changed by the user if necessary). The user can move off this record by either adding data to the subform, or moving to a previous record. In either case the main form record will be saved. If before entering data on the subform the user moves to the previous record on the main form I want to be able to undo that saved main form record. The only event that seems to be activated by clicking the navigation control is the forms on current event which occurs when the previous record is displayed.
Thanks again.
Neville

Reply | Quote

Since there is just the one field you’re concerned with on your sub-form could you create an unbound text box on the main form for the user to fill in this information? Then, if they fill in this information, save the main form, copy the data from the unbound text box to the subform, and move the focus to the subform. If the unbound text box is empty when they try to move to another record cancel the save of that record.

Reply | Quote

I’m obviously inept at explaining my situation, either that or I’m failing to understand the answers given.

My main form contains a number of fields, populated mainly by default values with one field populated via code in the forms ‘on current’ event if a new record is been added. All field values can be changed by the user.
The sub-form (continuous forms) has a number of controls which need to be filled in. I check the validity of each subform record when one particular field has data entered.

If the user moves off the main form for any reason, then the main form record is saved. At this point the record may or may not need to be retained. If the user moved to the subform and adds data to the subform then everything is ok.
If the user closes the form via a command button, and no records exist on the subform, I’m able to use the ‘on click’ event of the command button to undo the saved main form record and reset any variables.
Hovever if the user uses the main forms navigation controls the move to a previous (or first) record, and there are no underlying records for the subform then I want to discard the main form record.
When using the forms default navigation control, the only event that seem to be activated is the forms ‘on current’ event. It appears the navigation control has no accessable events. If I use individual navigation controls instead of the forms default navigation control set to yes, I am able to utilise each of the individual controls ‘on click’ event. As this event occurs prior to the move previous (or first) undoing the current record will be reasonably simple.
However for consistency of forms appearence it would be necessay to have individual navigation controls on all forms as the appearence of these controls are slightly different to the default and there are over 50 forms to alter.
Should I give up and surrender.
Neville

Reply | Quote

If I understand you correctly. The problem is not related to your navigation bar but rather to the way that Access saves records. When you leave a record by giving Focus to a diffent record, Access automatically saves the record you left. Clicking the Nav bar or into the subform will auto save the main form data. The easiest way to solve your problem without using things like recordset clones, tranactions or temporary intermediate tables would be to check for the field value in the On Exit event of the subform and have it delete the (already saved) associated record from the main forms table. Unfortunately if you are using an autonumber field on the main form for record identification you will also remove that particular number. But, if you don’t mind a sequence with a few numbers missing then you’re O.K. You might also want to check out the Microsoft Support Knowledge Base online to get info on the other (complex) ways to solve this problem.

Reply | Quote

Hi Neville,
I think what you want to be looking at is the BeforeUpdate event of the form – the one proviso being that I can’t remember for sure offhand if it’s triggered if the user doesn’t physically enter any data. Look it up in the VB help or give it a try.
Hope that helps.

Reply | Quote

The “Before Update” event will only trigger if there is a data change. The “Before Update” event of the main form is separate from the subform. The subform “Before Update” event will be triggered for each separate record of the subform. That is why I suggested using the “On Exit” event. The “On Exit” is a property of the whole subform area on the main form.

Reply | Quote

That’s true but what if the user never enters the subform? I was addressing Neville’s query which, as I understood it, was about a user moving to the previous record using the main form navigation buttons without having entered any records in the subform (at which point he wanted to discard the main form record).
I guess we’ll have to wait for Neville to decide if anyone has addressed his actual question, but it is at least an interesting discussion of event procedures!

Reply | Quote

Actually Charlotte mentioned using the sub-forms ‘on exit’ event, but was unable to find it on the forms property sheet. Well I have now, tried it, but apparently it isn’t activated if the subform isn’t entered.
Kevin mentioned a ‘query close’ event, where do I find that? I’ve never heard of it.
Anyway, I decided to do some deep thinking (God it hurts), and explore using the main forms ‘on current’ event to solve the problem, as this is the only event I found to be activated if the navigations controls are used to move to another record.
Thanks again.
Neville

Reply | Quote

Sorry if we all couldn’t help, sometimes you just have to be there. But keep in mind that the On Current event is for the record you enter, not the record you leave.

Reply | Quote

Hi Neville,
Have you tried the BeforeUpdate event of the main form as Charlotte suggested (which I’d missed when I posted it) – if a new record has been added on the main form, this event will be triggered by using the navigation buttons. However, it will also be triggered by entering the subform so you will need to allow for the fact that a user might be trying to enter data at that moment! I suspect what you need is a combination of that and the OnExit property of the subform control.
I hope that helps.

Reply | Quote