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

 

[missinglinq via AccessMonster.com]

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.

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]