Results 1 to 5 of 5
  1. #1
    Join Date
    Oct 2006
    Posts
    26

    Unanswered: Help! Replace problemed conditional macro with VBA for FindRecord

    I have a table, "ExpenseDetails", that possesses a field for "Cost" and "Date Purchased".

    I have two separate text boxes, [SearchByDate] and [SearchByCost],that I am using for two separate record searches. One to search the ExpenseDetails records by "Cost" and the other to search by "Date Purchased". I know that I could use a single text box but for the people who will use this it is seems to be more user friendly this way.

    My Macro is setup as follows:

    Condition Action

    IsNull([SearchByDate]) StopMacro
    OpenTable -> ExpenseDetails
    FindRecord -> =[SearchByDate]
    FilterBySelection


    What I want to do is enter a condition before "FilterBySelection" so that if there is not a record that matches what the user types into the text box a message box will be displayed and the table will be closed.

    Currently, when a search is performed and the value entered in the text box does not match any record the table still opens and filters to the first record.
    Yes, I know, if I have experience with VBA life would be easier. I have the database completely developed but really need help tweaking this last little thing.

    Oh, thank you ahead of time.
    Last edited by tlaurick; 10-06-06 at 08:12. Reason: Add VBA to Title

  2. #2
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    Not big on macros, like most posters here, but you can use DCount in macros, so I think you need to look into that as the criteria for stopping the macro.

    IsNull([SearchByDate]) StopMacro
    OpenTable -> ExpenseDetails
    FindRecord -> =[SearchByDate]
    If((DCount)< 1) -> StopMacro
    FilterBySelection
    Hope this helps!

    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  3. #3
    Join Date
    Oct 2006
    Posts
    26

    VBA Instead of Conditional Macro

    I'm not against using VBA. I think that the DoCmd FindRecord would work for me. It's just that I don't know how to write the following code so that the user can type in a value for "FindWhat". I also am unsure of how to limit the search to just the "Cost" and "DatePurchased" fields.



    DoCmd.FindRecord(FindWhat,[Match AsAcFindMatch=acEntire],[MatchCase],[SearchAsAcSearchDirection=acSearchAll],[SearchAsFormatted],[OnlyCurrentFieldAsAcFindField=acCurrent],[FindFirst])

    I know it is not right, but think it will appear something like this:

    DoCmd.FindRecord "[SearchByDate]",acEntire,False,acSearchAll,acAll,Ture

    I don't see how this search gets which is initiated from Form "ControlPanel", Text Box "SearchByDate" is directed to my Table "ExpenseDetails".

  4. #4
    Join Date
    Oct 2006
    Posts
    26
    I appreciate your willingness to help. I entered If((DCount)< 1) as a condition to run another macro which would display a messagebox, close the "ExpenseDetails" Table, and StopAllMacros.

    Whenever I execute the macro using my textbox search I get an error that reads:

    "The expression you entered has a function name that Microsoft Access can't find"

    I isolated all of the Macro commands and this is the only one that results in an error.

    I'm still searching for a way to use VBA. The catch is I need the filtered recordset to display as 'read only' when the search is complete.

  5. #5
    Join Date
    Oct 2006
    Posts
    26
    Okay, I threw out the whole textbox - macro thing. Life is much easier just creating a button that is linked to a parameter query.

    Thanks everyone for giving up a few minutes of your life on my problem.

Posting Permissions

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