Select Case Help

[profxavier27809]

Hello there,

Thanks for all the help you guys give first of all.

I have a report that returns values 1,2,3,4,5...each of these values represents a schedule B1,B2,B3,B4,B5. In the OnOpen I was trying to write like 10 IF statements but don't know how to properly do a bunch. I was writing like IF me.shift = 1 then me.text4 = B1 and tried to write that many times under the other ones. It tells me I entered an express that has no value. I searched forever and found that a select case might be the one i need but I cannot figure it out. Is there sample code I can try to use.

Thanks,

Raymond

 

[TheEvilBankers]

The code for select case is:

Select Case (testexpression)
Case (Variable)
what you want it to do
Case (Variable2)
what you want it to do
...
Case Else
What you want it to do
end select

I usually use it to select reports from an option group
Select Case Me!FrameName
Case 1
stDocName="rptAllAccounts"
Case 2
stDocName="rptBoxnumbers"
End Select

docmd.openreport.stDocName

where 1 and 2 are the optionbutton values

Hope this helps.

K

 

[profxavier27809]

I am not sure how i would apply that to select schedules. The values that show on the report are 1,2,3 etc...I don't have the B1, B2, B3, etc...directly connected with those numbers anywhere, so I need to in what the values for 1,2,3 should return. this would work if i was only looking for 1 number to have a value returned such as IF me.text2 = 1 then me.text3 = B1 end if

I suck I know it.

 

[WayneRyan]

Raymond,

Instead of the code, you need to do the following:

tblYourData
===========
SomeKey
YourValues --> the 1, 2, 3, 4, ...
SomeOtherFields

tblLookUp
=========
YourValue --> the 1, 2, 3, 4, ...
YourText --> the B1, B2, B3, B4, ...

Then when you make a form or report, base it on a query:

Select tblYourData.YourValue, --> the 1, 2, 3, 4, ...
       tblYourData.YourText,  --> the B1, B2, B3, B4, ...
       tblYourData.SomeOtherFields
From   tblYourData inner join tblLookUp On
         tblYourData.YourValues = tblLookUp.YourValue  
Order By tblYourData.SomeOtherFields

Then as you gather more information in your DB, you can
have the (1,2,3,4, ...) values anywhere and link them
to your new table. You don't have to (or want to) do
it with code.

btw,

With your current method, you could use an IIf statement
in a query and simulate the same result, but doing so would
mean that changing the # of values would be a real pain.
What if you had 500 values? Much better to create a very
easily managable table.

hth,
Wayne

 

[Bat17]

Wayne has the right way of doing it, but just to help clear up your original problem with the if's, They were probably OK but in the wrong event. OnOpen in a form the controls have no value you would need to work in the onFormat of the section where the shift field is.

Peter

 

[profxavier27809]

You guys have no idea how awesome you are! I so appreciate the suggestions and proactive attitudes in here. I will try that, but I seriously need to learn select case I think.