Full Version: Subform not updating a control
UtterAccess Forums > Microsoft® Access > Access Forms
cunningrat
(I'm attempting to teach myself Access by writing an application for a space game, hence the weird field names).
I have a form (called Turret Builder) with a subform on it. The subform basically lists all the weapons that can go into a turret. I also have two text boxes (Total_Mass and Total_Cost) which calculate the total mass and total cost of the turret and the weapons within, both done via queries. (I have to do it through a query rather than through an invisible field in the footer of the subform.)
Here's the problem. Whenever I update the subform, Total_Cost updates the value within it. Total_Mass doesn't update : I have to close and reopen the form for the correct value to show up. And I can't for the life of me understand *why* it's doing that.
THere is the Control Source code for the text boxes:
Total_Mass:
=DLookUp("[TL" & [TL] & "_Mass]","TurretBaseData","[Turret Type ID] =" & [Turret Type])+DLookUp("[SumWepMass]","SumWeaponMass")
Total_Cost:
=DLookUp(IIf([Streamlined]=Yes,"[SL_Cost]","[USL_Cost]"),"TurretBaseData","[Turret Type ID] =" & [Turret Type])+DLookUp("[SumWepCost]","SumWeaponCost")
And here is the code for the queries:
SumWeaponMass:
SELECT Sum(Weapons.Mass) AS SumWepMass FROM Weapons, TurretWeaponXref, Turret
WHERE (((TurretWeaponXref.Weapon)=Weapons.[Weapon Name]) And ((Turret.[Turret ID])=TurretWeaponXref.[Turret ID] And (Turret.[Turret ID])=Forms![Turret Builder].[Turret ID]));
SumWeaponCost:
SELECT Sum(Weapons.Cost) AS SumWepCost FROM Weapons, TurretWeaponXref, Turret
WHERE (((TurretWeaponXref.Weapon)=Weapons.[Weapon Name]) And ((Turret.[Turret ID])=TurretWeaponXref.[Turret ID] And (Turret.[Turret ID])=Forms![Turret Builder].[Turret ID]));
sredworb
Hi Rat,
Can you strip your db down and attach it?
Jerry
Not sure how to attach then click
HERE.
cheekybuddha
Hi,
Try adding the following code to the AfterUpdate event of the controls on the subform:
CODE
    With Me.Parent
        .Total_Mass.Requery
        .Total_Cost.Requery
    End With

hth,
d
cunningrat
I know how to attach -- I am not sure how to strip the DB down.
sredworb
Take all personal / confidential information out of the tables.
cunningrat
OK. Here you go.
cunningrat
cheekybuddha,
tried this solution, and doesn't work. Total_Mass updates but does not update properly -- it's one change behind.
Thanks, though.
cheekybuddha
Hi cunningrat,
found the problem!
In the code of form [Turret Builder] find the sub for Turret_Type_AfterUpdate and delete it.
The code made no sense and caused a compile error.
Your textboxes should update ok now.
hth,
d
cheekybuddha
ps. No need to add the code I suggested above
ps. It is a good idea to add the declaration 'Option Explicit' to the top of each module under 'Option Compare'. You can get the vba editor to do this automatically by going to the Tools menu in the VBA editor, select Options and in the dialog that appears check 'Require Variable Declaration' in the 'Editor' tab.
This will mean that all variables must be declared explicitly and spelling mistakes will not mean the creation of a new variant variable.
Then, every time you edit some code, go to the 'Debug' menu and choos 'Compile'. This will highlight any syntactical errors you may have made.
hth,
d
cunningrat
>
Thank you. I really do appreciate all the help you're giving me, and I am learning tons of new things.
owever.... Total_Mass still refuses to update itself like Total_Cost does. cryhard.gif
cheekybuddha
Hmm, that's strange.
That does it say?
cheekybuddha
Are you using the sub form in datasheet view or continuous form view?
cheekybuddha
OK,
If you are using datasheet view, add the code I gave you above to the subform's Form_After_Update event.
It will only requery the mainform textboxes when you move to another record inthe subform (ie from one weapon to another).
Can each turret only have the same type of weapon?
And if you change the values of that weapon you want to change the same values for all the turret's weapons?
What happens if you buy one weapon at one price and the next at a different price, or won't that happen?
d
cunningrat
>
Thank you!!! That works beautifully!
cheekybuddha
You're welcome! thumbup.gif
This is a "lo-fi" version of UA. To view the full version with more information, formatting and images, please click here.