Results 1 to 15 of 15
  1. #1
    Join Date
    May 2003
    Location
    Bristol
    Posts
    9

    Question Unanswered: Search records and display

    This is the first time I have used MS Access and am having problems already. I've made a database to collect information about PC spec, user details and location via a series of combo boxes. I can use the basic "find" function to search for records but what I want to do is create a search form where I can select one or several options via combo boxes (eg. office and CPU type). I then want these to be displayed in a list or table (displaying the fields I I need) and be able to print it. Any help would be appreciated as I have no idea where to go and the help files were very limited.

    Thanks.

  2. #2
    Join Date
    Jul 2002
    Location
    Romania
    Posts
    122

  3. #3
    Join Date
    May 2003
    Location
    Bristol
    Posts
    9
    DannyY,

    Thanks for your reply. Unfortunately I can't seem to make this work but then I don't now anything about code, modules or macros. I ahve followed the instructions (i think!) but when I put dnFltr in the toolbar properties i get the error message: Access couldn't find the toolbar 'dnFltr'. What am I doing wrong? Please excuse my ignorance.

  4. #4
    Join Date
    Jul 2002
    Location
    Romania
    Posts
    122
    When you import the form, make sure you click the Options button in the Import Object dialog. Then check 'Menus and Toolbars' box.

    Good luck

  5. #5
    Join Date
    May 2003
    Location
    Bristol
    Posts
    9
    Got it working now so that the toolbar is there but now got this message:

    Compile error:

    Ambiguous name detected: add_record_button_Click

    As I have no idea what this means I think I'll give up as I don't want to be wasting your time, but thanks for your help.

  6. #6
    Join Date
    Jul 2002
    Location
    Romania
    Posts
    122
    You have two procedures with the same name within the same object.
    This usually happens when you create a button with a wizard, then delete the button from the form and create it again.

    Access just creates the procedure once again.
    Open your form in Design view, go to View-Code and search for add_record_button_Click.

    When you make sure it's there twice, delete one of the procedures (from Private Sub... until End Sub)

    If I had thought it was a waste of time, I wouldn't be here

    Good luck

  7. #7
    Join Date
    May 2003
    Location
    Bristol
    Posts
    9
    OK I'll keep going so long as you don't mind all the questions. I had a couple of procedures I had to remove and now the toolbar is there. Next problem though is:

    The record source 'Select[PC Details].[office] From PC Details;' specified on this form or report does not exist.


    The form and table are called PC details and office is one of the fields. I have a combo box containing the location of our offices which are selected and then saved to the table.

    Any ideas?

  8. #8
    Join Date
    Jul 2002
    Location
    Romania
    Posts
    122
    Sorry, I tried to post a reply yesterday, but I had no 'reply' button available-funny...

    I don't know what to say here... I tested it and it works fine. Can you try to save the recordsource as a query and base your form on that query?

    Dan

  9. #9
    Join Date
    May 2003
    Location
    Bristol
    Posts
    9

    Red face

    Hi Dan,

    I tried this and got 'Syntax error in FROM clause'.

  10. #10
    Join Date
    Jul 2002
    Location
    Romania
    Posts
    122
    OK, let's try a different approach:

    Paste the function below in a module:

    Function SearchRecord(frm As Form, WhatToSearch As String) As Boolean
    '************************************************* ***********
    'Purpose: to locate a record on a form or subform
    'Inputs: frm - Form
    ' WhatToSearch: the string that identifies the record
    'Output: True if record was located
    ' False if record was not found
    '************************************************* ************
    On Error GoTo ErrHandler
    With frm.RecordsetClone
    .findfirst WhatToSearch
    If Not .nomatch Then
    frm.Bookmark = .Bookmark
    SearchRecord = True
    End If
    End With

    ExitHere:
    Exit Function

    ErrHandler:
    MsgBox Err.Number & ": " & Err.Description
    Resume ExitHere
    End Function


    Now, let's calculate WhatToSearch:


    The following code belongs to the form's module - in Declarations section:

    Sub sFilterMyForm()
    '*******************************************
    'Purpose: to inspect the values in combo boxes
    'and call the SearchRecord function
    'Replace: combo1, combo2, combo3 with
    'the actual names of the combo boxes
    'Field1, Field2, Field3 are the names of the
    'fields in the recordsource that return values
    'corresponding to combo1, combo2, combo3
    'respectively
    '*******************************************

    Dim strFltr As String

    If Not IsNull(Me![combo1]) Then
    strFltr = "Field1 = '" & me![Combo1] & "' And "
    End If

    If Not IsNull(Me!combo2) Then
    strfltr = "Field2 = '" & me![Combo2] & "' And "
    End If

    If Not IsNull(Me!combo3) Then
    strfltr = "Field3 = '" & me![Combo3] & "' And "
    End If

    '...and so on for as many combo boxes you have on the form

    If strFltr <> "" Then
    strFltr = Left(strFltr, Len(strFltr)-5
    IfSearchRecord(Me, strFltr) Then
    MsgBox "Search succeeded"
    '----------------------------------------------------------
    'Uncomment the following 2 lines to apply the filter
    'Me.Filter = strFltr
    'Me.FilterOn = True
    '----------------------------------------------------------
    Else
    MsgBox "Record Not Found
    End If
    End If
    End Sub

    Make the necessary name changes, then create a command button on the form and attach sFilterMyForm to it's Click event:

    Private Sub Command1_Click()
    sFilterMyForm
    End Sub

    Let me know

  11. #11
    Join Date
    May 2003
    Location
    Bristol
    Posts
    9
    Dan,

    I have made all the changes and when I click on the search button now get this message:

    Access can't find the macro 'sFilterMyForm'.

    I pasted the code in the form under (General) (Declararions). Is this right?

  12. #12
    Join Date
    Jul 2002
    Location
    Romania
    Posts
    122
    That's because it is not a macro.

    Open the form in Design View.
    Select the button you've created.
    Display the properties.
    Make sure there is nothing written in the OnClick line
    Go to View - Code

    Type sFilterMyForm

    Open the form in Normal View
    Make your selection
    Click the button.

    Got to go now...

    P.S. I assumed all your combos have text values. If numeric, remove the single quotes. If dates, replace the single quotes with #.

  13. #13
    Join Date
    May 2003
    Location
    Bristol
    Posts
    9
    OK, made the change for the ddata stored as a date. Now clicking the sreach button gives a compile error: syntax error and this line of code is highlighted:

    strFltr = Left(strFltr, Len(strFltr)-5

  14. #14
    Join Date
    May 2003
    Location
    Bristol
    Posts
    9
    Think I sorted the code. I added a ) to the end and it seems to have excepted it but now the line below is having a syntax error.

    IfSearchRecord(Me, strFltr) Then

  15. #15
    Join Date
    May 2003
    Location
    Bristol
    Posts
    9
    Dan,

    You were right about persistance. Just needed a space between If and SearchRecord. Thanks so much for your help. Just one final question. Is it possible to have a seperate form as the search form (no selections in the combo boxes) as opposed to the one used to enter of display the data? eg if it was a web page it might be a hyperlink to a search from from the main data entry/display form.

Posting Permissions

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