Run-time error '424': Object required - can't work out why

[dkintheuk]

Hi all,

I'm running a VERY simple bit of code that is supposed to set the
record source of the current form based on the entries in two unbound
fields to act as a rudimentary search tool.

The code is as follows... excuse my over use of ifs and thens but its
the only way my brain will logically work out what i am doing.

***Code Segment***

Private Sub Search_Click()

Dim strSQL As String, UserLogged As String

strSQL = ""

strSQL = "SELECT * FROM tblIncidents "

If (((Me.IDSearch = "") Or (Me.IDSearch Is Null)) And
((Me.KeywordSearch = "") Or (Me.KeywordSearch Is Null))) Then
'do nowt
Else
strSQL = strSQL & "WHERE "
End If

If Me.IDSearch = "" Or Me.IDSearch Is Null Then
'do nowt
Else
strSQL = strSQL & "(incidentid = '" & Me.IDSearch & "') "
End If

If (Me.IDSearch = "" Or Me.IDSearch Is Null) Then
'do nowt
Else
strSQL = strSQL & "AND "
End If

If Me.KeywordSearch = "" Or Me.KeywordSearch Is Null Then
'do nowt
Else
strSQL = strSQL & "(incidenttitle LIKE '*" & Me.KeywordSearch &
"*') "
End If

strSQL = strSQL & ";"

Me.RecordSource = strSQL

End Sub

***End Code Segment***

The Error message appears on the fifth line:

If (((Me.IDSearch = "") Or (Me.IDSearch Is Null)) And
((Me.KeywordSearch = "") Or (Me.KeywordSearch Is Null))) Then

right at the very beginning of the code. I can't understand why as the
objects in this simple line are all in scope as far as i can tell.

Any good ideas?

Rob.

[RoyVidar]

dkintheuk wrote in message
<11*********************@f14g2000cwb.googlegroups. com> :

Hi all,

I'm running a VERY simple bit of code that is supposed to set the
record source of the current form based on the entries in two unbound
fields to act as a rudimentary search tool.

The code is as follows... excuse my over use of ifs and thens but its
the only way my brain will logically work out what i am doing.

***Code Segment***

Private Sub Search_Click()

Dim strSQL As String, UserLogged As String

strSQL = ""

strSQL = "SELECT * FROM tblIncidents "

If (((Me.IDSearch = "") Or (Me.IDSearch Is Null)) And
((Me.KeywordSearch = "") Or (Me.KeywordSearch Is Null))) Then
'do nowt
Else
strSQL = strSQL & "WHERE "
End If

If Me.IDSearch = "" Or Me.IDSearch Is Null Then
'do nowt
Else
strSQL = strSQL & "(incidentid = '" & Me.IDSearch & "') "
End If

If (Me.IDSearch = "" Or Me.IDSearch Is Null) Then
'do nowt
Else
strSQL = strSQL & "AND "
End If

If Me.KeywordSearch = "" Or Me.KeywordSearch Is Null Then
'do nowt
Else
strSQL = strSQL & "(incidenttitle LIKE '*" & Me.KeywordSearch &
"*') "
End If

strSQL = strSQL & ";"

Me.RecordSource = strSQL

End Sub

***End Code Segment***

The Error message appears on the fifth line:

If (((Me.IDSearch = "") Or (Me.IDSearch Is Null)) And
((Me.KeywordSearch = "") Or (Me.KeywordSearch Is Null))) Then

right at the very beginning of the code. I can't understand why as the
objects in this simple line are all in scope as far as i can tell.

Any good ideas?

Rob.

I don't know for sure what causes the error, but in the following line
If (((Me.IDSearch = "") Or (Me.IDSearch Is Null)) And
((Me.KeywordSearch = "") Or (Me.KeywordSearch Is Null))) Then

you are testing with SQL syntax, rather than VB(A) syntax. In VBA,
use the IsNull function, or for instance combine the tests like this

if len(Me!IDSearch.value & "") = 0 then
' either Null or ""
end if

But do also ensure you've got correct spelling, too

--
Roy-Vidar

[Keith]

"dkintheuk" <rm*******@firenet.uk.com> wrote in message
news:11*********************@f14g2000cwb.googlegro ups.com...

The Error message appears on the fifth line:

If (((Me.IDSearch = "") Or (Me.IDSearch Is Null)) And
((Me.KeywordSearch = "") Or (Me.KeywordSearch Is Null))) Then

right at the very beginning of the code. I can't understand why as the
objects in this simple line are all in scope as far as i can tell.

Well "Is Null" is a SQL test, you could try using the "IsNull" VBA test.
There seem to be a lot of brackets there too.

If (Me.IDSearch = "" Or IsNull(Me.IDSearch)) And (Me.KeywordSearch = "" Or
IsNull(Me.KeywordSearch)) Then

Regards,
Keith.
www.keithwilby.com

[dkintheuk]

So blinking obvious - you're dead right of course. Got my syntax mixed
up... shouldn't have had the last beer last night.

Thanks,

Rob.