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 GoToLoanWithInput()
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 GoToLoanWithInput
Exit Function
End If

If Answer = "" Then
If IsLoaded("frmEditLoans") Then
DoCmd.Close acForm, "frmEditLoans"
End If
DoCmd.OpenForm "Switchboard"
End If

If Answer <> "" Then
DoCmd.OpenForm "frmEditLoans", , , "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(Cancel As Integer)

If IsDate(Me.Last_Statement) Then
LoanOrigDate.Enabled = False
Else
Me.LoanOrigDate.Enabled = True
(!**! debug highlights this:) Me.LoanOrigDate.SetFocus
If Me.CurtailmentReq.Value = "No" Then
Me.CurtailmentPaymentAmount.Visible = False
Me.FirstPayDueDate.Visible = False
Else: Me.CurtailmentPaymentAmount.Visible = True
Me.FirstPayDueDate.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:<1069475731.952084@teuthos>...

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*****@integranetics.com> wrote in message
news:2b*************************@posting.google.co m...

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 GoToLoanWithInput()
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 GoToLoanWithInput
Exit Function
End If

If Answer = "" Then
If IsLoaded("frmEditLoans") Then
DoCmd.Close acForm, "frmEditLoans"
End If
DoCmd.OpenForm "Switchboard"
End If

If Answer <> "" Then
DoCmd.OpenForm "frmEditLoans", , , "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(Cancel As Integer)

If IsDate(Me.Last_Statement) Then
LoanOrigDate.Enabled = False
Else
Me.LoanOrigDate.Enabled = True
(!**! debug highlights this:) Me.LoanOrigDate.SetFocus
If Me.CurtailmentReq.Value = "No" Then
Me.CurtailmentPaymentAmount.Visible = False
Me.FirstPayDueDate.Visible = False
Else: Me.CurtailmentPaymentAmount.Visible = True
Me.FirstPayDueDate.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*****@integranetics.com> wrote in message
news:2b*************************@posting.google.co m...

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 GoToLoanWithInput()
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 GoToLoanWithInput
Exit Function
End If

If Answer = "" Then
If IsLoaded("frmEditLoans") Then
DoCmd.Close acForm, "frmEditLoans"
End If
DoCmd.OpenForm "Switchboard"
End If

If Answer <> "" Then
DoCmd.OpenForm "frmEditLoans", , , "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(Cancel As Integer)

If IsDate(Me.Last_Statement) Then
LoanOrigDate.Enabled = False
Else
Me.LoanOrigDate.Enabled = True
(!**! debug highlights this:) Me.LoanOrigDate.SetFocus
If Me.CurtailmentReq.Value = "No" Then
Me.CurtailmentPaymentAmount.Visible = False
Me.FirstPayDueDate.Visible = False
Else: Me.CurtailmentPaymentAmount.Visible = True
Me.FirstPayDueDate.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*****@integranetics.com> wrote in message
news:2b**************************@posting.google.c om...

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:<1069475731.952084@teuthos>...

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*****@integranetics.com> wrote in message
news:2b*************************@posting.google.co m...

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 GoToLoanWithInput()
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 GoToLoanWithInput
Exit Function
End If

If Answer = "" Then
If IsLoaded("frmEditLoans") Then
DoCmd.Close acForm, "frmEditLoans"
End If
DoCmd.OpenForm "Switchboard"
End If

If Answer <> "" Then
DoCmd.OpenForm "frmEditLoans", , , "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(Cancel As Integer)

If IsDate(Me.Last_Statement) Then
LoanOrigDate.Enabled = False
Else
Me.LoanOrigDate.Enabled = True
(!**! debug highlights this:) Me.LoanOrigDate.SetFocus
If Me.CurtailmentReq.Value = "No" Then
Me.CurtailmentPaymentAmount.Visible = False
Me.FirstPayDueDate.Visible = False
Else: Me.CurtailmentPaymentAmount.Visible = True
Me.FirstPayDueDate.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*****@integranetics.com> wrote in message
news:2b**************************@posting.google.c om...

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:<1069475731.952084@teuthos>...

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*****@integranetics.com> wrote in message
news:2b*************************@posting.google.co m...

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 GoToLoanWithInput()
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 GoToLoanWithInput
Exit Function
End If

If Answer = "" Then
If IsLoaded("frmEditLoans") Then
DoCmd.Close acForm, "frmEditLoans"
End If
DoCmd.OpenForm "Switchboard"
End If

If Answer <> "" Then
DoCmd.OpenForm "frmEditLoans", , , "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(Cancel As Integer)

If IsDate(Me.Last_Statement) Then
LoanOrigDate.Enabled = False
Else
Me.LoanOrigDate.Enabled = True
(!**! debug highlights this:) Me.LoanOrigDate.SetFocus
If Me.CurtailmentReq.Value = "No" Then
Me.CurtailmentPaymentAmount.Visible = False
Me.FirstPayDueDate.Visible = False
Else: Me.CurtailmentPaymentAmount.Visible = True
Me.FirstPayDueDate.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]", "qryValVinCurrent", "[Last5] ='" & Answer
& "' ") Then
DoCmd.OpenForm "frmEditLoans", , , "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 GoToLoanWithInput

End If

SharonKY

"TC" <a@b.c.d> wrote in message news:<1069725021.674676@teuthos>...

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*****@integranetics.com> wrote in message
news:2b**************************@posting.google.c om...

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:<1069475731.952084@teuthos>...

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*****@integranetics.com> wrote in message
news:2b*************************@posting.google.co m...
> 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 GoToLoanWithInput()
> 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 GoToLoanWithInput
> Exit Function
> End If
>
> If Answer = "" Then
> If IsLoaded("frmEditLoans") Then
> DoCmd.Close acForm, "frmEditLoans"
> End If
> DoCmd.OpenForm "Switchboard"
> End If
>
> If Answer <> "" Then
> DoCmd.OpenForm "frmEditLoans", , , "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(Cancel As Integer)
>
> If IsDate(Me.Last_Statement) Then
> LoanOrigDate.Enabled = False
> Else
> Me.LoanOrigDate.Enabled = True
> (!**! debug highlights this:) Me.LoanOrigDate.SetFocus
> If Me.CurtailmentReq.Value = "No" Then
> Me.CurtailmentPaymentAmount.Visible = False
> Me.FirstPayDueDate.Visible = False
> Else: Me.CurtailmentPaymentAmount.Visible = True
> Me.FirstPayDueDate.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], "qryValVinCurrent", "[Last5] ='" & Answer & "' ") = Answer Then

or:
If DLookup(1, "qryValVinCurrent", "[Last5] ='" & Answer & "' ") = 1 Then
or (my favourite):
If nz (DLookup(True, "qryValVinCurrent", "[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*****@integranetics.com> wrote in message
news:2b**************************@posting.google.c om... 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]", "qryValVinCurrent", "[Last5] ='" & Answer
& "' ") Then
DoCmd.OpenForm "frmEditLoans", , , "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 GoToLoanWithInput

End If

SharonKY

"TC" <a@b.c.d> wrote in message news:<1069725021.674676@teuthos>...

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*****@integranetics.com> wrote in message
news:2b**************************@posting.google.c om...

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:<1069475731.952084@teuthos>...
> 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*****@integranetics.com> wrote in message
> news:2b*************************@posting.google.co m...
> > 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 GoToLoanWithInput()
> > 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 GoToLoanWithInput
> > Exit Function
> > End If
> >
> > If Answer = "" Then
> > If IsLoaded("frmEditLoans") Then
> > DoCmd.Close acForm, "frmEditLoans"
> > End If
> > DoCmd.OpenForm "Switchboard"
> > End If
> >
> > If Answer <> "" Then
> > DoCmd.OpenForm "frmEditLoans", , , "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(Cancel As Integer)
> >
> > If IsDate(Me.Last_Statement) Then
> > LoanOrigDate.Enabled = False
> > Else
> > Me.LoanOrigDate.Enabled = True
> > (!**! debug highlights this:) Me.LoanOrigDate.SetFocus
> > If Me.CurtailmentReq.Value = "No" Then
> > Me.CurtailmentPaymentAmount.Visible = False
> > Me.FirstPayDueDate.Visible = False
> > Else: Me.CurtailmentPaymentAmount.Visible = True
> > Me.FirstPayDueDate.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:<1069818500.872406@teuthos>...

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], "qryValVinCurrent", "[Last5] ='" & Answer & "' ") =

Answer Then

or:

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

or (my favourite):

If nz (DLookup(True, "qryValVinCurrent", "[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*****@integranetics.com> wrote in message
news:2b**************************@posting.google.c om...

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]", "qryValVinCurrent", "[Last5] ='" & Answer
& "' ") Then
DoCmd.OpenForm "frmEditLoans", , , "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 GoToLoanWithInput

End If

SharonKY

"TC" <a@b.c.d> wrote in message news:<1069725021.674676@teuthos>...

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*****@integranetics.com> wrote in message
news:2b**************************@posting.google.c om...
> 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:<1069475731.952084@teuthos>...
> > 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*****@integranetics.com> wrote in message
> > news:2b*************************@posting.google.co m...
> > > 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 GoToLoanWithInput()
> > > 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 GoToLoanWithInput
> > > Exit Function
> > > End If
> > >
> > > If Answer = "" Then
> > > If IsLoaded("frmEditLoans") Then
> > > DoCmd.Close acForm, "frmEditLoans"
> > > End If
> > > DoCmd.OpenForm "Switchboard"
> > > End If
> > >
> > > If Answer <> "" Then
> > > DoCmd.OpenForm "frmEditLoans", , , "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(Cancel As Integer)
> > >
> > > If IsDate(Me.Last_Statement) Then
> > > LoanOrigDate.Enabled = False
> > > Else
> > > Me.LoanOrigDate.Enabled = True
> > > (!**! debug highlights this:) Me.LoanOrigDate.SetFocus
> > > If Me.CurtailmentReq.Value = "No" Then
> > > Me.CurtailmentPaymentAmount.Visible = False
> > > Me.FirstPayDueDate.Visible = False
> > > Else: Me.CurtailmentPaymentAmount.Visible = True
> > > Me.FirstPayDueDate.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