IF Function within a Form

[MiniD011]

Hi Guys,

Just a quick query which I hope is going to be fairly straightforward. I have a form in which users enter a few fields, one being DebtGenerated(£) and the other being DebtCollected(£). What I am trying to do is add an AfterUpdate section of code which, once the DebtCollected(£) is completed, will compare this to the DebtGenerated, and if the DebtCollected exceeds that generated, a MsgBox will pop up.

I am struggling with this at the moment, but am not sure quite what the problem is. I have tried my code and changed whether the fields are classed as numbers or currency, neither of which makes any difference. My code is below and any help would be much appreciated. Thanks in advance!

Dan

Private Sub Debt_Collected__Cancellation__Change()
If [DebtCollected(£)].Value > [DebtGenerated(£)].Value Then
MsgBox "Debt Collected cannot exceed Debt Generated.", vbOKOnly
Me![DebtGenerated(£)].SetFocus
End If
End Sub

 

[gemma-the-husky]

i would use the beforeupdate event of the debtcollected control

this sort of thing

if  nz(debtcollected,0)> nz(debtoutstanding,0) then
   if msgbox("the debt collected is greater than the amount outstanding. Are you sure?",vbquestion+vbyesno,"Confirm") = vbno then
    cancel = true
    exit sub
end if

 

[JohnLee]

Hi,

It appears to me that you are missing the "Me!" part of the code that refers to the objects on your form, the reason I believe this is because you have the ".value" after [DebtCollected(£)] and [DebtGenerated(£)]. which normally suggests that you have objects on your form name as such.

Personally I would never use (£) in the nameing of an object, my experience has been that Access doesn't like that sort of nameing.

I would suggest you name your objects without the parenthesis and pound symbol.

If [COLOR=red]Me![/COLOR][COLOR=black][[/COLOR]DebtCollected(£)].Value > [COLOR=red]Me[/COLOR][COLOR=red]![/COLOR][DebtGenerated(£)].Value Then
MsgBox "Debt Collected cannot exceed Debt Generated.", vbOKOnly
Me![DebtGenerated(£)].SetFocus
End If

For

If Me![DebtCollected].Value > Me![DebtGenerated].value Then
     MsgBox "Debt Collected can not exceed Debt Generated.", vbOkOnly
     Me![DebtGenerated].SetFocus
End If

There may be more knowledgeable and experienced individuals on here that might suggest something better.

Regards

John

 

[MiniD011]

Hi Dave and John,

I have taken both of your advice and not only got it all working, but also improved the nomenclature of the various objects, so thank you both for your help and speedy responses!

Much appreciated as always,

Dan

 

[JohnLee]

Hi,

Glad to hear you have it working as desired, I'm happy to have been of some assistance.

John