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

    Help with query based form (2000)

    Hi,

    I've attached a database that I'm having issues with!
    The data that I need to calculate needs to be based on 71 days added onto ArrestDate and displayed in TargetDate. This data won't change.

    I have therefore created a form based on a query that will calculate TotalDays & DaysLeft on a daily basis. I also need to create an additional value of days over, which would be if days left equals zero, then however many days there are over this. I need to base some queries on these values for reports, etc.

    I am having problems with the calculations in qrymain, Total Days & Days Left. I'm not too sure I've grasped the DateDiff function correctly.
    The other issue I'm having is that the controls on FrmMainAdded don't all appear to work as they should. I don't know why.

    I also need to filter CboExisting on FrmMain added dependent upon the area selected.

    I also like to add a facility of the user being able to select Live or Dead cases from QryMain from FrmStartUp. Ideally to be done in coding as opposed to a combo box. I thought that the user would be able to select their appropriate area in FrmStartUp and then click on CmdOpenName, they would then be asked. "There are xxx dead cases for your area, do you wish to view them?"...dependant upon the reply, the user would then have all records filtered on the response dependant upon the value in TblMain of Case Status. Likewise with CboExisting in FrmMainAdded.

    I know thats quite a few questions but any help would be appreciated and would save me several hours of trying to fudge through it.

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

    Re: Help with query based form (2000)

    Let's start with the qryMain query. Please explain what you want Total Days and Days Left to be.

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

    Re: Help with query based form (2000)

    Hi,

    Total Days would be Now()-ArrestDate. So basically the days between the arrest date and today (updated whenever the database is opened).

    Days left would be TargetDate - Now(). So if today is before the target date, the number of days it is before the target date (Has to be less than 71)

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

    Re: Help with query based form (2000)

    I think the definition of Days Left should be
    <code>
    Days Left: IIf([TargetDate]<Date(),0,DateDiff("d",Date(),[TargetDate]))
    </code>
    Does that do what you want?

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

    Re: Help with query based form (2000)

    Yup thats resolved it and covers what is needed.

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

    Re: Help with query based form (2000)

    OK, on to the next question:

    > The other issue I'm having is that the controls on FrmMainAdded don't all appear to work as they should. I don't know why.

    Which ones don't work as they should? What is wrong?

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

    Re: Help with query based form (2000)

    Currently its the navigation controls only & cboExisting. These were added using the wizard, where you select the function of the control through that.

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

    Re: Help with query based form (2000)

    You have selected the wrong action for cmdNext in the wizard. It executes "find next" instead of "go to next record". And the code produces an error when you try to go beyond the first or new record. Change the code for the navigation buttons to

    Private Sub CmdNext_Click()
    On Error GoTo Err_CmdNext_Click
    DoCmd.GoToRecord , , acNext
    Exit Sub

    Err_CmdNext_Click:
    If Not Err = 2105 Then
    MsgBox Err.Description
    End If
    End Sub

    Private Sub CmdPrevious_Click()
    On Error GoTo Err_CmdPrevious_Click
    DoCmd.GoToRecord , , acPrevious
    Exit Sub

    Err_CmdPrevious_Click:
    If Not Err = 2105 Then
    MsgBox Err.Description
    End If
    End Sub

    There is no code behind cboExisting. Did you create this as Combo59, then rename it?

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

    Re: Help with query based form (2000)

    Sorry, its because I'm trying to get this rushed out and I'm making schoolboy errors (the joy's of managerial decisions!!). Indeed you are right and I have added the wrong coding.

    Finally in relation to opening FrmMainAdded from FrmStartUp, is it possible to filter FrmMainAdded & CboExisting based on a message box displaying "There are ",?????, "dead records. Do you wish these to view these?".
    I think you may use the intStore function, but I'm not too sure....

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

    Re: Help with query based form (2000)

    According to tblCaseStatus, Dead = 1. Shouldn't the where-condition use status = 1 instead of status = 2?

    Change the Bound Column property of CboArea on FrmStartArea to 1.
    Change the code for the command button to

    Private Sub CmdOpenMain_Click()
    Dim lngCount As Long
    Dim strWhere As String
    On Error GoTo Err_CmdOpen_Click
    strWhere = "<!t>[AreaNameID]<!/t> = " & Me.CboArea & " AND <!t>[CaseStatusID]<!/t>=1"
    lngCount = DCount("*", "qryMain", strWhere)
    If lngCount = 0 Then
    MsgBox "There are no dead cases here!"
    ElseIf MsgBox("There are " & lngCount & " dead cases." & vbCrLf & _
    "Do you want to view them?", vbQuestion + vbYesNo) = vbYes Then
    DoCmd.OpenForm "frmMainAdded", , , strWhere
    End If
    Exit Sub

    Err_CmdOpen_Click:
    MsgBox "Please select a Unit!", vbOKOnly, "Error!"
    End Sub

    Change the row source of CboExisting on FrmMainAdded to

    SELECT SURNAME, Forename, URN, Forename FROM qrymain WHERE AreaNameID=Forms!FrmStartArea!CboArea

    PS No idea what intStore is.

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

    Re: Help with query based form (2000)

    Thanks for that. I have amended the code slightly as I couldn't open the database and view live files. The coding below seems to work okay:

    Private Sub CmdOpenMain_Click()
    Dim lngCount As Long
    Dim strWhere As String
    Dim strWhere2 As String

    On Error GoTo Err_CmdOpen_Click
    strWhere = "[AreaNameID] = " & Me.CboArea & " AND [CaseStatusID]=1"
    strWhere2 = "[AreaNameID]=" & Me.CboArea & "AND [casestatusID]=2"

    lngCount = DCount("*", "qryMain", strWhere)
    If lngCount = 0 Then
    MsgBox "There are no dead cases here!"
    ElseIf MsgBox("There are " & lngCount & " dead cases." & vbCrLf & _
    "Do you want to view them now?", vbQuestion + vbYesNo) = vbYes Then
    DoCmd.OpenForm "frmMainAdded", , , strWhere
    Else
    DoCmd.OpenForm "frmMainAdded", , , strWhere2
    End If
    Exit Sub

    Err_CmdOpen_Click:
    MsgBox "Please select a Unit!", vbOKOnly, "Error!"

    End Sub

    The only other issue I have is making sure that all new records that are added relate to the area selected from FrmStartUp. Again this is my ineptitude at coding.

    On the load event of FrmMainAdded I've placed.

    Me.CboExisting.DefaultValue = Me.OpenArg

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

    Re: Help with query based form (2000)

    You don't specify the OpenArgs argument of DoCmd.OpenForm, so you can't use it in FrmMainAdded.
    It doesn't make sense to set a default value for CboExisting, since CboExisting is unbound.
    Instead, use the following line in the On Load event of FrmMainAdded:

    Me.AreaNameID.DefaultValue = Forms!FrmStartArea!CboArea

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

    Re: Help with query based form (2000)

    Thanks again.
    The only remaining thing I'm not sure of is how to dynamically alter the list in CboExisting on FrmMain dependent upon what is selected in the strWhere conditions.
    So if the user selects that they want to view all dead cases, that this is opened in FrmMainAdded (as it does) and CboExisting is filtered dependent upon the selection made.
    I'm not too sure whether to this needs to go into FrmStartUp or the onload event of FrmMainAdded

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

    Re: Help with query based form (2000)

    You can use the code behind the command button on FrmStartUp:

    ...
    ElseIf ... Then
    DoCmd.OpenForm "frmMainAdded", , , strWhere
    Forms!frmMainAdded!CboExisting.RowSource = _
    "SELECT Surname, Forename, URN FROM TblMain WHERE " & strWhere
    Else
    DoCmd.OpenForm "frmMainAdded", , , strWhere2
    Forms!frmMainAdded!CboExisting.RowSource = _
    "SELECT Surname, Forename, URN FROM TblMain WHERE " & strWhere2
    End If
    ...

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

    Re: Help with query based form (2000)

    Thank you Hans. As always you're help is vastly appreciated. Maybe one day I'll get to know how to do this thing properly!

Posting Permissions

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