Results 1 to 5 of 5
  1. #1
    3 Star Lounger
    Join Date
    Aug 2002
    Location
    milton keynes, Buckinghamshire
    Posts
    252
    Thanks
    0
    Thanked 0 Times in 0 Posts

    finding records (2003)

    I have created a form to give users view only access of the records in a table that contains names and addresses. Users can currently view each record of names and addresses in turn on the left of the screen and the normal find and replace functionality is enabled. I have left the right of the screen free for the following. I wish to create a bespoke find function which will:

    Disable the current replace functionality
    Display a drop-down box with the names of those records which have any fields matching the criteria typed into a search field
    Allow users to click on a record in the drop-down box and then
    Display all the details of the record that has been selected in the fields that already exist on the left of the screen

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

    Re: finding records (2003)

    > Display a drop-down box with the names of those records which have any fields matching the criteria typed into a search field

    If you don't want to use the built-in search facility (which does offer the option to search across all fields, but doesn't display the results in a combo box), this could become quite complicated. You'd have to create a query with criteria referring to the search text box, in a different row for each field.

  3. #3
    3 Star Lounger
    Join Date
    Aug 2002
    Location
    milton keynes, Buckinghamshire
    Posts
    252
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: finding records (2003)

    Hans

    Never one to be beaten, I have come up with the following within Access VBA:

    Sub GetMySet2()
    Dim myConnection As ADODB.Connection
    Set myConnection = CurrentProject.Connection
    Dim myRecordset As New ADODB.Recordset
    myRecordset.ActiveConnection = myConnection

    Dim mystring As String
    Dim SearchStr As String
    Dim SQLStatement As String

    'SearchStr = InputBox("Please enter the text to be sought")

    'SQLStatement = "SELECT Reference, OfficeName FROM DeliveryLocations WHERE Town LIKE '*" & SearchStr & "*';"
    SQLStatement = "SELECT Reference, OfficeName FROM DeliveryLocations WHERE Town LIKE '*Watf*';"

    mystring = ""
    myRecordset.Open SQLStatement, , adOpenStatic, adLockOptimistic

    On Error GoTo ErrorNoRecords
    myRecordset.MoveFirst

    While Not myRecordset.EOF
    'Debug.Print myRecordset.Fields(1).Value
    mystring = myRecordset.Fields("Reference").Value & vbTab & myRecordset.Fields("OfficeName").Value & vbCr & mystring
    myRecordset.MoveNext
    Wend
    myRecordset.Close
    Set myRecordset = Nothing
    Set myConnection = Nothing

    MsgBox (mystring)
    Exit Sub

    ErrorNoRecords:

    myRecordset.Close
    Set myRecordset = Nothing
    Set myConnection = Nothing
    MsgBox ("No records were found")
    End Sub

    However I have come up against a couple of issues:

    When I type "SELECT Reference, OfficeName FROM DeliveryLocations WHERE Town LIKE '*Watf*';"
    into a normal query it returns 2 records. But when I use it in the statement myRecordset.open SQLstatement it returns no records.
    When I use the SQL statement "SELECT Reference, OfficeName FROM DeliveryLocations WHERE Town LIKE 'Watford';"
    in the my recordset.open statement it returns the required 2 records.
    Does the recordset statement not like wild cards?

    Ideally I would like to run the above form within excel. Do I need to just change the statement: Set myConnection = CurrentProject.Connection
    to: Set myConnection = FullfileAddress.Connection

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

    Re: finding records (2003)

    If you want to search on just the Town field, you can easily use a query.

    In your code, you are using ADO, so you should use % as wildcard character instead of *.

    From Excel, you'd use something like

    Dim myConnection As New ADODB.Connection
    myConnection.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source:=CatabasesMyDatabase.mdb;"

    At the end of the code, you should close the connection before setting it to Nothing:

    myConnection.Close
    Set myConnection = Nothing

    This is different from running the code within the database (it doesn't make sense to close the connection from within the database itself).

  5. #5
    3 Star Lounger
    Join Date
    Aug 2002
    Location
    milton keynes, Buckinghamshire
    Posts
    252
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: finding records (2003)

    Thanks Hans. With a bit of adaptation of your code the following works

    Dim myConnection As ADODB.Connection
    Dim strconxn As String
    strconxn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=Documents and SettingsapergunasMy DocumentsMy KPMGFinanceIT PurchasingCardboxCardBox.mdb;Persist Security Info=False"
    Set myConnection = New ADODB.Connection
    myConnection.Open strconxn
    Dim myRecordset As New ADODB.Recordset
    myRecordset.ActiveConnection = myConnection

Posting Permissions

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