Results 1 to 9 of 9
  1. #1
    Daryl2106 is offline Competent Performer
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2011
    Posts
    167

    OpenQuery and Requery

    Hi,



    Thanks to everyone for their help on my last post.

    I created a subform that displays the records of a select query after selecting the desired data from a single combo box (for instructors name) and then using an OpenQuery macro attached to a button.

    Only 1 record is displayed in the subform each time I hit the button. If I look at the query return there is no change. However, if I close the query and select a new instructors name, although I only get one record in the subform, the query when opened shows all the correct records.

    What is wrong with my macro/subform?

    Thanks and take care,

    Daryl

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    You need to show us the query statements and the macro code. You could attach the project to post to make available for analysis. Really don't need to open query in order to filter the records of form. Requery the form.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    Daryl2106 is offline Competent Performer
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2011
    Posts
    167

    Attached Project

    Hi June7,

    Thanks for the reply. I have attached the project (minus the student's personal info) so I hope everything runs okay. It is the query and form/subform TeacherEnrollmentQuery that I am working on. The original post still applies.

    Any help is greatly appreciated.

    Thanks and take care,

    Daryl

  4. #4
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    The mainform has a combobox bound to Instructor field. The subform has only one textbox displaying the Instructor. Both forms have the same table as source data. There is no need for a subform.

    If purpose of the combobox is to search for an Instructor, cannot use a bound control for search criteria. Selecting an item will change the value in the table. Put an unbound combobox in form header as search control. Set form to Continuous View. Bind the main form to the query. The field expression in the query is not needed. Change criteria to InstructorID. Bring other fields into the query. Can put labels for these fields in the header, data textboxes in Detail. Can set sort order in the query instead of form properties. Can use combobox AfterUpdate event instead of requiring a button click to execute the record search. I normally don't use macros, VBA code instead. Apparently macro can't Requery the form and OpenQuery might be the only way in macro. I would use VBA.

    Best practice would be name parts in separate fields. Then would save instructor ID (the primary key) instead of name and could set up the combobox to sort by last names. What if you have more than one Jane Smith? Maybe not probable but is possible.

    You used wizard to build the form. I don't use design wizards, they do things I don't like and make it harder to edit design. The wizard 'groups' the controls. If you want to be able to move and resize the controls individually, select all controls, right click Layout > Remove.

    Attached is example with your data and revised objects.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  5. #5
    Daryl2106 is offline Competent Performer
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2011
    Posts
    167

    Wow! Thanks

    Hi June7,

    Thanks so much. That was really helpful having the text and file to learn from. I learned many different things from your tutorial. Thanks for the tip on the design wizard and introducing me to continous view.

    Just one final question. If I wanted to create a form to update a record or add a course in the courseinventory table, Is the VBA code similiar to the combo box you designed on the TeachersEnrollmentQuery Form?

    Thanks and take care,

    Daryl

  6. #6
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Not sure, what would be the purpose of the combobox - as search criteria to find a record or as a data entry control? Would the combobox also be to select teacher? The RowSource SQL would be same but VBA code only for filtering form to desired record.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  7. #7
    Daryl2106 is offline Competent Performer
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2011
    Posts
    167
    Hi,

    Yes the purpose of the combobox is as a data entry control and the combobox would be also for selecting the teacher?

    I still don't think I am clear on the difference between VBA and SQL, more so which is used when & where.

    I can get the LastName and FirstName to diplay but only by setting column widths. Of course that only displays one column (LastName) when selected and we have two MacDonalds!

    I tried several variations of the SELECT code but was unsuccessful.

    Any direction is greatly appreiated.

    Take care,

    Daryl

  8. #8
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    VBA (Visual Basic for Applications) is programming that manages user interface functionality. SQL (Structured Query Language) is code to manipulation data. VBA can execute SQL statements (not vice versa). Access also executes SQL statements as means to build saved queries for form/report RecordSource and combo and list box RowSource.

    Your name combobox should include unique ID field as will as name parts. I usually concatenate the name parts. Then the bound column should be the ID and search/filter accomplished with the ID.
    SELECT ID, LastName & ", " & FirstName As TeacherName FROM table ORDER BY LastName & ", " & FirstName;
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  9. #9
    Daryl2106 is offline Competent Performer
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2011
    Posts
    167

    Thank You

    Thanks June7. My database project and knowledge of Access has made great progress thanks to your help. I have learned a lot.

    Everything is working fine at this point, but I am sure I'll have more questions before my Decemebr 16th deadline.

    Take care,

    Daryl

Please reply to this thread with any new information or opinions.

Similar Threads

  1. DoCmd.OpenQuery with Parameters?
    By mwhcrew in forum Queries
    Replies: 5
    Last Post: 10-10-2014, 01:49 AM
  2. Requery
    By Grizz2 in forum Queries
    Replies: 2
    Last Post: 05-31-2011, 10:23 AM
  3. Replies: 1
    Last Post: 03-13-2011, 02:29 PM
  4. Requery fails
    By Dega in forum Forms
    Replies: 3
    Last Post: 09-28-2010, 08:35 AM
  5. Requery?
    By CO711 in forum Forms
    Replies: 0
    Last Post: 08-06-2008, 08:03 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums