Annoying error in Northwind sample?

[StuMunro]

I have a form similar to the Suppliers form in the sample NorthWind database.

Like Northwind it suffers from the same annoying problem with an Access error message displayed to the user.

To see the problem for yourself do the following in Northwind

Open the Suppliers table.
Select a new record.
Fill in valid information expect in the Country enter

Canada

and for the Postal Code enter

1234

Click the 'X' in the top right of the form to close it, WITHOUT saving the record first.

The incorrect validation error appears. Click ok.

Then the annoying message appears stating there was an error, do you want to close the object.

I want to get rid of this second error, and hence automatically return to the form, so the original data error can be corrected


Can anyone show me how to do this?

Stu

 

[ghubbell]

Hi Stu,
Microsoft has put a Postal/Zip code testing sub in the wrong place. If you want to fix this go to your suppliers form module and copy all of the contents of a sub called "Form before update". Hold these contents and remove the subs title and end sub lines. Now go to your postal code field and double-click on it to get its properties dialog box. Select the events tab, and find "After Update". Select "event procedure" and then click on the "..." button just to its right. Your forms module will open and label up this new sub. Now paste the contents of what you "stole" earlier. You will have to look at it and remove every line that says "Cancel = True" and for it to work properly the Country entry must be made before the Postal code (change the tab order). Close and save. What this little sub is doing is checking that your postal code is entered in agreement with the country you choose. In Canada, postal codes are like 1A2 3B4. The sub will now do this check immediately after you enter a postal code instead of when you "move" off the record by closing the form. If you don't like this feature, simply remove it all together. Good luck, Gord
ghubbell@total.net

 

[braindead2]

I did just as you said and the only annoying message that appears is the one that says You can't save this record at this time. However I had 2 warnings that the 1234 I entered was not in the correct format for the postal code field. In order to return to edit all I needed to do was click no and change the code
to avoid this all together I would move the code
from the Form_BeforeUpdate(Cancel As Integer)
event to the controls after update event. I would not even let them leave the field with Incorrect data.

 

[braindead2]

Sorry Gord You must have answered question about same time as I was.

 

[StuMunro]

Thanks for your help guys.

I tried what Gord suggested but now the form closes and saves the incorrect postal code, although without the annoying message.

What I want is for the incorrect post code message to appear, but not let the record save or close the form.

Can you get it to do this?

Stu

 

[ghubbell]

Stu,
Scrap or comment out all the previous stuff & see if this will do as you wish:

Private Function LetsCheckPCode() As Boolean
On Error GoTo ErrPC
LetsCheckPCode = True
If IsNull(Me.PostalCode) Then
LetsCheckPCode = False
MsgBox "Please enter a postal code.", vbInformation, "Postal Code missing..."
Me.PostalCode.SetFocus
Exit Function
End If
If IsNull(Me.Country) Then
LetsCheckPCode = False
MsgBox "Please enter a country first.", vbInformation, "Country missing..."
Me.Country.SetFocus
Exit Function
End If
Select Case Me.Country
Case "France", "Italy", "Spain"
If Len(Me.PostalCode) <> 5 Then
LetsCheckPCode = False
MsgBox &quot;Postal Code must be 5 characters&quot;, vbInformation, &quot;Postal Code Error.&quot;
End If
Case &quot;Australia&quot;, &quot;Singapore&quot;
If Len(Me.PostalCode) <> 4 Then
MsgBox &quot;Postal Code must be 4 characters&quot;, vbInformation, &quot;Postal Code Error.&quot;
LetsCheckPCode = False
End If
Case &quot;Canada&quot;
If Not Me.PostalCode Like &quot;[A-Z][0-9][A-Z] [0-9][A-Z][0-9]&quot; Then
MsgBox &quot;Postal Code not valid. Example of Canadian code: H1J 1C3&quot;, vbInformation, &quot;Postal Code Error.&quot;
LetsCheckPCode = False
End If
End Select

ExitPC:
Exit Function

ErrPC:
MsgBox Err.Number & &quot; &quot; & Err.Description, vbInformation, &quot;Check postal code error.&quot;
Resume ExitPC
End Function


'*Add in to &quot;Private Sub Form_Unload&quot;

If Not LetsCheckPCode Then Cancel = True

I hope this is what you would like. It will stop the form from closing, but not from cycling to another record. In that case we'll have to go a bit further. Give it a go! Gord
ghubbell@total.net