Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Microsoft Office > Access and Access VBA > Access
Access Discussion of Microsoft Access database design and programming. See also the forums for Access ASP and Access VBA.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
Old March 30th, 2016, 12:34 PM
Registered User
Join Date: Mar 2016
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts
Default Opening A Record in a subform, based on selection in another subform

Hello Everyone,

This is my first post, but often view this site to solve issues. Unfortuantely I have not been able to come up with a solution for my current development. I am working on a Task list with a different type of setup. The main form is a one stop shop for multiple users.

There are two tables

- ID (autonum)
- First Name (text)
- Last Name (text)
- Full Name (Calculated)

-ID (autonum)
- Task (short text)
- Description (Long Text)
- Start Date (short date)
- Assigned To (Lookup to Full Name)
- Etc (Additional fields)

The MainMenu (main form) is set up bound to the Team table. With a dropdown field to select a team member. The form has two subforms (CurrentTaskSub and NewTaskSub). Both Subforms are bound to Tasks. CurrentTaskSub is created from a query of Task where tasks are not complete and past the start date. NewTaskSub is a direct form of the task table.

When a name is selected it uses a parent/child relationship to filter the CurrentTaskSub to tasks assigned to that team member. That part works.

What I want to happen is when you select one of the tasks from CurrentTaskSub, it will populate in NewTaskSub to view all the info on the task and to change or update it. Then it will refresh both Subforms when a change is save in NewTaskSub whether that is completing a task, updating it, or creating a new task.

Here is what I have attempted:

On the CurrentTaskSub subform I select the task field and set that on click it will execute VBA code.

the current code of many I have tried is:

Forms!MainMenu![NewTaskSub].Form.Filter = "[Task] = " & Me.Task
Forms!MainMenu![NewTaskSub].Form.FilterOn = True


'With Forms(NewTaskSub).Recordset
'run a findfirst against that recordset
'NewTaskSub.FindFirst "[Task] = " & Me.Task
'if item not found issue warning
'If NewTaskSub.NoMatch Then MsgBox Me.Task & " not found"
'End With

Both these produce debug errors. I think I am on the right track but can seem to find the solution I am hoping for. Any assistance or advice is appreciated. Thank you.
Old August 21st, 2016, 01:55 PM
Registered User
Join Date: Aug 2016
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts

Maybe this code in the OnClick-event of CurrentTaskSub solves your question:
Dim sSQL As String
Forms!MainMenu![NewTaskSub].Form.RecordSource = sSQL

Last edited by TiemeWoldman; August 21st, 2016 at 01:59 PM..

Similar Threads
Thread Thread Starter Forum Replies Last Post
update subform based upon parent form Disgruntled Access VBA 0 March 24th, 2010 06:47 PM
Filter a subform based on value entered mackoy Access 1 August 25th, 2006 12:27 PM
subform based on a query Vince_421 Access VBA 2 May 10th, 2006 04:38 AM
Subform only may contains 1 record Stanny Access 5 December 21st, 2004 11:17 AM
Filter Subform B Based on Field in Subform A SerranoG Access VBA 3 June 18th, 2004 12:23 AM

Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.