Searching Problem

smirnoff

Registered User.
Local time
Yesterday, 20:28
Joined
Apr 8, 2005
Messages
13
I have implemented the search function in this datebase Search02.zip (found here http://www.access-programmers.co.uk/forums/showthread.php?p=319650#post319650 )

I am having one problem. The Search works fine, but whenever I click in the list box for record to be brought up nothing happens. Although it works fine when I click the add new record button and then click the appropriate record in the list box.

tia
-ryan
 
I'm a little bit confused because I downloaded Search02.zip and I do not find a "new record button". Aside from that, what do you have in the AfterUpdate event of the ListBox?
 
Sorry, I tried using the same idea in my form. I have a form that someone can enter a new record or modify an old one. I would like them to be able to search for a older record so they can modify it.
 
A ComboBox works really well for locating existing records or adding new ones from the same control.

Aside from that, what do you have in the AfterUpdate event of the ListBox?
 
Code:
Dim db As DAO.Database
Dim rs As DAO.Recordset

Set rs = Me.Recordset.Clone
rs.FindFirst "[CustomerID] = " & Str(Me![QuickSearch])
Me.Bookmark = rs.Bookmark
 
Try the following code in the AfterUpdate event:
Code:
Private Sub QuickSearch_AfterUpdate()
DoCmd.Requery               ' Get any changes to the table first.
' Find the record that matches the control.
Me.RecordsetClone.FindFirst "[CustomerID] = '" & Str(Me![QuickSearch]) & "'"
If Not Me.RecordsetClone.NoMatch Then
    Me.Bookmark = Me.RecordsetClone.Bookmark
End If
    MsgBox "Could not locate [" & Str(Me![QuickSearch]) & "]"
End Sub
 
I now get an error "Data type mismatch in criteria expression"

One thing I noticed was that it doesnt matter which record I select it always come up as the first record in the table.
 
Oops, Just fixed that. CustomerID should have been TransactionID. Now the search works for the most part. Thanks
 
You're welcome and thanks for posting back with your success.
 
using the same application i get an error that reads:

Run time error "91"
object variable or with block variable not set

using this code:

Code:
Option Compare Database
Option Explicit

Private Sub ClearIt_Click()
On Error GoTo Err_ClearIt

Me.Search = ""
 Me.Search2 = ""
  Me.QuickSearch.Requery
   Me.QuickSearch.SetFocus

Exit_ClearIt_Click:
    Exit Sub

Err_ClearIt:
    MsgBox Err.Description
    Resume Exit_ClearIt_Click

End Sub

Private Sub QuickSearch_AfterUpdate()
    Dim rs As Object

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

End Sub


Private Sub Search_Change()
Dim vSearchString As String

 vSearchString = Search.Text
 Search2.Value = vSearchString
 Me.QuickSearch.Requery

End Sub

and just wanted to say thanks to parker for the original post on how to make the search form
 
Use the QuickSearch_AfterUpdate code I posted earlier in this thread.
 
thanks! that takes care of that problem... now i get type mismatch error? arrrr
 
Is [CustomerID] a string? Is it the field you are looking for? Post your AfterUpdate code.
 
Last edited:
Code:
Option Compare Database
Option Explicit

Private Sub ClearIt_Click()
On Error GoTo Err_ClearIt

Me.Search = ""
 Me.Search2 = ""
  Me.QuickSearch.Requery
   Me.QuickSearch.SetFocus

Exit_ClearIt_Click:
    Exit Sub

Err_ClearIt:
    MsgBox Err.Description
    Resume Exit_ClearIt_Click

End Sub

Private Sub QuickSearch_AfterUpdate()
    DoCmd.Requery
    Me.RecordsetClone.FindFirst "[Main Table]![Name] = '" & Str(Me![QuickSearch]) & "'"
 If Not Me.RecordsetClone.NoMatch Then
    Me.Bookmark = Me.RecordsetClone.Bookmark
 End If
    MsgBox "Could not locate [" & Str(Me![QuickSearch]) & "]"
End Sub


Private Sub Search_Change()
Dim vSearchString As String

 vSearchString = Search.Text
 Search2.Value = vSearchString
 Me.QuickSearch.Requery

End Sub
 
Is there more than one [Name] field in your Recordset? If not then you do not need the [Main Table] reference. Surley the [Name] field is a string field isn't it?

Maybe replace Str(Me![QuickSearch]) with just Me!QuickSearch
 
dont know if this is restating the obvious but my problem is getting the two areas of the search form to work in sync..

at the moment, the portion of the form that allows you to edit the records will not use the main table as its control source, however, the search function of the other part of the form works just fine. it is just that when you click a record in the search box to have it show up in the other portion of the form that i run in to problems. thanks for any help!
 
[name] is a column in the main table of the database
 
How about zipping up what you have and posting it and I'll have a look/see. I'm having some trouble visualizing your issue. It is my old and feeble brain hardening with age. :)
 
hmm too big of a file.. do you have a gmail account or something? i am having trouble uploading...
 

Users who are viewing this thread

Back
Top Bottom