Opening a form depending on IF statement

[MadCat]

Hi all. I have a form that i wish only to open if a particular criteria is met. The code i have got so far is as follows:

Private Sub OpenTraineeDetailsBtn_Click()

MsgBox Me.NumOnCourse
MsgBox Me.Location.Column(2)
If (Me.NumOnCourse < Me.Location.Column(2)) Then

On Error GoTo Err_OpenTraineeDetailsBtn_Click

Dim stDocName As String
Dim stLinkCriteria As String

DoCmd.GoToRecord , , acNext
DoCmd.GoToRecord , , acPrevious
stDocName = "Trainee_Details_FM"
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_OpenTraineeDetailsBtn_Click:
Exit Sub

Err_OpenTraineeDetailsBtn_Click:
MsgBox Err.Description
Resume Exit_OpenTraineeDetailsBtn_Click

Else
MsgBox "No More Trainees Allowed On Course"
End If

End Sub

The initial messgae boxes output the correct values so i know my variables are correct in the if statement. The Statement however always opens the form, Even when the NumOnCourse is greater than Column 2 in the location combobox (this is the capacity of a location). I was hoping someone could pick the fault in my code as to why it seems to be ignoring the else part of the statement

This may help. When looking at the VB debugger the value in the NumOnCourse is not within "". Where as the column value is in quotes. is there a way i can get them both the same.


Thanks in advance.

 

[FoFa]

First, I would change your code more like this:

Private Sub OpenTraineeDetailsBtn_Click()

MsgBox Me.NumOnCourse
MsgBox Me.Location.Column(2)
If (Me.NumOnCourse => Me.Location.Column(2)) Then
MsgBox "No More Trainees Allowed On Course"
Exit Sub
End If
On Error GoTo Err_OpenTraineeDetailsBtn_Click

Dim stDocName As String
Dim stLinkCriteria As String

DoCmd.GoToRecord , , acNext
DoCmd.GoToRecord , , acPrevious
stDocName = "Trainee_Details_FM"
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_OpenTraineeDetailsBtn_Click:
Exit Sub

Err_OpenTraineeDetailsBtn_Click:
MsgBox Err.Description
Resume Exit_OpenTraineeDetailsBtn_Click

End Sub

If it is always opening the form, then your IF criteria is true or in the case above, not true (false).
Also verify the data types are correct for the compare.

 

[MadCat]

I think it may be the data types but i'm not sure where i can change them. I have changed the format of the NumOnCourse text box so that it is general number and that the capacity column in the location lookup table (location.column(2)) is set to a number data type and is a general number format. This is still producing a discrepancy when viewing the de-bugged output.

I'm really stuck with where else i need to change the datatypes.

Thanks for the reply

 

[MadCat]

Thanks Pat, i learn something new everyday. Any ideas with regards to where the data types may be wrong with my initial problem. I've changed all i can think of so that they are in the general number form.

Thanks

 

[MadCat]

It now doesn't matter. i got the problem sorted with the CInt() function. Thanks to the both of you for your help.


Cheers