DataEntry = False, after form is open ????

[Guest]

Hi

I just read in the help file "Setting it to No by using Visual Basic is
equivalent to clicking Remove Filter/Sort on the Records menu"

the default for my form is DataEntry=True. I would like to set a Toggle when
the form is open already so that if the search box i have on the form has
criteria, then form.recordsource = myQuery (generated in VBA), otherwise i
want it to stay blank and only show a new entry but nothing else. i am
pasting in the code, if anybody has some input please help.

there may something about filters i can create, but i am not sure.

thanks,

sam


Code:
strSQL = "SELECT myTbl.* FROM myTbl "
bolCondition = False

If Not IsNull(Me.IDChoice) Then
strTempSQL = "WHERE myTbl.ID = " & Me.IDChoice
bolCondition = True
End If
If Not IsNull(Me.txtDateChoice) Then
If bolCondition = False Then
strTempSQL = " Where myTbl.DateWorked= #" & Me.txtDateChoice & "#"
Else
strTempSQL = strTempSQL & " And myTbl.DateWorked= #" &
Me.txtDateChoice & "#"
End If
bolCondition = True
End If
If Not IsNull(Me.AChoice) Then
If bolCondition = False Then
strTempSQL = " Where myTbl.AgencyID= " & Me.AChoice
Else
strTempSQL = strTempSQL & " And myTbl.AID= " & Me.AChoice
End If
bolCondition = True
End If
If Not IsNull(strTempSQL) Then
Me.DataEntry = False
Else
Me.DataEntry = True
End If
strSQL = strSQL & strTempSQL & " ORDER BY myTbl.DateWorked DESC;"
Me.RecordSource = strSQL
Me.Requery

 

[Graham Mandeno]

Hi Sam

I think you are confusing a Null value with a null (or empty) string.

Only a variable of type Variant can ever be Null.

A string may be empty (it has zero length and its value is "") but it can
never be Null.

Therefore, if strTempSQL is a string variable, then IsNull(strTempSQL) can
never be True.

I think it will work if you change your code thus:

If Len(strTempSQL) <> 0 Then
Me.DataEntry = False
Else
Me.DataEntry = True
End If

Or, to put it more simply:

Me.DataEntry = ( Len(strTempSQL) = 0 )

 

[Guest]

i greatly appreciate it. i actually modified my code yesterday but i didn't
realize the null value thing. thanks, i checked now and it works. i have
another question, unrelated, i will post it seperately.

Thanks,

sam

Hi Sam

I think you are confusing a Null value with a null (or empty) string.

Only a variable of type Variant can ever be Null.

A string may be empty (it has zero length and its value is "") but it can
never be Null.

Therefore, if strTempSQL is a string variable, then IsNull(strTempSQL) can
never be True.

I think it will work if you change your code thus:

If Len(strTempSQL) <> 0 Then
Me.DataEntry = False
Else
Me.DataEntry = True
End If

Or, to put it more simply:

Me.DataEntry = ( Len(strTempSQL) = 0 )

--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

Hi

I just read in the help file "Setting it to No by using Visual Basic is
equivalent to clicking Remove Filter/Sort on the Records menu"

the default for my form is DataEntry=True. I would like to set a Toggle
when
the form is open already so that if the search box i have on the form has
criteria, then form.recordsource = myQuery (generated in VBA), otherwise i
want it to stay blank and only show a new entry but nothing else. i am
pasting in the code, if anybody has some input please help.

there may something about filters i can create, but i am not sure.

thanks,

sam


Code:
strSQL = "SELECT myTbl.* FROM myTbl "
bolCondition = False

If Not IsNull(Me.IDChoice) Then
strTempSQL = "WHERE myTbl.ID = " & Me.IDChoice
bolCondition = True
End If
If Not IsNull(Me.txtDateChoice) Then
If bolCondition = False Then
strTempSQL = " Where myTbl.DateWorked= #" & Me.txtDateChoice &
"#"
Else
strTempSQL = strTempSQL & " And myTbl.DateWorked= #" &
Me.txtDateChoice & "#"
End If
bolCondition = True
End If
If Not IsNull(Me.AChoice) Then
If bolCondition = False Then
strTempSQL = " Where myTbl.AgencyID= " & Me.AChoice
Else
strTempSQL = strTempSQL & " And myTbl.AID= " & Me.AChoice
End If
bolCondition = True
End If
If Not IsNull(strTempSQL) Then
Me.DataEntry = False
Else
Me.DataEntry = True
End If
strSQL = strSQL & strTempSQL & " ORDER BY myTbl.DateWorked DESC;"
Me.RecordSource = strSQL
Me.Requery