Use fields in query based subform to populate a table

[moore020]

I have a main screen that has a tabbed form in it. Each of those tabs has a sub-form in it that displays information and allows some information to be updated.

In one tab I have a query based sub-form that returns information related to the project. This information cannot be manipulated or changed as it comes out of a company managed database. I would like the analysts to be able to add more granular information to a new table by inputting information in a dropdown field for one of the new table fields, but utilizing two of the existing sub-forms fields as identifiers in the new table.

Any ideas?

 

[MarkK]

"Any ideas?" is a very broad question.

I would create a new main form that shows each record in your current subform. Then, create a new subform based on your new table that links to the master table on the two fields you mention. You may need to manually edit the fields in the LinkMasterFields and LinkChildFields properties of the subform control for the new subform.

 

[moore020]

I tried to add a sub-form to the existing sub-form but it would not allow me to do this on a continuous form. Is there a workaround?

 

[vbaInet]

Add the subform below it and link to it via the Link Master/Child Field properties.

 

[moore020]

The problem with doing it this way is that there could be five lines in the primary sub-form and as such there should be five lines in the secondary sub-form.

 

[vbaInet]

You're not explaining your problem properly. Remember that we know nothing about your database, i.e. tables, queries or forms so please try and give as much information as possible. How is that a problem?

A subform linked to it's parent will filter down based on the current record in the parent. It can be linked via one or more fields.

 

[moore020]

I have a main large form that is based on an identifier of a project number. One of the tabs holds a sub-form that shows the order information, based on a query, associated with each project (this order information is from a company table and cannot be modified by me.) I need the user to be able to indicate what the scope is for each of the orders on the third form which would populate an independent table with order id, line number, and the scope.

 

[moore020]

Maybe there is a possibility to skip using a sub form on it at all and instead add an unbound control to the order information sub-form that would use an after update event to populate the information from the order information sub-form to the new table, "order scope", with the three columns necessary?

 

[vbaInet]

You can't use an unbound control a form that's set as a datasheet or a continuous form for this sort of thing.

I'm still unclear what your challenges are with using a subform as I described? Perhaps you're not aware that you can link subforms together? Is that the issue?

 

[MarkK]

If it's just for display you can use a listbox. If you need to edit the data you need a subform.

But more generally, it sounds like you may be designing a monolithic user interface that shows ALL your data, ALL the time. If so, what you may eventually prefer is to study the exact jobs a user performs, and create a single user interface for each job.

 

[moore020]

Apologies for not explaining this in a good enough detail to make it understandable. I have several analysts that perform the same functions but for different project sets. Each analyst has multiple projects that they manage. Each of those projects will end up with multiple orders, and each of those orders will have a different scope.

Currently, the analyst can search a broad datasheet for any of their projects, double click the line and it will take them to a project management form. The project management form has pertinent unchanging project information in the header tab of the form and it has a tabbed control in the body of the form. There are 11 tabs in this form each setup with a parent-child relationship based on the project number as an identifier.

On the order information tab, information is displayed from a table that is managed by our business consulting group at a corporate level and I cannot add to or take away from that information, I can only read it. I am attempting to add a drop down box onto this tab that will let the analyst assign a scope to each of the different orders. When the user selects the scope type, I would like for Access to add an entry to a table that inputs the order ID, the order line number, and the selected scope. This table would not be project number related, it would be related by order ID and order line number, which is related to the project number.

I hope this is helpful to understanding my situation. Apologies again for being vague and maybe not understanding all of the technical descriptions of ms access approaches.

 

[MarkK]

You have received suggestions to create a new mainform/subform interface, and to add an addition subform and link it to the existing subform. Is there a specific problem with either of these approaches?

I think your problem is clear, that you have added a new table that is a child of a child or your Project data.

If the solution has some shape to you that differs from the solutions already offered, maybe you can describe that, or if there is a specific problem implementing the solution that your are running into, but in response to your addtional post, I would offer the same advice I've already offered.

 

[moore020]

Thank you MarkK, when I try to add a subform to the form, I get the error that "a form with a subform object can't have its defaultview property set to continuous form", is the only way to do this to have the main subform as a datasheet view?

 

[MarkK]

I get the error that "a form with a subform object can't have its defaultview property set to continuous form"

OK, so obviously, you can't do it that way. What has been suggested is that you add the new subform NEXT to the existing subform, and then don't link to the mainform, rather, modify the LinkMasterFields and LinkChildFields properties to link the new subform to data on the existing subform. This was suggested by vbaInet in post #4.

Another suggestion is create a new popup interface in a new form completely specific to this task to add/edit data, and then, in your tabbed form, just show the data without it being editable.

A main form with too many subforms becomes very slow and difficult to maintain and commonly doesn't guide user well through their workflow. Yes, it portrays that data as it exists in the table, but workflow is not commonly structured the same as data.

Hope this helps,

 

[moore020]

Thank you again for the help.

Using vbaInet's advice and putting the sub-forms side by side, when I click on the "..." next to link master fields, it only gives the option of a master field coming from the main form field list. Is there a way I can override that and type in, in its entirety, the form name and field of the left sub-form and the right sub-form for the child?

 

[vbaInet]

Is there a way I can override that and type in, in its entirety, the form name and field of the left sub-form and the right sub-form for the child?

That was why I asked if you didn't know this was possible, so yes you can type it in.

Have a look at this link:
http://www.access-diva.com/f2.html

 

[moore020]

I am going to step back and re-evaluate. I think this is probably the strategy that I needed to use but, I also needed it to create new field entries for the second sub-form.

Thank you for all of the help.

 

[JLCantara]

Hello Moore!

Let me add my two cents to this community chest.
1 - Allow a form header of ~1/2" where you will create fields to receive the information you want to transfer.
2 - Add a form DblClick event to the subform you want to fetch information from;
3 - In this procedure, transfer field(s) to the header using me.Parent... etc.
4 - On the right side of the header put a command button (save info) that will manualy (using VBA) add this info to the extra table.

Hope it makes sense to you, JLCantara.

 

[moore020]

Thank you JLCantera, I will definitely consider this when I re-visit.