Regular form from multiple table without subform

[AccessBee]

Form: field taken from various related tables

I am trying to build a database. And, I want a form that pulls in info from different tables. I build this form from scratch only to discover afterwards that this form would not show any records since fields are from more than one table.

So next, I played around and created the form the same way but with info from only table "POInfoDDDT1"; since the fields are filled, the form showed the data nicely. As soon as I include the fields projectName and ProjectType from "CCCInfoT1," form does not display any data. But, those tables are related! The design view of the form has option like "Add existing field" and within that there is a link "show all tables" or "related fields" but if Access does not allow to show all info together, then what is the point of this function? I don't want any subform visible.

So, basically POInfoDDDT1 and CCCInfoT1 will be prefilled which means the top two sections of form will be filled. The user will fill out the info on the bottom, which comes from two separate tables. All project info comes from "CCCInfoT1" and Service info comes from "POInfoUserT2." The user needs to be able to see those top info in order to fill the bottom section. So, when user puts in info, those info would be saved to these two different tables.

How to achieve this structure and functionality of the form?

 

[Uncle Gizmo]

How about using combo boxes

 

[plog]

When making forms that directly interact with data (add/edit/delete) those forms should be based on just a table, not a query. This may mean using sub-forms where there are 1-many relationships, but if a form is to directly interact with data, they should be based on just a table.

You kind of lost me with all your sections and what they are based on, but if you want to allow users to add/edit/delete data from a form, it should be based on just a table.

Without understanding your data, your relationships look good (well CmbPSerCatT4 should be directly linked to CCCInfoT1 and not through a CmbProjTypeT3).

 

[AccessBee]

Combo box will not work, since info will be prefilled in upper section. Also, the form does not even display information from the main table, when fields from related table are included in the form.

Yes, this is a form that edits and enters data in more than one table. I thought you create smaller section of your database in Access and relate them with table relationship. If I want to create one giant table, then I would have worked with excel.

I will start from the right side of the relationship, and try to explain my database a little bit.

One ProjType has many ServiceCat and one ServiceCat has many SerType. These are the bottom section of the form, these will be presented through a cascading combo box. I have almost become an expert in that. So, once user chooses one of many ProjType from combobox, which makes SerCat combobox filter options. Once user chooses SerCat, that further filters SerType.

Ok next, CCCInfoID, this table will be filled beforehand (before the form in pic is used) and there is a separate form to fill out this table. Here I don't have any problem since form is getting or putting info from or into one table. For example, once the user puts in ProjType, the form is set up to automatically fill in the ProjTypeID (because of relation to CmbProjTypeT3) and yes I could do this with combo box, because user had to choose ProjType from combobox in the first place and ProjTypeID was another column I same combo that I could throw into another field in the form. If ProjType is already filled, and I can’t use that property. So, in table CCCInfoT1 a particular CCCElement wil have unique ProjName, ProjType, ProjLoc.

That is the problem, I am having with this form in attachment, the one attached. 'POInfoDDDT1" has many different CCCElement, each may appear hundreds of time. Now, all fields in 'POInfoDDDT1" will be imported from an excel file. I don't want to create a giant speardsheet and then create a form from that where I have to put in same ProjType for the 500 same CCCElement. ProjType are unique to CCCElement and so are many other fields. So, I created a CCCInfoT1 to hold those, and related POInfoDDDT1 to that table.

The user needs to know the ProjType and other fields unique to CCCelement in order to fill the bottom section of the form. So, in the form since POInfoDDDT1 will be prefilled, CCCInfoID will be filled as well. I want the form to use this relationship to link to table CCCInfoT1, to pull out appropriate ProjName, ProjType, ProjLocation once a particular CCCElement record is shown in the form from the table POInfoDDDT1.

In short POInfoDDDT1 and CCCInfoT1 will be filled already. I want the form to show info from three different tables POInfoDDDT1, CCCInfoT1 and POINfoUserT2, so that user has the ability to look into other information and fill in data that will only go to POInfoUserT2.

Also, I am very novice user of Access, just started last week for this project. If there is another way to show info on forms from related tables those solutions are welcomed too. Should I create a giant query and then create the form? So, query will pull out info from four different tables, I create the form. Form will have filled out info from 2 tables already, but the user have to fill info for the other two table. Since, this form will come through a query, will filling out data in two different table work form this kind of form?

 

[JLCantara]

Can you submit a dummy version of your DB? Respectfully, your DB schema is strange and your form is equally strange.