Updating subform records based upon form control changes?

  • Thread starter Thread starter TonyB
  • Start date Start date
T

TonyB

Hi,
I'm not sure if I should be asking this question here for in the forms
newsgroup but I'm guessing this may require a vba solution.
On a subform which is displaying one more related records, I have some vba
to write a field in the record depending on both a checkbox in the main form
and a value on another field in the subform.As I add new records in the
subform or alter values directly in the subform on a particular record, the
vba recalculates the new value for this field depending on the main form
checkbox value and the value of the other field in this record.
This was straightforward as it done on an individual record, and only had to
refer to a single field in it's own record and the setting of the check box
in the form above.
What I'm trying to do is to recalculate these values in any records
displayed in the subform if the checkbox in the main form is altered.
Obviously I can use the afterupdate event on the check box to run some code,
but I'm stuck as to how to make each record displayed in the subform
recalculate this value for each record depending on the check box.
I need to go through each record currently displayed in the subform and
recalculate the relevent field depending both on the other field in the
selected record and value of the checkbox.
How do I find out how many records are shown in the sub form from the code
in the main form event, and then syntactically refer to the field in each
record in turn to recalculate the value for for each record ?
TIA
Tony
 
Tony

Your explanation didn't give me enough specifics to visualize what you are
trying to do. Can you provide a more concrete example?
 
Hi Jeff,
I have :
tblItems: cost,qty,orderID, productID.(orderID+productID PK)
tblProducts : productID, description,uscost,ukcost.
tblOrders : orderID, chkUS plus other stuff.

The subformItems displays each record in tblLineItems with field
description,cost,qty,totalcost (=qty*cost) using a query as data source.
The formOrders has orderID, chkUS controls on the form, plus subformItems.

I enter data into the tblOrder from formOrders, which displays also the line
items on the order in the subform, and I enter line items for that orderID
into tblItems via this subform.

When a new record is created on the main form a new orderID in tblOrder is
created, plus a new record in the tblItems. When I enter a new item in the
subform by selecting description I can calculate a cost depending on the
description, and the value of chkUS control in the main form (using dlookup
to get the UK price or US price) and write this into the cost field in the
current record in tblItems, and then update the totalcost control for that
subform.That works fine and if any item changes in the subform I can use vba
to update get new costs, quantities and hence total cost.

However having entered a number of records in the subform each based upon
chkUS being false for instance, if the user now sets chkUS true on the main
form, I want all the records currently displayed in the subform for this
orderID to be updated to use the US price not the UK price.

I can detect the update event for chkUS, but I'm not sure how to find out
how many records are currently displayed in the subform, and then how to
update the cost field with either the us or uk cost from the code in the
main form ?

Thanks
Tony
 
Best is to open a separate filtered recordset from your table (not hte
subform) that matches the content of the subform and Update each record or
you could even do it with an UpdateQuery and then requery your subform.

Upon your requery the subform will reload the modified data from the table.

rgds
AndreG
 
Hi Andre,
That looks a better way of answering my question. However my experience in
VBA at using recordsets is limited.
Basically in the update event, I can find out from the orderID which records
are in the subform, and what value
chkUS is.
I then need to open a filtered recordset from tblorder which has that
orderID. So I would use something like ?
Dim db As Database
Dim rs As Recordset
Dim sqlstr As String

Set db = CurrentDb
sqlstr = "SELECT * FROM tblItems WHERE [orderID] = orderID'"
Set rs = db.OpenRecordset(sqlstr)
With rs
Do While Not .EOF
If chkUS = TRUE
rs.cost = dlookup([Price US],"tblProducts",[ProductID]=
..productID)
Else
rs.cost = dlookup([Price UK],"tblProducts",[ProductID]=
..productID)
End If
.MoveNext
Loop
End With
Some_Recordset.Close
Set db = Nothing

My grasp of recordset syntax and useage is shaky at the moment so I'm sure
this is not going to work yet.
I''ll let you know how it goes.
Thanks
Tony
 
a few corrections to start with:
- dim rs as DAO.recordset (since officeXP you should use the latter sintax)
- sqlstr = "SELECT * FROM tblItems WHERE [orderID] = " & str(me!orderID)
where me!orderid points to the "OrderId" field of yr form
- Set rs = db.OpenRecordset(sqlstr, dbopendynaset)
and to move trough the recordset you should first (before the loop) add:
.movefirst
- before editing i.e. after the if statement add:
.edit
- at the end of editing i.e. before the endif statement add:
.update
- another remark would be that Dlookup is a lousy and very slow working
function once yr routine works you'd better switch to a second recordset of
your tblproducts table and navigate through it using the findfirst statement
like this


set rstbl = db.openrecordset("tblProducts", dbopensnapshot)
...
rstbl.findfirst "[ProductId]="& str(!Productid)
if rstbl.nomatch then
msgbox "I did not find product".....
else
.edit
if chkUS = TRUE
.cost = rstbl![Price US]
Else
.cost = rstbl![Price UK]
End If
.update
endif
.MoveNext
loop
rstbl.close
.close
endwith
set rs=nothing
set rstbl = nothing
set db = nothing


Andre
 
Back
Top