Select Case Len(Me.txtUserInput)
Case 5
Set rstedit = db.OpenRecordset("SELECT * FROM
tblAlternateDealerInfo WHERE [PDN] = " & searchcrit)
Case Else
Set rstedit = db.OpenRecordset("SELECT * FROM tblDealerInfo
WHERE [PDN] = " & searchcrit)
Case Else
MsgBox "Please enter a 5 digit primary dealer number."
Me.txtUserInput = ""
End Select


There is my code... I want to open one tbl and if the record is not
there open the second tbl and if it is not there i want it to put that
message box.

How can i do this?

Thanks

You've not really explained what you're tyring to do, but the first problem I
see is that you have TWO Case Else's!!! Generally speaking, it looks like
everything from "Case 5" on down needs to be in "Case 5." You need to put
logic there to check to see if the record is found in the first table and if
not, check to see if it's in the second table, and if not there throw up
your message box.

Viper wrote:
>Select Case Len(Me.txtUserInput)
> Case 5
> Set rstedit = db.OpenRecordset("SELECT * FROM
>tblAlternateDealerInfo WHERE [PDN] = " & searchcrit)
> Case Else
> Set rstedit = db.OpenRecordset("SELECT * FROM tblDealerInfo
>WHERE [PDN] = " & searchcrit)
> Case Else
> MsgBox "Please enter a 5 digit primary dealer number."
> Me.txtUserInput = ""
>End Select
>
>There is my code... I want to open one tbl and if the record is not
>there open the second tbl and if it is not there i want it to put that
>message box.
>
> How can i do this?
>
>Thanks

--
There's ALWAYS more than one way to skin a cat!

Answers/posts based on Access 2000

Message posted via http://www.accessmonster.com

On 19 Dec 2006 12:41:31 -0800, "Viper" <(E-Mail Removed)> wrote:

>Select Case Len(Me.txtUserInput)
> Case 5
> Set rstedit = db.OpenRecordset("SELECT * FROM
>tblAlternateDealerInfo WHERE [PDN] = " & searchcrit)
> Case Else
> Set rstedit = db.OpenRecordset("SELECT * FROM tblDealerInfo
>WHERE [PDN] = " & searchcrit)
> Case Else
> MsgBox "Please enter a 5 digit primary dealer number."
> Me.txtUserInput = ""
>End Select

Well, this makes no sense to me. You can't have two different Case
Else clauses in a SELECT CASE; the Case 5 will be executed if the user
enters five characters, the first Case Else will fire if they do
anything else. There's nothing that can cause the second Case Else to
fire.

>
>There is my code... I want to open one tbl and if the record is not
>there open the second tbl and if it is not there i want it to put that
>message box.
>
> How can i do this?

What does the length of txtUserInput have to do with anything? Could
you not simply put an Input Mask on the textbox of 00000 to force the
user to enter five numeric digits? I presume you have defined
searchcrit previously... or did you mean to use Me.txtUserInput in its
place?

That said... try

If Len(Me.txtUserInput) = 5 Then
Set rstedit = db.OpenRecordset("SELECT * FROM " _
& "tblAlternateDealerInfo WHERE [PDN] = " & searchcrit)
If rxt.RecordCount = 0 Then
Set rstedit = db.OpenRecordset("SELECT * FROM " _
& "tblDealerInfo WHERE [PDN] = " & searchcrit
End If
Elxe
MsgBox "Please enter a 5 digit primary dealer number."
Me.txtUserInput.Undo
End If


John W. Vinson[MVP]

Thanks A Lot John

That works just fine
WOW



John Vinson wrote:
> On 19 Dec 2006 12:41:31 -0800, "Viper" <(E-Mail Removed)> wrote:
>
> >Select Case Len(Me.txtUserInput)
> > Case 5
> > Set rstedit = db.OpenRecordset("SELECT * FROM
> >tblAlternateDealerInfo WHERE [PDN] = " & searchcrit)
> > Case Else
> > Set rstedit = db.OpenRecordset("SELECT * FROM tblDealerInfo
> >WHERE [PDN] = " & searchcrit)
> > Case Else
> > MsgBox "Please enter a 5 digit primary dealer number."
> > Me.txtUserInput = ""
> >End Select
>
> Well, this makes no sense to me. You can't have two different Case
> Else clauses in a SELECT CASE; the Case 5 will be executed if the user
> enters five characters, the first Case Else will fire if they do
> anything else. There's nothing that can cause the second Case Else to
> fire.
>
> >
> >There is my code... I want to open one tbl and if the record is not
> >there open the second tbl and if it is not there i want it to put that
> >message box.
> >
> > How can i do this?
>
> What does the length of txtUserInput have to do with anything? Could
> you not simply put an Input Mask on the textbox of 00000 to force the
> user to enter five numeric digits? I presume you have defined
> searchcrit previously... or did you mean to use Me.txtUserInput in its
> place?
>
> That said... try
>
> If Len(Me.txtUserInput) = 5 Then
> Set rstedit = db.OpenRecordset("SELECT * FROM " _
> & "tblAlternateDealerInfo WHERE [PDN] = " & searchcrit)
> If rxt.RecordCount = 0 Then
> Set rstedit = db.OpenRecordset("SELECT * FROM " _
> & "tblDealerInfo WHERE [PDN] = " & searchcrit
> End If
> Elxe
> MsgBox "Please enter a 5 digit primary dealer number."
> Me.txtUserInput.Undo
> End If
>
>
> John W. Vinson[MVP]