Boolean as flag 3

[Cloonalt]

I have a boolean declared as
Public blnchk As Boolean in the Declaration area of a form and Option Explicit set

On after update of a text box I have:

blnchk = True
CheckBoxData (blnchk)
If blnchk = False Then
Me.txtBox_Num = ""
Me.txtBox_Num.SetFocus
Exit Sub
Else
...
End If

CheckBoxData is

Public Sub CheckBoxData(blnchk As Boolean)

On Error GoTo Err_CheckBoxData

Dim rs As ADODB.Recordset
Dim swhere As String

If IsNull(Me.txtBox_Num.Value) Or Me.txtBox_Num = "" Then
MsgBox "Please enter a valid Box Number", , "AHP - Box Lookup"
blnchk = False
Me.txtBox_Num.SetFocus
Exit Sub
Else

Set rs = New ADODB.Recordset
Dim sql As String

rs.ActiveConnection = CurrentProject.Connection
rs.CursorType = adOpenStatic
rs.LockType = adLockOptimistic
rs.Source = "SELECT Boxes.Box_num " & _
"FROM Boxes " & _
"WHERE Boxes.Box_num =" & "'" & Me.txtBox_Num & "'" & _
"ORDER BY Boxes.Box_num "

rs.Open Options:=adCmdText

If rs.RecordCount = 0 Then

If rs.BOF And rs.EOF Then
MsgBox "Not a valid Box number", vbOKOnly, "Box Lookup"
blnchk = False
Exit Sub
End If
End If
End If

rs.Close

Exit_CheckBoxData:
Exit Sub

Err_CheckBoxData:
MsgBox "Please Try Again or Contact your Administrator", , "CheckBoxData"
Resume Exit_CheckBoxData
End Sub

HOWEVER, if CheckBoxData looks at the recordset and finds no data and sets blnchk to False, it returns back to the afterupdate with blnchk set to true

Please, what am I doing? Any help is appreciated? Thanks

 

[RoyVidar]

Now this is a bit peculiar - I think what happens is that

- since you also pass the public varible to the sub, by reference, since nothing is specified, I think it might create a local version of the variable within the function, which is assigned false, so the public does not alter value at all. Now this is just an assumption, thought ...

Try removing the parameter from the sub

Also, if the Check thingie is a public sub in standard module (which I doubt, since you're referencing form controls), consider using a public variable declared in the declaration section of the standard module in stead of in the form module.

Roy-Vidar

 

[abaldwin]

I would clean this up a bit first.

1 Get rid of the blnchk variable. Unless you are using it somewhere else you do not need it.

2 Change the
Public Sub CheckBoxData(blnchk As Boolean) to a function

Public Function CheckBoxData() as Boolean

3. Change the code in the calling to
If CheckBoxData = false
Me.txtBox_Num = ""
Me.txtBox_Num.SetFocus
Exit Sub
Else
...
End If

4. Instead of doing this during the update I would do it on the loose focus. Your code is changing the value if the checkboxdata returns false. This fires off the check again. Doing this on lose focus would allow you to cancel the loose focus thus remaining on the control.





Andy Baldwin

"Testing is the most overlooked programming language on the books!

 

[Cloonalt]

Andy, thanks.

I like the idea of getting rid of the variable. I changed the sub to a function.

Public Function CheckBoxData() as Boolean

and used

If CheckBoxData = false
Me.txtBox_Num = ""
Me.txtBox_Num.SetFocus
Exit Sub
Else
...
End If

Putting checkboxdata on LostFocus didn't work. When I tried to loop through the code it just jumped back and forth between

Public Function CheckBoxData() As Boolean

and

On Error GoTo Err_CheckBoxData

otherwise having it on the afterupdate fires it off again.

And CheckboxData is now always false. Where does it get set to true?

 

[abaldwin]

Sorry, In your function you need to add an else statement.

I think here

if rs.BOF And rs.EOF Then
MsgBox "Not a valid Box number", vbOKOnly, "Box Lookup"
CheckBoxdata = false
else
checkboxdata = true
end if
rsdata.close

Dont worry about the exit sub in this if statement. The rest of the flow of code will handle that for you.

Also if you have not done so already you will need to change exit sub and end sub to exit function and end function.



Andy Baldwin

"Testing is the most overlooked programming language on the books!

 

[Cloonalt]

Thanks, Andy. Now it always returning false. Where's does it get set to true if it doesn't hit the rs.bof and rs.eof?

 

[Cloonalt]

Roy, CheckBox is a public sub called on the AfterUpdate of the text box.

CheckBoxData is a public sub called from CheckBox.

I changed the variable to a public variable in a standard module.

Still always returning to CheckBox as true.

Thanks for your help.

 

[abaldwin]

Quick look but take out the if record count = 0. My understanding is that if no records are returned then you want false else true.


Andy Baldwin

"Testing is the most overlooked programming language on the books!

 

[PHV]

Public Function CheckBoxData() As Boolean
Dim rs As ADODB.Recordset
If Trim(Me.txtBox_Num & "") = "" Then
MsgBox "Please enter a valid Box Number", , "AHP - Box Lookup"
CheckBoxData = False
Else
Set rs = New ADODB.Recordset
rs.ActiveConnection = CurrentProject.Connection
rs.CursorType = adOpenStatic
rs.LockType = adLockOptimistic
rs.Source = "SELECT Box_num " & _
"FROM Boxes " & _
"WHERE Box_num =" & "'" & Me.txtBox_Num & "'"
rs.Open Options:=adCmdText
CheckBoxData = Not (rs.BOF Or rs.EOF)
rs.Close
Set rs = Nothing
End If
End Function

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Cloonalt]

Thanks, PHV. I won't forget this one

 

[abaldwin]

YEa thanks PHV.

Andy Baldwin

"Testing is the most overlooked programming language on the books!

 

[RoyVidar]

What I tried to address was was

1 - not to pass variables as parameters to subs when they have scope within the module
2 - mind how one passes the parameters one do pass

Now

[tt]CheckBoxData (blnchk) ' the initial call[/tt]

disregarding the passing of public variable, has the problem that it passes the variable by value (which I didn't look closely enough to spot prior to first reply), which means that whatever is performed on the variable witihin a sub/function, stays within that sub or function. It is not reflected in the calling routine. So this, in itself, would ensure that no changes would occur in the "public variable".

For that to happen, one would need to pass the variable by reference (again totally disregarding that one should not pass variables when they have scope within the routine ;-)), which can be done very easily by either

[tt]CheckBoxData blnchk
' or
Call CheckBoxData(blnchk)[/tt]

CajunCenturion explains such much better than me, perhaps take a look at faq702-5309 for some more info.

You are probably also creating some unnecessary overhead. A readonly forwardonly recordset should suffice (or use DAO, which is faster on most operations on native and linked tables)

[tt]Public Function CheckBoxData() As Boolean
Dim rs As ADODB.Recordset
If Trim(Me.txtBox_Num & "") = "" Then
MsgBox "Please enter a valid Box Number", , "AHP - Box Lookup"
CheckBoxData = False
Else
Set rs = CurrentProject.Connection.Execute( _
"SELECT Box_num FROM Boxes " & _
"WHERE Box_num ='" & Me.txtBox_Num & "'",, adcmdtext)
CheckBoxData = Not (rs.BOF Or rs.EOF)
rs.Close
Set rs = Nothing
End If
End Function[/tt]

- typed not tested ...

Roy-Vidar

 

[Cloonalt]

Roy, Thanks for the good advice.