Code Newbie (1 Viewer)

Tina49

Registered User.
Local time
Today, 14:38
Joined
Sep 29, 2011
Messages
34
I am working on a database that I didn't create. I was asked to create a report which I though wasn't a big deal. However the database is setup completely different then what I am used to and everything is written in code. What I have so far is the end user choses a Quarter date, either 1, 2, 3, 4 or Annual. They choose to print Therapy Report. The code should then collect data based on the date, each therapist, all services that each therapist has provided and a summary of survey questions regarding that therapist and those services. Can anyone send me a link to any online help that shows how to create reports in VBA that allows multiple pieces of criteria. What I have so far is based on another report that I generated but doesn't work for multiple pieces of criteria. This code allows me to pick what report to print and what date to use but it doesn't allow me to print using the criteria listed above. OPProvides are my lists of Therapist. OPServices are the services they provide, Qtext is the questions on the survey, Data is the main table where the surveys are entered. Tally is the table where the questions are tallied. So 5 for excellent services and 1 for poor service. Here is what I have so far:

Code:
Private Sub Command1_Click()
 Dim db As Database
  Dim rsdata As Recordset, rstally As Recordset
  Dim rskey As Recordset, rstext As Recordset
  Dim Bdate As Date
  Dim Edate As Date
  Dim tst As String
  Dim atch As String
  Dim txt As String, cnt As Integer
  Dim ans As Integer, j As Integer, k As Integer
  Dim xkey As String, xmonth As Integer, xquarter As Integer, xyear As Integer
  Dim xtype As Integer
  Dim keywant As String, qtrwant As Integer, yerwant As Integer
  Dim survey_form As Integer 'indicates which question set to use
  Dim qcvt(12) As Integer
  Dim thebatch As String
  Dim S(20), btch As String
  Dim scores(20, 6) As Long
  Dim flipit As Boolean, refcode As String
  Dim msg, style, response
  
  Set db = CurrentDb()
  Set rsdata = db.OpenRecordset("Data", dbOpenTable)
  rsdata.Index = "IDNmb"
  Set rstally = db.OpenRecordset("Tally", dbOpenTable)
  Set rskey = db.OpenRecordset("OPProviders", dbOpenTable)  
  rskey.Index = "OPNmb"
  Set rskey = db.OpenRecordset("OPServices", dbOpenTable)
  rskey.Index = "RecNmb"
  Set rstext = db.OpenRecordset("QText", dbOpenTable)
  rstext.Index = "RCode"
  qcvt(1) = 1
  qcvt(2) = 1
  qcvt(3) = 1
  qcvt(4) = 2
  qcvt(5) = 2
  qcvt(6) = 2
  qcvt(7) = 3
  qcvt(8) = 3
  qcvt(9) = 3
  qcvt(10) = 4
  qcvt(11) = 4
  qcvt(12) = 4
'--------------------
  rskey.MoveFirst
  Do While Not rskey.EOF
    keywant = rskey!Key
    survey_form = 9
    qtrwant = Forms!PreRep!TheQuarter
    yerwant = Forms!PreRep!TheYear
    'rstally!Abatch = rskey!Batch
    Forms!Main!RpTitle = "Therapy Services - " & rskey!Name
    GoSub DoTally
    msg = rskey!Name & " - Print ?"
    style = vbYesNo + vbCritical + vbDefaultButton2
    response = MsgBox(msg, style)
    If response = vbYes Then    ' User chose Yes.
      DoCmd.OpenReport "Report1" ', acViewPreview
      DoCmd.OpenReport "Report2" ', acViewPreview
    End If
    rskey.MoveNext
  Loop
'--------------------
  rsdata.Close
  rstally.Close
  rskey.Close
  rstext.Close
  
  Set rsdata = Nothing
  Set rstally = Nothing
  Set rskey = Nothing
  Set rstext = Nothing
  Set db = Nothing
Exit Sub
'=================================================
DoTally:
    DoCmd.SetWarnings False
      DoCmd.RunSQL "delete * from Tally;"
      For j = 1 To 20
      For k = 1 To 6
        scores(j, k) = 0
      Next k
      Next j
    DoCmd.SetWarnings True
    rsdata.MoveFirst
    Do While Not rsdata.EOF
      If Len(rsdata!Batch) = 7 Then
        xmonth = Val(Left(rsdata!Batch, 2))
        xquarter = qcvt(xmonth)
        
        If qtrwant = 5 Then xquarter = 5 'avz
        
        xyear = Val(Mid(rsdata!Batch, 4, 4))
        xtype = Nz(rsdata!Type, " ")
        xkey = Nz(rsdata!Service, " ")
      Else
        xquarter = 0
      End If
      If (xtype = 9) And (xkey = keywant) And (xquarter = qtrwant) And (xyear = yerwant) Then
        S(1) = rsdata!Q1
        S(2) = rsdata!Q2
        S(3) = rsdata!Q3
        S(4) = rsdata!Q4
        S(5) = rsdata!Q5
        S(6) = rsdata!Q6
        S(7) = rsdata!Q7
        S(8) = rsdata!Q8
        S(9) = rsdata!Q9
        S(10) = rsdata!Q10
        S(11) = rsdata!Q11
        S(12) = rsdata!Q12
        S(13) = rsdata!Q13
        S(14) = rsdata!Q14
        S(15) = rsdata!Q15
        S(16) = rsdata!Q16
        S(17) = rsdata!Q17
        S(18) = rsdata!Q18
        S(19) = rsdata!Q19
        S(20) = rsdata!Q20
        For k = 1 To 20
          If Nz(S(k), 0) > 0 Then
          scores(k, S(k)) = scores(k, S(k)) + 1
          scores(k, 6) = scores(k, 6) + 1
          End If
        Next k
      End If
    rsdata.MoveNext
    Loop
    '--------------
    For k = 1 To 20
      rstally.AddNew
      'rstally!tbatch = thebatch 'avz
      rstally!SurveyID = 1
      rstally!QNmb = k
      refcode = Format(survey_form, "00") & Format(k, "00")
      rstally!RCode = refcode
      rstext.Seek "=", refcode
      If rstext.NoMatch Then
        flipit = False
      Else
        flipit = rstext!Flip
      End If
      If flipit = True Then
        rstally!VeryGood = scores(k, 1)
        rstally!Good = scores(k, 2)
        rstally!Fair = scores(k, 3)
        rstally!Poor = scores(k, 4)
        rstally!Excellent = scores(k, 5)
        rstally!Total = scores(k, 6)
        If scores(k, 6) > 0 Then
          rstally!VeryGoodPct = scores(k, 1) / scores(k, 6)
          rstally!GoodPct = scores(k, 2) / scores(k, 6)
          rstally!FairPct = scores(k, 3) / scores(k, 6)
          rstally!PoorPct = scores(k, 4) / scores(k, 6)
          rstally!ExcellentPct = scores(k, 5) / scores(k, 6)
        End If
      Else
        rstally!Poor = scores(k, 1)
        rstally!Fair = scores(k, 2)
        rstally!Good = scores(k, 3)
        rstally!VeryGood = scores(k, 4)
        rstally!Excellent = scores(k, 5)
        rstally!Total = scores(k, 6)
        If scores(k, 6) > 0 Then
          rstally!PoorPct = scores(k, 1) / scores(k, 6)
          rstally!FairPct = scores(k, 2) / scores(k, 6)
          rstally!GoodPct = scores(k, 3) / scores(k, 6)
          rstally!VeryGoodPct = scores(k, 4) / scores(k, 6)
          rstally!ExcellentPct = scores(k, 5) / scores(k, 6)
        End If
      End If
      rstally.Update
    Next k
'--------------
  Return
End Sub
/[code]
 

theDBguy

I’m here to help
Staff member
Local time
Today, 14:38
Joined
Oct 29, 2018
Messages
21,358
Hi. If you take a look at the several form search demos available, you might be able to find a technique you like.

To open a report with multiple criteria, you simply supply the criteria in the WhereCondition argument. For example:
Code:
DoCmd.OpenReport "ReportName", , , "Field1=Value1 AND Field2=Value2 AND Field3=Value3"
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 05:38
Joined
May 7, 2009
Messages
19,169
based your report on Query (and your code should be
translated to Total Query).
then you only need to "Filter" the query where
your report is based.
 

Tina49

Registered User.
Local time
Today, 14:38
Joined
Sep 29, 2011
Messages
34
I guess I don't know enough about code to understand what you are saying. I only kind of understand the code listed above. Where are they calling the query listed above. When I manually create a query I have to list criteria to not show nulls. I can manually make that into a report but can't seem to automate the process. I'm looking to print all Therapists with each therapy that they have preforms listed in a sub list with the tally of the survey questions listed. And this code was for a totally different report and I tried to manipulate it for the new report. I think I need some training in reports and VBA. Once again if someone could direct me to a site that explains this I would really appreciate it.
 

Mike Krailo

Well-known member
Local time
Today, 17:38
Joined
Mar 28, 2020
Messages
1,030
Tina49, a report can be based on a named Query or a Table directly. You can also use VBA to set the SQL string directly as the source for the report from your form which is the way I think I would do it in your case. I think Arnel eluded to using a totals query and I agree.

Use a custom totals query, where all the criteria on the form are referred to the controls on the form. See this article here for the various options. To create the necessary code, just create a manual query that has hard coded data for one particular report view. If you can manually make the query and post the SQL code back here, then post a sample database that contains the form that your user inputs the criteria for the report, we could possibly help you get it working. Some of the criteria appears to be based on those custom recordsets and I'm not entirely sure that is even needed if a query could do the same thing.

Based on the posted code shown, there is a lot of criteria involved which does make it more complex but I bet the query solution would work better. This can be done in stages, first get the user input form to simply generate the correct query results to base the report off of. Then use VBA to set the record source of the actual report using a button on the form after the user has entered all the criteria in the form.

I am working on a database that I didn't create. I was asked to create a report which I though wasn't a big deal. However the database is setup completely different then what I am used to and everything is written in code.

That's unfortunate. Reports are real easy to do if the underlying table structure is correct, if not, then it could be a nightmare. So I hope the table structure is sound. I'm not sure what you mean by "everything is written in code". The database has tables right? You make your reports based off of queries on those tables. Code only comes in when there is a special function needed for something or you need to create a custom query on the fly which is what you may need to do in your case.

Since someone else created the database, is there any existing reports? What's wrong with the existing reports?
 

Tina49

Registered User.
Local time
Today, 14:38
Joined
Sep 29, 2011
Messages
34
The existing reports were ok. However they were based on two piece of criteria. Type and Date. There are tables and queries but only two tables out of the 12 had a relationship and there was no relationship to the main table that holds all the data. I did create relationships. The data doesn't exist in some cases so I have to have the criteria of not null in the query. The new report is based on two different types (There are five in all), Therapists, Services, and a tally of the questions. I get how to do reports of simply queries; I just haven't figured out how to pull the data I need into the query. The new reports I created I have simply copied his code and made the necessary adjustments. However this time when I added a new variable and called the field I get an error that the object doesn't exist. I think I am going to have a separate form for this report instead of running all the reports from one form so that the end user can input the criteria. I let you know how it goes....and no there is no referential integrity here. Thank you all for all your helpful suggestions. I will let you know how far I get. Just working on this in the evenings.
 

oldaf294

Registered User.
Local time
Today, 21:38
Joined
Mar 29, 2002
Messages
91
I did all of my reports with queries. Design a query with all the fields required. Set the Criteria to the information needed and produce a form with the fields you need. Add subforms based on other queries if the result requires.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 17:38
Joined
Feb 19, 2002
Messages
42,976
Looks like the app was written by a programmer who knew nothing about database design or Access. I would work around it wherever possible.
 

Tina49

Registered User.
Local time
Today, 14:38
Joined
Sep 29, 2011
Messages
34
I ended up getting it all to work; however, that being said I think I'm going to redo the whole survey database. There is simply too much room for error the way it is set up. Thanks everyone for your help. That being said I could really use a course in VBA. Any recommendations? Free if possible.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 14:38
Joined
Oct 29, 2018
Messages
21,358
I ended up getting it all to work; however, that being said I think I'm going to redo the whole survey database. There is simply too much room for error the way it is set up. Thanks everyone for your help. That being said I could really use a course in VBA. Any recommendations? Free if possible.
Hi. Congratulations! Glad to hear you got it sorted out. The only (free) place I could think of is YouTube. Good luck!
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 17:38
Joined
Feb 19, 2002
Messages
42,976
Make sure that your survey questions are rows in the table rather than columns. That way, you can add new questions without modifying the table schema or form/report/query objects.
 

Users who are viewing this thread

Top Bottom