Solved Inherit field values from mainform to subform (1 Viewer)

ChristianEhlers

New member
Local time
Today, 01:57
Joined
Jan 6, 2022
Messages
8
Good evening,
at fist please excuse my english as I am not a nativ speaker. I do not know all the right IT expressions.

My problem:

I did create an incoming process with Access. In this I do have a main table (TIncomingHead) and a main form (FIncomingHead) with several data (like supplier, date, ...). The main table does have an primary key "WEKennz1".
For the incoming details I do have a sub table (TIncomingDetail) and a sub form (FIncomingDetail) with data for each incoming line item. This table has another primary key.
Both forms are connected via the key"WEKennz1"

Sometimes I need to cancel receivings. To do this I do have a field "Storno" in the table (TIncomingDetail) as I do not wish to delete the data sets.
Sometimes I need to cancel not only line items but the complete receiving with all line items of one "WEKennz1"
I would like to do this directly from the main form with a mouse klick on a button called "DeleteAll"

Unfortunately I do not know how to select all line items with the current WEKennz1 and set the "Storno" field to yes.

Any ideas and help would be greatly appreciated.
Thank you very much and best regards
Christian
 

June7

AWF VIP
Local time
Yesterday, 15:57
Joined
Mar 9, 2014
Messages
4,451
So some line items for a receiving can be Storno and some not?

Run an UPDATE action SQL to edit all items for a receiving.

UPDATE TIncomingDetail SET Storno = True WHERE WEKennz1=Forms!TIncomingHead!WEKennz1;
 

ChristianEhlers

New member
Local time
Today, 01:57
Joined
Jan 6, 2022
Messages
8
Can I enter this as code in the cmdbox? Or do I have to create an query?
 

June7

AWF VIP
Local time
Yesterday, 15:57
Joined
Mar 9, 2014
Messages
4,451
In VBA:

CurrentDb.Execute "UPDATE TIncomingDetail SET Storno = True WHERE WEKennz1=" & Me!WEKennz1
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 07:57
Joined
May 7, 2009
Messages
16,810
you add the Code to the Click Event of DeleteAll button:
Code:
Private Sub DeleteAll_Click()
' if WEKennz1 is numeric
Currentdb.Execute "Update TIncomingDetail Set Storno=  -1 Where WEKennz1 = " & Nz([WEKennz1], 0)
'if WEKennz1 is string
'Currentdb.Execute "Update TIncomingDetail Set Storno=  -1 Where WEKennz1 = '" & Nz([WEKennz1]) & "'"
End Sub
 

ChristianEhlers

New member
Local time
Today, 01:57
Joined
Jan 6, 2022
Messages
8
you add the Code to the Click Event of DeleteAll button:
Code:
Private Sub DeleteAll_Click()
' if WEKennz1 is numeric
Currentdb.Execute "Update TIncomingDetail Set Storno=  -1 Where WEKennz1 = " & Nz([WEKennz1], 0)
'if WEKennz1 is string
'Currentdb.Execute "Update TIncomingDetail Set Storno=  -1 Where WEKennz1 = '" & Nz([WEKennz1]) & "'"
End Sub

Dear arnelgp,
thank you very much, as WEKennz1 is a string your second option works great.

I hope I am not too outrageous but I do need some more: The code should be run after I clicked on an option field (yes / no) and changed the value. This field in the form is called "Gesamtstorno" and refers to my main table "TIncomingHead" field "Storno"

After this the code shall change the field "Storno" in the table "TincomingDetail" to the same value the field "Storno" in the main table "TincomingHead" has.

Currently I do not kmow how to get the field value "Storno"

Can someone help with this too?

Thank you and best regards
Christian
 

June7

AWF VIP
Local time
Yesterday, 15:57
Joined
Mar 9, 2014
Messages
4,451
Why save Storno in TincomingHead? Main record status can be determined by status of dependent records.

Gesamtstorno is a textbox name?
 

ChristianEhlers

New member
Local time
Today, 01:57
Joined
Jan 6, 2022
Messages
8
Because for me it is useful to see if the comlete incoming shipment has been canceled or only some line items. You are right, your idea could work too, but I prefer to have it in the "TIncomingHead" if I cancel the complete shipment.

Gesamtstorno is also a Yes/No field.

But I did get the solution in between. Thank yuo very much for your time and effort.
Best regards
Christian
 

Users who are viewing this thread

Top Bottom