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

    OpenArg again..... (2000)

    Hi,
    I have this code:

    Private Sub CmdReport_Click()
    If DCount("*", "QryOutstandingForm1", "[ConfirmedCourtCPO] Is Null ") > 0 Then
    Dim stDocName As String
    Dim stLinkCriteria As String
    stDocName = "FrmReportfromMain"
    DoCmd.OpenForm stDocName, , , stLinkCriteria
    Else
    MsgBox "The are no Outstanding Directions", vbInformation, "Note"

    End If

    End Sub

    What I

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

    Re: OpenArg again..... (2000)

    Please explain what you want to accomplish. What OpenArgs?

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

    Re: OpenArg again..... (2000)

    I want to filter on AreaID, I seem to be having problems with a previous database of about a week ago (it used to work!).
    The OpenArgs relates to FrmStartUp where
    DoCmd.OpenForm "FrmMain", , , "[AreaID] = " & Me.CboAreaID, , , Me.CboAreaID
    and then this filters the main form where any record that has been entered relates to this area via AreaID on the Main form

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

    Re: OpenArg again..... (2000)

    Nigel, could you explain in detail what you are trying to accomplish and in what way the code fails? Thank you.

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

    Re: OpenArg again..... (2000)

    <P ID="edit" class=small>(Edited by armitageshanks on 11-May-05 09:10. Forgot to attach file!!)</P>Sorry me being flippant and somewhat vague.
    Attached is a stripped database.
    The user starts with FrmStartUp. Here they select an AreaID from CboAreaID. Once selected, then the click CmdStart which opens FrmMain and filters on the selection made in CboAreaID. Therefore all records visible or added relate to that area.
    Once on FrmMain the user can cycle through the records viewing the relevant details from the form. In FrmDirectionSub1 there are a list of tasks that need to be done and a due date.
    As the user cycles through the records, if there are any outstanding entries in FrmDirectionSub1 then FrmOutstanding is displayed. This will relate to all entries in FrmHearingSub and its related entries in FrmDirectionSub1. An outstanding action is where the field ConfirmedCourtCPO is Null in FrmDirectionSub1. So basically each entry in FrmMain can have multiple entries in FrmHearingSub of which each of these entries can have multiple entries in FrmDirectionSub1.
    FrmOutstanding is based on QryOutstanding which checks to see if each record in TblMain (of which FrmMain is based) has any null values in TblDirection.
    The problem lays with FrmOutstanding. I seem to be unable to get it to 'refresh'. I believe this is because the form isn't properly closing, so that the data that is being displayed relates to the first entry. If I manually close the form the data is refreshed.
    The other problem I'm having is with CmdReport, on the bottom left of FrmMain. CmdReport should open FrmReportfromMain that is based on QryOutstandingForm1. QryOutstandingForm1 checks to see if there are any null values in ConfirmedCourtCPO as above and assigns a value to the entry dependant upon how close today is to the due date. The value relates to a bitmap colour.
    FrmReportfromMain doesn't show any records relating to the specific area as explained further up this post. I can't find out where I'm going wrong.
    I hope that makes more sense than my previous posting. If you need me to clarify things further let me know.

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

    Re: OpenArg again..... (2000)

    About frmOutstanding: you must requery this form as the user moves from record to record in frmMain:

    Private Sub Form_Current()
    If DCount("*", "QryOutstanding") > 0 Then
    DoCmd.OpenForm "FrmOutstanding"
    Forms!frmOutStanding.Requery
    Else
    DoCmd.Close acForm, "frmOutstanding", acSaveNo
    End If
    End Sub

    The criteria for AreaID in the query QryOutstandingForm1 should be [Forms]![FrmMain]![AreaID] instead of [Form]![FrmMain]![AreaID]

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

    Re: OpenArg again..... (2000)

    Thanks Hans, you're a star!!! <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

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

    Re: OpenArg again..... (2000)

    Argh, again I've another question..........I must apologise!!!
    In FrmMain there is CboFindRecord that does what it says on the tin. It lists records in relation to the AreaID and if the record is selected, displays it on FrmMain.
    Its row source is as follows:

    SELECT [TblMain].[DefSurname], [TblMain].[DefForename], [TblMain].[URN], [TblMain].[AreaID] FROM TblMain WHERE ((([TblMain].[AreaID])=2)) ORDER BY [TblMain].[DefSurname];

    On FrmMain I've got an OpenArg which filters FrmMain dependant upon what is selected in FrmStart.
    On the load event of FrmMain is:

    Me.CboFindRecord.RowSource = "SELECT [TblMain].[URNID], [TblMain].[DefSurname], " & _
    "[TblMain].[DefForename], [TblMain].[URN], [TblMain].[AreaID] FROM TblMain " & _
    "WHERE [TblMain].[AreaID]ORDER BY TblMain.DefSurname=" & Me.OpenArgs

    Which basically sets the content of CboFindRecord to the AreaID selected.

    I cannot get the Surnames in CboFindRecord to display alphabetically. I presume that it is due to the rowsource being set in the On Load event of FrmMain. I've tried setting the SQL of CboFindRecord to DefSurname>Ascending, but with no result.

    I'll promise to leave you alone after this one!!

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

    Re: OpenArg again..... (2000)

    The SQL for the row source in the On Load is wrong. Change it to

    Me.CboFindRecord.RowSource = "SELECT [TblMain].[URNID], [TblMain].[DefSurname], " & _
    "[TblMain].[DefForename], [TblMain].[URN], [TblMain].[AreaID] FROM TblMain " & _
    "WHERE [TblMain].[AreaID]=" & Me.OpenArgs & " ORDER BY DefSurname"

    (you had mixed the ORDER BY and WHERE parts together)

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

    Re: OpenArg again..... (2000)

    Again, thank you, this works fine

Posting Permissions

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