Results 1 to 3 of 3
  1. #1
    Join Date
    Aug 2011
    Posts
    11

    Question Unanswered: Query to open form in edit mode with filters.

    Hi guys and gals!

    This seems to be the most helpful place out there so I'm pinning my hopes on you guys. I reckon I've done fairly well so far from knowing very little code, etc but I am now stuck.

    I've made database at work for our customers and enquiries/bookings with two tables (enquiries become bookings in the same table with a checkbox.) On the form for the customers, I have a button that opens the enquiry form and fills in the appropriate details already (starts an enquiry off). I didnt like using subforms etc and this way works better for me.

    What I want is for the queries I have (for example to search for customer's surname) to open the customer's form at the first of the matching records (filtered i guess) so I can have access to the button again to start an enquiry.

    Scenario: Someone calls, I ask if they have enquired before and search for them (via surname, forname, address etc). I want to be able to open their record and click the "Start Enquiry" button or find some way of doing this.

    If it helps, all the queries I refer to are parameter based and each has a macro to run it from the switchboard...

    Whew! Thanks in advance!

    EDIT-- I am using Access 2007 BTW
    Last edited by BitofaNoob; 08-12-11 at 06:41. Reason: missed important information

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Once you know the customer's surname (let's call the field customer_surname), you can use:
    Code:
    DoCmd.OpenForm "Customers_Form", , , "customers_surname = '" & <customer's surname> & "'"
    Where Customers_Form is the name of the form to open and <customer's surname> is the string variable (or Value property of an object) containing the customer's surname.
    Have a nice day!

  3. #3
    Join Date
    Aug 2011
    Posts
    11

    Wow Thanks!

    Thanks for your help. I really appreciate it. A few more noobish questions though if you'll indulge me...

    Can I still use this in terms of a parameter query? So will this code prompt the user for the variable (say, Customer Surname).

    I've also just quickly tried to implement this, but i'm not quite sure where to enter it. Im assuming this will not work in the "critera" of the query, and I need to be able to run it from a switchboard.

    So I get the switchboard to "Run Code", right?

    How do I save this code as a function to be referenced in the switchboard?

    Thanks again.


    EDIT--
    Ok, I've got it to work by creating a macro that runs an OpemForm Command with the arguments above. The string is the WHERE condition and to maintain the paramater functionality, I entered:

    Customers, Form, , [Surname] Like "*" & [Please Enter Customer's Surname:] & "*", Edit, Normal

    Where "Customers" is the name of the form and "[Surname]" is the field I wish to search by.


    This is great and does exactly what I want it to do, however, in the interest of not having a macro for each button on the switchboard, how would I save this code as a function so it can be referenced by the "Run Code" Instruction of the switchboard?

    Thanks
    Last edited by BitofaNoob; 08-12-11 at 08:07. Reason: update

Tags for this Thread

Posting Permissions

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