Select Case, please help

[Gina Whipp]

Hello All,

Below I have a Select Case statement. All is well except when there is No
Data in a particular query then the form blanks out and you can't even make
another selection. I would like to create a message and then have the form
remain available to make another selection. No matter what I try I can get
a message, so perhaps I am not doing it right. Any ideas???

Select Case cboDocumentName
Case "Got Your Referral"
Me.RecordSource = "qryGotReferral"
Me!cboCustomerID.RowSource = "SELECT qryGotReferral.CUSTOMERNBR,
qryGotReferral.NAME FROM qryGotReferral GROUP BY qryGotReferral.CUSTOMERNBR,
qryGotReferral.NAME;"
Case "No Rewards Letter"
Me.RecordSource = "qryNoReward"
Me!cboCustomerID.RowSource = "SELECT qryNoReward.CUSTOMERNBR,
qryNoReward.NAME FROM qryNoReward GROUP BY qryNoReward.CUSTOMERNBR,
qryNoReward.NAME;"
Case "The Money Letter"
Me.RecordSource = "qrySold"
Me!cboCustomerID.RowSource = "SELECT qrySold.CUSTOMERNBR,
qrySold.NAME FROM qrySold GROUP BY qrySold.CUSTOMERNBR, qrySold.NAME;"
End Select

Thanks for any help you can give...
Gina Whipp

"I feel I have been denied critical, need to know, information!" - Tremors
II

 

[John W. Vinson]

Hello All,

Below I have a Select Case statement. All is well except when there is No
Data in a particular query then the form blanks out and you can't even make
another selection. I would like to create a message and then have the form
remain available to make another selection. No matter what I try I can get
a message, so perhaps I am not doing it right. Any ideas???

So if qrySold happens to return no records...?

Try checking the query to see if it has records first. Add

Dim rs As DAO.Recordset
Dim db As DAO.Database
Set db = CurrentDb

before the executable statements, then open a recordset based on the query:
e.g.

Set rs = db.OpenRecordset("qrySold")
If rs.Recordcount = 0 Then
Msgbox "No data found!"
Else
Me.RecordSource = "qrySold"
End If
rs.Close
set rs = Nothing

and similarly for the other queries.

John W. Vinson [MVP]

 

[Gina Whipp]

Are you sying I should abandon my Select Case? Probably better anyway...

 

[Gina Whipp]

John,

I think I misunderstood. I have never tried to open 3 seperate recordsets
in one AfterUpdate event, please explain...

 

[Gina Whipp]

Okay, I'm slow... I got it big THANKS!!!!

 

[John W. Vinson]

I got it big THANKS!!!!

You're welcome, Gina!

John W. Vinson [MVP]