[ACCESS 97] Search multiple keywords

bodylojohn said:
One more question ;-)

How can it be that on my laptop (Windows XP (SP2) and office 97) it works perfect.

But on the pc of my friend (Windows XP (SP2), office 97) the newely built function doesnt work?

He recieved the correct file.
Click to expand...
What exactly is it doing wrong? What error message are you getting?
 
jal said:
What exactly is it doing wrong? What error message are you getting?
Click to expand...

There is no error message.
The function simply doesnt work.

I put a trace on the split function but it doesnt even get there.
And I use the same search criteria as I use on my laptop where the function DOES work.

Im lost here.
 
Does the PC have more than one version of Access installed?

Did you put the Split code in the form? Or in a module? Which one? (And make sure it isn't a class module. A standard module should work, but for troubleshooting I'd recommend you move it to the form).

Are you saying that you put a breakpoint on the line of code that calls "Split"? And yet it doesn't step into the split code when you step through using F8 to step one line at a time?

One cause of "no error messages" is if your code calls "On Error Resume Next." If so, resume error messages by means of "On Error Goto 0".
 
jal said:
Does the PC have more than one version of Access installed?

Did you put the Split code in the form? Or in a module? Which one? (And make sure it isn't a class module. A standard module should work, but for troubleshooting I'd recommend you move it to the form).

Are you saying that you put a breakpoint on the line of code that calls "Split"? And yet it doesn't step into the split code when you step through using F8 to step one line at a time?

One cause of "no error messages" is if your code calls "On Error Resume Next." If so, resume error messages by means of "On Error Goto 0".
Click to expand...

Hi Jal,

Again thanks for the quick reply.

There is only 1 version of access installed. (access 97)

I put the split code in the form.

I think its realy realy strange because the code works perfect on my laptop.
 
bodylojohn said:
I think its realy realy strange because the code works perfect on my laptop.
Click to expand...
Not so strange to me. This kind of discrepancy is frequent with computers - that's how complicated they are. For instance their might be two different versions of the JET engine runnong on those computers. Or, one could be in sandbox mode while the malfunct PC lacks it. Or any number of other possible disparities.


More to the point - are you setting breakpoints and stepping through the code line by line as it is running? We need to find out at exactly what line the code is failing or execution is halting.
 
bodylojohn said:
One more question ;-)

How can it be that on my laptop (Windows XP (SP2) and office 97) it works perfect.

But on the pc of my friend (Windows XP (SP2), office 97) the newely built function doesnt work?

He recieved the correct file.
Click to expand...

I would suggest that you check the references to make sure there are not any marks MISSING. One missing reference can cause all the VBA in a database to stop working.
 
OK guys,

I found out what the problem was.

A huge MISCOMUNICATION between me and the other person.
I will sort it out and give you the detailed story ;-)

Thanks again guys.
 
It seems that because of the misscomminication there is one last obstacle left.

This is the original code:
Code: 
 If blnNext_i Then
               .FindNext "artOms LIKE ""*" & strOms & "*"""
           ElseIf blnPrev_i Then
               .FindPrevious "artOms LIKE ""*" & strOms & "*"""
           Else
               .FindFirst "artOms LIKE ""*" & strOms & "*"""
           End If

And as the user is typing the matching record is selected.

I tried following.
But with no success.
I get the error `invalid use of null`

This is the code I wrote±
Code: 
Const comma = "," 
            Dim words As Variant, word As Variant
            words = Split(txtOmsZ, comma)
            For Each word In words
            
            Dim strTest As String
            strTest = strTest & "[artOms] LIKE '*" & Trim(word) & "*'  AND "
            Next word
            If InStr(strTest, " AND ") > 0 Then strTest = Left(strTest, Len(strTest) - 4)

             If blnNext_i Then
                .FindNext strTest
            ElseIf blnPrev_i Then
                .FindPrevious strTest
            Else
                .FindFirst strTest
            End If

Did I do something wrong?
 
I don't know if this is the problem, but definitely this line

Dim strTest As String

should come before the loop (you put it inside the loop).
 
Also make sure that no zero-length words are added, by changing this:

strTest = strTest & "[artOms] LIKE '*" & Trim(word) & "*' AND "

to this:

if len(trim(word & "")) > 0 then strTest = strTest & "[artOms] LIKE '*" & Trim(word) & "*' AND "
 
jal said:
Also make sure that no zero-length words are added, by changing this:

strTest = strTest & "[artOms] LIKE '*" & Trim(word) & "*' AND "

to this:

if len(trim(word & "")) > 0 then strTest = strTest & "[artOms] LIKE '*" & Trim(word) & "*' AND "
Click to expand...

Hello Jal,

I tried the following code and still I get the error message:
Invalid use of null.

Code: 
 Dim strTest As String
            Const comma = ","
            Dim words As Variant, word As Variant
            words = Split(txtOmsZ, comma)
            
            For Each word In words
                        
            If Len(Trim(word & "")) > 0 Then strTest = strTest & "[artOms] LIKE '*" & Trim(word) & "*' AND "
            Next word
            If InStr(strTest, " AND ") > 0 Then strTest = Left(strTest, Len(strTest) - 4)

            If blnNext_i Then
                .FindNext strTest
            ElseIf blnPrev_i Then
                .FindPrevious strTest
            Else
                .FindFirst strTest
            End If
 
You will need to set breakpoints to figure which line of code is throwing the error. By the way, you are using methods unfamiliar to me - I never use "FindFirst" and therefore have no idea what this code is supposed to be doing. What's also confusing is that you begin with a period:

.FindFirst

which implies you had a With block

With rs
.FindFirst
End With

but I don't see any With-block in your code. Where is your with-block?
 
jal said:
You will need to set breakpoints to figure which line of code is throwing the error. By the way, you are using methods unfamiliar to me - I never use "FindFirst" and therefore have no idea what this code is supposed to be doing. What's also confusing is that you begin with a period:

.FindFirst

which implies you had a With block

With rs
.FindFirst
End With

but I don't see any With-block in your code. Where is your with-block?
Click to expand...


Sorry Jal,

Here is the whole codeblock:

Original code (no invalid use of null error)
Code: 
Function InLijst(varArtNr_i As Variant, varArtOms_i As Variant, blnNext_i As Boolean, blnPrev_i As Boolean) As Boolean
    On Error GoTo Err_InLijst
    
    Dim qryRes As Recordset
    Dim strArtNr As String
    Dim strOms As String
    
    strArtNr = varArtNr_i & ""
    strOms = varArtOms_i & ""
    If strArtNr <> "" Then
        Set qryRes = [Hoofd lijst].Form.RecordsetClone
        With qryRes
            If blnNext_i Then
                .FindNext "artNr LIKE """ & strArtNr & "*"""
            ElseIf blnPrev_i Then
                .FindPrevious "artNr LIKE """ & strArtNr & "*"""
            Else
                .FindFirst "artNr LIKE """ & strArtNr & "*"""
            End If
            If Not .NoMatch Then
                InLijst = True
                [Hoofd lijst].Form.Bookmark = .Bookmark
                'SendKeys "{DOWN}"
                'SendKeys "{UP}"
                'SendKeys "{F2}"
            ElseIf blnNext_i Or blnPrev_i Then
                MeldingLog "Niet gevonden", vbInformation
            End If
            .Close
        End With
    ElseIf strOms <> "" Then
        Set qryRes = [Hoofd lijst].Form.RecordsetClone
        With qryRes
            If blnNext_i Then
                .FindNext "artOms LIKE ""*" & strOms & "*"""
            ElseIf blnPrev_i Then
                .FindPrevious "artOms LIKE ""*" & strOms & "*"""
            Else
                .FindFirst "artOms LIKE ""*" & strOms & "*"""
            End If
            If Not .NoMatch Then
                InLijst = True
                [Hoofd lijst].Form.Bookmark = .Bookmark
                'SendKeys "{DOWN}"
                'SendKeys "{UP}"
                'SendKeys "{F2}"
            ElseIf blnNext_i Or blnPrev_i Then
                MeldingLog "Niet gevonden", vbInformation
            End If
            
            .Close
        End With
        
    End If
Exit_InLijst:
    Exit Function
    
Err_InLijst:
    MeldingLog Error$
    Resume Exit_InLijst
End Function

My code (with invalid use of null error)
Code: 
Public Function ZetFilter(strIn_i As String)
    On Error GoTo Err_ZetFilter
    
    Dim iCount As Integer
    Dim lngAgrId As Long
    Dim strArtNr As String
    Dim strArtOms As String
    
    '2 parameters: de groep en het artikelnr
    iCount = StrCount(strIn_i, ";")
    lngAgrId = StrFirst(strIn_i, ";")
    strArtNr = StrNth(strIn_i, ";", 2)
    strArtOms = StrNth(strIn_i, ";", iCount + 1)
    txtOmsZ = strArtOms
    'groep
    If lngAgrId <> 0 Then
        cboAgrId = lngAgrId
    Else
        cboAgrId = Null
    End If
    ToonSub
    'zet de focus op het artikelnummer
    InLijst strArtNr, strArtOms, False, False
    Me.SetFocus
Exit_ZetFilter:
    Exit Function
    
Err_ZetFilter:
    MeldingLog Error$
    Resume Exit_ZetFilter
End Function

Function InLijst(varArtNr_i As Variant, varArtOms_i As Variant, blnNext_i As Boolean, blnPrev_i As Boolean) As Boolean
    On Error GoTo Err_InLijst
    
    Dim qryRes As Recordset
    Dim strArtNr As String
    Dim strOms As String
    
    strArtNr = varArtNr_i & ""
    strOms = varArtOms_i & ""
    If strArtNr <> "" Then
        Set qryRes = [Hoofd lijst].Form.RecordsetClone
        With qryRes
            If blnNext_i Then
                .FindNext "artNr LIKE """ & strArtNr & "*"""
            ElseIf blnPrev_i Then
                .FindPrevious "artNr LIKE """ & strArtNr & "*"""
            Else
                .FindFirst "artNr LIKE """ & strArtNr & "*"""
            End If
            If Not .NoMatch Then
                InLijst = True
                [Hoofd lijst].Form.Bookmark = .Bookmark
                'SendKeys "{DOWN}"
                'SendKeys "{UP}"
                'SendKeys "{F2}"
            ElseIf blnNext_i Or blnPrev_i Then
                MeldingLog "Niet gevonden", vbInformation
            End If
            .Close
        End With
    ElseIf strOms <> "" Then
        Set qryRes = [Hoofd lijst].Form.RecordsetClone
        With qryRes
            
                   
            
            Dim strTest As String
            strTest = ""
            Const comma = ","
            Dim words As Variant, word As Variant
            words = Split(txtOmsZ, comma)
            
            For Each word In words
                        
            If Len(Trim(word & "")) > 0 Then strTest = strTest & "[artOms] LIKE '*" & Trim(word) & "*' AND "
            Next word
            If InStr(strTest, " AND ") > 0 Then strTest = Left(strTest, Len(strTest) - 4)

            If blnNext_i Then
                .FindNext strTest
            ElseIf blnPrev_i Then
                .FindPrevious strTest
            Else
                .FindFirst strTest
            End If
            
            If Not .NoMatch Then
                InLijst = True
                [Hoofd lijst].Form.Bookmark = .Bookmark
                'SendKeys "{DOWN}"
                'SendKeys "{UP}"
                'SendKeys "{F2}"
            ElseIf blnNext_i Or blnPrev_i Then
                MeldingLog "Niet gevonden", vbInformation
            End If
            
            .Close
        End With
        
    End If
Exit_InLijst:
    Exit Function
    
Err_InLijst:
    MeldingLog Error$
    Resume Exit_InLijst
End Function

I hope you can help me on this one.

Thanks in advance..
 
Did you find out which line of code is throwing the error?
 
jal said:
Did you find out which line of code is throwing the error?
Click to expand...

I posted the whole application in access 97.
Create a folder on your C drive and call it "ofb" (c:\ofb) and unpack the front and backend there.

1. Then open the application.
2. Go to "Artikelen" (in the menubar).
3. Go to "Zoek omschr." and type something. The message "unvalid use of null" will appear.

You can download the application here:
http://www.sosol.nl/access/ofb.rar
 
I've never used a rar file. Maybe I'll try that when I have time.

However, for the third time, have you set breakpoints to figure out which line of code is throwing the error? If not, why not? Gotta go.
 
I took a brief look at your files, although there is a bit of a language barrier. Also I don't use databinding so I might not be much help. Also I had to convert to Access 2003 format to play around with it.

Anyway I noticed in the textbox's GotFocus event you call subform1.SetFocus (well, you used a German name I guess). At that point the subform, visibily, still has no records on which to setfocus so this might be the source of the error. I'm not sure why you are handling the textbox's Getfocus event to begin with, nor wny, when the user enters the textbox, your code immediately sets focus to a subform even before the user has a chance to type anything.
 
Dear Jal,

Again thank you very much for your help so far.

I finally found the line where the error is.

its on:
Code: 
Function MeldingLogLIB(strPakket_i As String, strMelding_i As String, Optional intButtons_i As Integer = 0) As Integer
    
    'tonen
    MeldingLogLIB = MsgBox(strMelding_i, intButtons_i, "VDH")
End Function

the value of strMelding_i = "invalid use of null"

But I dont know what the H.... its doing.

BTW..I am dutch..lol
 
Also in the piece of code above the one i posted earlier:

Code: 
Function MeldingLog(strMelding_i As String, Optional intButtons_i As Integer = 0) As Integer
    MeldingLog = MeldingLogLIB("OFB", strMelding_i, intButtons_i)
End Function

the value of strMelding_i is also "invalid use of null"
 
I have a feeling it's not "strictly" an Access error, probably more of a logic error. What I mean is it almost looks as though the programmer is sending a msgbox to himself whenever things are not as they should be, as part of his error routine. Maybe MeldingLog means "Error Log". Do you know this language? Is it German?

That's some pretty advanced code, so finding logic errors wouldn't be easy for me, especially with the language barrier.
 
You must log in or register to reply here.

Similar threads

A
Receiving field keeps reverting to numeric
Replies
6
Views
731
JonXL
JonXL
E
Search Button Not Finding Record and populating the text boxes on form.
Replies
13
Views
797
Pat Hartman
P
oxicottin
Cant get form filter to work correctly
Replies
8
Views
377
plog
P
K
Solved Search comboBox on a bounded form
Replies
2
Views
318
Pat Hartman
P
I
Filter between two data
Replies
3
Views
196
sonic8
S

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom