IfThen doesn't respond

[kupe]

Why won't this work, Experts, please, either as a module or as a sub attached to the subform's Form_Current() ?

If Forms!frmMatrix.frmWorkers.Form!WorkStartDate = " " And Abs(DateDiff("d", LastJobEndDate, Date)) >= 28 Then
Forms!frmMatrix.frmWorkers.Form!DailyGrind = "Available"

End If

 

[workmad3]

Are you checking for an empty textbox or label by any chance?

 

[kupe]

Hi workmad3

This is an empty textbox - Forms!frmMatrix.frmWorkers.Form!WorkStartDate = " "

So one of the things I tried was to change the code to

If Forms!frmMatrix.frmWorkers.Form!DailyGrind = "Dismissed" And Abs(DateDiff("d", LastJobEndDate, Date)) >= 28 Then
Forms!frmMatrix.frmWorkers.Form!DailyGrind = "Available"

End If

But that won't work either. I'm doing something quite wrong because I can't test with a Breakpoint. But I can't see what it is ... Hope you might be able to.

 

[workmad3]

if its testing an empty textbox, then the error is in the
Forms!frmMatrix.frmWorkers.Form!WorkStartDate = " "
line.

You are testing for a textbox with a space in it. You need to check for

Forms!frmMatrix.frmWorkers.Form!WorkStartDate = ""

and possibly

IsNull(Forms!frmMatrix.frmWorkers.Form!WorkStartDate)

so if you try

If (Forms!frmMatrix.frmWorkers.Form!DailyGrind = "" Or IsNull(Forms!frmMatrix.frmWorkers.Form!DailyGrind)) And Abs(DateDiff("d", LastJobEndDate, Date)) >= 28 Then
Forms!frmMatrix.frmWorkers.Form!DailyGrind = "Available"

and let me know if it works then

 

[kupe]

Unfortunately it doesn't work. I tried many variations of your suggestion.

Before, this is the code that worked well on a previous form.

If Me.DailyGrind.Value = "Dismissed" And _
Abs(DateDiff("d", Me.LastJobEndDate, Date)) >= 14 Then
Me.DailyGrind.Value = "Available"

Perhaps belonging to a subform upsets it all.

 

[kupe]

Workmad3
Do you think it might work if I approached it from the query with an IIf perhaps?

 

[KeithIT]

Are you speaking about a subform? Because from the look of it in your code you have three forms open, two of which are subforms. If this is the case, search for "subforms" on this forum and research how to reference them in your code.

 

[kupe]

Hi Keith
Yes, it is of course a subform, and thanks for the advice, and I have delved through Guru McAbney's excellent tutorial. Hence the prefixes on my opener at the top of the page. However, doesn't make any difference to the function, sadly.
Cheers

 

[Rich]

Which event are you using? The code belongs in the subForm CurrentEvent, also do away with the Forms! references and use the Me keyword instead

 

[kupe]

Hi Rich

Yes, I have it on Private Sub Form_Current()

Actually, I've tried it there as a sub and also having the form_current() call it as a public function.

I confess I have also tried it with and without those prefixes. It doesn't seem interested. (It's got so problematic that I've even tried it on the main form's current.)

Perhaps the clue is that I can't get it to respond to a breakpoint debug.

However, if your ideas suggest that that's where the problem lies, I will tackle it again from there.

Much obliged, Rich.

 

[Rich]

Where are the text boxes you're referring to, on the main form or subForm?

 

[kupe]

On the subForm, frmWorkers.

 

[kupe]

Hi Rich
Thanks for your good pointing. I did it all again in that area and it's working. Phew! Very grateful.

Forgive me, please, if I ask another question. The last 'If Then Else' refers to a field on the main form and back to a field on this subform. I was doing it with the "Form.frmMatrix.frmEtc" method in a Public Sub. Access says it doesn't support that. What mystery lies here?

Many thanks.

 

[Rich]

Forms!MainForm!FieldName

 

[kupe]

Many thanks, Rich and workmad3. Airborne, at last, Deo gracias - and you, and workmad3.