Solved

Coding

Posted on 2013-06-19
17
332 Views
Last Modified: 2013-07-02
Hi Experts,
Below is my coding but I need some advise:
Select Case EmplStat
      Case "1"
        stCriteria = "Docket = '" & Me.DocketNumber & "'"
        stDocName = "rptSuCCE"
        DoCmd.OpenReport stDocName, acNormal
      Case "2"
         stCriteria = "Docket = '" & Me.DocketNumber & "'"
         stDocName = "rptSuSCE"
         DoCmd.OpenReport stDocName, acNormal
         stDocName = "EnvSuSAddr"
         DoCmd.OpenReport stDocName, acNormal
      Case Else
         stCriteria = "Docket = '" & Me.DocketNumber & "'"
         stDocName = "rptSuWork"
         DoCmd.OpenReport stDocName, acNormal
        stDocName = "rptSuLetter"
        DoCmd.OpenReport stDocName, acNormal
        If Empl = "UNAVAILABLE" Or Empl = "SELF EMPLOYED BLANK ADDRESS" Then
        Else
           stDocName = "EnvSuAddr"
           DoCmd.OpenReport stDocName, acNormal
        End If
     'Do Until rs.EOF  ?
    'rs.MoveNext  ?
    'Loop  ?
    End Select
   

This coding is only print out the form if it's case "1" or case "2" or case else like I put,  how can I use the "Loop" to get both print out if a case has both select cases or something else that I can use?

thanks,
0
Comment
Question by:urjudo
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 5
  • 3
  • +2
17 Comments
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 39261135
I guess I'm confused.  How can you have "both select cases"

Are you using a couple of radio buttons or check boxes to indicate what you want to print?  You could do this if you don't put them inside a option group, but you would have to test the value of each control, not the value of the option group.
0
 
LVL 84
ID: 39261710
In general, if your user can select one or more items from a listing you don't use a SELECT CASE. You'd use a series of IF statements:

If EmplStat="1" Then
  '/ do something
End If

If EmplStat = "2" Then
  '/ do something else
End If
0
 
LVL 50

Expert Comment

by:Gustav Brock
ID: 39261779
> how can I use the "Loop" to get both print out if a case has both select cases

You are confused. A case is one item/value only. Otherwise the syntax would have been Select Cases, and it is not so.

/gustav
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:urjudo
ID: 39262978
I totally re-do this, here is the new coding but I got error message on
   -- Do Until rs.EOF
   -- Empl = rs!EMPLOYER
   -- emplstat = rs!ECCSTEMPL
these three lines

Private Sub Command7_Click()
'On Error GoTo Err_Command7_Click
    Dim iNoofCopies As Integer
    Dim stDocName As String
    Dim stCriteria As String
    Dim stCriteria2 As String
    Dim rs As Recordset
    Dim db As Database
    Set db = CurrentDb
   
    Form_frmPrintedFormsAll.DocketNo = Me.DocketNumber
    Form_frmPrintedFormsAll.CourtDate = Me.CourtDate
   
    If IsNull(DocketNumber) Or IsNull(CourtDate) Then
       MsgBox "Please enter Docket No and Court Date before proceeding."
       Exit Sub
    End If

    stCriteria2 = "Select * from qrySubpoenaWorkLookUp where DOCKET = '" & Me.DocketNumber & "'"
    Set rs = db.OpenRecordset(stCriteria2, dbOpenDynaset, dbSeeChanges)
    Do Until rs.EOF          ---  error message "object variable or with block variable not set"
    Empl = rs!EMPLOYER   ---  error message "object variable or with block variable not set"
    emplstat = rs!ECCSTEMPL  ---  error message "object variable or with block variable not  set"

   
   
    Select Case emplstat
    Case "1"
          stCriteria = "Docket = '" & Me.DocketNumber & "'"
          stDocName = "rptSubpoenaWorkCCE"
          DoCmd.OpenReport stDocName, acNormal

    Case "2"
          stCriteria = "Docket = '" & Me.DocketNumber & "'"
          stDocName = "rptSubpoenaWorkState"
          DoCmd.OpenReport stDocName, acNormal
          stDocName = "EnvSubpoenaNCPEmplSTAddr"
          DoCmd.OpenReport stDocName, acNormal

    Case Else
          stCriteria = "Docket = '" & Me.DocketNumber & "'"
          stDocName = "rptSubpoenaWork"
          DoCmd.OpenReport stDocName, acNormal
          stDocName = "rptSubpoenaWorkRRLetter"
          DoCmd.OpenReport stDocName, acNormal
          If Empl = "UNAVAILABLE" Or Empl = "SELF EMPLOYED BLANK ADDRESS" Then
          Else
            stDocName = "EnvSubpoenaNCPEmplAddr"
            DoCmd.OpenReport stDocName, acNormal
          End If
    End Select
     rs.MoveNext
   Loop

Exit_Command7_Click:
    DoCmd.Close acForm, "frmParmSubpoena"
    Exit Sub

Err_Command7_Click:
    MsgBox Err.DESCRIPTION
    Resume Exit_Command7_Click
End Sub
0
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 39263206
Change the dim to

Dim rs as ado.recordset
Dim db as dao.database
0
 

Author Comment

by:urjudo
ID: 39263244
I change the dim but still the same error message
0
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 39263421
I usually use a syntax like:

While not rs.eof

    'insert other code here..
    Rs.movenext
Wend
Rs.close
Set rs = nothing

Unfortunately, I'm on my iPad so I cannot test.  It may also have to do with the SQL string.  I'd recommend that you print out the SQL string and test that as well.
0
 
LVL 50

Expert Comment

by:Gustav Brock
ID: 39263436
It should be:

Dim rs As DAO.Recordset
Dim db As DAO.Database

/gustav
0
 

Author Comment

by:urjudo
ID: 39263456
unfortunately, after I made the changes, there still the same error message, I just couldn't figure out why.
0
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 39263487
Missed that Gustav, damn iPad auto-correct!
0
 
LVL 24

Expert Comment

by:Bitsqueezer
ID: 39263523
Hi,

try it with this:

Private Sub Command7_Click()
'On Error GoTo Err_Command7_Click
    Dim iNoofCopies As Integer
    Dim stCriteria As String
    Dim rs As DAO.Recordset
    Dim db As DAO.Database
    
    Set db = CurrentDb
    
    With Form_frmPrintedFormsAll
        .DocketNo = Me.DocketNumber
        .CourtDate = Me.CourtDate
    End With
    
    If Nz(Me.DocketNumber) = "" Or Nz(Me.CourtDate) = "" Then
       MsgBox "Please enter Docket No and Court Date before proceeding."
       Exit Sub
    End If

    Set rs = db.OpenRecordset("SELECT * FROM qrySubpoenaWorkLookUp " & _
                              " WHERE Docket = '" & Me.DocketNumber & "'", _
                              dbOpenForwardOnly, dbReadOnly)
    
    If Not rs Is Nothing Then
        If Not (rs.BOF And rs.EOF) Then
            Do Until rs.EOF
                Empl = Nz(rs!EMPLOYER)
                
                stCriteria = "Docket = '" & Nz(Me.DocketNumber) & "'"

                Select Case Nz(rs!ECCSTEMPL)
                    Case "1"
                        DoCmd.OpenReport "rptSubpoenaWorkCCE", acViewNormal, , stCriteria
                    Case "2"
                        DoCmd.OpenReport "rptSubpoenaWorkState", acViewNormal, , stCriteria
                        DoCmd.OpenReport "EnvSubpoenaNCPEmplSTAddr", acViewNormal, , stCriteria
                    Case Else
                        DoCmd.OpenReport "rptSubpoenaWork", acViewNormal, , stCriteria
                        DoCmd.OpenReport "rptSubpoenaWorkRRLetter", acViewNormal, , stCriteria
                        
                        If Not (Empl = "UNAVAILABLE" Or Empl = "SELF EMPLOYED BLANK ADDRESS") Then
                            DoCmd.OpenReport "EnvSubpoenaNCPEmplAddr", acViewNormal, , stCriteria
                        End If
                End Select
                
                rs.MoveNext
            Loop
        End If
    End If

Exit_Command7_Click:
    DoCmd.Close acForm, "frmParmSubpoena"
    Exit Sub

Err_Command7_Click:
    MsgBox Err.Description
    Resume Exit_Command7_Click
End Sub

Open in new window


You have some unnecessary variables which I removed (unfortunately Access itself also produces such code) to make the code more readable.

The recordset should always be opened in ReadOnly mode if you don't need to do any changes on the data. The "ForwardOnly" option is the fastest method to read if you only want to use a loop with "MoveNext".

You defined a "stCriteria" but never used it. I added it to the DoCmd.OpenReport lines where it would make sense, but if you don't need that, remove the ", , stCriteria" at the end, in this case you can also delete the declaration of stCriteria and the line where it is filled with a string.

"DocketNumber" - if this is a number and not a text field, you should also remove the single quotes in the query and in the definition of stCriteria.

"Empl": It is not clear if this is a form control or a not declared variable. If it is only a not declared variable you maybe has no "Option Explicit" at the top of this module, this should always be added to any module (standard, class, form, report). Then start the compiler to look for any not declared variables and then create a decaration.
If it should be a variable then it is also not needed in the code here. It would only make sense if you want to display the field on the form but in this case a "DoEvents" is missing to refresh the display while printing.

Moreover you should always use "Nz" if you read out the value of a recordset field as it could also be NULL. Nz replaces this by default with an empty string, in other cases you can add your own replacement value to the Nz function as parameter (like Nz(Field,0) to replace the NULL in a number field with a 0).

Cheers,

Christian
0
 

Author Comment

by:urjudo
ID: 39263557
I tried Bitsqueezer's suggestion, but I got an another error message "run-time error 3061, too few parameters, expected 2" on this line
 Set rs = db.OpenRecordset("SELECT * FROM qrySubpoenaWorkLookUp " & _
                              " WHERE Docket = '" & Me.DocketNumber & "'", _
                              dbOpenForwardOnly, dbReadOnly)
0
 
LVL 24

Expert Comment

by:Bitsqueezer
ID: 39264284
Hi,

depends on the query "qrySubpoenaWorkLookUp", does it need any parameters? Can you show the SQL text of the query?

Cheers,

Christian
0
 

Author Comment

by:urjudo
ID: 39264360
here is the query;

SELECT DISTINCT qrySubpoenaInfoWorkCCE.CASENO, qrySubpoenaInfoWorkCCE.DOCKET, qrySubpoenaInfoWorkCCE.COURTDATE, qrySubpoenaInfoWorkCCE.COURTTIME, EmployerCode.EMPLOYER, EmployerCode.EADDRESS, [ECity] & ", " & [Est] & "  " & [EZip] AS EmployerCitySt, EmployerCode.ECCSTEMPL
FROM ((((Custodial_Parent INNER JOIN [Non-Custodial_Parent] ON Custodial_Parent.CPCASENUM = [Non-Custodial_Parent].NCPCASENUM) INNER JOIN Child ON [Non-Custodial_Parent].NCPCASENUM = Child.CHCASENUM) INNER JOIN qrySubpoenaInfoWorkCCE ON (Child.CHDOCKET = qrySubpoenaInfoWorkCCE.DOCKET) AND (Child.CHCASENUM = qrySubpoenaInfoWorkCCE.CASENO)) LEFT JOIN Employer ON [Non-Custodial_Parent].NCPRIN = Employer.NCPRIN) LEFT JOIN EmployerCode ON Employer.EmployerCodeID = EmployerCode.EmployerCodeID
WHERE ((([Non-Custodial_Parent].EXNCP) Is Null Or ([Non-Custodial_Parent].EXNCP)="N") AND ((Employer.ESTATUS)="C"));
0
 
LVL 24

Assisted Solution

by:Bitsqueezer
Bitsqueezer earned 250 total points
ID: 39264442
Hi,

I did not change anything in your SELECT command in the OpenRecordset method except the access method and the formatting of the command. The query you posted here depends on at least one other query  "qrySubpoenaInfoWorkCCE" so you must also check this one also. Maybe you should first create a test query which does the same as the query in the OpenRecordset method and try to run this directly without VBA to see if the query itself runs without a problem. Sometimes it is only a problem of naming in columns or too deep nested queries and so on.

I cannot help you at this point without seeing your database file. If possible, you should upload a demo database containing only the needed objects (forms, code, tables, queries) with anonymous demo data and describe the steps to reproduce the problem.

Here is some info about the error:
http://support.microsoft.com/kb/209203/en-us

Cheers,

Christian
0
 
LVL 47

Accepted Solution

by:
Dale Fye (Access MVP) earned 250 total points
ID: 39264771
The other thing is that if the nested query depends on a parameter, that parameter should be declared in both queries.
0
 

Author Comment

by:urjudo
ID: 39266322
I think it might be th problem in query because I got an error message "too few parameters, expected 2", but I did put the parameters in both queries. I also tried to removed the sub-query from the main query,  but I still got the same error message.
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

735 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question