Results 1 to 5 of 5
  1. #1
    D'Anconia is offline Novice
    Windows Vista Access 2007
    Join Date
    Feb 2014
    Location
    Rancho Cucamonga, CA
    Posts
    13

    How do I filter 2nd form recordset from 1st form's content, and return?

    Hi!

    This seems like it should be so simple--basic even--but I'm having a really terrible time with this. How hard can it be to go from one form to another and back?

    I have two tables, each with corresponding forms to display their data. The first table is tSelection, which holds items that are to be or have been graded. The second table is tGrade, which holds the actual grades. For each selection there are 2 to 10 grades (criteria upon which they are to be evaluated). The common field between them is SelectionId. The form for displaying tSelection Data is (what else?) fSelection, and the grade form is fGrade. The record source for fSelection is a select query that uses an already filtered subset of selection records to be displayed (qvSelectedGrds4AudWk). There are two areas I'm having trouble with:
    1. On the fSelection form, there is a button which, when clicked, should bring up the fGrade form showing only those records with the same SelectionId displayed in the txtSelId text box on the fSelection form when it was clicked.
    2. Conversely, once the fGrade form is up, there is a button to return to the previously-displayed tSelection record.

    For problem 1, I tried several things, some of which worked until I tried to return from fGrade to fSelection. Here are 3 of my attempts:
    A. I had a query that would select the grades based on the content of the txtSelId text box displayed on the Selection form:
    SELECT DISTINCT *
    FROM qvSelectedGrds4AudWk
    WHERE SelectionId = Forms!fAuditWorkSelection!txtSelId;
    The query works fine when run as a query. But when I run it from within the form, Access doesn't seem to understand the "Forms!fAuditWorkSelection!txtSelId" part (it asks for it as if it's a parameter to be entered.)
    B. I've also tried running it as a DoCmd.RunSql string, using either the same form control reference or a variable that I've assigned with the SelectionId value. But I guess you can't use such references or variables in a SQL string, as it is rejected as not being a valid SQL statement.
    C. Lastly, I've tried throwing the SelectionId into a 1-record, 1-field table in the fSelection's Current event (tCurrentId), and filtering the Grades on that, which worked fine going into fGrade, but gave me problems returning to fSelection. (It either returned to the top of the recordset (not the Selection from which it came) or else it said there was no current record when it hit the fSelection form.)

    Problem 2 will probably depend on how Problem 1 is solved.

    Without asking me what I did, can someone please just tell me how to do it? I'm really under some pressure here, and this has eaten up way too much of my time. I would be SO grateful for any help!

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    Have you tried a form/subform arrangement?
    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
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    Its possible the query is missing the 'variable' part, assuming SelectionID is numeric...:
    ssql = "SELECT DISTINCT * FROM qvSelectedGrds4AudWk WHERE SelectionId = " & Forms!fAuditWorkSelection!txtSelId

    The field cannot be in the sql statement..it has to resolve.

  4. #4
    D'Anconia is offline Novice
    Windows Vista Access 2007
    Join Date
    Feb 2014
    Location
    Rancho Cucamonga, CA
    Posts
    13
    To June7: Yes, I had considered it, but rejected it for several reasons I won't go into here.

    To ranman256: THANK YOU SO MUCH!!!! That worked like an absolute charm! I just knew it had to be some silly little syntactical error! The hair that I was tearing out may now grow back in peace. I can't begin to tell you what you've spared me. Thanks again!

  5. #5
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    As ranman shows, concatenate variables. Reference to control on form as parameter input is a variable. Glad you got it resolved.
    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.

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

Similar Threads

  1. Replies: 3
    Last Post: 07-20-2012, 11:41 AM
  2. Replies: 2
    Last Post: 03-08-2012, 12:59 PM
  3. Want my form to auto suggest content
    By kjmikich in forum Database Design
    Replies: 4
    Last Post: 12-07-2011, 07:26 AM
  4. Display web content on a form.
    By ser01 in forum Forms
    Replies: 3
    Last Post: 05-30-2011, 10:21 AM
  5. Replies: 9
    Last Post: 02-15-2011, 03:05 PM

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