Select Case Statement

[BrianM2]

Hi,

A week or two back, with some invaluable help from this forum, I installed a button on a form which enabled me to produce a set of prints being one of each of a number of records selected by the backing query to that form. This saved me a great deal of time. Since then I've added a Select Case Statement which is intended to recognize different types of device within a record set and to select the correct report format in each case.

I've spent a great deal of time on this but can not get it to run correctly.

Some strange things happen. The code as attached will always print a ***D report regardless of whether the record field Me!ExType1 = d or n. However I had noted that the VBA editor always changed the n to uppercase. Thinking this might be because the ExType1 field was text I added quotes to the d & n. This stopped the case change but resulted in the printing of all records on the ***EN report. So I tried adding the quotes to the d only. The output reverted to everything printed on ***D reports!

Can someone tell me what is wrong with what I have been doing.



I also need some guidance on the selection of the "expression" in Select Case Expression. Everything I have read says it can be any string or numeric. However at least some examples I have looked at seem to suggest that it needs to refer to a specific object in the database.

 

[boblarson]

It should be this:

    Select Case ReportType
    
        Case Me!ExType1 = [B][COLOR="Red"]"[/COLOR][/B]d[COLOR="red"][B]"[/B][/COLOR]
             stDocName = "rptElecInspD"
        Case Me!ExType1 = [COLOR="red"][B]"[/B][/COLOR]N[COLOR="red"][B]"[/B][/COLOR]
             stDocName = "rptElecInspEN"

    End Select

Also, is ReportType a control on the form? If so, you might want to reference it with Me.ReportType instead of by itself.

 

[BrianM2]

Bob,
That is one of the options I have tried. I've set up the query to select one "d" type device and one "n" type device. When both d and n have quote marks it prints the EN report, when neither or only the d are quoted it prints both on the D report.

Also I've tried it with an IIf function which I use all the time in sql and it always prints on the first option regardless!

The initial expression is not a control. Can I actually make it any string or numeric statement?

Brian

 

[boblarson]

What is ReportType and where does it come from?

 

[BrianM2]

It is just a descriptive name. I treated the Select Case statement as I would a file that I was naming. By no means convinced that it was what I should be doing but the stuff that I read said it could be any string or number.

 

[boblarson]

It needs to be something. You need to test for what it's value is and then the Case statements within will assign the correct Report Type.

So, actually I just figured out that which I had missed earlier. It should be:

Select Case Me!ExType1
    
        Case "d"
             stDocName = "rptElecInspD"
        Case "N"
             stDocName = "rptElecInspEN"

    End Select

Sorry, for missing that earlier. I guess I had brain meltdown.

 

[BrianM2]

It had to be something easy!! I have to stop now so will test tomorrow morning.

Thank you very much indeed. You have no idea how much time I've spent on this.

Hey - while I have you attention - and this is an easy one. How do you get the code on your replies into that neat little box with title "Code:-" amd a coloured background?

Brian.

 

[boblarson]

How do you get the code on your replies into that neat little box with title "Code:-" amd a coloured background?

Brian.

 

[BrianM2]

Not sure that I follow but I'll think about it. What does the top line say - the one that is hidden behind the cloud? Is this a Word table? (I'm a Wordperfect user)

 

[boblarson]

Not sure that I follow but I'll think about it. What does the top line say - the one that is hidden behind the cloud? Is this a Word table? (I'm a Wordperfect user)

That part doesn't matter. The screenshot is showing you the window where you type your message on the forum. If you type the word CODE in between square brackets and then at the end of the code you type /CODE in between square brackets.

 

[BrianM2]

Well - I stayed in the office a little longer. The working code needed a small change to:

Code:

    Dim rst As Recordset
    Dim stDocName As String
    Set rst = Me.RecordsetClone
    rst.MoveFirst
    
     Do
        
    Select Case rst!ExType1
    
        Case "d"
             stDocName = "rptElecInspD"
        Case "n"
             stDocName = "rptElecInspEN"

    End Select

And finally it works. Next challenge is to make it public as I need it in several places.

Best regards - Brian

 

[DCrake]

Here is a document that handles passing public variables from forms to queries. which can be used as underlying recordsources for other objects such as reports.

 

[BrianM2]

Thank you - I've just had word that I have to leave the office. Will down load and have a look at this tomorrow.