Can I change the "You can't go to the specified record" message?

[Guest]

Hi,
I made a few fields in my table required fields. Now in the form if you do
not enter the required field and try to add a new record, it gives the "You
can't go to the specified record." Is there any way to change this so it
says something like... "Please enter a date before submitting"?
Thank You

 

[BruceM]

I would not have expected that particular error message. If a table field
is required and you do nothing in code to check the field, you will receive
a default error message. If you instead set the Required property of a
table field to No, set the Validation Rule to Is Not Null, and add a
Validation Message, I believe you will receive an error message if the field
is left empty.
I usually use form-level validation by applying rules in the form's Before
Update event:

If IsNull(Me.txtCity) Then
msgbox "You need to enter something"
Me.txtCity.SetFocus
Cancel = True
End If

This lets me bring the user directly to the missing field.

 

[Guest]

Ok, Here's the update,
The name of the form is "Door Gap"
The name of the combo box that I want to be sure they enter something into
is called "Line"

This is what I put in the code...

Private Sub Form_BeforeUpdate(Cancel As Integer)
If IsNull(Line) Or Line = "" Then
MsgBox "Please enter the correct line number."
Line.SetFocus
Cancel = True
End If
End Sub

When a "Line" isn't entered and I try and update I get the new message
"Please enter the correct line number." followed by the old message "You
can't go to the specifed record."

I guess this is an improvement but I still wish it didn't give the old
message...
In the table, the field isn't required and there is no validation rule.
I'm stuck

 

[BruceM]

Are there validation rules for any of the other fields? If every field
except the Line field is filled in, do you still get the error message?
By the way, is Line the name of the combo box? Does the code work exactly
as you have written it? What happens if you use Me in front of the control
name, which is the usual syntax? It would be something like this:

If IsNull(Me.Line) Or Me.Line = "" Then
etc.

Also, if Line is the name of the field and the control, you could have
various problems. In that case the best thing is to change the control's
name, but it should also work to use the "Bang" instead of the "Dot":
Me!Line

 

[Guest]

Ok, Here's the update,
Here is the exact code I have written.

Private Sub Form_BeforeUpdate(Cancel As Integer)
If IsNull(Me.cmbxline) Or Me.cmbxline = "" Then
MsgBox "Please enter the correct line number."
Me.cmbxline.SetFocus
Cancel = True
End If

I changed the name of the combo box from "Line" to "cmbxline", I turned off
all required fields in the table and I turned off all validation rules in the
table. Then I enter in all the other information in the form except the
cmbxline, and I still get the two errors when trying to add the record...
"Please enter the correct line number." followed by "You can't go to the
specified record."
Still stuck...

PS: I also tried everything the same with a "!" instead of a "." in the code.
If IsNull(Me!cmbxline) Or Me!cmbxline = "" Then
MsgBox "Please enter the correct line number."
Me!cmbxline.SetFocus
Still gave the same error.

 

[BruceM]

Does it happen if you attempt to navigate to the previous record, or only
when you try to go to a new record? Are you using custom navigation
buttons, or the standard ones, when you try to go to a new record? If you
are using a custom button, try changing to the built-in ones. The error
could result from attempting to go to a non-existent record. There are ways
of getting custom buttons to behave once the troubleshooting is done, but I
won't get into that just now, especially since I don't know if you are even
using custom buttons.
Other things to look for: Is the form's Allow Additions property set toYes?
If the form is based on a query, is the query itself updatable? You could
test that by trying to add a new record directly to the query.

 

[Guest]

On the form, I have a submit button named "Add_Record" that I made. The code
for the Add_Record button is....
--------------------------------------
Private Sub Add_record_Click()
On Error GoTo Err_Add_record_Click


DoCmd.GoToRecord , , acNewRec
Me.Model_number.SetFocus

Exit_Add_record_Click:
Exit Sub

Err_Add_record_Click:
MsgBox Err.Description
Resume Exit_Add_record_Click

End Sub
--------------------------------------------
The form "Allows Additions"
I was using the form for quite some time. Now I just wanted to make sure
some fields are being entered and not forgotten to be filled out. It had no
problems adding records before.

 

[BruceM]

You say the form worked before, and now it doesn't. I still think it would
be worthwhile to know if the built-in navigation buttons cause the same
problem. Something has changed, but I am running out of ideas about what it
might be. Assuming you backed up the database, you could try importing the
old version of the form and table. Other than that about the only thing I
have left to suggest is that you import all of the database objects (tables,
forms, etc.) into a new blank database.
That error message occurs in my experience when, for instance, I use a
custom button for going to the previous record, but I am already at the
first record, or when I try to navigate to the next record when I am at the
last one of a filtered recordset. For whatever reason it can't go to a new
record because there isn't one. Do you get the default message if all of
the fields are filled in? If you don't, clear one field and try again.
Keep clearing fields until you get the error message. You need to isolate
the problem.

 

[Guest]

Hey,
I figured something out.
In the code for the button "Add_record", If I take out the line
MsgBox Err.Description
from the code, it only gives me the 1st error that I wanted.
Do you there is any harm in deleting this section of code if the other error
pops up on its own?

 

[BruceM]

You can take it out, but you won't have a way of identifying errors, which
is the point of error handling. It would be better to use something like
this:
MsgBox "Error #" & Err.Number & ", " & Err.Description & " -
Add_Record_Click"
This way you can find out the error number, which will help you research the
error. If the error (I'll say it is #2501) turns out to be benign then you
could change the code to something like this:

Err_Add_record_Click:
Select Case Err.Number
Case Is <> 2501
MsgBox "Error #" & Err.Number & ", " & Err.Description & " -
Add_Record_Click"
End Select
Resume Exit_Add_record_Click

A variant of this code is good for all error handling, although you need to
change the last part of the MsgBox line in order to identify the event that
has generated the error. By the way, I think that the generic ProcExit: and
ProcErr: are all you need for the line labels (rather than
Err_Add_record_Click and so forth). The line label does nothing to identify
the error, and just adds typing time, as far as I can see.

Having said all this, the error is occurring for a reason. I think you
would do well to continue hunting it down.

 

[Guest]

You were close with the error number.
It is 2105.
Now I have a statment in the button like this....
This other form's button is "submit2"
Err_Submit2_Click:
Select Case Err.Number
Case Is = 2105
MsgBox "Please fill out all the necessary boxes before clicking
the submit button."
Case Is <> 2105
MsgBox "Error #" & Err.Number & ", " & Err.Description
End Select

Resume Exit_Submit2_Click

 

[BruceM]

There are a number of ways to handle the error message syntax, but the
important point is that you are able to learn something from the error
message. I still think that identifying the event makes sense, since it
isn't always clear which event has prompted the error.
I also think the error shouldn't be happening. I guess it's OK if blocking
the error works, but if it was me I would want to know why it's happening.
It could matter later.