Need help on type mismatch

[Noreene Patrick]

Thanks to everyone, I have my db finished..except for 1 thing.

On my form, I have an unbound textbox that I enter associates (number). This textbox is the last part of a 3 part calculation..when I enter the number of associates the next textbox(unbound) shows calculation (textbox name is expectedcalc).

Since (I couldnt change their mind) this is a calculated field they want to save to the table, I had to put calculation in the controlsource of expectedcalc (=DateAdd("n",([Tcards]*[cbominutes]/[Associates]),0) and reference this at bound textbox named expected. Now, I put this in the controlsource because I couldnt get this calculation to work as VBA code.

me.expected.value = me.expectedcalc.value is on the associates_exit event

This is the code I used to make the reference. It works, but if I go into the associates field and take out the number after it has calculated, the me.expected.value field does not change and it causes a type mismatch and highlights the VBA code. I need to know how to make the expected field be blank if I take out the number of associates. Does that make sense? I just dont want this code to be available if a user happens to take out the number and hits enter after it has already calculated.

I have tried to put the code in associates_change event but in some of the events the code will not work..the expected textbox does not show in table.

Thanks, Noreene

 

[Moniker]

Can you not put a quick If..Then test in the control source to see if the associates value is empty?

Iif (NumAssociatesField<>"", YourCalculationHere, ExpectedCalcFieldName here)

 

[Noreene Patrick]

Thanks for your reply, Moniker.

When I put this in controlsource of associates, it gives me #name, and when I try to put it in code it gives me #error.

 

[Noreene Patrick]

If I wanted to put this calculation in code instead of the controlsource, exactly how do I do that?

=DateAdd("n",([Tcards]*[cbominutes]/[Associates]),0)

Thank you...

 

[PeterF]

If it's in code behind the form something like this should work:

If Len(me.tcards & vbnulstring) <> 0 And _
   Len(me.cbominutes & vbnulstring) <> 0 And _
   Len(me.assocates & vbnulstring) <> 0 And _
   me.assocates > 0 Then
   me.yourfieldname = DateAdd("n", (me.tcards * me.cbominutes) / me.assocates, 0)
End If

There's some basic testing for empty fields (no check for the type of data) and I assumed your controlnames are different from the names in the table.

 

[Noreene Patrick]

Thank you PeterF...It works just great..everyone is so kind and so intelligent in this forum. I am grateful to all who have helped me.

Noreene