CBO box to show on subform record from source table and save to destination table

[vazdajic]

Dear All,



I have a challenge, to populate destination table using a subform via CBO box. Please see the picture:



Any help is highly appreciated.

Thank you very much.

[June7]

What table is combobox RowSource? Does it have fields Description, Size, Unit_Time, Unit? You want to copy this data into another table? Why duplicate?

[vazdajic]

Hi There, thank you for the reply..

I have attached the DB in Zip format..

Thing is, "tbl_Alle_Kataloge" is a list of all possible options, let's say a list of all the things you can buy in a shop..
In table "tbl_Kalkulationen" I want to see an overview of all of all items purchased, per each purchase (Arbeitspaket) (here "tbl_Arbeitspaket")... When I choose in subform one or many options (rows) for a single "Arbeitspaket", I need to save all of those options (rows) to "tbl_Kalkulationen" as you can see in the DB attached. This is why I have named fields in both tables the same.

Later, I will add more fields to "tbl_Kalkulationen" so I can see e.g. "Quantity" so, if I have used option one once but with quantity of 3, sum of the money spent is triple.. Like an overview of shopping list.

Also, I need this (to populate CBO2 with different field from same ID like in CBO1):



Thank you very much for your help. I am a beginner with Access and English is not my native language so please accept my apologies if I haven't express myself properly.

[Welshgasman]

Just use

Me.Size = Me.Description.column(3)

in the AfterUpdate of the Description combo. This link recommends the OnChange event.

https://www.techrepublic.com/blog/microsoft-office/automatically-fill-in-access-text-boxes-based-on-a-combo-box-selection/

Not sure why Size is a combo though for that process.?
I would have thought you would need to populate that with all sizes for whatever is in the Description combo?

It is called Cascading Combos.

[Welshgasman]

Cross posted https://www.access-programmers.co.uk...-table.318540/

[mike60smart]

Hi Vaz

You need to search for how you define Relationships between related tables.

I have only looked at the tables shown in the attached ER Diagram

Study how the tables are related and also how you lookup values from another table that contains a list of values.

I have specified ALL the Primary Keys in all of the tables as Autonumbers - You had specified them as Text Data Types.

The Related Foreign Keys are defined as Number Data Types.

See your modified Forms attached.

[vazdajic]

Dear Mike..

Thank you so much for fixing my DB in every way. Also, thank you for instructions on how to define relations and primary keys.. I will have to investigate this further.

In the database you have modified and sent, I have tried to add a criteria to Combo22 ([Forms]![subform_Kalkulationen]![Combo51]) (like on the photo below) but I keep having the same issue..

I need Description combo (Combo22) to offer me only possible records from the group chosen in Combo51... Also, to update (requery??) Combo22 after making selection in Combo51.


If you could help me this, I would be very grateful.. I have even tried to use a combo on main form in which a group can be selected and add afterupdate event on it to requery description combo but no success

[mike60smart]

Hi Vaz

The subform you are displaying is constructed in the wrong way.

Look at the attached screenshot of My Subform.

You do not need to use any Forms Reference.

[vazdajic]

Hi there..

Thank you for your reply and help.. Yeah, something is wrong.. In my head it is simple but I just cannot make it work..I just cannot

I need to chose Arbeitspaket (e.g.M21002511) on my main form and then below on sub-form attach some calculations to it.. But, as I have 1000+ calculations to choose from, first I need to narrow the choice with left combo (Group) and then e.g. inside DEMOLITION I want to see let's say, 55 possible demolition related steps. In this case I do not want/need to see BOLTING, PREFAB..

Uh, 10h I am behind computer without success haha.. I have set up Criteria in a query of right combo to take into account choice just selected in left one but it does not work.. Sometimes I make it work when I open subform only but then it does not work when I call the subform from the main form. haha

[mike60smart]

Hi

Does the attached do what you need?

[vazdajic]

Hi there.. Thank you for your reply.. It filters as it should but now two new problems:



So once again, when adding a new record to the subform I need to filter/narrow combo list of choices bound to "tbl_Alle_Kataloge" to find my calculation I need and then add it, another words, save it automatically to "tbl_Kalkulationen". Later, I will add more fields like Quantity to multiple each calculation with coefficient etc..

I think, as other fields in subform just show another columns (Unit Time, Size, Unit) of same record, those get overwritten every time I add new record.. This is why, somehow, I have to copy those to "tbl_Kalkulationen" so when I add a new record, those do not get updated

[June7]

Expression references Combo57 not Combo51. Should be like: WHERE Group = [Combo51]

I downloaded db modified by Mike. Not saving GroupID as foreign key in tbl_alle_Kataloge, saving Group. This means Combo51 value does not match to field in tbl_alle_Kataloge.

[vazdajic]

Hi June7.. I am lost now.. My Access understanding is limited haha.. Please don't take it against me.. Tomorrow I will start with another approach, to open calculations in totally new form. Maybe I make t work there, somehow .

Thanks for your reply and help.

[mike60smart]

Hi

See if this does what you need now.

[vazdajic]

Hi Mike.. Thanks.. Works perfect.. Now I can proceed with calculations and reporting.