Solved Dlookup using boolean criteria

[chizzy42]

Hi all ,,I'm trying to check a table which has a Boolean field before a form is opened, the idea being if the Boolean is true then the form will not open. I already had a check in place which tested if the id number ([text4]) was in the database before the form was opened see below

If Len(Me.MyTextboxName.Value & vbNullString) > 0 Then

Dim RPSno As Long 'the primary key of the  table

RPSno = Nz(DLookup("[text4]", "tblRma", "[text4]= " & Me.MyTextboxName.Value), 0)
'MsgBox RPSno
    If (RPSno <> 0) Then
        Cancel = True
    DoCmd.OpenForm "frmRmaRemove", , , "[text4] = " & Me.MyTextboxName.Value
        Else
        MsgBox ("Number Does NOT exist in the Database")
        Me.MyTextboxName.Value = ""
    End If

End If

but I've been trying add a Boolean test that checks if the record has been removed ie the registered field is ticked. The code I'm using always comes up false, i had a look at allen browne page and the format looked to be
No delimiters. Same as numbers, e.g.:
DLookup("FieldName" , "TableName" , "Criteria = False")
but when i try

Dim boolno As Boolean
boolno = Nz(DLookup("[text4]", "tblRma", "registered = false"))

i always get a false? can anyone please see what my error is , thanks in advance

 

[arnelgp]

If Len(Me.MyTextboxName.Value & vbNullString) > 0 Then

Dim RPSno As Long 'the primary key of the  table
Dim var As Variant, IsReg As Boolean
var = Nz(DLookup("[text4] & '|' & [registered]", "tblRma", "[text4]= " & Me.MyTextboxName.Value), "")
'MsgBox RPSno
If Len(var) <> 0 Then
    RPSNo = Val(Split(var,"|")(0))
    'IsReg can be True or False
    IsReg = Val(Split(var, "|")(1))
    If (RPSno <> 0) Then
        Cancel = True
    DoCmd.OpenForm "frmRmaRemove", , , "[text4] = " & Me.MyTextboxName.Value
        Else
        MsgBox ("Number Does NOT exist in the Database")
        Me.MyTextboxName.Value = ""
    End If
End If

 

[chizzy42]

Hi arnelgp thanks for the quick reply, I tried the code there and it still opens the form if the registered field box is ticked in the form and if the number doesn't exist in the table nothing happens when i click the button, its the boolean part i seem not to be getting...thanks again

 

[arnelgp]

you add another test, the last code i gave you does not do that.

If Len(Me.MyTextboxName.Value & vbNullString) > 0 Then

Dim RPSno As Long 'the primary key of the  table
Dim var As Variant, IsReg As Boolean
var = Nz(DLookup("[text4] & '|' & [registered]", "tblRma", "[text4]= " & Me.MyTextboxName.Value), "")
'MsgBox RPSno
If Len(var) <> 0 Then
    RPSNo = Val(Split(var,"|")(0))
    'IsReg can be True or False
    IsReg = Val(Split(var, "|")(1))
    If (RPSno <> 0) Then
        If IsReg = False Then
            DoCmd.OpenForm "frmRmaRemove", , , "[text4] = " & Me.MyTextboxName.Value
        End If
      Else
        MsgBox ("Number Does NOT exist in the Database")
        Me.MyTextboxName.Value = ""
    End If
End If

 

[chizzy42]

Thanks arnelgp

 

[chizzy42]

I've got the clown shoes on today...just noticed when i used the msgbox ..the boolean field is removed.......registered is a date....DOHHHH!