Solved

Coding

Posted on 2013-06-19
17
328 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
  • 6
  • 5
  • 3
  • +2
17 Comments
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
Comment Utility
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
Comment Utility
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 49

Expert Comment

by:Gustav Brock
Comment Utility
> 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
 

Author Comment

by:urjudo
Comment Utility
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)
Comment Utility
Change the dim to

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

Author Comment

by:urjudo
Comment Utility
I change the dim but still the same error message
0
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
Comment Utility
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 49

Expert Comment

by:Gustav Brock
Comment Utility
It should be:

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

/gustav
0
Free Gift Card with Acronis Backup Purchase!

Backup any data in any location: local and remote systems, physical and virtual servers, private and public clouds, Macs and PCs, tablets and mobile devices, & more! For limited time only, buy any Acronis backup products and get a FREE Amazon/Best Buy gift card worth up to $200!

 

Author Comment

by:urjudo
Comment Utility
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)
Comment Utility
Missed that Gustav, damn iPad auto-correct!
0
 
LVL 24

Expert Comment

by:Bitsqueezer
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Most if not all databases provide tools to filter data; even simple mail-merge programs might offer basic filtering capabilities. This is so important that, although Access has many built-in features to help the user in this task, developers often n…
Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

772 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now