Solved Dlookup using boolean criteria (1 Viewer)

chizzy42

Registered User.
Local time
Today, 17:52
Joined
Sep 28, 2014
Messages
115
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


Code:
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
Code:
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

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 01:52
Joined
May 7, 2009
Messages
19,169
Code:
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

Registered User.
Local time
Today, 17:52
Joined
Sep 28, 2014
Messages
115
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

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 01:52
Joined
May 7, 2009
Messages
19,169
you add another test, the last code i gave you does not do that.
Code:
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

Registered User.
Local time
Today, 17:52
Joined
Sep 28, 2014
Messages
115
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!
 

Users who are viewing this thread

Top Bottom