Results 1 to 13 of 13

Thread: Filter (2000)

  1. #1
    4 Star Lounger
    Join Date
    Feb 2006
    Posts
    504
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Filter (2000)

    Hi,

    Currently I have a list box in form a and I have another form b. I am using the following code in form a to open the form b base on the MRN that I selected from list box.

    Me.List16.SetFocus
    DoCmd.OpenForm "b"
    Forms!frmSearchbilling.FilterOn = True
    DoCmd.ApplyFilter WhereCondition:="MRN = " & Me.List16
    Me.Requery

    I am tring to create a Msgbox if form b doesn't have any MRN that match with MRN in list box of form a. I tried different way, but all is not work.

    Any idea can make it working? I am really appreciated.

    Regards

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

    Re: Filter (2000)

    I would test before opening form b, and not open it at all if there are no records to be shown. You can do this using DCount:

    Dim strWhere As String
    strWhere = :="MRN = " & Me.List16
    If DCount("*", "NameOfRelevantTableOrQuery", strWhere) = 0 Then
    MsgBox "There are no records for this MRN.", vbInformation
    Else
    DoCmd.OpenForm FormName:="frmSearchBilling", WhereCondition:=strWhere
    End If

    Substitute the appropriate table or query name.

  3. #3
    4 Star Lounger
    Join Date
    Feb 2006
    Posts
    504
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Filter (2000)

    Thanks, Hans.

    But I got the Compile error: Expected: expression on below code:

    strWhere = :="MRN = " & Me.List16

    Regards

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

    Re: Filter (2000)

    Sorry, should be

    strWhere = "MRN = " & Me.List16

  5. #5
    4 Star Lounger
    Join Date
    Feb 2006
    Posts
    504
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Filter (2000)

    Thanks, Hans.

    But I get the Run-Time error '3707'

    The specified field 'MRN' coud refer to more than one table listed in the From clause of you SQL statement.

    Any idea?

    Regards

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

    Re: Filter (2000)

    In that case, you must prefix MRN with the name of the table it comes from, for example
    <code>
    strWhere = "tblSomething.MRN = " & Me.List16</code>

  7. #7
    4 Star Lounger
    Join Date
    Feb 2006
    Posts
    504
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Filter (2000)

    Thank you so much Hans, it's works.

  8. #8
    4 Star Lounger
    Join Date
    Feb 2006
    Posts
    504
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Filter (2000)

    Hi Hans,

    I just find out there's a problem. The filter is works and I can't edit the record that is filtered. It's seems like only view only. What should I do now?

    Thanks

    Regards

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

    Re: Filter (2000)

    Filtering a form has no influence on whether records can be edited or not. What happens if you open the form directly from the database window? Can you edit the records?

  10. #10
    4 Star Lounger
    Join Date
    Feb 2006
    Posts
    504
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Filter (2000)

    Hi Hans,

    Yes, you are correct. Even I open the form directly from the database window, I can't edit the reords. Form will auto load all the records which is 66678.

    So is this mean that form have problem? Where should I change in order to me to be able edit the records?

    Thanks

    Regards

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

    Re: Filter (2000)

    Open the form in design view.
    Activate the Data tab of the Properties window.
    Make sure that the Allow Edits property is set to Yes, and that the Recordset Type property is set to Dynaset.
    If that does not solve the problem, the Record Source of the form is probably not updatable. Some queries are not updatable, for example totals queries. We'd have to know more about the form's record source to know why it is not updatable.

  12. #12
    4 Star Lounger
    Join Date
    Feb 2006
    Posts
    504
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Filter (2000)

    Hi Hans,

    You are correct. Something is wrong with query, I even can't edit in query. But as long as I change the Dynaset to Dynaset (Inconsistent Updates) of Recordset Type in query & form properties and I am able to edit the record.

    So my question is why Dynaset & Dynaset (Inconsistent Updates) will make difference since both are Dynaset?

    Thanks

    Regards

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

    Re: Filter (2000)

    This probably means that your query is based on more than one table, and that there is no unique index on the "one" side of one of the joins, and/or that referential integrity hasn't been enforced for the corresponding relationship. Setting the Recordset Type to Dynaset (Inconsistent Updates) may have undesired side effects - you could create values that violate the relationship between the tables. So in general, it is better to avoid this.
    Again, we would need to know more about the query to know the exact cause.

Posting Permissions

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