Validate control on exit of form - Access 2000

[dduffy]

Hi I am having trouble with validation on exit of a form.

I have a form that has 3 controls involved somehow in the validation.

Cont1 is and amount being calculated depending on the users selection (using a query)

Cont2 is the same as above but over another table

Cont3 has a query over it which subtracts the results of the query bound to Cont1 from the result of the query bound to Cont2.

I have setup validation in a Macro attached to the exit button (there are several other commands in this macro) to return a message and cancel any further events where the result returned in Cont3 <>0

Problem at the moment is that I get a false returned in the Macro regardless of the result in Cont3 whereas I expect to only get a false returned when Cont3=0.

It am guessing it may be my reference syntax but I have tried a few different things. My current syntax is as follows.

[Forms]![MAIN FORM]![Cont3]<>0

I tried Forms![Main Form].Controls!Cont3<>0 also (Access adds brackets around Forms and Cont3).

Any ideas?

 

[thornmastr]

You have an interesting problem. Unfortunately, you are using macro’s which are at best difficult to debug. They just halt. I would suggest you use the tools>macros>convert macros to visual basic then comment out the on error goto statements. That way, when they fail, you will know exactly why and where they fail, and you will have the chance to do some serious debugging.

Good luck.
Robert Berman
Data Base consultant
Vulcan Software Services
thornmastr@yahoo.com

 

[dduffy]

I don't seem to have that functionality, I will look to install it if possible.

Cheers

 

[thornmastr]

What version of Access are you using? Robert Berman
Data Base consultant
Vulcan Software Services
thornmastr@yahoo.com

 

[dduffy]

Access 2000

I have not had a lot of experience with VBA which is why I was using the Macros, but I have had to use some code already as I am fast learning the limitations of the macros.

Cheers

 

[thornmastr]

Open your form in design mode. From the access tool bar, click on tools, click on macros. at that point your first choice will be to convert forms macros to VB code.

If, for some reason, it is grayed out. Get your install CD. When you start it, one of your choices should be to add/delete options for applications. Click on Access, and select all for Access. That will get you every possible Access option, add in, library, etc. Robert Berman
Data Base consultant
Vulcan Software Services
thornmastr@yahoo.com

 

[dduffy]

I now have that sorted, trouble is it seems to think that it is fine and the code is simple enough.

It appears that the code is running fie but it does not seem to assess the value in the control as part of the argument.

You can see that I am asking to return a message box and then cancel any further processing if the (result) value in Control ContAmtVal is not equal to 0. If I step through the macroI get a false result for the if statement reagardless of the value in the control.

Private Sub closemanualrec_Click()
On Error GoTo closemanualrec_Click_Err

If (ContAmtVal <> 0) Then
Beep
MsgBox &quot;test&quot;, vbOKOnly, &quot;test1&quot;
End
End If

any ideas? I will try the control followed by the source of the control (control is actually a listbox).

Cheers

 

[thornmastr]

Assuming that the combo box is on the active form and that the name of the combo box is contamtvalue, try it like this

If me.contamtval.value <> 0 then
Beep
Msgbox “test”,vbinfo
Endif

Robert Berman
Data Base consultant
Vulcan Software Services
thornmastr@yahoo.com

 

[dduffy]

That still doesn't work, something I had not thought though.

Cheers