Case and Recordsets

[Viper]

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

 

[John Vinson]

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]

 

[Viper]

Thanks A Lot John

That works just fine
WOW

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]