Results 1 to 15 of 15
  1. #1
    2 Star Lounger
    Join Date
    Jul 2005
    Location
    North Carolina, USA
    Posts
    195
    Thanks
    4
    Thanked 0 Times in 0 Posts

    Combo Box Search Trouble (2003)

    Hi, I have a little problem that would like to get a second opinion. I have two databases (1) without any tabs in the form, I have a search combo box to find record using the below code; and it works fine.

    Private Sub cobSearchInstructors_AfterUpdate()
    ' Find the record that matches the control.
    Dim rs As Object

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

    (2) The second database have tabs included in the form and the combo search doesnt change records, but keeps the new file inside the combo box. I even tried the control wizard and the new created combo box doesnt work. So, I ended up using the below code that find the new record from the combo box. I would like to know what the above code could not work.

    Private Sub cobSearchData_AfterUpdate()
    ' Find the record that matches the control.
    Dim rs As Object
    stDocName = "frmPersonnel"

    Set rs = Me.Recordset.Clone

    stLinkCriteria = "[PID]=" & "'" & Me![cobSearchData] & "'"
    DoCmd.OpenForm stDocName, , , stLinkCriteria
    If Not rs.EOF Then Me.Bookmark = rs.Bookmark

    End Sub

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

    Re: Combo Box Search Trouble (2003)

    What is the data type of the PID field?

    Why do you have a line

    If Not rs.EOF Then Me.Bookmark = rs.Bookmark

    without a FindFirst line? And what is its purpose if you're opening another form?

  3. #3
    2 Star Lounger
    Join Date
    Jul 2005
    Location
    North Carolina, USA
    Posts
    195
    Thanks
    4
    Thanked 0 Times in 0 Posts

    Re: Combo Box Search Trouble (2003)

    PID has a text data type. The line was a mistake. The search combo box is located in the same form. When I look for a record and click or press ENTER, the information doesnt update, so to fix the problem I make it open the same form that is located and it will retrieve the info for the new record.

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

    Re: Combo Box Search Trouble (2003)

    Could you post a stripped down copy of the database with the problem? See <post#=401925>post 401925</post#> for instructions.

  5. #5
    2 Star Lounger
    Join Date
    Jul 2005
    Location
    North Carolina, USA
    Posts
    195
    Thanks
    4
    Thanked 0 Times in 0 Posts

    Re: Combo Box Search Trouble (2003)

    Not a problem.

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

    Re: Combo Box Search Trouble (2003)

    OK, I'll wait...

  7. #7
    2 Star Lounger
    Join Date
    Jul 2005
    Location
    North Carolina, USA
    Posts
    195
    Thanks
    4
    Thanked 0 Times in 0 Posts

    Re: Combo Box Search Trouble (2003)

    This stripped version is working like a champ, now. can you try the below code instead the one I have in it. I think most of the problem that happen was in the placement of the codes in the form.

    Private Sub cobSearchInstructors_AfterUpdate()
    ' Find the record that matches the control.
    Dim rs As Object

    Set rs = Me.Recordset.Clone
    rs.FindFirst "[fldInstructor] = '" & Me![cobSearchInstructors] & "'"
    If Not rs.EOF Then Me.Bookmark = rs.Bookmark
    End Sub
    Attached Files Attached Files

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

    Re: Combo Box Search Trouble (2003)

    The combo box cobSearchData has the following properties:

    Column Count: 2
    Column Widths: 0";1.7"
    Row Source: SELECT tblDataPersonnel.PID, tblDataPersonnel.LastName FROM tblDataPersonnel;
    Bound Column: 1

    This means that the first column (the PID field) is hidden, and that the second column (the LastName field) is displayed. However, since the Bound Column property is 1 (i.e. the first column), the value of the combo box is not the last name, but the PID of the selected item.

  9. #9
    2 Star Lounger
    Join Date
    Jul 2005
    Location
    North Carolina, USA
    Posts
    195
    Thanks
    4
    Thanked 0 Times in 0 Posts

    Re: Combo Box Search Trouble (2003)

    Hans, I want to use the code

    Private Sub cobSearchData_AfterUpdate()
    ' Find the record that matches the control.
    Dim rs As Object

    Set rs = Me.RecordsetClone
    rs.FindFirst "[PID] = '" & Me![cobSearchData] & "'"
    If Not rs.EOF Then Me.Bookmark = rs.Bookmark

    Me.cobSearchData.Value = Clear
    Me.cobSearchData.Requery
    End Sub

    In the provided Sample. It looks cleaner and does not use the DoCmd.OpenForm like the previous code which it is already open. I tried the DoCmd.FindRecord or GoToRecord but it doesnt look like it works. What am I missing. Any suggestion.

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

    Re: Combo Box Search Trouble (2003)

    I'm sorry, what exactly do you want? The code in your post works as intended in the sample database from higher up in this thread, so why would you want to use DoCmd.FindRecord or DoCmd.GoToRecord?

  11. #11
    2 Star Lounger
    Join Date
    Jul 2005
    Location
    North Carolina, USA
    Posts
    195
    Thanks
    4
    Thanked 0 Times in 0 Posts

    Re: Combo Box Search Trouble (2003)

    Sorry for the confusion. I would like to get the previous code posted to work instead the one I have currently working. using the rs.FindFirst... I cant get it to work and post the information in the form.

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

    Re: Combo Box Search Trouble (2003)

    I'm getting more and more confused. The code using FindFirst does work. If you don't want that, can you please explain clearly what you want to accomplish?

  13. #13
    2 Star Lounger
    Join Date
    Jul 2005
    Location
    North Carolina, USA
    Posts
    195
    Thanks
    4
    Thanked 0 Times in 0 Posts

    Re: Combo Box Search Trouble (2003)

    If you open the sample database, you will notice in the form the procedure for cobSearchData Afterupdate it is not using the below code

    Private Sub cobSearchData_AfterUpdate()
    ' Find the record that matches the control.
    Dim rs As Object

    Set rs = Me.RecordsetClone
    rs.FindFirst "[PID] = '" & Me![cobSearchData] & "'"
    If Not rs.EOF Then Me.Bookmark = rs.Bookmark

    End Sub

    I tried the above code in the sample database but the cobSearchData doesnt post the search record information in the form or doesnt change the current form information after the click. I would like to get to work.

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

    Re: Combo Box Search Trouble (2003)

    But it does work! See the attached version.
    Attached Files Attached Files

  15. #15
    2 Star Lounger
    Join Date
    Jul 2005
    Location
    North Carolina, USA
    Posts
    195
    Thanks
    4
    Thanked 0 Times in 0 Posts

    Re: Combo Box Search Trouble (2003)

    Thanks Hans. You are the best.

Posting Permissions

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