Results 1 to 10 of 10
  1. #1
    2 Star Lounger
    Join Date
    Sep 2002
    Location
    East Coast(USA)
    Posts
    109
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Search button (Acc 2K)

    Greetings,

    I have a main form (frmHouseHold) and a subform (sfrmApplicants). In both forms I have a field called Social Security Number (HouseHold

  2. #2
    2 Star Lounger
    Join Date
    Sep 2002
    Location
    East Coast(USA)
    Posts
    109
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Search button (Acc 2K)

    Hi Hans,
    Following your steps (please see the attachment), I got it to work for the main form: when I type in SSN from the main form and click the Search button, it returns the record, and works fine. But when I search for SSN from the subform or type in any SSN that doesn't exist in both Main form and Subform,
    I get ' Too few Parameters. Expected 1. message.
    Also, is there a way to clear the SSN from the text box once you get the result?

    Thanks!
    Attached Files Attached Files

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

    Re: Search button (Acc 2K)

    1. The 'too few parameters' error you get probably indicates that you use a non-existing field name in the part after rst.NoMatch. My guess would be that either HouseHoldID or HHoldID is wrong. To find out where exactly the error occurs, temporarily make the line

    On Error GoTo ErrHandler

    into a comment by inserting an apostrophe ' in front of it. The next time the error occurs, you'll be given a choice End, Debug or Help. Choose Debug, and see which line of the code is highlighted. Try to find if there is a wrong name in that line.

    By the way, are tblHouseHold and tblApplicants really linked on HouseHoldID (or HHoldID) and AppID?

    When the error has been found and repaired, uncomment the On Error line by removing the apostrophe.

    2. You can add a line

    Me.txtSearch = Null

    in the "cleaning up" part of the code, after ExitHandler: but before Exit Sub.

  4. #4
    2 Star Lounger
    Join Date
    Sep 2002
    Location
    East Coast(USA)
    Posts
    109
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Search button (Acc 2K)

    Hans,

    Following your suggestions:
    1. rst2.close was the line of code highlighted
    2. To verify if HholdID and AppID are linked, I opened the form in design view, and highlighted the whole subform go to data properties and saw the following:

    Link Child Fields: AppID
    Link Master Fields: HholdID

    Thanks!
    OCM

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

    Re: Search button (Acc 2K)

    Thanks for the details; I'm afraid it doesn't point me to a solution. Would it be possible to post a stripped down version of the database?
    <UL><LI>Make a copy of the database and work with that.
    <LI>Remove all database objects (tables, queries, forms, reports, macros and modules) that are not relevant to the problem.
    <LI>In the remaining table(s), remove most records - leave only the minimum number necessary to demonstrate the problem.
    <LI>Remove or modify data of a confidential nature.
    <LI>Do a compact and repair (Tools/Database Utilities).
    <LI>Make a zip file containing the database; it should be below 100KB.
    <LI>Attach the zip file to a reply.[/list]This will allow other Loungers to look at the problem directly.

  6. #6
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Newbury, Berkshire, England
    Posts
    712
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Search button (Acc 2K)

    >>>>>Remove or modify data of a confidential nature<<<<<
    I found this This Clever DB that Scrambles your data...

    Click the "Attachment" link to download it.

    Note: you CAN'T un-scramble it so make a BACKUP FIRST !!!!

  7. #7
    2 Star Lounger
    Join Date
    Sep 2002
    Location
    East Coast(USA)
    Posts
    109
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Search button (Acc 2K)

    Thanks for the info.

    OCM

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

    Re: Search button (Acc 2K)

    Something along the following lines should do what you want, <img src=/S/crossfingers.gif border=0 alt=crossfingers width=17 height=16>

    Put a text box named txtSearch and a command button cmdSearch on the main form.
    Select the command button.
    Activate the Event tab of the Properties window.
    Click in the On Click event.
    Select Event Procedure from the dropdown list.
    Click the builder button, i.e. the three dots ... to the right of the dropdown arrow.
    The Visual Basic Editor will open.
    Select Tools | References...
    Locate "Microsoft DAO 3.6 Object Library" in the list, and make sure that the corresponding check box is ticked.
    Click OK.
    Make the cmdSearch_Click procedure look like the code in the attachment.

    In the code, tblMain and tblSub are the tables behind the main form and subform. I have assumed that tblMain has a numeric primary key MainID, and the corresponding field in tblSub is LinkID. I have named the SSN field simply that, in both tables. All these names will have to be replaced by the names you have.

    Note: the original attachment contained an error. The line

    rst2.FindFirst "[tblSub].[SSN]=" & Me.txtSearch

    should have been

    rst2.FindFirst "[tblSub].[SSN]=" & Chr(34) & Me.txtSearch & Chr(34)

    This has been corrected
    Attached Files Attached Files

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

    Re: Search button (Acc 2K)

    The attachment to <post#=331723>post 331723</post#> contained an error; it has been corrected. In your implementation, you use a field name HouseHoldID that doesn't exist, it should be HHoldID. This causes the "too few parameters" error.

  10. #10
    2 Star Lounger
    Join Date
    Sep 2002
    Location
    East Coast(USA)
    Posts
    109
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Search button (Acc 2K)

    Hans,
    Thank you very much and the search button now works in both main and subforms, just like I want it.

    Thanks,

    OCM

Posting Permissions

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