Passing typo to form gets runtime error 2105

[swathky]

I've tried mutiple things but no go -- (sorry this is so long)
I'm collecting a 5 digit number in an input box function and all works
fine until a number is passed that doesn't exist in the database. I
can't figure out where/how to check that the number exists before I
get the error code 2105. Here's my function to get the number that I
run when a command button is clicked to open the frmEditLoans form:

Public Function GoToLoanWithInp ut()
Dim Msg, Title, Defvalue, Answer, Cancel

Msg = "Enter VIN # last five digits" _
& vbCrLf & vbCrLf & "Quick Tip: if you do not know Vin #," _
& vbCrLf & " Copy and Paste from the ""Last 5 digits of Vin #""" _
& vbCrLf & "field on the Edit menu's View Loan/Find Loan form."

Title = "Open Edit Loan Form"

Defvalue = "00000"

Answer = InputBox(Msg, Title, Defvalue)

If Answer = "00000" Then
MsgBox ("You need to enter a valid VIN ID #")
Call GoToLoanWithInp ut
Exit Function
End If

If Answer = "" Then
If IsLoaded("frmEd itLoans") Then
DoCmd.Close acForm, "frmEditLoa ns"
End If
DoCmd.OpenForm "Switchboar d"
End If

If Answer <> "" Then
DoCmd.OpenForm "frmEditLoa ns", , , "Right([VehicleVin],5)='" &
Answer & "' "
Exit Function
End If

End Function

Here's my unrelated conditional code that kicks in On Open event of
frmEditLoans:

Private Sub Form_Open(Cance l As Integer)

If IsDate(Me.Last_ Statement) Then
LoanOrigDate.En abled = False
Else
Me.LoanOrigDate .Enabled = True
(!**! debug highlights this:) Me.LoanOrigDate .SetFocus
If Me.CurtailmentR eq.Value = "No" Then
Me.CurtailmentP aymentAmount.Vi sible = False
Me.FirstPayDueD ate.Visible = False
Else: Me.CurtailmentP aymentAmount.Vi sible = True
Me.FirstPayDueD ate.Visible = True
End If
End If
End Sub

If the VIN # doesn't exist, the runTime error 2105 "You can't go to
specified record." appears. Clicking debug highlights at the !**!
above. Putting the error in a Sub Form_Error event doesn't seem to
work unless I'm not contructing it correctly--which is possible.
I've learned so much from reading your answers to other people's
problems. Hope you can help me now.
Sharon

[swathky]

Thanks so much, TC. I'll give this a shot - I'm assuming I'll work
this in when the Answer <> "" Then ...right before telling the form to
open the next form?
Sharonky

"TC" <a@b.c.d> wrote in message news:<106947573 1.952084@teutho s>...

If you want to check whether there is a record in table XXX where the value
of field FFF is 999:

if nz (dlookup(true, "XXX", "FFF=999"), false) then
' it DOES eist.
else
' it DOES NOT exist.
endif

Use your own values for XXX, FFF and 999.

HTH,
TC
"swathky" <sw*****@integr anetics.com> wrote in message
news:2b******** *************** **@posting.goog le.com...

I've tried mutiple things but no go -- (sorry this is so long)
I'm collecting a 5 digit number in an input box function and all works
fine until a number is passed that doesn't exist in the database. I
can't figure out where/how to check that the number exists before I
get the error code 2105. Here's my function to get the number that I
run when a command button is clicked to open the frmEditLoans form:

Public Function GoToLoanWithInp ut()
Dim Msg, Title, Defvalue, Answer, Cancel

Msg = "Enter VIN # last five digits" _
& vbCrLf & vbCrLf & "Quick Tip: if you do not know Vin #," _
& vbCrLf & " Copy and Paste from the ""Last 5 digits of Vin #""" _
& vbCrLf & "field on the Edit menu's View Loan/Find Loan form."

Title = "Open Edit Loan Form"

Defvalue = "00000"

Answer = InputBox(Msg, Title, Defvalue)

If Answer = "00000" Then
MsgBox ("You need to enter a valid VIN ID #")
Call GoToLoanWithInp ut
Exit Function
End If

If Answer = "" Then
If IsLoaded("frmEd itLoans") Then
DoCmd.Close acForm, "frmEditLoa ns"
End If
DoCmd.OpenForm "Switchboar d"
End If

If Answer <> "" Then
DoCmd.OpenForm "frmEditLoa ns", , , "Right([VehicleVin],5)='" &
Answer & "' "
Exit Function
End If

End Function

Here's my unrelated conditional code that kicks in On Open event of
frmEditLoans:

Private Sub Form_Open(Cance l As Integer)

If IsDate(Me.Last_ Statement) Then
LoanOrigDate.En abled = False
Else
Me.LoanOrigDate .Enabled = True
(!**! debug highlights this:) Me.LoanOrigDate .SetFocus
If Me.CurtailmentR eq.Value = "No" Then
Me.CurtailmentP aymentAmount.Vi sible = False
Me.FirstPayDueD ate.Visible = False
Else: Me.CurtailmentP aymentAmount.Vi sible = True
Me.FirstPayDueD ate.Visible = True
End If
End If
End Sub

If the VIN # doesn't exist, the runTime error 2105 "You can't go to
specified record." appears. Clicking debug highlights at the !**!
above. Putting the error in a Sub Form_Error event doesn't seem to
work unless I'm not contructing it correctly--which is possible.
I've learned so much from reading your answers to other people's
problems. Hope you can help me now.
Sharon

[TC]

If you want to check whether there is a record in table XXX where the value
of field FFF is 999:

if nz (dlookup(true, "XXX", "FFF=999"), false) then
' it DOES eist.
else
' it DOES NOT exist.
endif

Use your own values for XXX, FFF and 999.

HTH,
TC
"swathky" <sw*****@integr anetics.com> wrote in message
news:2b******** *************** **@posting.goog le.com...

I've tried mutiple things but no go -- (sorry this is so long)
I'm collecting a 5 digit number in an input box function and all works
fine until a number is passed that doesn't exist in the database. I
can't figure out where/how to check that the number exists before I
get the error code 2105. Here's my function to get the number that I
run when a command button is clicked to open the frmEditLoans form:

Public Function GoToLoanWithInp ut()
Dim Msg, Title, Defvalue, Answer, Cancel

Msg = "Enter VIN # last five digits" _
& vbCrLf & vbCrLf & "Quick Tip: if you do not know Vin #," _
& vbCrLf & " Copy and Paste from the ""Last 5 digits of Vin #""" _
& vbCrLf & "field on the Edit menu's View Loan/Find Loan form."

Title = "Open Edit Loan Form"

Defvalue = "00000"

Answer = InputBox(Msg, Title, Defvalue)

If Answer = "00000" Then
MsgBox ("You need to enter a valid VIN ID #")
Call GoToLoanWithInp ut
Exit Function
End If

If Answer = "" Then
If IsLoaded("frmEd itLoans") Then
DoCmd.Close acForm, "frmEditLoa ns"
End If
DoCmd.OpenForm "Switchboar d"
End If

If Answer <> "" Then
DoCmd.OpenForm "frmEditLoa ns", , , "Right([VehicleVin],5)='" &
Answer & "' "
Exit Function
End If

End Function

Here's my unrelated conditional code that kicks in On Open event of
frmEditLoans:

Private Sub Form_Open(Cance l As Integer)

If IsDate(Me.Last_ Statement) Then
LoanOrigDate.En abled = False
Else
Me.LoanOrigDate .Enabled = True
(!**! debug highlights this:) Me.LoanOrigDate .SetFocus
If Me.CurtailmentR eq.Value = "No" Then
Me.CurtailmentP aymentAmount.Vi sible = False
Me.FirstPayDueD ate.Visible = False
Else: Me.CurtailmentP aymentAmount.Vi sible = True
Me.FirstPayDueD ate.Visible = True
End If
End If
End Sub

If the VIN # doesn't exist, the runTime error 2105 "You can't go to
specified record." appears. Clicking debug highlights at the !**!
above. Putting the error in a Sub Form_Error event doesn't seem to
work unless I'm not contructing it correctly--which is possible.
I've learned so much from reading your answers to other people's
problems. Hope you can help me now.
Sharon

[Larry Linson]

Or, perhaps if you added some error handling code to field the
no-record-found-condition, you wouldn't need to check beforehand.

I don't see any error handling code in what you quoted. If you don't handle
the errors, Access does, in the not-too-friendly way you describe.

You really ought to have some error handling, anyway, because a missing
record isn't the only kind of error that could happen on the operation.

Larry Linson
Microsoft Access MVP

"swathky" <sw*****@integr anetics.com> wrote in message
news:2b******** *************** ***@posting.goo gle.com...

Thanks so much, TC. I'll give this a shot - I'm assuming I'll work
this in when the Answer <> "" Then ...right before telling the form to
open the next form?
Sharonky

"TC" <a@b.c.d> wrote in message news:<106947573 1.952084@teutho s>...

If you want to check whether there is a record in table XXX where the value of field FFF is 999:

if nz (dlookup(true, "XXX", "FFF=999"), false) then
' it DOES eist.
else
' it DOES NOT exist.
endif

Use your own values for XXX, FFF and 999.

HTH,
TC
"swathky" <sw*****@integr anetics.com> wrote in message
news:2b******** *************** **@posting.goog le.com...

I've tried mutiple things but no go -- (sorry this is so long)
I'm collecting a 5 digit number in an input box function and all works
fine until a number is passed that doesn't exist in the database. I
can't figure out where/how to check that the number exists before I
get the error code 2105. Here's my function to get the number that I
run when a command button is clicked to open the frmEditLoans form:

Public Function GoToLoanWithInp ut()
Dim Msg, Title, Defvalue, Answer, Cancel

Msg = "Enter VIN # last five digits" _
& vbCrLf & vbCrLf & "Quick Tip: if you do not know Vin #," _
& vbCrLf & " Copy and Paste from the ""Last 5 digits of Vin #""" _
& vbCrLf & "field on the Edit menu's View Loan/Find Loan form."

Title = "Open Edit Loan Form"

Defvalue = "00000"

Answer = InputBox(Msg, Title, Defvalue)

If Answer = "00000" Then
MsgBox ("You need to enter a valid VIN ID #")
Call GoToLoanWithInp ut
Exit Function
End If

If Answer = "" Then
If IsLoaded("frmEd itLoans") Then
DoCmd.Close acForm, "frmEditLoa ns"
End If
DoCmd.OpenForm "Switchboar d"
End If

If Answer <> "" Then
DoCmd.OpenForm "frmEditLoa ns", , , "Right([VehicleVin],5)='" &
Answer & "' "
Exit Function
End If

End Function

Here's my unrelated conditional code that kicks in On Open event of
frmEditLoans:

Private Sub Form_Open(Cance l As Integer)

If IsDate(Me.Last_ Statement) Then
LoanOrigDate.En abled = False
Else
Me.LoanOrigDate .Enabled = True
(!**! debug highlights this:) Me.LoanOrigDate .SetFocus
If Me.CurtailmentR eq.Value = "No" Then
Me.CurtailmentP aymentAmount.Vi sible = False
Me.FirstPayDueD ate.Visible = False
Else: Me.CurtailmentP aymentAmount.Vi sible = True
Me.FirstPayDueD ate.Visible = True
End If
End If
End Sub

If the VIN # doesn't exist, the runTime error 2105 "You can't go to
specified record." appears. Clicking debug highlights at the !**!
above. Putting the error in a Sub Form_Error event doesn't seem to
work unless I'm not contructing it correctly--which is possible.
I've learned so much from reading your answers to other people's
problems. Hope you can help me now.
Sharon

[TC]

Sure, you would put the OpenForm (to open the next form) in the "true" part
of the if-test, for example:

if nz (dlookup(true, "XXX", "FFF=999"), false) then
' it DOES exist.
docmd.openform ...
else
msg "That number is not on file"
endif

HTH,
TC
"swathky" <sw*****@integr anetics.com> wrote in message
news:2b******** *************** ***@posting.goo gle.com...

Thanks so much, TC. I'll give this a shot - I'm assuming I'll work
this in when the Answer <> "" Then ...right before telling the form to
open the next form?
Sharonky

"TC" <a@b.c.d> wrote in message news:<106947573 1.952084@teutho s>...

If you want to check whether there is a record in table XXX where the value of field FFF is 999:

if nz (dlookup(true, "XXX", "FFF=999"), false) then
' it DOES eist.
else
' it DOES NOT exist.
endif

Use your own values for XXX, FFF and 999.

HTH,
TC
"swathky" <sw*****@integr anetics.com> wrote in message
news:2b******** *************** **@posting.goog le.com...

I've tried mutiple things but no go -- (sorry this is so long)
I'm collecting a 5 digit number in an input box function and all works
fine until a number is passed that doesn't exist in the database. I
can't figure out where/how to check that the number exists before I
get the error code 2105. Here's my function to get the number that I
run when a command button is clicked to open the frmEditLoans form:

Public Function GoToLoanWithInp ut()
Dim Msg, Title, Defvalue, Answer, Cancel

Msg = "Enter VIN # last five digits" _
& vbCrLf & vbCrLf & "Quick Tip: if you do not know Vin #," _
& vbCrLf & " Copy and Paste from the ""Last 5 digits of Vin #""" _
& vbCrLf & "field on the Edit menu's View Loan/Find Loan form."

Title = "Open Edit Loan Form"

Defvalue = "00000"

Answer = InputBox(Msg, Title, Defvalue)

If Answer = "00000" Then
MsgBox ("You need to enter a valid VIN ID #")
Call GoToLoanWithInp ut
Exit Function
End If

If Answer = "" Then
If IsLoaded("frmEd itLoans") Then
DoCmd.Close acForm, "frmEditLoa ns"
End If
DoCmd.OpenForm "Switchboar d"
End If

If Answer <> "" Then
DoCmd.OpenForm "frmEditLoa ns", , , "Right([VehicleVin],5)='" &
Answer & "' "
Exit Function
End If

End Function

Here's my unrelated conditional code that kicks in On Open event of
frmEditLoans:

Private Sub Form_Open(Cance l As Integer)

If IsDate(Me.Last_ Statement) Then
LoanOrigDate.En abled = False
Else
Me.LoanOrigDate .Enabled = True
(!**! debug highlights this:) Me.LoanOrigDate .SetFocus
If Me.CurtailmentR eq.Value = "No" Then
Me.CurtailmentP aymentAmount.Vi sible = False
Me.FirstPayDueD ate.Visible = False
Else: Me.CurtailmentP aymentAmount.Vi sible = True
Me.FirstPayDueD ate.Visible = True
End If
End If
End Sub

If the VIN # doesn't exist, the runTime error 2105 "You can't go to
specified record." appears. Clicking debug highlights at the !**!
above. Putting the error in a Sub Form_Error event doesn't seem to
work unless I'm not contructing it correctly--which is possible.
I've learned so much from reading your answers to other people's
problems. Hope you can help me now.
Sharon

[swathky]

Yes! this works. Point well taken, Larry, I tried several error
handling lines that didn't work before I wrote in, and, since I
couldn't get them to do what I wanted, I apparently still have a great
deal to learn there. Thanks again, TC!

'check to see if valid VIN #
If DLookup("[Last5]", "qryValVinCurre nt", "[Last5] ='" & Answer
& "' ") Then
DoCmd.OpenForm "frmEditLoa ns", , , "Right([VehicleVin],5)='" &
Answer & "' "
Else

'VIN# doesn't exist
MsgBox ("The VIN # you entered is not valid." & Chr(13) &
"Please verify # and try again.")
Call GoToLoanWithInp ut

End If

SharonKY

"TC" <a@b.c.d> wrote in message news:<106972502 1.674676@teutho s>...

Sure, you would put the OpenForm (to open the next form) in the "true" part
of the if-test, for example:

if nz (dlookup(true, "XXX", "FFF=999"), false) then
' it DOES exist.
docmd.openform ...
else
msg "That number is not on file"
endif

HTH,
TC
"swathky" <sw*****@integr anetics.com> wrote in message
news:2b******** *************** ***@posting.goo gle.com...

Thanks so much, TC. I'll give this a shot - I'm assuming I'll work
this in when the Answer <> "" Then ...right before telling the form to
open the next form?
Sharonky

"TC" <a@b.c.d> wrote in message news:<106947573 1.952084@teutho s>...

If you want to check whether there is a record in table XXX where the value of field FFF is 999:

if nz (dlookup(true, "XXX", "FFF=999"), false) then
' it DOES eist.
else
' it DOES NOT exist.
endif

Use your own values for XXX, FFF and 999.

HTH,
TC
"swathky" <sw*****@integr anetics.com> wrote in message
news:2b******** *************** **@posting.goog le.com...
> I've tried mutiple things but no go -- (sorry this is so long)
> I'm collecting a 5 digit number in an input box function and all works
> fine until a number is passed that doesn't exist in the database. I
> can't figure out where/how to check that the number exists before I
> get the error code 2105. Here's my function to get the number that I
> run when a command button is clicked to open the frmEditLoans form:
>
> Public Function GoToLoanWithInp ut()
> Dim Msg, Title, Defvalue, Answer, Cancel
>
> Msg = "Enter VIN # last five digits" _
> & vbCrLf & vbCrLf & "Quick Tip: if you do not know Vin #," _
> & vbCrLf & " Copy and Paste from the ""Last 5 digits of Vin #""" _
> & vbCrLf & "field on the Edit menu's View Loan/Find Loan form."
>
> Title = "Open Edit Loan Form"
>
> Defvalue = "00000"
>
> Answer = InputBox(Msg, Title, Defvalue)
>
> If Answer = "00000" Then
> MsgBox ("You need to enter a valid VIN ID #")
> Call GoToLoanWithInp ut
> Exit Function
> End If
>
> If Answer = "" Then
> If IsLoaded("frmEd itLoans") Then
> DoCmd.Close acForm, "frmEditLoa ns"
> End If
> DoCmd.OpenForm "Switchboar d"
> End If
>
> If Answer <> "" Then
> DoCmd.OpenForm "frmEditLoa ns", , , "Right([VehicleVin],5)='" &
> Answer & "' "
> Exit Function
> End If
>
> End Function
>
> Here's my unrelated conditional code that kicks in On Open event of
> frmEditLoans:
>
> Private Sub Form_Open(Cance l As Integer)
>
> If IsDate(Me.Last_ Statement) Then
> LoanOrigDate.En abled = False
> Else
> Me.LoanOrigDate .Enabled = True
> (!**! debug highlights this:) Me.LoanOrigDate .SetFocus
> If Me.CurtailmentR eq.Value = "No" Then
> Me.CurtailmentP aymentAmount.Vi sible = False
> Me.FirstPayDueD ate.Visible = False
> Else: Me.CurtailmentP aymentAmount.Vi sible = True
> Me.FirstPayDueD ate.Visible = True
> End If
> End If
> End Sub
>
> If the VIN # doesn't exist, the runTime error 2105 "You can't go to
> specified record." appears. Clicking debug highlights at the !**!
> above. Putting the error in a Sub Form_Error event doesn't seem to
> work unless I'm not contructing it correctly--which is possible.
> I've learned so much from reading your answers to other people's
> problems. Hope you can help me now.
> Sharon

[TC]

Er, you are assuming that every VIN number that is on file, will evaluate as
True. (That is because the result of the DLookup is the value of [Last5],
and that result is being used directly in an If-test.)

That assumption may well be true. But why make it, when you do not have to?
It would be safer, IMO, to do something like this. Then it doesn't matter
whether VIN numbers, if used in an If-test, would evaluate to True, or
False.

If DLookup("[Last5], "qryValVinCurre nt", "[Last5] ='" & Answer & "' ") = Answer Then

or:
If DLookup(1, "qryValVinCurre nt", "[Last5] ='" & Answer & "' ") = 1 Then
or (my favourite):
If nz (DLookup(True, "qryValVinCurre nt", "[Last5] ='" & Answer & "' "), False) Then

Be aware that DLookup() returns NULL if a match is not found. And NULL does
not ever meet any test. Your code could get hit by that (in certain cases).
The last suggested format is the only one that is really safe, in all cases.

HTH,
TC
"swathky" <sw*****@integr anetics.com> wrote in message
news:2b******** *************** ***@posting.goo gle.com... Yes! this works. Point well taken, Larry, I tried several error
handling lines that didn't work before I wrote in, and, since I
couldn't get them to do what I wanted, I apparently still have a great
deal to learn there. Thanks again, TC!

'check to see if valid VIN #
If DLookup("[Last5]", "qryValVinCurre nt", "[Last5] ='" & Answer
& "' ") Then
DoCmd.OpenForm "frmEditLoa ns", , , "Right([VehicleVin],5)='" &
Answer & "' "
Else

'VIN# doesn't exist
MsgBox ("The VIN # you entered is not valid." & Chr(13) &
"Please verify # and try again.")
Call GoToLoanWithInp ut

End If

SharonKY

"TC" <a@b.c.d> wrote in message news:<106972502 1.674676@teutho s>...

Sure, you would put the OpenForm (to open the next form) in the "true" part of the if-test, for example:

if nz (dlookup(true, "XXX", "FFF=999"), false) then
' it DOES exist.
docmd.openform ...
else
msg "That number is not on file"
endif

HTH,
TC
"swathky" <sw*****@integr anetics.com> wrote in message
news:2b******** *************** ***@posting.goo gle.com...

Thanks so much, TC. I'll give this a shot - I'm assuming I'll work
this in when the Answer <> "" Then ...right before telling the form to
open the next form?
Sharonky

"TC" <a@b.c.d> wrote in message news:<106947573 1.952084@teutho s>...
> If you want to check whether there is a record in table XXX where the

value

> of field FFF is 999:
>
> if nz (dlookup(true, "XXX", "FFF=999"), false) then
> ' it DOES eist.
> else
> ' it DOES NOT exist.
> endif
>
> Use your own values for XXX, FFF and 999.
>
> HTH,
> TC
>
>
> "swathky" <sw*****@integr anetics.com> wrote in message
> news:2b******** *************** **@posting.goog le.com...
> > I've tried mutiple things but no go -- (sorry this is so long)
> > I'm collecting a 5 digit number in an input box function and all

works > > fine until a number is passed that doesn't exist in the database. I > > can't figure out where/how to check that the number exists before I > > get the error code 2105. Here's my function to get the number that I > > run when a command button is clicked to open the frmEditLoans form: > >
> > Public Function GoToLoanWithInp ut()
> > Dim Msg, Title, Defvalue, Answer, Cancel
> >
> > Msg = "Enter VIN # last five digits" _
> > & vbCrLf & vbCrLf & "Quick Tip: if you do not know Vin #," _
> > & vbCrLf & " Copy and Paste from the ""Last 5 digits of Vin #""" _ > > & vbCrLf & "field on the Edit menu's View Loan/Find Loan form." > >
> > Title = "Open Edit Loan Form"
> >
> > Defvalue = "00000"
> >
> > Answer = InputBox(Msg, Title, Defvalue)
> >
> > If Answer = "00000" Then
> > MsgBox ("You need to enter a valid VIN ID #")
> > Call GoToLoanWithInp ut
> > Exit Function
> > End If
> >
> > If Answer = "" Then
> > If IsLoaded("frmEd itLoans") Then
> > DoCmd.Close acForm, "frmEditLoa ns"
> > End If
> > DoCmd.OpenForm "Switchboar d"
> > End If
> >
> > If Answer <> "" Then
> > DoCmd.OpenForm "frmEditLoa ns", , , "Right([VehicleVin],5)='" & > > Answer & "' "
> > Exit Function
> > End If
> >
> > End Function
> >
> > Here's my unrelated conditional code that kicks in On Open event of > > frmEditLoans:
> >
> > Private Sub Form_Open(Cance l As Integer)
> >
> > If IsDate(Me.Last_ Statement) Then
> > LoanOrigDate.En abled = False
> > Else
> > Me.LoanOrigDate .Enabled = True
> > (!**! debug highlights this:) Me.LoanOrigDate .SetFocus
> > If Me.CurtailmentR eq.Value = "No" Then
> > Me.CurtailmentP aymentAmount.Vi sible = False
> > Me.FirstPayDueD ate.Visible = False
> > Else: Me.CurtailmentP aymentAmount.Vi sible = True
> > Me.FirstPayDueD ate.Visible = True
> > End If
> > End If
> > End Sub
> >
> > If the VIN # doesn't exist, the runTime error 2105 "You can't go to > > specified record." appears. Clicking debug highlights at the !**!
> > above. Putting the error in a Sub Form_Error event doesn't seem to
> > work unless I'm not contructing it correctly--which is possible.
> > I've learned so much from reading your answers to other people's
> > problems. Hope you can help me now.
> > Sharon

[NeilAnderson]

Why go to all the bother if checking that a record exists? Surely
you're only slowing things up. If error 2105 means that the record
isn't there (I haven't looked it up so make sure that this is what is
causing the error), just write an error handler to trap error 2105 &
warn the user that this number doesn't exist / is mistyped? As one of
the other posters pointed out this isn't the only thing that can go
wrong here, so you're going to need some sort of error handler anyway.

[swathky]

OK, TC, I'll do your favorite way. I thought I didn't have to use NZ
since I thought I covered nulls with the statement 'If Answer = ""
Then do some things'.
Thanks to all for your input & help.
Sharon
"TC" <a@b.c.d> wrote in message news:<106981850 0.872406@teutho s>...

Er, you are assuming that every VIN number that is on file, will evaluate as
True. (That is because the result of the DLookup is the value of [Last5],
and that result is being used directly in an If-test.)

That assumption may well be true. But why make it, when you do not have to?
It would be safer, IMO, to do something like this. Then it doesn't matter
whether VIN numbers, if used in an If-test, would evaluate to True, or
False.

If DLookup("[Last5], "qryValVinCurre nt", "[Last5] ='" & Answer & "' ") =

Answer Then

or:

If DLookup(1, "qryValVinCurre nt", "[Last5] ='" & Answer & "' ") = 1 Then

or (my favourite):

If nz (DLookup(True, "qryValVinCurre nt", "[Last5] ='" & Answer & "' "),

False) Then

Be aware that DLookup() returns NULL if a match is not found. And NULL does
not ever meet any test. Your code could get hit by that (in certain cases).
The last suggested format is the only one that is really safe, in all cases.

HTH,
TC
"swathky" <sw*****@integr anetics.com> wrote in message
news:2b******** *************** ***@posting.goo gle.com...

Yes! this works. Point well taken, Larry, I tried several error
handling lines that didn't work before I wrote in, and, since I
couldn't get them to do what I wanted, I apparently still have a great
deal to learn there. Thanks again, TC!

'check to see if valid VIN #
If DLookup("[Last5]", "qryValVinCurre nt", "[Last5] ='" & Answer
& "' ") Then
DoCmd.OpenForm "frmEditLoa ns", , , "Right([VehicleVin],5)='" &
Answer & "' "
Else

'VIN# doesn't exist
MsgBox ("The VIN # you entered is not valid." & Chr(13) &
"Please verify # and try again.")
Call GoToLoanWithInp ut

End If

SharonKY

"TC" <a@b.c.d> wrote in message news:<106972502 1.674676@teutho s>...

Sure, you would put the OpenForm (to open the next form) in the "true" part of the if-test, for example:

if nz (dlookup(true, "XXX", "FFF=999"), false) then
' it DOES exist.
docmd.openform ...
else
msg "That number is not on file"
endif

HTH,
TC
"swathky" <sw*****@integr anetics.com> wrote in message
news:2b******** *************** ***@posting.goo gle.com...
> Thanks so much, TC. I'll give this a shot - I'm assuming I'll work
> this in when the Answer <> "" Then ...right before telling the form to
> open the next form?
> Sharonky
>
>
>
> "TC" <a@b.c.d> wrote in message news:<106947573 1.952084@teutho s>...
> > If you want to check whether there is a record in table XXX where the
value > > of field FFF is 999:
> >
> > if nz (dlookup(true, "XXX", "FFF=999"), false) then
> > ' it DOES eist.
> > else
> > ' it DOES NOT exist.
> > endif
> >
> > Use your own values for XXX, FFF and 999.
> >
> > HTH,
> > TC
> >
> >
> > "swathky" <sw*****@integr anetics.com> wrote in message
> > news:2b******** *************** **@posting.goog le.com...
> > > I've tried mutiple things but no go -- (sorry this is so long)
> > > I'm collecting a 5 digit number in an input box function and all works > > > fine until a number is passed that doesn't exist in the database. I > > > can't figure out where/how to check that the number exists before I > > > get the error code 2105. Here's my function to get the number that I > > > run when a command button is clicked to open the frmEditLoans form: > > >
> > > Public Function GoToLoanWithInp ut()
> > > Dim Msg, Title, Defvalue, Answer, Cancel
> > >
> > > Msg = "Enter VIN # last five digits" _
> > > & vbCrLf & vbCrLf & "Quick Tip: if you do not know Vin #," _
> > > & vbCrLf & " Copy and Paste from the ""Last 5 digits of Vin #""" _ > > > & vbCrLf & "field on the Edit menu's View Loan/Find Loan form." > > >
> > > Title = "Open Edit Loan Form"
> > >
> > > Defvalue = "00000"
> > >
> > > Answer = InputBox(Msg, Title, Defvalue)
> > >
> > > If Answer = "00000" Then
> > > MsgBox ("You need to enter a valid VIN ID #")
> > > Call GoToLoanWithInp ut
> > > Exit Function
> > > End If
> > >
> > > If Answer = "" Then
> > > If IsLoaded("frmEd itLoans") Then
> > > DoCmd.Close acForm, "frmEditLoa ns"
> > > End If
> > > DoCmd.OpenForm "Switchboar d"
> > > End If
> > >
> > > If Answer <> "" Then
> > > DoCmd.OpenForm "frmEditLoa ns", , , "Right([VehicleVin],5)='" & > > > Answer & "' "
> > > Exit Function
> > > End If
> > >
> > > End Function
> > >
> > > Here's my unrelated conditional code that kicks in On Open event of > > > frmEditLoans:
> > >
> > > Private Sub Form_Open(Cance l As Integer)
> > >
> > > If IsDate(Me.Last_ Statement) Then
> > > LoanOrigDate.En abled = False
> > > Else
> > > Me.LoanOrigDate .Enabled = True
> > > (!**! debug highlights this:) Me.LoanOrigDate .SetFocus
> > > If Me.CurtailmentR eq.Value = "No" Then
> > > Me.CurtailmentP aymentAmount.Vi sible = False
> > > Me.FirstPayDueD ate.Visible = False
> > > Else: Me.CurtailmentP aymentAmount.Vi sible = True
> > > Me.FirstPayDueD ate.Visible = True
> > > End If
> > > End If
> > > End Sub
> > >
> > > If the VIN # doesn't exist, the runTime error 2105 "You can't go to > > > specified record." appears. Clicking debug highlights at the !**!
> > > above. Putting the error in a Sub Form_Error event doesn't seem to
> > > work unless I'm not contructing it correctly--which is possible.
> > > I've learned so much from reading your answers to other people's
> > > problems. Hope you can help me now.
> > > Sharon