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

    Some form questions (2000)

    Hi,
    1) I have a form with a combobox(CboFindRecord) that finds the records in the form (frmMain). So the user clicks in the combobox and selects a record which is then displayed on the form.The form has the following on the On Open event:
    Me.[AreaID].DefaultValue = Me.OpenArgs
    There is a previous form(frmStart) with a combobox that lists area, its fields are AreaID and AreaName. The On click event of a command button on this form has this:
    DoCmd.OpenForm "FrmMain", , , "[AreaID] = " & Me.CboAreaID, , , Me.CboAreaID
    Basically the main form is filtered on the AreaID selected in CboAreaID selected in FrmStart.

    Question1) How do I get CboFindRecord to also filter on the OpenArg?
    I've tried this:
    SELECT [TblMain].[URNID], [TblMain].[DefSurname], [TblMain].[DefForename], [TblMain].[URN], [TblMain].[AreaID] FROM TblMain WHERE [TblMain].[AreaID]=Me.OpenArgs;

    Question2) Is it also possible to have a Combobox display more than ne column when closed, I have Surname, forename fields which display when the user enters into the combobox, but not when its 'idle'

    Question3) The main form(Main1), has a sub form, the sub form(sub1) also has a sub form(subsub2).
    Main1 records a file, this has a One-To-Many relationship to sub1 which records a date, this is One-To-Many to subsub2, which records actions to be taken. Therefore, for each file there can be multiple dates, with each date having multiple actions. What I'd like to do is display through a message box whether there are any outstanding actions in relation to any of the dates that relate to that specific file. Basically these actions cannot be missed, and I'd prefer the user to be made aware of any outstanding actions without having to scroll through all the dates in relation to that one file. The actions will be recorded as complete once a certain field is filled in with a date, say DateActioned.

    Thats it................whew

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

    Re: Some form questions (2000)

    Sorry for the delay in getting back to you, had a nice long break over the Easter weekend.
    Right, where was I..................
    The tables involved are:
    TblMain, which I don't need to do much with. But TblMain relates to TblHearing.
    TblHearing has four fields:
    HearingID (Autonumber, PK)
    URNID (Number, Linked to TblMain which has this field as PK)
    CourtID (Number, Linked to TblCourt, PK. TblCourt is solely a table listing venues, and only has two records)
    CourtDate(Date/Time).
    This table is linked to TblDirections by HearingID. It has the fields:
    DirectionsID (Autonumber, PK)
    HearingID (Number, FK)
    CourtDirection (Text)
    DueDate(DateTime)
    Reminder (Number)
    CPOAction (Text)
    Comments (Memo)
    DateChecked (Date/Time)
    ConfirmedCourtCPO (Date/Time)
    Just to confirm what I'm trying to do. I need to display a message box (of some form or another) dependant upon whether ConfirmedCourtCPO is Null in TblDirections. I ideally also need to colour code this as in this <!post=post,462130>post<!/post>, which is reliant on what how close due date is. This is mainly so that the user can tell that there are outstanding actions without scrolling through all the records.
    Ideally I'd be trying to get a simple 'There are outstanding actions' message displayed on the Main Form, and then the user could scroll through the entries in TblHearing to find the relevant hearing. I suspect that I'll also need to flag up the relevant hearing somehow..........

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

    Re: Some form questions (2000)

    Perhaps you can use something like the attached (rough) demo. The On Current event of the main form checks if there are outstanding actions and if so, pops up a form listing them. This is the code used:

    Private Sub Form_Current()
    If DCount("*", "qryOutstanding") > 0 Then
    DoCmd.OpenForm "frmOutstanding"
    Else
    DoCmd.Close acForm, "frmOutstanding", acSaveNo
    End If
    End Sub

    (trying to close a form that is not open doesn't harm)

  4. #4
    3 Star Lounger
    Join Date
    Jun 2002
    Location
    Nottingham, Nottinghamshire, United Kingdom
    Posts
    257
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Some form questions (2000)

    Hans

    How did you get the overdue date on frm Outstanding to show as bold red? I can't see any code for this.

    Regards
    David

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

    Re: Some form questions (2000)

    I used conditional formatting. To see how it was done, open frmOutstanding in design view, select the DueDate text box, then select Format | Conditional Formatting...

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

    Re: Some form questions (2000)

    Thanks Hans, thats perfect........works a treat!
    I'm still having problems with question 1 and the following argument:

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

    I get an error "Compile error: Method or Data member not found", highlighting the bold as above. Any ideas?

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

    Re: Some form questions (2000)

    Added later: FindRecord should be RowSource

    1. You can set the Row Source of the combo box in the On Load or On Open event of frmMain:

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

    Note that Me.OpenArgs is kept outside the quotes.

    2. No, the text box part of the combo box only displays the first column whose width is greater than 0. You can use text boxes to display the values of the other columns, for example a text box with Control Source

    =[CboFindRecord].[Column](1)

    The columns start counting at 0, so Column(1) is the second column of the combo box.

    3. Can you provide details about the names of the tables and fields involved?

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

    Re: Some form questions (2000)

    FindRecord was wrong - it should have been RowSource, as should be obvious from the first line of my reply. Sorry!

    (I have no idea how this typo arose)

  9. #9
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Goose Creek, South Carolina, USA
    Posts
    108
    Thanks
    4
    Thanked 0 Times in 0 Posts

    Re: Some form questions (2000)

    Hans:

    You mean sometimes your keyboard goes dyslexic too?!?!

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

    Re: Some form questions (2000)

    Still having problems with this bloomin thing..........
    Its now filtered on the OpenArgs, but when the record is displayed in the Combobox and selected, its not being displayed in the form.......
    I've tried recreating the standard coding as supplied by the Wizard in the after Update event, but this doesn't work either!

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

    Re: Some form questions (2000)

    Does the After Update box in the Event tab of the Properties window contain [Event Procedure]?
    Could you post the code for the After Update event you now have?

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

    Re: Some form questions (2000)

    This is the standardised code, but with this is doesn't filter. However if I remove it, the combobox does filter:

    Dim rs As Object

    Set rs = Me.Recordset.Clone
    rs.FindFirst "[URNID] = " & Str(Me![CboFindRecord])
    Me.Bookmark = rs.Bookmark

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

    Re: Some form questions (2000)

    Is CboFindRecord bound to a field? It should be unbound, i.e. its Control Source should be blank.

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

    Re: Some form questions (2000)

    Yep its control source is blank as below:

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

    Re: Some form questions (2000)

    Very blank indeed. Could you post a stripped down version of your database? I don't think we're going to solve this otherwise.

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
  •