form-sub form problem

[bigmac]

hi all can you help please, I have a form [mainfrm] with a sub form [subfrm], on the main form I have a text box [status update], on the sub form a text box [status] , when i select a new record on the form I want to be able to look the [status] text boxes (there could be many or just one depending on how many records there are )and if they ALL hold "expired" or "no longer used" then the [status update] test box would change to "inactive", if just one holds "active" then the [status update] would stay unchanged. can you help please

 

[arnelgp]

are both textboxes bound to a field?
is the subform have Link Master Fields/Link Child Fields?

if both the above are true, use your main form's current event to update the [status update] textbox:

private sub form_current()
dim bolActive as boolean
dim rs as dao.recordset
set rs=me!subformName.Form.RecordsetClone
with rs
if not (.bof and .eof) then .movefirst
do while not .eof
if ![status] = "active" then
bolActive = True
exit do
end if
.movenext
loop
.close
end with
if bolActive = False then
me.[status update]="inactive"
me.dirty = false
end if
end sub

 

[bigmac]

thank you again arnelgp, for your response , i will try this mate

 

[arnelgp]

i will answer here mr.bigmac.
on what fieldname are your tables related.
you can use an update query. this will be done once, to update the [status update] of your table in main form:

update mainTable set [status update]="inactive" where idField Not IN (select idField from subformTable where [status]="active")