Complicating the absolute

[kupe]

I want to add a proviso into a function. If a record is about to become "Available", I want to have a message box ask first something like "Has he passed the test?"

If the answer is "Yes", show "Available". But if it's "No", then keep with the status quo.

At present, this decides the availability question -

If Abs(DateDiff("d", Me!cboDoA, Date)) >= 14 And Me!cboWork = "Induction" Then Me!cboWork = "Available"

Back to the drawing board, or can I incorporate the MsgBox in here, please, Experts?

 

[KenHigg]

I think you may be able to incorporate something like the following in your code:

if msgbox("Did he pass test?",35,"Test") = 6 then 
'do stuff
End if

???
kh

 

[kupe]

Thanks, Ken. I'm trying something similar, but I wondered whether perhaps an Abs couldn't be tampered with. I'll report back.

 

[KenHigg]

Hum... I don't really see the significance of the ABS() function in the scenario you have...

kh

 

[kupe]

The Abs() has been getting round the previous complication. That the date must be more than 14 days after cboDOA and that the entry must be Induction in cboWork.

Now I need to say if he has these qualifications, he must have his work certificate.

Abs() was working well. But now there's this further complication, Ken.

 

[KenHigg]

If Abs(DateDiff("d", Me!cboDoA, Date)) >= 14 And _
    Me!cboWork = "Induction" And _
    msgbox("Did he pass test?",35,"Test") = 6 then
       Do stuff
End If

???
kh

Edit: Got a little ahead of myself...

 

[kupe]

Hi Ken - This is almost a copy of my attempts, but only almost so I will happily give it a run. Back soon. Many thanks

 

[kupe]

Perfect, Ken, naturally. I am very obliged to you for taking the trouble.

H O W E V E R, it inspires a problem. When the database is called up with only the form appearing, message boxes appear seeking a yes or a no. Is there some code that stops the function going to work before the form opens, please, or a way round this nuisance?

 

[KenHigg]

I'm not entirely sure I understand what all is happening but...

Maybe you could put this code in a module that gets called on the after update for the cboDoA and cboWork controls.

???
ken

 

[kupe]

No ... tried it out but that isn't the answer. I need the function to work on change, and that's what causes the msgbox to show when the db opens. But I am using a test db, set up to require this function to be busy.

Perhaps it will be ok in its real position ... more thinking required from me.

Your code will be doing a good job even so, Ken, and I learned a couple of extra things from it too. (I'm trying to say that your time has certainly not been wasted.) All the best and many thanks

 

[kupe]

What a curse, Ken. It applies the question to every record. Back to the drawing board. Cheers

 

[KenHigg]

Kupe, Let me try to get this straight. You have a form and it has a date fld named cboDoA. It also has a combo box named cboWork. Now when the cboWork is 'Induction' and when cboDoA is 14+ days you want to ask the user if the person in question has passed a test. Assuming this is correct, then I would have a stand alone sub routine that changed cboWork from 'Induction' to 'Available'. This code would be run when either of your cbo's values change. The only down side is that if both cbo's passed the test but the user had not passed the test, then the only thing that would kick off the code would be for one of the cbo's to change...

Hum... I think I would have a fld that stored the fact that the user did or did not pass the test. Then you would not have to even screw with updating anything. When you need to know if all three conditions have changed you would just calculate it on the fly wherever you needed it.

???

 

[kupe]

Thanks, Ken. Yes, I tried to avoid another field, and was being forced into it, and then I was getting this calculation to add a string to the new fld, txtCertified.

I see what you mean about having the new field do the job itself. Either the certificate has been awarded or not.

There's no-one here I can ask about this sort of thing. Very grateful for your advice. All the best.