Open List Items Edit Form to a Specific Record (1 Viewer)

Tophan

Registered User.
Local time
Today, 09:17
Joined
Mar 27, 2011
Messages
362
Hello,

I have a client task sheet form with a combo box cboJobName that lists all the jobs for that specific client. I have the List Items Edit Form property set to frmClients (which includes the subform frmJobs).

How can I open the frmClients to the Client whose task sheet I am working in when I need to add a new job to the combo box cboJobName?
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 09:17
Joined
Feb 19, 2002
Messages
42,970
Use the "where" argument of the OpenForms method. Here's an example

DoCmd.OpenForm "frmVerificationPhase", , , "DepID = " & Me.DepID, , acWindowNormal, Me.Parent.Name
 

Tophan

Registered User.
Local time
Today, 09:17
Joined
Mar 27, 2011
Messages
362
Thank you but I don't understand exactly where I would enter this command. Is there a particular event for the cboJobName I should enter this under?
 

theDBguy

I’m here to help
Staff member
Local time
Today, 06:17
Joined
Oct 29, 2018
Messages
21,357
Hi. Not sure I am following; the List Items Edit Form is usually used to add a new item to the Combobox list.
 

Tophan

Registered User.
Local time
Today, 09:17
Joined
Mar 27, 2011
Messages
362
Hi. Not sure I am following; the List Items Edit Form is usually used to add a new item to the Combobox list.
Hi.

I'll give some more detail.

The form frmClients includes the subform frmJobDetails which is linked to the frmClients by the ClientID. In frmJobDetails I list the job name and a brief description of the type of project

Each client has a task sheet. The subform frmTaskSheetDtls has the combo box cboJobName where I select the particular project and enter the task. This combo box is a cascading combo box which is filtered according to the Client selected in a combo box in the frmTaskSheet.

What I would like to do is if I am updating the task sheet for a Client and I have a new job to add, that I use the List Items Edit Form to open frmClients to the specific client I am updating the task sheet for instead of having to close the task sheet, open and update the client form and then reopen the task sheet to continue updating it.

I understand the command suggested by Pat Hartman but I just don't know exactly where to enter it.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 06:17
Joined
Oct 29, 2018
Messages
21,357
What I would like to do is if I am updating the task sheet for a Client and I have a new job to add, that I use the List Items Edit Form to open frmClients to the specific client I am updating the task sheet for
Hi. If you want to add a new job that's not available from the dropdown list, does it mean this new job is only applicable to a specific client, or can this new job/task also be assigned to other clients?
 

Tophan

Registered User.
Local time
Today, 09:17
Joined
Mar 27, 2011
Messages
362
Hi. If you want to add a new job that's not available from the dropdown list, does it mean this new job is only applicable to a specific client, or can this new job/task also be assigned to other clients?
To the specific client only
 

theDBguy

I’m here to help
Staff member
Local time
Today, 06:17
Joined
Oct 29, 2018
Messages
21,357
To the specific client only
Okay, then I guess you could try what Pat suggested, and I guess it also means you can't use the List Items Edit Form for it. To be specific, the form listed in the List Items Edit Form is usually bound to the Row Source of the Combobox, not to some other related table. So, if I am understanding your setup correctly, your List Items Edit Form should have the name of a form for your Job/Task table - not the form for your Clients (frmClients).
 

theDBguy

I’m here to help
Staff member
Local time
Today, 06:17
Joined
Oct 29, 2018
Messages
21,357
Hello,

I have a client task sheet form with a combo box cboJobName that lists all the jobs for that specific client. I have the List Items Edit Form property set to frmClients (which includes the subform frmJobs).

How can I open the frmClients to the Client whose task sheet I am working in when I need to add a new job to the combo box cboJobName?
Going back to your original post, what is the name of this "client task sheet form?" Is it the same form you have as a subform to frmClients? If so, it sounds like you have some sort of a circular reference going on.
 

Tophan

Registered User.
Local time
Today, 09:17
Joined
Mar 27, 2011
Messages
362
Going back to your original post, what is the name of this "client task sheet form?" Is it the same form you have as a subform to frmClients? If so, it sounds like you have some sort of a circular reference going on.
The forms are as follows:

-frmClients linked to subform frmJobs by the ClientID field
-frmTaskSheet linked to subform frmTaskSheetDtls by the TaskID field
-frmTaskSheet linked frmClients by ClientID field

Each Client has a Task sheet e.g. ClientID #1 tasks are entered in TaskSheet #1

I just don't understand where I would enter the DoCmd.OpenForm even if I change the List Edit form to frmJob
 

theDBguy

I’m here to help
Staff member
Local time
Today, 06:17
Joined
Oct 29, 2018
Messages
21,357
The forms are as follows:

-frmClients linked to subform frmJobs by the ClientID field
-frmTaskSheet linked to subform frmTaskSheetDtls by the TaskID field
-frmTaskSheet linked frmClients by ClientID field

Each Client has a Task sheet e.g. ClientID #1 tasks are entered in TaskSheet #1

I just don't understand where I would enter the DoCmd.OpenForm even if I change the List Edit form to frmJob
Let me see if I understand...
  1. You have a Clients table
  2. You also have a Tasks table
  3. Clients can be assigned one or more Tasks (1 to many)
  4. You also have a Task Details table
  5. Each Task can have one or more Task Details (1 to many)
If the above is correct, where is the Combobox in question located? Also, what is the difference between frmJobs and frmTaskSheet?

Are you able to post a sample copy of your db with test data? That would help clarify some of these back and forth questions.
 

Tophan

Registered User.
Local time
Today, 09:17
Joined
Mar 27, 2011
Messages
362
Unfortunately I don't have winzip. Is there another way I can share the db with you?
 

theDBguy

I’m here to help
Staff member
Local time
Today, 06:17
Joined
Oct 29, 2018
Messages
21,357
Unfortunately I don't have winzip. Is there another way I can share the db with you?
Don't need Winzip. From the Windows Explorer, right click the file and select Send To > Compressed Folder.
 

Tophan

Registered User.
Local time
Today, 09:17
Joined
Mar 27, 2011
Messages
362
Please see attached db. The most important form is the frmTaskSheet as this is what I use to generate invoices.

Looking forward to your feedback
 

theDBguy

I’m here to help
Staff member
Local time
Today, 06:17
Joined
Oct 29, 2018
Messages
21,357
Thanks. I'll check it out tomorrow. Good night.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 21:17
Joined
May 7, 2009
Messages
19,169
on the Task Sheet form->frmTaskSheetDtls subform, type the New Job to cboJobName combobox.
it will ask whether you want to add the job.

see On Not in List event of cboJobName combo.
see Load/Timer event of frmClients.
 

Tophan

Registered User.
Local time
Today, 09:17
Joined
Mar 27, 2011
Messages
362
on the Task Sheet form->frmTaskSheetDtls subform, type the New Job to cboJobName combobox.
it will ask whether you want to add the job.

see On Not in List event of cboJobName combo.
see Load/Timer event of frmClients.
Thank you so much. That works! Really appreciate both your and theDBGuy's help.

If it's ok I will delete the db from the forum as some of the info is not sample data. I'll just wait a bit for theDBGuy to see your solution and then delete.

Again...THANK YOU!
 

Mike Krailo

Well-known member
Local time
Today, 09:17
Joined
Mar 28, 2020
Messages
1,030
Arnel, I see you are using the timer event for this and I never seen it used quite like this before. That was a creative way to handle the problem BTW. When the frmClients form loads you set the timerinterval to 100 only if there are openargs to essentially activate what ever you put in the timer event. I probably would have used tempvars to do the same thing and just removed the tempvars inside the form load event. It's just nice to see yet another way to achieve the same results.

1616247972544.png
 

theDBguy

I’m here to help
Staff member
Local time
Today, 06:17
Joined
Oct 29, 2018
Messages
21,357
Thank you so much. That works! Really appreciate both your and theDBGuy's help.

If it's ok I will delete the db from the forum as some of the info is not sample data. I'll just wait a bit for theDBGuy to see your solution and then delete.

Again...THANK YOU!
Good morning! Looks like my good friend Arnel already came to the rescue while I was asleep. Glad to hear you got it sorted out. Good luck with your project.
 

Users who are viewing this thread

Top Bottom