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

 

[Pat Hartman]

Binding your forms to naked tables rather than to queries with selection criteria is not an advantage and could over time, lead to very slow forms. Nothing that needs to be changed immediately.

Swapping the view seems awkward. I would probably use 1 form with subform for documents/filename and a second mainform with subform for comments/tag number.

I think you need to ask yourself what you need to see at any one time. How is the data coming to you that you are using copy/paste to enter it? If it comes from spreadsheets, it would be far simpler to link to the workbook and run one or more append queries to copy in the data.

Here is a picture of an unbound form with three subforms. The list on the left controls the other two. When you select an endorsement from the list, form two and form three change to show the child data for that endorsement.

 

[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

 

[Pat Hartman]

no copy and paste of long lists

But you said in your question that you were doing copy/paste??

I'm interested to your comment about the query with selection criteria.

The user can't really work on more than one record at a time so loading a form with an entire table is wasteful. This is much less of a problem when the BE is Jet or ACE and the BE is on the small side. It is very poor practice when the BE is SQL Server. Given that many apps that become mission critical eventually need to be converted to SQL Server, I start out with a design that is ready from day 1 for upsizing. I never use form filters. Each form has one or two combos or textboxes that are used for selecting records. If the app needs a complicated search form, I use that instead to feed selected records to the edit form. The edit form is bound to a query that includes a where clause that references the search fields on the form. Therefore, the form opens empty. The user then uses the combo/textboxes to select a record to edit. The code runs a Me.Requery and the record is selected.

 

[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

 

[Pat Hartman]

Instead for the tagnumber i want find the way to past and copy from a long list.

Don't use copy/paste. Link to the spreadsheet and use an append query with criteria that supplies the FK of the comment you are linking to.

 

[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.

 

[Pat Hartman]

I have no idea what you want to do. Please start by stating your problem rather than stating a solution.
How does the data come to you? Is it a machine readable file? Sounds like it isn't. Therefore, unless you can get the data in a machine readable file, you will need to type it.

Access can't read .pdf files. I have a bank reconciliation application. The data comes from the bank as a .pdf. To process the .pdf, I use Able2Extract. It is OCR software and converts the image to a spreadsheet. I then import the spreadsheet.

 

[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...

 

[Pat Hartman]

Where do the tag numbers come from? How do you know what they are associated with?

It seems like you have to enter all this information manually unless it originates with you somehow. PDF's are image files. If you have something like Adobe, you can use that software to extract data from the PDF if it is in a table. Doesn't sound like Able2Extract would solve your problem.

One of my clients manufactures steel construction components and the drawing log system I created for them works nothing like what you are describing. It tracks who's got a drawing and when it is due back and what its status is. Whatever they communicate about the drawing itself is done with word or excel, not hand written on the drawing and scanned.

 

[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...

 

[Pat Hartman]

How could anyone simplify your data entry for you when we have no idea what your input looks like? And we don't have any sample files. Besides, manual input is manual input. The best way to optimize it is to organize the data entry form as closely as possible to the layout of the source document.

I have never gotten this to work correctly since the feature is targeted at word but you might try to play with Excel and try to use your voice. Read the source document and speak the text you want to enter. It might work directly into Access also. It really depends on what kind of data you need to record. If you can get windows to type the data for you, that goes quite far to simplify the data entry. You could even try to import it into a word table. The word table would be harder to import into Access than a spreadsheet but I'm pretty sure you can do it.

 

[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.

 

[Pat Hartman]

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