Change the Sub-Form View by using VBA

[caliandro]

Hi,
I have this idea: inside the main form I have another form (sub-form) that can be managed in different view:
1) datasheet view for a simple data entry (past and copy is working very well for multiple records in one time)
2) single form (when I need to add an event to one object inside the sub-mask, or if I need to add another sub-mask)
Both the mask and the sub-mask (and the sub-sub-mask) are based on tables (not query).
This idea is to make more easy the data entry of multiple information in different table connected by relationships.

My idea is to create a code event with a button on the main mask.
Is it something that can be realized? Can you give me just an initial direction?

the specific case is:

table documents
table file_name of documents
table comments of file_name
table tagnumber of comments

there are 4 tables one connected to the other and the data entry needs to be done in the same moment (manually).
For this reason it is useful to pass from data sheet view to single form view.

thank you in advance
Francesco

 

[theDBguy]

You could try something like:

DoCmd.RunCommand acCmdDatasheetView

 

[gemma-the-husky]

There's a numeric value for the setting. I have used it from time to time, but more often as a read value, I think.

 

[DHookom]

To get the “numeric value for the setting”, open the immediate window and enter:
? acCmdDatasheetView

 

[caliandro]

Thank you to all for the several answers!


@Pat Hartman
Dear Pat,
your answer is helping me think about the path to take. Unfortunately, data entry is almost all manual (no copy and paste of long lists). The form you sent is very interesting (Looks like a split Form). My idea is to use the double-click event function to open the child data entry subforms. And leave only one subform in the main form. To make scrolling through the records of the main form (documents) faster.
I need to do it more easy and fast.
I'm still in time to change because I'm not already started to develop it.
PS: I'm interested to your comment about the query with selection criteria. If you have something to suggest me (also past threads of this forum) about the use of query with selection criteria for data entry please send me something because I'm using only forms with main table and sub-table. For example:

table register
table item list register

Form register
Sub-Form item list register

Thank you
Francesco

 

[arnelgp]

you can also create two subforms:
1 datasheet and the other single form.\

now add a Tab Control on your form with 2 tabs and put the subforms on their respective tab.
then add a Command button on the form that serve as a switch between form views of your
subform. You can Hide the Tabs on the Property of the Tab Control.

see this demo.

 

[caliandro]

you can also create two subforms:
1 datasheet and the other single form.\

now add a Tab Control on your form with 2 tabs and put the subforms on their respective tab.
then add a Command button on the form that serve as a switch between form views of your
subform. You can Hide the Tabs on the Property of the Tab Control.

see this demo.

@arnelgp
Thank you very much arnelgp
this solution looks very smart.
I will try to apply it in my case.
Francesco

 

[caliandro]

@Pat Hartman
Dear Pat, thank you for your reply. I have multiple situation in my case. The application is for registration of results of document review.
When document return with comment I need to register each comment and for each comment I need to list the tagnumber "impacted" by the specific comment.
Comment registration is not past and copy, I need to work record by record. Instead for the tagnumber i want find the way to past and copy from a long list.
For the tagnumber data entry I believe that your solution is the best way (I would like to have a combo box that extract all the tagnumbers list relevant the specific Purchase Order and the user can select the tagnumber to be registered, even if in this case also the selection by a flag can be complicated when the list is with 150 tagnumber. But it can work..).

All this registration will be repeated when the document submission/approval cycle will restart from a new revision (new revision of the same document resubmitted). In this case the registration can be facilitate by past and copy of the last registration + additional data entry / modification.

Thank you for your support
Francesco

 

[caliandro]

@Pat Hartman
Dear Pat, thank you. The matter of the fact is that I haven't a spread sheet (i need to create it each time I need to register a document). In all cases the document is a pdf document and I need to read it and register the tagnumber. I have not well identified the best way to do that... I have an excel file with the full list of tagnumbers only (but not all the tagnumber of the excel file are included into the document). I should select from this excel file and register into my data base. Do you think could be a good solution to create a combo box where I can select multiple tagnumbers (multiple flag to be applied) and by clicking on a button to select and append new records on the tagnumber table? I believe that doing it page by page could be easy for me.

 

[caliandro]

@Pat Hartman ,
Dear Pat, first of all thank you for your follow-up. I will explain my situation and what I want to do. I'm working as document controller in the middle between several small companies (vendors) and one big contractor (company). Basically the flow of my job should be as below:
1) each vendor issue a document in his own format (pdf) and different each vendor (image a drawing with a general assembling of a machine or a switch board for example). This document is 20 pages and each page can contain a reference to 8 tagnumbers (total 160 tags for this example of document).
2) Company received the document from vendor start to review it and at the end return the document to vendor with several comment reported as mark-up in the documents itself. Each comment is referring to a specific tagnumber. Some tagnumbers can be free of comments after company review (tagnumbers approved). In case of critical comment the tagnumber cannot be fabricated and vendor need to reissue another revision document for the next cycle of review and approval.

My job should consist of:

In occasion of point 1) register the document submission (transmittal) and incorporate in the form the list of tagnumbers included in the document (because not all the tagnumbers are always included in the document) Very often this documentation activity need time and vendor need to submit partial tagnumbers in the first revision. The full list of tagnumbers scheduled to be issued is in my hand because received from Company (it is a master excel file as main reference for me).

In occasion of point 2) I need to register the result of the review from company. A table with comment with relation ship comment / file_name.
For each comment I need to register the tagnumbers involved by each single comment.

Once completed the point 1) and 2) I will create query by selecting the tagnumbers or document with high critical comments and follow up the resolution day by day...

this is what I need to do.
How is another problem...

 

[caliandro]

@Pat Hartman
Dear Pat, I understand what you mean for make easy automation between drawing issue and control of the documentation always well updated. This is possible when the drawing format is consolidated and the company can invest money. This happen in most of cases.
In my case I'm free lancer without such resources.. . In addition , I need to follow up several different suppliers with different formats and they never want to share with me the native file of any report of documents (for know how protection reason).
So in briefly I can and I need to do all by my self even if I'm not a programmer but I'm just an old user of ACCESS.
You can understand that for me the option to do all by manual input is a chance that I consider and that I'm currently adopting because I need to do my job in the time requested by my client.
Try to improve this not perfect data base if for me a nice time spending and especially a way to try to reduce my stress during data entry activity.
Simplify the manual data entry is my scope in this moment...

 

[caliandro]

@Pat Hartman
Dear Pat Hartman, thank you again for your follow-up. Very appreciated. In my case the format of the data to be registered are pretty complicated to be managed by voice. Format of tagnumber is always a string composed by 12 characters for example "45**AC342001".
In addition, may be I have not explained well this information in the past threads: I have the entire list of the tagnumbers "ordered" to the supplier ("tagnumber master list table" as access table). So in other words I already have the source of data to be entered in the table: it is the "tagnumber master list" composed by 250 different tagnumbers. I just need to read the document and page by page I need to select the tagnumbers included in the document and register it on another table (document_tagnumbers table). If in the document are present for example 160 tagnumbers i can suppose that those 160 tagnumbers are listed in the master list composed by 250 tagnumbers. 90 tagnumbers listed in the master list should be not registered because not included into the present revision of the document.
I would consider to append data while reading the document by using a combo box with multiple choose taken by the master list of tagnumbers filtered by the specific purchase order - sorted in the best way to easy identify the tagnumbers to be selected.
I don't know if the policy of this forum allow my to attach a sample photo of what I have in mind.

 

[Gasman]

You are able to attach images. Just not links.

 

[caliandro]

If you have to make a comment for each tag you record, then a multi-select combo will be no help to you.

@Pat Hartman
Dear Pat. The relation between comments and tagnumbers is 1 to many. One comment to many tagnumbers.
I cannot publish the supplier document for legal reason but image the comment is for example: "painting color RAL should be RAL1731" and this comment is applied to all the tags listed in the page where the comment is inserted.
@Gasman
Dear Gasman, thank you for your instruction, I will prepare a sample of the mask for the data entry of the tagnumbers and then I will post the photo.

 

[Uncle Gizmo]

now add a Tab Control on your form with 2 tabs and put the subforms on their respective tab.
then add a Command button on the form that serve as a switch between form views of your
subform. You can Hide the Tabs on the Property of the Tab Control.

Further to ArnelGP's excellent suggestion, it's often a good practice to set the Record Source of subforms to nothing if they're not actively displaying information. By only assigning the Record Source when you open the tab containing the subform, you can significantly improve performance, especially when dealing with large datasets.

You can check out a method I use to achieve this in the video linked here:-

72 Fields on a MsAccess 2007 form

to separate comparison data for each group, but have now changed the layout to 5 Tabs on the sub form to analyze / display similar data groups. I hope to import the data from our Excel Spreadsheets Think about your data. It sounds like you don't want tables that mimic the spreadsheets. You...

www.access-programmers.co.uk