Results 1 to 3 of 3
  1. #1
    2 Star Lounger
    Join Date
    Nov 2004
    Location
    London, United Kingdom
    Posts
    152
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Using combo box to find a record (2000)

    Hi there

    I had a combo box on my access form that I used to find a record on the form. This was set up using the wizard. The record that is beeing looked up is the primary key. However after some recent changes to my form (I added a subform), this combo box no longer works. I therefore deleted the combo box and tried to put it on again. However when I selected the field that I needed in the wizard I get the message, "Data cannot be retreived from the source you have selected. You must select a different table or query to continue in the wizard." And I can not create the combo box.

    Any help would be much appreciated

    Amanda

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

    Re: Using combo box to find a record (2000)

    You can create the combo box without the wizard, and create the code for the After Update event of the combo box yourself. The code generated by the wizard looks like this:

    Private Sub cboSearch_AfterUpdate()
    Dim rs As Object
    Set rs = Me.Recordset.Clone
    rs.FindFirst "[NameOfField] = " & Nz(Me.cboSearch, 0)
    If Not rs.EOF Then
    Me.Bookmark = rs.Bookmark
    End If
    End Sub

    where cboSearch must be replaced with the name of the combo box and NameOfField must be replaced with the name of the field you want to search in.

    Notes:
    The above code assumes tnat the field is a number field. If it is a text field, change the line

    rs.FindFirst "[NameOfField] = " & Nz(Me.cboSearch, 0)

    to

    rs.FindFirst "[NameOfField] = " & Chr(34) & Nz(Me.cboSearch, "") & Chr(34)

    and if it is a date field, change it to

    rs.FindFirst "[NameOfField] = #" & Format(Nz(Me.cboSearch, 0) & "#"

  3. #3
    2 Star Lounger
    Join Date
    Nov 2004
    Location
    London, United Kingdom
    Posts
    152
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Using combo box to find a record (2000)

    Hans

    Thanks a million that works a treat.

    Amanda

Posting Permissions

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