Automatically bring back information entered in the sub form and modify it

[vba_newbie]

Hi

I hope someone will be able to assist as I've been a week trolling through websites trying to the find the answer. I'm going to have a breakdown and it's killing me I can't seem to get this to work

At work I've built an access database which has a data table with a unique code ID for each record. The database also has a form and sub form which collects the information. There are 70 fields which the users would enter into the sub form so the data table collects this information. Once the user puts the details in the sub form, it gets saved into the table.

So here's the challenge I need to modify the database so the user will use a drop down BOX (unique ID) to bring back the record into the sub form of the information they had filled in the fields. When they do this they will be able to change the information or update the information which populates in the sub form.

I followed this video provided but it only applies to main forms so I am stuck as my database uses sub forms.

Hope to achieve the below
- Users can retrieve a record by doing a search on the Identifier so the record of the identifier appears on the sub form
- Users can amend the sub form and details
- User saves updated information in sub form and then table gets updated

My database is very simple so I hope someone knows how to do this

Thank you

 

[Uncle Gizmo]

Is your subform Embedded in a main form?

Welcome to AWF BTW!

Cheers Tony

 

[vba_newbie]

Hi uncle gizmo thank you for your reply. Yes I do believe the sub form is embedded in the parent form.

I had to capture 70 fields of data so creating a sub form was the only way I could think of to organise the data collection. There are multiple tabs of the sub form within the parent form. So users would just go into each tab of the sub form to fill the data.

Hope that makes sense.

Anh

 

[Uncle Gizmo]

So does each tab have a subform in it?

 

[vba_newbie]

Hi Uncle Gizmo

I've attached a sample of the database. It's very simple as I am slowly learning the ropes.

Update existing Document - That is the drop down box I have created so if the user wants to recall data in Documents table they select the ID and in theory the sub forms will populate what has been saved in data table. When the data has been recalled in theory users can amend the information

Sub Forms (preliminary, Red Flags, High Level, Cohorts, Features, Commentary, etc) are the tabs what I would like it automatically update when user selects the drop down box (update existing document which has the ID's)

Hope this makes sense. If you know someone that has done it or you know where i've gone wrong really would live your feedback

 

[Uncle Gizmo]

I cannot look at the db as I am answering questions using Tapatalk on my mobile phone.

In any event it is much better if you explain the situation because this helps you understand what you are doing and often the process of explanation leads you to the answer.

 

[vba_newbie]

Hi
Yes no problems.

The database has a parent form and sub form embedded which users enter information and it gets stored in the table via the sub form. The information entered in creates an ID. Sometimes users may not fill in some of the fields in the sub form because they don't have the information at the time.

So I need to modify the database so that USERS can select the ID through a drop down box and the sub forms will populate the information that was entered. Users can then update or modify the fields in the sub form and gets saved.

The problem at the moment is I've created the combo box which has the list of IDs but the sub form doesn't seem to automatically update. That is where i am stuck unfortunately.

 

[Uncle Gizmo]

I don't understand? The main form should be supplying the ID for the subform.

A common example of a main form with a subform would be and invoicing system. The main form would generate the invoice number, record the name of the customer, the date, information like that pertaining to an invoice. The subform would list the invoice line items, that is the individual items that were ordered. The invoice number provided by the main form would identify each line item, matching them to the invoice number on the main form. This relationship is maintained automatically by Microsoft Access.

 

[vba_newbie]

Hi
I've just built subforms for my database. There is no main form that has any fields. So from your response I need to put fields in the main form (following an example of your invoice system) and the sub form will have addition details relating to the main form?

So at the moment what I have will not work for what I am trying to do?

 

[Uncle Gizmo]

I don't have a clear picture of your setup. It might be an idea if you list the names of your tables and subforms.

 

[vba_newbie]

Hi Uncle Gizmo believe it or not you have actually solved my problem. When you gave me the invoice example. By that I modified the database to create a form that has both sub form and parent form and it worked!

Thank you so much

I have attached a working database for those also having the same problem as me!

Thank you