Form code help

[twimms]

I have a form with several required fields. I have created a custom message in the before update event of the field that gives the user a friendly message if any of the required fields are left null. It works fine. However, an additional error can be encountered that states "You cannot change or add a record at this time because a related record is required...." I don't really need to address this because the field that causes it is one that I've set to rquired, so my before update event already addresses it. I've captured this message number, but I'm not sure how to repress it. Any help is appreciated!

I posted a question about part of this earlier, but have since decided to delete all the code for this particular form and start over from scratch. I'm getting conflicting advice and have wrecked the code anyway from trying too many approaches. I thought I had it a few times, but I'm just confusing myself at this point. Hopefully, someone here will be willing to walk me through this step by step and hopefully I'll learn something along the way.

I have a database with a form that I would like to require data to be entered in certain fields. I would like to use the before Update event for this. I would like to check each of the 4 fields, and if any of them are null, I'd like a message telling the user this. One of these required fields is also a required record for another table, so if the user happens to leave that particular field empty, they also trigger the "Record cannot be added or changed because a related record is required..." message. I would like to completely supress and ignore that message since the Before Update event that validates data will already establish this as a required field for the user. The required fields are: strCity. strAddress, strContact, and numCounty.

Thanks so much for anyone who is wiiling to help me through this.

 

[PNGBill]

Re: Form error handling help needed - starting from scratch

While there may be varies ways to do this surely they all boil down to checking the four fields do not hold Null and if required, that what data they do hold, is formatted correctly and or matches some data in another table.
And, that this test must be done before the the process of leaving the form takes place and or the data saved to a table.

If I am correct, then the "test" on the 4 text box controls needs to be nutted out which may require four distinct checks. Can be all in one vba procedure just done one after the other.

The argument then is only where to place this test procedure.
This will depend on how your system is set up.
Do you have a command button that is clicked when the form is "completed" ? if so, then have your tests performed in the on click event of the command button and if any failure is detected, then exit the button with a relevant message.

If this advice is on the right track, then your hard task is to construct the test code.

You can then put this code into the few options available and see which gives the best result eg On Click event of command button or some form event.

The tests can even be on each respective text box control.

 

[vbaInet]

If you don't want it to be required then don't set the Required property in the table. Handle any other requirements in code.

 

[Steve R.]

Re: Form error handling help needed - starting from scratch

All the fields in the form need to be unbound.
Define 4 logical variable, one for each field.
create a "save" button. Set its enabled property to "NO".
---------------------
Use the before update event on each field to test for "bad" data.
If the field data is good, set the logical value for that field to "true".
If all four logical values are "true", then the "save" button enabled property should be set to "true".
When you press (on click event) the "save" button, the save action creates a new record and assigns the value of each field in the form to a field on the new record. See below.

        Set RST = CurrentDb.OpenRecordset("SELECT * FROM dbo_InspectionTable ORDER BY Recordid", dbOpenDynaset, dbSeeChanges)
            RST.AddNew
                RST!PermitNumber = Me.Text5
                RST!Permittee = Me.Text1
                RST!PermitType = Me.Combo11
                RST!InspectionDate = Me.Text17
                RST!EnterDate = Format(Now(), "short date")
                RST!Description = Me.Text19
                RST!Representative = Me.Combo21
                RST!InspectionType = Me.Combo23
                RST!Comments = Me.Text10
                RST!Violation = Nz(Me.Toggle0, False)
                RST!District = Me.Combo13
                RST!CountyName = Me.Combo15
                RST!AddressLocation = Me.Text6
                RST!Township = Me.Text8
                RST!ErrorFlag = False
            RST.Update
            Rem moves pointer to the new record
            RST.Bookmark = RST.LastModified

PS: I Doubled with PNGBill

 

[twimms]

Re: Form error handling help needed - starting from scratch

While there may be varies ways to do this surely they all boil down to checking the four fields do not hold Null and if required, that what data they do hold, is formatted correctly and or matches some data in another table.
And, that this test must be done before the the process of leaving the form takes place and or the data saved to a table.

If I am correct, then the "test" on the 4 text box controls needs to be nutted out which may require four distinct checks. Can be all in one vba procedure just done one after the other.

The argument then is only where to place this test procedure.
This will depend on how your system is set up.
Do you have a command button that is clicked when the form is "completed" ? if so, then have your tests performed in the on click event of the command button and if any failure is detected, then exit the button with a relevant message.

If this advice is on the right track, then your hard task is to construct the test code.

You can then put this code into the few options available and see which gives the best result eg On Click event of command button or some form event.

The tests can even be on each respective text box control.

There is no specific command button for what to do when the user has completed the form. Instead, the user chooses one of several command buttons depending on what they want to do with the data next. Because of this, I assumed the best place to put the code was in the BeforeUpdate event. I actually can do this part of the code, but the structure I have isn't friendly at all. I want it to display my custom error message just once regardless of how many of the fields are null. Instead, the way I have it now, if 3 of the fields are null, it displays the message 3 times, if 2 are null it displays it twice, etc.

Aside from cleaning up the structure of the BeforeUpdate event, I need help supressing the "You cannot add a record at this time..." error. This is the piece I'm really confused about. I have identified the error number and have put code in the form's On Error event to bypass it, but no matter how I try to structure it, I either end up still getting the standard message or the message is bypassed, but the form closes as if the user chose the cancel option on the standard message.

 

[PNGBill]

Re: Form error handling help needed - starting from scratch

Your post implied you were starting from scratch.

Can your test not throw an error until all four have been done.
SteveR refers to changing the setting of the control rather then throwing an error message (as an individual check)

Can't you turn warnings off?

Repeat the code on each of the buttons, with possible minor edits, to check the Sate of the four control boxes.

I am getting out of my depth here but surely the code that runs when a button is clicked and tests the state of the controls can default to no action if the test shows an error rather then close the form.

 

[vbaInet]

Re: Form error handling help needed - starting from scratch

It's a Form Error. This will give you some idea on how to trap it:

http://www.databasedev.co.uk/custom-error-message.html

 

[pbaldy]

Re: Form error handling help needed - starting from scratch

This is untested, as I don't want to put something together to duplicate your situation, but I suspect this is a form level error (which it sounds like you know). Try adding

response = acDataErrContinue

when you trap the appropriate error number and see if that avoids the system level message.

 

[twimms]

Re: Form error handling help needed - starting from scratch

Your post implied you were starting from scratch.

Can your test not throw an error until all four have been done.
SteveR refers to changing the setting of the control rather then throwing an error message (as an individual check)

Can't you turn warnings off?

Repeat the code on each of the buttons, with possible minor edits, to check the Sate of the four control boxes.

I am getting out of my depth here but surely the code that runs when a button is clicked and tests the state of the controls can default to no action if the test shows an error rather then close the form.

Sorry if my post was confusing. I am starting the code for these particular issues from scratch, yes. I understand what Steve is trying to do and I appreciate his input, but to be honest his code is way over my head. I was really hoping to accomplish this with code that I at least sort of understand (which I admit isn't much!) and the approach I initially chose (using the BeforeUpdate event) should work using the level of simple code that I'm most familiar with.

 

[PNGBill]

Re: Form error handling help needed - starting from scratch

Try posting the code you have so far. - of course after following the advice of vbaInet and pbaldy.

 

[twimms]

Re: Form error handling help needed - starting from scratch

Try posting the code you have so far. - of course after following the advice of vbaInet and pbaldy.

I will certainly do that. Unfortunately, I have to leave for class for a few hours. I will post it later tonight though in case anyone would like to check in later. Any additional help I can get in understanding this would be greatly appreciated.

 

[missinglinq]

...I have created a custom message in the before update event of the field that gives the user a friendly message if any of the required fields are left null.

Sorry, but this doesn't make any sense! Did you mean to say that you have this code in the BeforeUpdate event of the Form?

 

[vbaInet]

missinglinq, this is pretty much related:

http://www.access-programmers.co.uk/forums/showthread.php?t=196179

Same OP.

 

[PNGBill]

Duplicate Post going around in circles?

 

[vbaInet]

Duplicate Post going around in circles?

Pretty much

 

[boblarson]

Duplicate Post going around in circles?

And now a MERGED thread...

 

[twimms]

Sorry for the confusion my threads have caused. I did comment in the previous post that I was starting a new one because I was starting from scratch. The confusion shown in my questions is representative of how many times/ways I've tried to accomplish what I need. My head was spinning so much that I couldn't even express myself and it clearly shows. Again, I apologize and do appreciated the efforts you've all made.

I'll do my best to take your various suggestions and see if I can work this out myself.