Results 1 to 6 of 6
  1. #1
    mond007's Avatar
    mond007 is offline Advanced Beginner
    Windows 7 Access 2007
    Join Date
    May 2010
    Posts
    52

    Using parameterised Query as Record Source on Main Form

    Hi,



    I have a Table Tbl_Starter_Leaver and Tbl_Dept. My main form consists of all the fields from source table and this works fine. Incidentally, 70 to 80 depts want to enter data for their respective departments and as such they would only want to see their own departments records. So as the theory goes I think the way forward would be to use a combobox which has a list of the current depts who have entered data into the system.

    Click image for larger version. 

Name:	parameter_img_rejig.jpg 
Views:	24 
Size:	76.0 KB 
ID:	25833

    Just to test this I have managed to get a parameterised query working (Hurrah) : Qry_starter_leaver.
    I understand that I can now :

    1) Setup the combobox at the top header of the main Form
    2) Set the Record Source of my main form to be the results of the parameterised query....

    I would like to ask how this will work in practice ...
    (I have been told Tbl_starter_leaver are synonymous to Qry_starter_leaver so any insert into the query result would insert a records into the table ?
    At the moment new records are inserted into the Tbl_Starter_Leaver table where/how will records be inserted into the table if the query results are set as the record source ?

    Hope the question is clear. Thanks in advance.

  2. #2
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    If this is a critical requirement
    ...70 to 80 depts want to enter data for their respective departments and as such they would only want to see their own departments records.
    , then I think you need to focus on how to do this as you move through sign on and all forms, reports.... .
    Since people can join and leave a department, and since a department could be renamed at anytime, these factors should be considered in your design.

    You may get some ideas from Steve Bishop's youtube videos --here is one about user access levels.

    I suggest you model the necessary structures to support your requirements, and test it with some mock data --before getting too fancy with forms and reports that you might have to redesign because of your "user access requirement to only see their own records". Do you have a group (senior manager/CEO...) that would want to see all data?

  3. #3
    mond007's Avatar
    mond007 is offline Advanced Beginner
    Windows 7 Access 2007
    Join Date
    May 2010
    Posts
    52
    Hi,

    Thanks for the reply, I am beyond the design stage feel I have it all covered I had reduced my question, from a very large solution, to simplify the response. There is no user login screen or need for any user access control as only a handful to depart leads/admin will enter department starter leaver DB. There is no secrecy required, its all open book. There is only the need to see and enter data for your own dept.
    I have worked out that the combobox value can be used in the form of a filter for the record - entered a wild card can retrieve all.

    See my other post :
    https://www.accessforums.net/showthr...737#post323737

    Click image for larger version. 

Name:	Starter_Leaver_Table_Layout.jpg 
Views:	20 
Size:	162.3 KB 
ID:	25844

    I am using the following criteria within the Query to limit the record selection.

    Like [Forms]![Frm Starter Leaver]cboSelectedPtSubDept & *

    My question still remains as above :

    At the moment new records are inserted into the Frm_Starter_Leaver linked to Qry_starter_leaver query where/how will records be inserted into the table if the query results are set as the record source ?

    Thanks in advance.
    Last edited by mond007; 09-19-2016 at 12:37 AM. Reason: Typo

  4. #4
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 8 Access 2013
    Join Date
    Aug 2013
    Posts
    7,862
    If the combobox is set up correctly, you can reference it in your parameterized query. IIRC, binding your form to that query will be the only thing left to do. However, you may need to requery the form in the Combobox's AfterUpdate event.

    As for editing the data in the table(s). Use your form's properties that are listed within the Property Sheet (udder the Data tab) to restrict or allow edits.

  5. #5
    mond007's Avatar
    mond007 is offline Advanced Beginner
    Windows 7 Access 2007
    Join Date
    May 2010
    Posts
    52
    Hi
    "binding your form to that query will be the only thing left to do".

    I am unsure of how to "bind" the "Form" to the "Query" ?.

    The way I have tried to do this is to change Record Source to Qry_Starter_Leaver by PT but when I run it show no data in the Form.
    I am sure I am missing the vital missing piece here. I have a button to test the fetching/running of the query and when I press it it runs the query but still does not show the records.
    (I understand the requery part, but I have to get the Main Frm working first)

    If I look at the form fields or look at Add Existing fields it only shows fields from Tables not queries.

    Click image for larger version. 

Name:	Set Qry Source.jpg 
Views:	10 
Size:	163.8 KB 
ID:	25849

    Do the fields on the main form now have to originate from the query ?. If how does one achieve this?

    Thanks in advance

    ps my original question :
    At the moment new records are inserted into the Frm_Starter_Leaver linked to Qry_starter_leaver query, how will records be inserted into the table if the query results are set as the record source ?

  6. #6
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 8 Access 2013
    Join Date
    Aug 2013
    Posts
    7,862
    Quote Originally Posted by mond007 View Post
    ...If I look at the form fields or look at Add Existing fields it only shows fields from Tables not queries. ...
    If you are using Forms and subforms you need to make sure you are assigning the correct query to the correct form. You can assign objects like Tables and Queries by adding the name of the object to the form's RecordSource. You can do this via the Property Sheet, under the Data tab. You can select Objects via the pulldown for the RecordSource.

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

Similar Threads

  1. Replies: 4
    Last Post: 09-08-2016, 06:35 AM
  2. Replies: 4
    Last Post: 08-09-2015, 10:03 AM
  3. Replies: 3
    Last Post: 05-24-2013, 04:39 PM
  4. Replies: 2
    Last Post: 05-23-2013, 08:29 AM
  5. Parameterised IN (SELECT ...
    By asearle in forum Queries
    Replies: 1
    Last Post: 07-27-2010, 09:48 AM

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
  •  
Other Forums: Microsoft Office Forums