Select Case Question

[Bluezman]

I have a hidden textbox called CurrentStatus which holds the result of the expression

=DLookUp("[activityType]","qryCurrentStatus","[activityID]=[forms]![MemberForm]![memberID]")

Am I correct in assuming that it's possible to code another textbox to look at the contents of CurrentStatus, then based on a SELECT CASE statement have it choose either ACTIVE or INACTIVE to display?

I'm having trouble figuring out the correct syntax or where I should place the code.

Anyone have an idea?

Thanks!!

Bluez

 

[Bluezman]

Thanks for responding so quickly!

I'll try your suggestion and post back my results.

Bluez

 

[Bluezman]

Well, it doesn't seem to be working, so I don't think I'm putting this expression in the right area.

Pat, using your CHOOSE() expression, I'm not seeing where I can select either Active or Inactive based on the contents of the DLookUp.

In my tblActivities I have 14 seperate activityTypes and the running of the qryCurrentStatus it groups them on the MemberID field. I had thought the DLookUp was going to the last record in tblActivities and displaying it in CurrentStatus, or at least it always seemed to be.

So in my other textbox, (lets call it ActivityStatus) all I wanted to do was see what the result of CurrentStatus is, and then based on the result (i.e. Initiated, Suspended, etc.) I could say "if the result of CurrentStatus is Initiated, display Active".

You mentioned IIF statements, and I agree that going that route can become a snakepit very quickly, that's whay I thought about using Select Case in code if it would work.

Am I making this too hard? I tend to do that I think.

Thanks Pat!

Bluez

 

[Travis]

Set the ControlSource of "ActivityStatus" to : =funActiveStatus(Me.[CurrentStatus])

Then add this code to your forms Code:

Private Function funActiveStatus(ByVal CurStat as String) as String
Dim Return as String

     Select Case CurStat
          Case "Initiated":  Return="Active"
          Case ...
          Case Else 
              Return="Default if not in your list"
     End Select

     funActiveStatus=Return

End Function

 

[Bluezman]

Hi Travis,

Your code looks to be exactly what I need, but now I have a confession to make. Sorry to be such a pain, but I've tried putting your code in just about every place imaginable on the Forms code and all I get back is #Name?, like I have a typo but for the life of me I can't seem to see one. Should I have this in the General Declarations area, the Form Open event, On Current Event???


Thanks again for all your help!

Bluez

**Found Solution**

in the "=funActiveStatus(Me.[CurrentStatus])" statement, I just had to remove the Me. and it works great!!

Thanks again Travis!!

Bluez

 

[Bluezman]

One more question, I promise.

The code Travis showed me is working beautifully, in fact it's giving me more flexability than I originally thought I'd need.

My problem now lies in the fact that not all members have entries in tblActivities so there is a blank record when viewing that subform. When the DLookUp runs it brings back a blank, so that when funActvitityType fires, none of the Select Case statements apply and the CASE ELSE result doesn't get entered, only an #error statement.

Is there a way to have the code that Travis lists above, test the contents of CurStat, and if it's null or empty, return "ACTIVE"??

I've tried using

IF(IsNull([CurStat]) THEN _
CurStat = ""
Else
Select Case ....

and then insterting

Case "": Return = "ACTIVE"

But it doesn't work and still gives #error message.

Any ideas??

Bluez

 

[Drevlin]

Since your using a string variable a null will return as Empty not null.
Try commenting out the code for:

IF(IsNull([CurStat]) THEN _
CurStat = ""
Else

but leave in the:

Case "": Return = "ACTIVE"

line.

I won't guarantee that this will work. But I'm thinking it should.

 

[Bluezman]

Hi Drevlin,

Nope, no go. Still returns #error. I'll keep plugging away at this, but thanks for the help!

Bluez

 

[Drevlin]

Ok, since my last comment didn't work... try this.

Change the:

Private Function funActiveStatus(ByVal CurStat as String) as String

to:

Private Function funActiveStatus(ByVal CurStat as Variant) as String

This should make your "Select Else:" work with an Empty Value.

At least it did when I tested it.

Or if you want the Empty Value to do something different, your
If IsNull(CurStat) idea will work.

 

[Bluezman]

Anyone elevated you to God status yet this week? If not, I nominate you out of gratitude for helping me through this. Thanks a bunch!!

You can get a jump on next week too if you can tell me if I can use this textbox result in a query??? i.e. I want to be able to get a list of ONLY Active members. I can't seem to get my attempts at it to work.

Thanks a million again Drevlin!

Bluez

 

[Drevlin]

I'm thinking that Travis probably deserves your gratitude far more then I do, I just told you to change a word.

If I understand what your asking correctly, I wouldn't use a form. I would write a query that does what your wanting. You can use the Function that Travis gave you (just place it into a Module and don't use Private). So you're Field in Query Design Mode would look like this:

CurrentStatus: funActiveStatus([LookupField])

You'd also need a field for your Dlookup. But from what you've written I don't understand why you can't just create a Join between the two tables (if in fact there are two tables). I, of course, have no idea how your information is set up in your form so it's hard for me to say. You could attempt to write your DLookup in your query, it should work pretty much the same way.

As seems to be a popular comment in many posts:

HTH

 

[Bluezman]

As you have probably guessed already, I'm still getting used to VBA and modules and all this just got extremely over my head.

But thanks for trying to help out anyway, I appreciate it.

Bluez

 

[Bluezman]

Ok Travis, Drevlin, Pat and anyone else who has tried to help with this issue. I really do appreciate all the suggestions so far and I apologize for blowing the defeatist horn the other day. I've taken a day off, cleaned out all the frustration on this and decided to attack this the best possible way.

If everything that has gone before needs to be trashed and started over another way, I completely understand and am willing to do whatever it takes to get this done right.

You have an open book here to suggest anything and everything you would do to in this situation.

Thanks again for all your help!!

Bluez

 

[Drevlin]

I wouldn't suggest scrapping anything. It's just hard to problem solve your situation without knowing all the factors involved in what your doing. Perhaps you could post (or email me directly) an example of your database (feel free to gut it of any information that shouldn't be passed along to others). I'd be happy to look at it and maybe suggest something to you.
But at the very least it would be helpfull to know exactly what your information looks like and the purpose behind your setup.

 

[Bluezman]

Thank you Drevlin for all your help. You've solved what was a very frustrating problem for me, and I very much appreciate your assistance.

If anyone was following this thread and wonders what was done to get this to work, please email me and I would be happy to show you all the great work Drevlin did.

Bluez