Search a specific filed in a subform Recordset

[NTF73]

I'm sure this is an easy one but I can't find help anywhere. Maybe i'm using the wrong termonology!

All I want to do is display a command button in a main form if a date field in the subform is <= Date() - 90.

I've managed to do this by doing the following:

Private Sub Form_Timer()
If Forms!Exams!ExamsSubform!Date <= Date - 90 Then
cmdProcess.Enabled = True
Else
cmdProcess.Enabled = False
End If

This only works if the first record in the subform matches the date criterea. What I want to do is search the date field in all the records of the subform and apply the condition.

Can anyone help?

Thanks in advance

 

[gemma-the-husky]

in the subform, in the current event for the record put something like

if checkdate-date <90 then
parent!cmdbutton.visible = true
end if

correct syntax might be parent. rather than parent!

---------
this can be rewritten more efficiently as

parent!cmdbutton.visible = checkdate-date <90

note that you might also need this in the subforms afterupdate event, if you modify the checkdate field, to reset the parents cmdbutton

 

[NTF73]

I get runtime error 438 when I open the form using this method

Object doesn't support this property or method

 

[boblarson]

1. I would avoid the use of the timer (the form's On Current event should work like gemma mentioned)

2. When you say "...when I open the form using this method" post exactly what you are using and where.

 

[NTF73]

Not sure the On Current event is what i should be looking at.

When the subform is queried, the current record will be record 1

If the process date does not match the first record returned in the subform, but the second or third records do, will the command button be enabled or disabled using the on current event?

 

[gemma-the-husky]

the command button will be updated as you move from record to record in the subform - thats what the current event means - the currently active row/record

if you are trying to do something else with the commandbutton that does not relate to each row in the subform, then that is a different thing

 

[georgedwilkinson]

What I want to do is search the date field in all the records of the subform and apply the condition.

Put an invisible text box in the footer of your subform. Calculate the minimum value of the field in question in the subform into the new text box. Then use the On Current event of the main form to do your comparison/enable your button.

This is very similar to the putting a subtotal from a subform on the mainform tutorial on MS's site.

 

[NTF73]

Thanks for that George.

I set Min([ProcessDate]) in the footer of the subform and put a hidden text box on the main form just like you said.

I kept the on timer event but changed it to

If Me.txtMinDate <= Date - 90 Then
cmdProcess.Enabled = True
Else
cmdProcess.Enabled = False
End If

Works a treat.

Thanks

 

[georgedwilkinson]

Glad it worked out for you. And I'm glad you took the beginning of an idea and ran with it yourself, it makes it easier to help more people.

Sometimes all it takes is another set of eyes.

 

[NTF73]

Can't beleive I spent so much time on it when the solution ended up being so simple!

Think I need to take five (weeks off work!)

 

[boblarson]

I kept the on timer event but changed it to

Just be aware - that is sucking up a lot of CPU cycles just to ensure something is enabled or not.