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

    Condition problem (2K)

    Hi,
    I have the following condition in the Open event of a form:

    Me.CboFindName.RowSource = "SELECT [Tbldefendant].[SURNAME], " & _
    "[Tbldefendant].[Forename], [Tbldefendant].[URN], [Tbldefendant].[AreaName] FROM Tbldefendant " & _
    "WHERE [Tbldefendant].[AreaName]=" & Me.OpenArgs & " ORDER BY SURNAME"

    However it doesn't appear to work, I can't quite work out where I'm going wrong....

    Any assistance would be appreciated

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

    Re: Condition problem (2K)

    If AreaName is a text field, you must put quotes around the value:

    ...
    "WHERE [Tbldefendant].[AreaName]=" & Chr(34) & Me.OpenArgs & Chr(34) & " ORDER BY SURNAME"

    Chr(34) is the double quote <code>"</code>.

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

    Re: Condition problem (2K)

    Thanks Hans, appreciated as always!

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

    Re: Condition problem (2K)

    Argh. I now have the problem where the Combobox is not selecting the name if the person has the same surname and the record is second in the list. Like....
    JONES, Adam
    JONES, Steve
    Only JONES, Adam can be selected.

    The is the form open event:

    Private Sub Form_Load()
    DoCmd.ShowToolbar "Menu Bar", acToolbarNo
    Me.Combo89.RowSource = "SELECT [Tbldefendant].[SURNAME], " & _
    "[Tbldefendant].[Forename], [Tbldefendant].[URN], [Tbldefendant].[AreaName] FROM Tbldefendant " & _
    "WHERE [Tbldefendant].[AreaName]=" & Chr(34) & Me.OpenArgs & Chr(34) & " ORDER BY SURNAME"
    Me.[AreaName].DefaultValue = Chr(34) & Me.OpenArgs & Chr(34)
    End Sub

    And the Cbo event.
    Private Sub Combo89_AfterUpdate()
    ' Find the record that matches the control.
    Dim rs As Object

    Set rs = Me.Recordset.Clone
    rs.FindFirst "[SURNAME] = '" & Me![Combo89] & "'"
    If Not rs.EOF Then Me.Bookmark = rs.Bookmark
    End Sub

    I've come across this problem before but I cannot find the post it related to. Any ideas?

    Sorry, code adjusted since posting

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

    Re: Condition problem (2K)

    Does tblDefendant have a unique ID field, for example an AutoNumber field? If so, what is the name of that field?

  6. #6
    5 Star Lounger
    Join Date
    Apr 2003
    Location
    Hampshire, United Kingdom
    Posts
    602
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Condition problem (2K)

    The problem is with this line:
    rs.FindFirst "[SURNAME] = '" & Me![Combo89] & "'"

    This will find the first occurrence of the selected surname, and doesn't care what the forename is. If you don't have an index field (see HansV's post) you could use a calculated field - a contacentation of [SURNAME] and [FORENAME] - to return the unique values you need.
    Waggers
    If at first you do succeed, you&#39;ve probably missed something.

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

    Re: Condition problem (2K)

    Yes, Tbldefendant has a unique ID called ID.

    I presumed this was the reason behind it not identifying the second surname in the list. I have fiddled with the code to insert it but couldn't get it to work.

  8. #8
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Condition problem (2K)

    What does your code look like now?

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

    Re: Condition problem (2K)

    Change the code to set the row source to

    Me.Combo89.RowSource = "SELECT [TblDefendant].[ID], [Tbldefendant].[SURNAME], " & _
    "[Tbldefendant].[Forename], [Tbldefendant].[URN], [Tbldefendant].[AreaName] FROM Tbldefendant " & _
    "WHERE [Tbldefendant].[AreaName]=" & Chr(34) & Me.OpenArgs & Chr(34) & " ORDER BY SURNAME"

    Increase the Column Count of the combo box by 1, and insert <code>0;</code> (the digit 0 followed by a semicolon) in front of the Column Widths property.

    Change the line
    <code>
    rs.FindFirst "[SURNAME] = '" & Me![Combo89] & "'"
    </code>
    in the After Update event of the combo box to
    <code>
    rs.FindFirst "[ID] = " & Me![Combo89]</code>

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

    Re: Condition problem (2K)

    Thanks Hans, I was pretty close. The only bit I was getting wrong was the rs.FindFirst "[ID] = " & Me![Combo89] .
    Thank you for your help.

Posting Permissions

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