Alternative Error Message

[BillBee]

I use the following code to automatically enter an updated cage number after entering the Class number

Private Sub CageNo_Enter()
On Error GoTo Total_In_Class_Err

' Get ExhibitorID from [Forms]![ Entry Form]![ExhibitorID] and put into [Forms]![Entry Form]![Bird Entry SubForm]![Bird Entry SubForm]![ExhibitorID]
Forms![Entry Form]![Bird Entry SubForm]!ExhibitorID = Forms![Entry Form]!ExhibitorID
' Open Total In Class Form Window mode is Hidden
DoCmd.OpenForm "Total in Class Form", acNormal, "", "", , acHidden
' Get value form [Forms]![Total In Class]![NumberInClass] and put into [Forms]![Entry Form]![Bird Entry SubForm]![CageNo]
Forms![Entry Form]![Bird Entry SubForm]!CageNo = Eval("[Forms]![Total in Class Form]![NumberinClass]")
' Close Total In Class Form
DoCmd.Close acForm, "Total in Class Form"
' Turn off warnings about updating
DoCmd.SetWarnings False
' UpGrade CageNo +1 Query to next cage number
DoCmd.OpenQuery "Update CageNo +1 Query", acViewNormal, acEdit
' Close update Query
DoCmd.Close acQuery, "Update CageNo +1 Query"

Total_In_Class_Exit:
Exit Sub

Total_In_Class_Err:
MsgBox Error$
Resume Total_In_Class_Exit

End Sub

There are gaps in the Class numbers between each Section of birds. If a number that does not exist is entered accidentally I get the Runtime error 3101 (sometimes with the Visual Basic Screen) If I click "End" the message box "The value entered isn't valid for this Field" appears. Click okay in this message box leaves the flashing cursor and the number 0 in the Cage number cell. press ESC and everything returns to normal. I am wondering if any other code could be added to the above that doesn't return the Runtime Error. Just shows the Value entered isn't valid message and asks for the ESC button to be pressed or something similar to make it easier. Thanks

 

[stopher]

You should test for invalid entries before trying to use them. For example, you could use the DCOUNT function to test the existence of the entered value before you start opening forms etc.

Furthermore, you might want to consider using a Combo as the entry for the value where the combo only contains valid entries. This way the user can easily see/choose the correct entry.

hth
Chris

 

[BillBee]

Tried Combo Box but too many items in field to be practical.

 

[stopher]

Ok,then use something like:

IF DCOUNT("myPrimaryKey","myClassNumberTable","ClassNumber=" & Forms![Entry Form]!ExhibitorID )>0 THEN

 ' the code you want to run if the ExhibitorID is acceptable e.g. the code you already have

ELSE

    'the code you want to run if the ExhibitorID is invalid e.g. an error message

ENDIF

note that I'm not sure exactly which field on your form you want to check so the above is just an example. Also, the code above assumes the value being tested is a Number not Text. There is a slightly different syntax for text.

Apologies if I'm stating the obvious here.

Chris

 

[Rabbie]

Tried Combo Box but too many items in field to be practical.

How many items are we talking about? Remember that as you type in the characters of an item the combo scrolls automatically to the pright place so an item is easier to find than you first expect.

 

[BillBee]

Numbers range from 1 to 980 with some blanks in between. In hindsight the combo box should be a good option as you only see the numbers listed. I have tried to use the combo box but cant get it to show any data, or if I do get data then when I click on enter to move to the text box nothing happens. Below is the SQL View for the Record source for the subform
SELECT Entries.ExhibitorID, Entries.ClassID, Entries.CageNo, Class.[Class Description], Sections.SectionName, Class.Gender, Class.Year, Sections.Heading
FROM (Sections INNER JOIN Class ON Sections.SectionID = Class.SectionID) INNER JOIN Entries ON Class.ClassID = Entries.ClassID;

and the next is the row source for the Combo box.
SELECT Entries.ClassID
FROM Entries
ORDER BY Entries.ClassID;

Does that help with where I am going wrong?


Stopher. I haven't tried your code but the Field I am looking at for the invalid number is ClassID