Opdatere flere rækker med UPDATE, VBA

Reason why its only updating one record is because only the current record is selected

I would go about updating "Lagerbeholdning" in the after update event of "Ny Lagerbeholdning"

So no need for the code you have just something like

Me.Lagerbeholdning = Me.[Ny Lagerbeholdning] in the event mentioned

Another idea would be to have a Yes/no (Boolean) field in the table and when you enter a "New Lagerbeholdning" set the Yes/No field to true. Then I'm assuming you press "Godkend" button to update all changed records you can then UPDATE all records where Yes/No field is True.
And also remember to set that field to False also ;-)

Jeg kan ikke rigtig få det til at funke. Subformens kilde er en query, og i denne er "Ny lagerbeholdning" et udregnet udtryk.:
https://imgur.com/a/Z2cD0

Jeg prøvede med:
Private Sub AmountResult_AfterUpdate()
Me.StorageQry_Amount = Me.AmountResult
End Sub

Men skal det være i feltet udtryk1 i subformen at den skal ligge?

Jeg er sikker på min post er lige så forvirrende som jeg er lige nu. :-D

Det med et Yes/No field er en rigtig god idé.

You are welcome to send the dB and I'll take a look for you

ekspertenATsanthell.dk
AT=@

If AmountResult is a calculated field then an AfterUpdate isn't going to happen. AfterUpdate occurs AFTER the field gets changed.

I thought that AmountResult was getting entered manually but because it isn't, then the idea of having a yes/no field isn't relevant either.

If all the records in the form are getting updated then you could just make a query which does that. Then make some code which executes the query and finally requeries the form to show new values

Hej terry

Jeg har valgt at redesigne alle tabellerne, da det første var noget rod. Ikke desto mindre, vil jeg komme til samme problem som ovenstående igen. Så jeg vælger at tage mod dit tilbud, om at sende db til dig. Gør det så snart jeg kommer til problemstillingen. Tak. :-)

OK
If you tell us what you are trying to achieve then maybe we could make some suggestions so you could avoid the problem

Jeg har 3 tabeller der omhandler dette problem.

StorageTbl (Dette er mit lager)
RecipesTbl (Dette er "opskrifter")
ProductionsTbl (Denne indeholder de produktioner der bliver lavet.)

Når der indtastes en ny production, vælger man ud fra en opskrift. Denne opskrift indeholder forskellige værdier. Lad os bare lade som om vi bager brød. Så ville der indgå:
2kg mel
4 æg
100g smør
etc.

Når man så opretter en production i ProductionTbl, vil jeg gerne at værdier i RecipesTbl, udfra hvilket brød man nu bager (der er nemlig en del forskellige opskrifter), bliver fratrukket mit lager (StorageTbl), således jeg altid kan se hvor mange materialer jeg har på lager.

Giver det mening?

I think I understand what you are trying to do ;-)
If you could show the fields you have then it'll be a bit easier to give examples.

Bit busy most of tomorrow but will take a look if/when I have time.

Haster ikke. :-)

Jeg skal forsøge at forklare mig - ikke altid min bedste side.:

MastersTbl indeholder hovedeforskrifter:
https://imgur.com/a/SasNe

RecipesTbl indeholder opskrifterne til hver af masterne ovenfor:
https://imgur.com/a/fYgbF

StorageTbl indeholder så mit lager:
https://imgur.com/a/HIkzG

Alt dette er kædet sammen:
https://imgur.com/a/qz2Yy

Når man så har lavet dagens produktion, skal man vælge hvilken master (sker fra en combo), og indtaste forskellige oplysninger. Dette puttes ind i ProductionsTbl. Og det er her, at jeg gerne vil, at opskrift-mængderne til den master man har valgt, fratrækkes i storage, så man altid kan holde øje med lageret.

I think you have the same table shown twice

Sorry.

RecipesTbl:
https://imgur.com/a/1oQUo

Here is an UPDATE query which will ask you for a specific MasterID. It will subtract RecipeAmounbt from ProductAmount for all Recipedid for the MasterID you enter.

Make a query and give it the name qryUpdateAmount.

In my test database I have made a form with a combo from where I can choose the masterID I want to use in this production. The combo is named MasterID.

There is also a button on the form named cmdUpdate

In the Click event for the button I have made some code which executes the query I made previously.
This time it doesnt ask me for the MasterID but uses the one I selected from the combo.


Private Sub cmdUpdate_Click()
Dim qdf As DAO.QueryDef
   
    Set qdf = CurrentDb.QueryDefs("qryUpdateAmount")
    qdf!ThisMasterID = Me.MasterID '<<<<<<<<<<  This is the MasterID in selected in combo which is put in the paramter ThisMasterID of the query
   
    qdf.Execute
   
    Set qdf = Nothing

End Sub

Oops, heres the UPDATE query, notice PARAMETER at the start!

PARAMETERS ThisMasterID Long;
UPDATE (RecipesTbl INNER JOIN StorageTbl ON RecipesTbl.StorageID = StorageTbl.StorageID) INNER JOIN mastersTbl ON RecipesTbl.MasterID = mastersTbl.MasterID SET StorageTbl.ProductAmount = [ProductAmount]-[RecipeAmount]
WHERE (((mastersTbl.MasterID)=[ThisMasterID]));

PARAMETERS ThisMasterID Long;

the data type Long needs to be changed to the data type of MasterID

This is absolutely fantastic Terry!

Jeg måtte lave en lille ændring, da jeg har 2 comboboxe på min input form. Da brugeren skal vælge hvilken tank tingene kommer fra 1000 L / 6000 L. Checkboxene bestemmer så hvilken combobox der vises. Men jeg smed bare MasterId værdien over i et andet felt, som jeg kalder WhichMasterId og ændrede linien til:

qdf!ThisMasterID = Me.WhichMasterID

Men det virker rigtig godt. Tusing mange tak. :-)

Great its working :-)