Bah! I could use help with this! 'Select Case' perhaps?

[Sausagefingers]

Hi,
I've managed to drop myself in it again! My boss has asked me to take charge of one of our existing Access dbs. The guy who wrote it is no longer with us and I can no longer contact him for advice and help on it.

It is, essentially, a stock take tool and I've been saddled with the task of adding a little more functionality here and there.

One of my tasks is to isolate and show (currently by forms/reports) areas (locations) in the warehouse where stock is showing both a quantity difference and a currency variance during stock count.

Currently, we have:
1. A 'Freeze' qty/value
2. A 'Count' qty/value
3. A 'Recount 1' qty/value
4. A 'Recount 2' qty/value
5. A 'Recount 3' qty/value
6. A 'Final Count' qty/value

for every catalogue number in every area of the warehouse.

I've produced a set of querys and forms that I want to run from an OnClick() event from one of the existing forms I have to work with.

The existing form displays (in Datasheet view) checkboxes indicating whether or not a count has been completed (countOne through countSix) and the name of that area. I want to display the appropriate quantity differences and currency variances for that area via the OnClick() event on completed counts.

So, at present I have a MESS of nested If's that are neither easy to read OR work properly. I'm now desperate for help as this needs some testing as early as Friday or early next week.

I guess I should be using a 'Select Case' statement but I'm not sure how to do this. I spent some time yesterday trying and get my head around the concept but produced nothing! I get the very basic idea but the specifics of my problem are a little too hard for me to fathom

If I can explain what I'm trying to achieve, maybe somebody can offer me some help. For example:

If checkOne and checkTwo = True I want to open Form1
If checkOne, checkTwo and checkThree = True I want to open Form2
If checkOne, Two, Three and Four = True open Form3 and so on.

All these forms are basically comparisons BETWEEN the completed counts.

A problem occurs if for example, "Area1" has a freeze count and the first count matches the freeze, a recount is not required.

In the above example, only checkOne, Two and Six would ever be True.

How would I write a Select Case statement which could cover all these eventualities??

Sorry for the long-winded post but panic mode is setting in

Below is a sample of the mess i've been making for myself:


If chkCountOne.Value And chkCountTwo.Value = True And chkCountThree.Value = False Then
stDocName = "frm_difference1"
stLinkCriteria = "zoneAisle = '" & Me.strZone & "" & Me.strAisle & "' "
DoCmd.OpenForm stDocName, acNormal, , stLinkCriteria

End If

' If chkCountOne.Value And chkCountTwo.Value And chkCountThree.Value = True And chkCountFour = False Then
stDocName = "frm_difference2"
stLinkCriteria = "zoneAisle = '" & Me.strZone & "" & Me.strAisle & "' "
DoCmd.OpenForm stDocName, , , stLinkCriteria

End If

Thanks in advance

 

[Brianwarnock]

I have a feeling that we have not got all the info, your examples suggest that if you reverse check the "checks" and open the form corresponding to the first hit.

ie If check6 true open form6
else if check5 true open form5
etc

but it can'y be that simple can it?


Brian

 

[The_Doc_Man]

I'm no expert on the ins and outs of stock management from the accounting side of things, but from the database side of things, that structure - as you represented it - doesn't look quite normalized. Read up on database normalization if you are not familiar with it. If you decide to do something about that later, come back and ask for comments on the specific subject. Probably under either the Tables or Design sub-themes would be most appropriate.

On the other hand, normalizing and fixing up someone else's work is not an overnight thing. I know that one from a particularly painful personal experience about five years ago. I'll not belabor that issue now, just suggest it as a thought.

OK, let's take a look at your problem. You want to do different actions based on which combination of several checkboxes is checked (=true). We can take this approach, perhaps...

Dim TrueMask as Long
... {other stuff}

TrueMask = 0
If chkCountOne.Value = True then TrueMask = TrueMask OR 1 End If
If chkCountTwo.Value = True then TrueMask = TrueMask OR 2 End If
If chkCountThree.Value = True then TrueMask = TrueMask OR 4 End If
If chkCountFour.Value = True then TrueMask = TrueMask OR 8 End If
etc.

However many checkboxes you've got, you use the OR operator to punch in a power of 2 (a different power for each box) until all the checks have been accounted for. Let's say the sequence you need is for six such boxes. OK, then if you just used the powers of 2 from 2^0 through 2^5 (six different bits...), your answer will range from 0 to binary 0111111 = decimal 63. Work out your action table on paper, then you can use something like this...

Select TrueMask
  Case 1, 5, 9, 17, 33  {do thing 1}
  Case 2, 10, 18, 34 {do thing 2}
  etc. etc.
  Case else {do the catch-all and error checking thing}
End Select

The idea being that you can bit-pack a list of the Boolean results of the tests and then do your select on a single compacted bit-holder. Don't take those numbers that I used as meaning anything special; I was just showing possible syntax. Figure out what cases YOU have and use those numbers instead.

Speaking of which, you can write that series of tests using

"If chkCountOne = True then ..."

because by default, VBA will use the .Value property for any control that HAS a .Value property. And in further fact, since the IF statement looks for some expression to be true or false, you could even write it as

"If chkCountOne then ...."

since if the .Value of chkCountOne is False, the "Then" clause won't be executed.

Finally, because you are using a really simple ending statement, the "End If" is also optional for single-line cases, as

"If chkCountOne then TrueMask = TrueMask OR 1"

 

[Sausagefingers]

Hi Brian and thanks.
Sorry. In an effort to present my problem as fully as possible I failed to describe very much of it at all.

I have an existing form (frmStockCount) which has a subform (frmAisles).

frmAisles has 6 checkbox columns which represent stock take counts and 256 rows which represent individual Aisles (warehouse areas).

As its default view is datasheet, I want to use the OnClick() event of the row selector (on frmAisles) to launch one of 5 forms in order to display the discrepant currency/quantity data that my querys find.

These forms will display the variations in both quantity and currency between the most recent count and its previous count. For example:

Form1 will compare the differences between countOne and countTwo
Form2 will compare the difference between countTwo and countThree
etc..

Lets say Aisle 'AA' has had counts one, two and three completed. A single click on that row should then open Form2 (which compares count 2 with the most recent (3)) and display the difference.

Aisle AB on the other hand, has had counts one and two completed but because these two counts match and there are no differeces, there is no need for any further counts. Count two, in this case also becomes count six (or the Final count).

I have a situation then, where the checkboxes in row 'AA' are awaiting further counts (four and possibly five) wheras row 'AB' is complete with only three checkboxes ticked (one, two and six(Final)).

The logic of the code i need to write should take all this into consideration.

I guess my question is then, what is the best way to try to achieve this?

My previous attempts at simple or nested If, Then, Else statements is sending me round the bend!

Thanks for your input.

 

[Sausagefingers]

On the other hand, normalizing and fixing up someone else's work is not an overnight thing.

I second that!

I will be looking at adopting the approach you have suggested. Thanks very much for your help. Looks like I'm in for a bit of a late night!

 

[Brianwarnock]

The approach I mentioned earlier still looks on

If checkbox 6 is true then
open form 5
and do what needs to be done
else if checkbox 5 is true open form 4
else if checkbox 4 is true open form 3
else if checkbox 3 is true open form 2
else if checkbox 2 is true open form 1
end if

It doesn't matter how many others are true you are only interested in the highest.

Brian