Apply Select Case To All Records

  • Thread starter Thread starter CJ
  • Start date Start date
C

CJ

Hi Groupies

I have never used the Select Case statement before and am having a little
trouble.

I have the Select Case code attached to a command button on a form. When I
push the button, the code runs for the selected record only. How do I make
it run for all of the records in the recordset?

Here is some of the code:

Private Sub cmdAutoFlight_Click()
Dim Rank As Integer, Flight As String, Saturday_Tee_Time As Date
Select Case Rank
Case Is <= 16
Me.Flight = "Championship"
Me.Saturday_Tee_Time = "1:00:00 PM"
Case Is <= 32
Me.Flight = "1"
Me.Saturday_Tee_Time = "1:00:00 PM"
Case Is <= 48
Me.Flight = "2"
Me.Saturday_Tee_Time = "1:00:00 PM"
<snip>
Case Else
Me.Flight = "16"
Me.Saturday_Tee_Time = "7:00:00 AM"
End Select
End Sub
 
Instead of having a select case statement, a better bet would be to create a
query as the basis for your form, and use calculated fields to produce the
results.
For example, TeeTime = IIF(Rank <=48, '1 P.M.' , '7 a.m.), and Flight =
IIf(Rank < 48 and Rank > 32, "2", Iif(Rank <=32 and rank >16, '1', Iif(Rank
<16, "Championship", "16"))))

I haven't tested the code above but if uses nested IIF statements to
calculate the tee time and flight fields. That way if you change a rank, the
tee times and flights will update automatically when you requery your form.
 
Hi, thanks for your input.

I had thought of IIF statements but I thought you were limited to how many
nests you can have and how long the expression can be. When it gets down to
assigning golfers to their hole, I would need 35 nests.

CJ

mray29 said:
Instead of having a select case statement, a better bet would be to create
a
query as the basis for your form, and use calculated fields to produce the
results.
For example, TeeTime = IIF(Rank <=48, '1 P.M.' , '7 a.m.), and Flight =
IIf(Rank < 48 and Rank > 32, "2", Iif(Rank <=32 and rank >16, '1',
Iif(Rank
<16, "Championship", "16"))))

I haven't tested the code above but if uses nested IIF statements to
calculate the tee time and flight fields. That way if you change a rank,
the
tee times and flights will update automatically when you requery your
form.

CJ said:
Hi Groupies

I have never used the Select Case statement before and am having a little
trouble.

I have the Select Case code attached to a command button on a form. When
I
push the button, the code runs for the selected record only. How do I
make
it run for all of the records in the recordset?

Here is some of the code:

Private Sub cmdAutoFlight_Click()
Dim Rank As Integer, Flight As String, Saturday_Tee_Time As Date
Select Case Rank
Case Is <= 16
Me.Flight = "Championship"
Me.Saturday_Tee_Time = "1:00:00 PM"
Case Is <= 32
Me.Flight = "1"
Me.Saturday_Tee_Time = "1:00:00 PM"
Case Is <= 48
Me.Flight = "2"
Me.Saturday_Tee_Time = "1:00:00 PM"
<snip>
Case Else
Me.Flight = "16"
Me.Saturday_Tee_Time = "7:00:00 AM"
End Select
End Sub
Click to expand...
Click to expand...
 
Hi Groupies

I have never used the Select Case statement before and am having a little
trouble.

I have the Select Case code attached to a command button on a form. When I
push the button, the code runs for the selected record only. How do I make
it run for all of the records in the recordset?

Here is some of the code:

Private Sub cmdAutoFlight_Click()
Dim Rank As Integer, Flight As String, Saturday_Tee_Time As Date
Select Case Rank
Case Is <= 16
Me.Flight = "Championship"
Me.Saturday_Tee_Time = "1:00:00 PM"
Case Is <= 32
Me.Flight = "1"
Me.Saturday_Tee_Time = "1:00:00 PM"
Case Is <= 48
Me.Flight = "2"
Me.Saturday_Tee_Time = "1:00:00 PM"
<snip>
Case Else
Me.Flight = "16"
Me.Saturday_Tee_Time = "7:00:00 AM"
End Select
End Sub
Click to expand...

You have to iterate through the recordset with a while loop. If you
are using a bound form, then I believe you have to create a copy of
the recordset using recordsetclone.

Dim Rank As Integer, Flight As String, Saturday_Tee_Time As Date
Dim rs As Recordset

Set rs = Me.RecordsetClone
while not rs.eof
rs.edit
Select Case rs!Rank
Case Is <= 16
rs!Flight = "Championship"
rs!Saturday_Tee_Time = "1:00:00 PM"
Case Is <= 32
rs!Flight = "1"
rs!Saturday_Tee_Time = "1:00:00 PM"
Case Is <= 48
rs!Flight = "2"
rs!Saturday_Tee_Time = "1:00:00 PM"
Case Else
rs!Flight = "16"
rs!Saturday_Tee_Time = "7:00:00 AM"
End Select
rs.update
rs.movenext
wend
rs.close
set rs = nothing
 
I didn't realize you had so many cases. In that case, you might think about
making a separate lookup able with these columns, for a simpler approach

Rank Teetime Flight
1 1 p.m. Championship
2 1 p.m. Champioinship
16 1 p.m. 1
....etc.

Go up as high as you want in Rank in the lookup table. ONe way to do this
would be to create the Rank field as an Autonumber field, so each time you
add a new record it automatically increments. You could also do this in code
using a recordset but it's probably more involved than you want to get.

Then you join to this table using Rank and you would automatically get the
correct Teetime and Rank in your query. I'm assuming you're wanting to
display these values on a form or report.

The problem with your previous Select Case is that you have conditions
<=16 and <=32, etc. so 15 for example would meet both cases (it's less than
16 and less than 32.. You'd need something like Case <=16, Case >16 and <=32,
etc.




CJ said:
Hi, thanks for your input.

I had thought of IIF statements but I thought you were limited to how many
nests you can have and how long the expression can be. When it gets down to
assigning golfers to their hole, I would need 35 nests.

CJ
Click to expand...
 
Hi Groupies

I have never used the Select Case statement before and am having a little
trouble.

I have the Select Case code attached to a command button on a form. When I
push the button, the code runs for the selected record only. How do I make
it run for all of the records in the recordset?

Here is some of the code:

Private Sub cmdAutoFlight_Click()
Dim Rank As Integer, Flight As String, Saturday_Tee_Time As Date
Select Case Rank
Case Is <= 16
Me.Flight = "Championship"
Me.Saturday_Tee_Time = "1:00:00 PM"
Case Is <= 32
Me.Flight = "1"
Me.Saturday_Tee_Time = "1:00:00 PM"
Case Is <= 48
Me.Flight = "2"
Me.Saturday_Tee_Time = "1:00:00 PM"
<snip>
Case Else
Me.Flight = "16"
Me.Saturday_Tee_Time = "7:00:00 AM"
End Select
End Sub
Click to expand...

Rather than using any sort of code, I'd create a table TeeTimes with three
fields: Rank, Flight, and TeeTime. Just for flexibility I'd fill it with all
possible values of Rank each with its corresponding flight and teetime. You
could then join this table in a Query to look up the flight and tee time; or
even use it in an Update query to store the values, if that's appropriate.

John W. Vinson [MVP]
 
OldPro said:
You have to iterate through the recordset with a while loop. If you
are using a bound form, then I believe you have to create a copy of
the recordset using recordsetclone.

Dim Rank As Integer, Flight As String, Saturday_Tee_Time As Date
Dim rs As Recordset

Set rs = Me.RecordsetClone
while not rs.eof
rs.edit
Select Case rs!Rank
Case Is <= 16
rs!Flight = "Championship"
rs!Saturday_Tee_Time = "1:00:00 PM"
Case Is <= 32
rs!Flight = "1"
rs!Saturday_Tee_Time = "1:00:00 PM"
Case Is <= 48
rs!Flight = "2"
rs!Saturday_Tee_Time = "1:00:00 PM"
Case Else
rs!Flight = "16"
rs!Saturday_Tee_Time = "7:00:00 AM"
End Select
rs.update
rs.movenext
wend
rs.close
set rs = nothing
Click to expand...

Hi, thanks for popping in.

What you suggest makes sense. I gave it a try but I am getting a Type
Mismatch error on Set rs = Me.RecordsetClone.
 
Brilliant! Got it working very, very quickly. The light is considerably
brighter at the end of the tunnel.

--
Thanks in advance for your continued brilliance!

CJ
-----------------------------------
Work together.....Save your sanity

mray29 said:
I didn't realize you had so many cases. In that case, you might think
about
making a separate lookup able with these columns, for a simpler approach

Rank Teetime Flight
1 1 p.m. Championship
2 1 p.m. Champioinship
16 1 p.m. 1
...etc.

Go up as high as you want in Rank in the lookup table. ONe way to do this
would be to create the Rank field as an Autonumber field, so each time you
add a new record it automatically increments. You could also do this in
code
using a recordset but it's probably more involved than you want to get.

Then you join to this table using Rank and you would automatically get the
correct Teetime and Rank in your query. I'm assuming you're wanting to
display these values on a form or report.

The problem with your previous Select Case is that you have conditions
<=16 and <=32, etc. so 15 for example would meet both cases (it's less
than
16 and less than 32.. You'd need something like Case <=16, Case >16 and
<=32,
etc.
Click to expand...
 
Don't worry about this anymore. I am going with a lookup table.

Thanks for your brainwaves.
CJ

CJ said:
Hi, thanks for popping in.

What you suggest makes sense. I gave it a try but I am getting a Type
Mismatch error on Set rs = Me.RecordsetClone.
Click to expand...
 
Thanks for your input John.

Your suggestion was suggested by mray29 a few minutes ago. Much easier than
what I was doing.

Thanks again.
 
Back
Top