Perevent user from leaving the subfrom blank

[Overtime]

Hi,

Is there a way to prevent users from entering main form data, then exiting (or moving on to a new record) without entering a single line in the subform?

I want to prevent the user from creating empty receipts (i.e. entering receipt number and date, and not entering any "items" in the subform.) Users wouldn't want to do that anyway, if it happens, it would have happened by accident, and I want to prevent such mistakes as much as possible.

Please note that I have several events set up to lock the subform until the main form data has been entered. This is to prevent the user from creating parent-less items.

I would be grateful for any help or input... thanks!

 

[mdlueck]

The way I do this sort of validation is to validate all fields in some sort of "commit" area of code, such as when records are getting INSERT / UPDATE performed. I use VBA code to validate each field's value. Fields found with an invalid value I set the field background color to red. Then I simply do not call the INSERT / UPDATE code.

I choose that method over tying "can depart" to each field control on the form. Such methods fail to handle correctly complex business rules such as "Fields A, B, C must be populated in that order. So fill a value into A, B, then blank out A, and commit the changes". Field based validation rules would allow such to be performed. Entire form validation would catch that scenario.

 

[spikepl]

I have a case like this, where if the main record exists (i.e. has been saved) , there has to be at least one child record.

I cover all the exits. I have a sub which checks whether the current main record has any children. I have a custom next/previous/new record button, that ask whether the user really want to leave a childless record, and if so, then I delete that record. You may just remind them that they haven't finished. Also Close button and the form's Close -event (if user kills the form or entire db by clicking on the cross in the corner).

 

[Overtime]

The way I do this sort of validation is to validate all fields in some sort of "commit" area of code, such as when records are getting INSERT / UPDATE performed. I use VBA code to validate each field's value. Fields found with an invalid value I set the field background color to red. Then I simply do not call the INSERT / UPDATE code.

I choose that method over tying "can depart" to each field control on the form. Such methods fail to handle correctly complex business rules such as "Fields A, B, C must be populated in that order. So fill a value into A, B, then blank out A, and commit the changes". Field based validation rules would allow such to be performed. Entire form validation would catch that scenario.

I'm not sure I understood this correctly. I've only been using Access for around 10 days now, and a lot of what you said went completely over my head. How do you control the Insert/update event? By default, Access immediately creates a new record, and saves fields as they are entered, it does not wait for an Insert or Update instruction. Unless you're talking about insert/update queries? Do you mean that you use unbound forms, and then commit them by using an insert/update if the entries pass your validation test?

If that's the case, I don't think it would work for me. My subform is a continuous form, and one of the fields is a calculated field, it is populated using a query based on 3 other fields in the same row. I tried leaving it unbound, because I don't really want to save the contents of that field, as it is for display purposes only, and you know the rule about never saving derived fields. But I couldn't have it my way, because if a field was left unbound, Access would update this unbound control in ALL the lines in the continuous form with the same value, based on the current field. Apparently, there's no way for access to differentiate between the unbound control in line 1 and the unbound control in line 6. So, after 2 days of banging my head against the screen, I had to create a column for that control in the source table, and "bind" it to that column. I didn't like that, but it was the only way I could solve the problem. I tried other solutions people posted online, but none of them worked, and I really had to move on.

If you're method is based upon using unbound controls and committing them later, I don't see how access would sort out the continuous form mechanism.

Unless, you turn the subform's dataEntry mode off? Hmmmm....that sounds like it would work. I'll wait for your feedback..

Thanks a lot

 

[Overtime]

I have a case like this, where if the main record exists (i.e. has been saved) , there has to be at least one child record.

I cover all the exits. I have a sub which checks whether the current main record has any children. I have a custom next/previous/new record button, that ask whether the user really want to leave a childless record, and if so, then I delete that record. You may just remind them that they haven't finished. Also Close button and the form's Close -event (if user kills the form or entire db by clicking on the cross in the corner).

Hi Spikepl, thanks for replying.

I thought about that, but what if the user pressed Ctrl + Tab? That would take him directly to the next page (a blank new record,) even if the first record was still child-less. I don't know which event corresponds this keyboard shortcut. I tried "OnClose" but it only gets called when the whole form is closed, which makes sense I suppose.

 

[spikepl]

Good point - haven't thought about Ctrl+Tab, coz my users hardly know what a keyboard is. You can probably trap it and tell access to ignore it. . This would likely require that you set Form>Property Sheet>Events>Key Preview to yes (last item in that tab) and use OnKey down event for the trap. As to what actual code is for Crtl+Tab I don't know - such fun questions are left to the reader

Maybe changing Form>Property Sheet>Other>Cycle will kill it - try that first

 

[mdlueck]

How do you control the Insert/update event?

I was speaking of "unbound forms" where the forms are not directly associated with database table fields.

VBA code looks up a record to bring into edit mode, and fills in all of the fields with the database record fields.

The commit button would then read the screen fields and only if all are valid transfer the fields back to a SQL UPDATE to store the changed record in the table.

<><><><>

Now with bound forms... hhhmmm.... I would think that Access forms have a method to call to abandon any changes, and one to try to commit the changes. I believe Access forms use DAO type database connections, so perhaps start searching in that direction.

I make it a point to have all multi-record list views READ ONLY. Double clicking (or edit button) brings the ONE selected record into edit mode. That greatly reduces and simplifies record locking in a multi-user environment. The only time records end up being locked is when an UPDATE occurs against xyz database record.

 

[Overtime]

Good point - haven't thought about Ctrl+Tab, coz my users hardly know what a keyboard is. You can probably trap it and tell access to ignore it. . This would likely require that you set Form>Property Sheet>Events>Key Preview to yes (last item in that tab) and use OnKey down event for the trap. As to what actual code is for Crtl+Tab I don't know - such fun questions are left to the reader

Maybe changing Form>Property Sheet>Other>Cycle will kill it - try that first

Hey,

I changed key previews to "yes" like you suggested, but I got stuck on the next step. I've been trying to get the form to do the validation when Ctrl+tab is pressed, but the Keydown event only runs when one key is pressed, for some reason it doesn't read combinations. When I press one key (ex: Ctrl) it runs before I press the second key. I've been searching for some information about this, but I haven't been able to find anything useful so far.

 

[Overtime]

Hey,

I changed key previews to "yes" like you suggested, but I got stuck on the next step. I've been trying to get the form to do the validation when Ctrl+tab is pressed, but the Keydown event only runs when one key is pressed, for some reason it doesn't read combinations. When I press one key (ex: Ctrl) it runs before I press the second key. I've been searching for some information about this, but I haven't been able to find anything useful so far.

I copied the code posted here but it didn't work for combination keys either, although the code is apparently an example published by Microsoft.

 

[Overtime]

I was speaking of "unbound forms" where the forms are not directly associated with database table fields.

VBA code looks up a record to bring into edit mode, and fills in all of the fields with the database record fields.

The commit button would then read the screen fields and only if all are valid transfer the fields back to a SQL UPDATE to store the changed record in the table.

<><><><>

Now with bound forms... hhhmmm.... I would think that Access forms have a method to call to abandon any changes, and one to try to commit the changes. I believe Access forms use DAO type database connections, so perhaps start searching in that direction.

I make it a point to have all multi-record list views READ ONLY. Double clicking (or edit button) brings the ONE selected record into edit mode. That greatly reduces and simplifies record locking in a multi-user environment. The only time records end up being locked is when an UPDATE occurs against xyz database record.

Well, I can't use unbound forms for the reason I explained earlier. Plus, I think you need to be an expert to do that, and I'm barely slugging along.

Thank you so much for your suggestions, I really appreciate you taking the time to answer.

 

[spikepl]

perhaps you can set ctrl tab as a shortcut that does nothing

http://stackoverflow.com/questions/...d-shortcut-to-a-button-in-an-access-2007-form

if not, I guess trapping a key combo involves a two-step: remembering that CTRL was the previous key pressed, when you process the next key down. That's what I would try, knowing little about this, and assuming that KeyDown cannot trap CTRL combinations.

 

[mdlueck]

Well, I can't use unbound forms for the reason I explained earlier.

For muli-record forms I too use bound forms. Just I have them be READ ONLY. Double click a record in the list to bring that one record into edit mode. Those record edit forms are unbound single-record forms.

I download record lists from the BE DB into an FE temp table, then display those local records in a multi record form. You can compute fields which are not actually stored in the BE DB and do have them in the FE temp table. Thus you could solve your problem of the unbound field. (Download the records, then do a "table scan" computing the dynamic values for the records downloaded.)

I actually do that very thing in one spot... I have part Revisions stored in Base 10 numbers, but for UI display I need to convert that back to "Base 36" which is 0 - 99, then A - ZZ. I download records into the FE temp table, then after the records are in the FE temp table I call the "Base10to36" routine which computes the RevDisplay field from the Rev field. There is only a Rev field in the BE DB, but the FE temp table adds the RevDisplay column. If I did not use such an elaborate scheme, then part Revs sort very strangely. When I wish to display records sorted by Rev, then I sort by the Rev column, and naturally RevDisp which is shown on the form is sorted correctly.

 

[Overtime]

Thank you, I'll consider your suggestions

 

[gemma-the-husky]

i would think the easiest way to do this, is to input into temporary files first. I do this in a lot of apps.

say, you have an order, and order lines

input a temporary order, and temporary orderlines, and have an accept button. at the point the accept button is clicked, reject the order if it does not validate. if it does validate, then run an append query to insert the order and order lines into the "true" data tables.