Page 1 of 2 12 LastLast
Results 1 to 15 of 23
  1. #1
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Norfolk, England
    Posts
    744
    Thanks
    0
    Thanked 0 Times in 0 Posts

    3 databases in one. (2000)

    Anybody know how I could go about basing a single database on three areas.
    I want something like a front form that has 3 control buttons for the 3 areas. Say A, B or C. The user clicks on A and the database displays all records in relation to area A.
    I'm guessing the table will be filtered onto the main form dependent upon whether the area equals A,B or C. Presumably through a query, say queryMain.
    The bit where I get a little foggy is the point where this is based on what control is clicked and how to then carry this over to the QueryMain.
    I'm pretty sure that I will only need 1 query that will define the parameters of the database.

    Then another foreseeable pain is that I need to do reports based on those queries. I'm hoping that I don't have to design seperate reports for each are............

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: 3 databases in one. (2000)

    You can specify a Where condition when you open a form or report. Say that the field containing the area is a text field named Area.

    <code>Private Sub cmdFormA_Click()</code>
    <code> DoCmd.OpenForm FormName:="frmSomething", WhereCondition:="Area='A'"</code>
    <code>End Sub</code>

    <code>Private Sub cmdReportA_Click()</code>
    <code> DoCmd.OpenReport ReportName:="rptSomething", View:=acViewPreview, WhereCondition:="Area='A'"</code>
    <code>End Sub</code>

    and similar for B and C.

  3. #3
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Norfolk, England
    Posts
    744
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: 3 databases in one. (2000)

    Hi Hans, Thanks for the reply.
    I've attached yet another database I seem to have found myself working on.
    The opening form would be FrmStart, then linking to FrmMain.
    FrmMain relates to all areas in the sense that all of the Lawyers names from this area are visible. I'm pretty sure that the coding would cover this. Presumably I could run a query to filter the list of names so that only the Lawyers for the selected area are visible. I'm not too sure how to relate this to the control that is selected in FrmStart.
    I've been working on the report side of it, trying to relate the information into seperate queries and then run of specific reports. Is there an easier way to produce the reports without specifying what data goes into each textbox, ie =DCount("*","QryPreviousMonth","(Decision)='Martin Ivory'").
    I'm guessing that something like a crosstab query to start off with will be able to filter this out. But I'm unsure, again, how to link this to the control selected in FrmStart............
    Guidance would be a blessing....
    Attached Files Attached Files

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: 3 databases in one. (2000)

    See attached modified version. I have modified frmStart to open frmMain with an OpenArgument value that is used in the On Load event of frmMain to set the row source of a combo box.

    I have added a form frmReport that opens a modified version of rptLawyerFigures. This report contains two subreports based on queries. This is much more convenient than having to list the lawyers explicitly.
    Attached Files Attached Files

  5. #5
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Norfolk, England
    Posts
    744
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: 3 databases in one. (2000)

    Wow, thanks Hans, didn't expect that....................Thank you for your work...........
    I notice in the coding there is the following on the open event :

    Private Sub Form_Load()
    Me.cboLawyer.RowSource = "SELECT * FROM tblLawyer WHERE LawyerArea=" & _
    Chr(34) & Me.OpenArgs & Chr(34)
    End Sub

    Can the following be added without affecting the coding:

    Private Sub Form_Load()
    Me.cboLawyer.RowSource = "SELECT * FROM tblLawyer WHERE LawyerArea=" & _
    Chr(34) & Me.OpenArgs & Chr(34)
    Me.cboDecision.Rowsource = "SELECT * FROM tblLawyer WHERE LawyerArea ="&_
    Chr(34) & Me.OpenArgs & Chr(34)
    End Sub.

    This hopefully will also limit the list on Decision in FrmMain to the specific area.

    I am impressed with the queries and the fact that they are not limited in the format to specific names. This is something that I wouldn't have known (and still don't) how to do.........

    Thanks for essential doing the database......................... <img src=/S/thankyou.gif border=0 alt=thankyou width=40 height=15>

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: 3 databases in one. (2000)

    >> Can the following be added without affecting the coding

    Yes, of course. I only put in the code for one combo box to show how it is done; it is up to you to complete the forms and reports.

  7. #7
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Norfolk, England
    Posts
    744
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: 3 databases in one. (2000)

    Hi Hans,
    Before I fiddle with the reports to gain more stats I'm hoping that I can use a similar method to what you've used in the other reports. Would you mind explaining the formulas for the expressions:
    DD: Abs(Sum(Not IsNull([QryPreviousMonth].[Lawyer])))
    M: Abs(Sum(Nz([Missed],0)))
    D: Abs(Sum(Nz([Days],0)>5))
    I think I can more or less work them out but I'm not too sure

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: 3 databases in one. (2000)

    The expressions use two "tricks":

    1) True is equivalent to -1 and False is equivalent to 0.
    The expression IsNull([QryPreviousMonth].[Lawyer]) returns True if the Lawyer field is blank, False if it is filled.
    Not IsNull([QryPreviousMonth].[Lawyer]) returns False if the Lawyer field is blank, True if it is filled.
    In Sum(IsNull([QryPreviousMonth].[Lawyer])), each record in which the Lawyer field is blank contributes 0 (so it is not counted), and each record in which the Lawyer field is filled contributes -1. The sum is -(count of records with Lawyer filled).
    The Abs function removes the minus from the result: Abs(-37) = 37, Abs(25) = 25.

    2) The Nz function can be used to replace Null (missing) values by a value of your choice. Since we display ALL lawyers in an area, the Missed and Days fields can be Null (missing). Null values cannot be compared with "real" values: Null > 5 is neither True nor False, but Null. So Iused Nz([Days],0) to replace Null values by 0 values. That makes it possible to apply the Abs(Sum(...)) calculation.

  9. #9
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Norfolk, England
    Posts
    744
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: 3 databases in one. (2000)

    That makes sense, I think.
    My next question is how did you filter the relevant lawyers to the relevant area. As far as I can see there is no coding behind the reports or queries that would relate the report to the area. Is this picked up by the filtering of the entries when the user enters into FrmMain?
    I need to do another two reports that I'm pretty sure that I will be able to base on the reports that you have done, picking out whether the Non-letter log is True of False and all full entries for that month in relation to each area.

  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: 3 databases in one. (2000)

    The On Click event procedure for the command button on frmReports passes a where-condition to the report in the DoCmd.OpenReport instruction:

    Private Sub cmdReport_Click()
    Dim strArea As String
    Select Case Me.frmUnit
    Case 1
    strArea = "Western"
    Case 2
    strArea = "Eastern"
    Case 3
    strArea = "Trials Unit"
    End Select
    DoCmd.OpenReport "ReportLawyerFigures", acViewPreview, , "LawyerArea = " & Chr(34) & strArea & Chr(34)
    End Sub

    When the report opens, it will only display the record that satisfies this where-condition. No adaptation of the queries or the report needed. The subreports of rptLawyerFigures are linked to the main report by LawyerArea, so they also display only the records for this area.

  11. #11
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Norfolk, England
    Posts
    744
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: 3 databases in one. (2000)

    Hi Hans,
    I have nearly finished the database with no further issues. I do have a query with the FrmReport though.
    I understand how the coding works behind the control button cmdReport and that it bases the information on the LawyerArea. How is this information transfered into this coding from the relevant option button, namely: Keuzerondje3, Keuzerondje5,Keuzerondje7?
    I want to produce something similar to what you have done for another report that needs to be in a landscape format. It is a detailed list of all the case that fall within QryPreviousMonth for each area so may be quite an extensive wad of paper. Obviously I don't want this included in the main report due to the size it will be once the database goes 'live'.
    Thanks for your help throughout.

  12. #12
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: 3 databases in one. (2000)

    You don't refer to the individual option buttons in code, but to the value of the option group box containing them. Each option button has an associated Option Value, listed in the Data tab of the Properties window; the Option Group Wizard always suggests 1, 2, 3, ... but you can specify other values if desired. When the user clicks an option button, the Option Value of this option button is assigned to the option group as a whole.

  13. #13
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Norfolk, England
    Posts
    744
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: 3 databases in one. (2000)

    That makes sense and I think I should be able to cope............ <img src=/S/clapping.gif border=0 alt=clapping width=19 height=23>
    Again, thanks for the help

  14. #14
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Norfolk, England
    Posts
    744
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: 3 databases in one. (2000)

    Sorry, as always, one more question.
    Instead of me having multiple forms to produce various reports, would it be possible to have a combobox (CboSelectReport) on FrmReports detailing which report is to be produced and carry this to the coding of:

    Private Sub CmdReport_Click()
    Dim strArea As String
    Select Case Me.frmUnit
    Case 1
    strArea = "Western"
    Case 2
    strArea = "Eastern"
    Case 3
    strArea = "Trials Unit"
    End Select
    DoCmd.OpenReport "ReportLawyerFigures", acViewPreview, , "LawyerArea = " & Chr(34) & strArea & Chr(34)
    End Sub

  15. #15
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: 3 databases in one. (2000)

    Yes, use this:

    ...
    If Me.cboSelectReport.ListIndex = -1 Then
    MsgBox "Please select a report.", vbExclamation
    Me.cboSelectReport.SetFocus
    Exit Sub
    End If
    DoCmd.OpenReport Me.cboSelectReport, acViewPreview, , "LawyerArea = " & Chr(34) & strArea & Chr(34)

Page 1 of 2 12 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •