Recordset.FindNext Question ????

[pnmng49]

Hi everyone,
The following code produces an "Invalid use of Null" message.(Access 2000) I am trying to test if the strUserInput matches a value in the ORI field of tblValidationListMaster. If it does I want to set MailFlag by running strSQL; if the strUserInput is not found as a value in the ORI column then I need to notify the user. Your help would be greatly appreciated.

Private Sub cmdFindByORI_Click()
On Error GoTo Err_cmdFindORI_Click
    Dim strUserInput As String
    Dim strSQL As String
    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Set db = CurrentDb()
    Set rs = db.OpenRecordset("tblValidationListMaster", dbOpenDynaset)
    
    Do
        strUserInput = InputBox("Enter ORI or QUIT")
       
        With rs
               .FindFirst ORI = strUserInput
                   If .NoMatch = True Then
                           MsgBox ("That ORI is not in table tblValidationListMaster")
                   Else
                          strSQL = "UPDATE tblValidationListMaster SET MailFlag = True WHERE ORI = "
                          strSQL = strSQL + "'" + strUserInput + "';"
                          Debug.Print strSQL
                          DoCmd.RunSQL strSQL
                          DoCmd.RunCommand acCmdSaveRecord
                   End If
        End With

    Loop While strUserInput <> "Quit"
    
    rs.Close

Exit_cmdFindORI_Click:
    Set rs = Nothing
    Set db = Nothing
    Exit Sub

Err_cmdFindORI_Click:
    MsgBox Err.Description
    Resume Exit_cmdFindORI_Click
    
End Sub

 

[wazz]

i haven't looked through the whole thing but i spotted this:

Do
strUserInput = InputBox("Enter ORI or QUIT")
rs.FindFirst ORI = strUserInput
 
With [COLOR=red]rstblValidationListMaster[/COLOR]

Do
strUserInput = InputBox("Enter ORI or QUIT")
 
With [COLOR=red]rs[/COLOR]
    .FindFirst ORI = strUserInput

try wrapping the code in your post with code tags: [ code ] your code [ /code ] (no spaces). the code will come out in those neat boxes and you can format it.

 

[pnmng49]

Thanks wazz, I think the code is properly formatted now.

 

[wazz]

thanks. that helps.
did that little adjustment to the rs part help?

 

[pbaldy]

I don't normally use FindFirst, but I believe it's looking for a string. Since it looks like ORI is text:

.FindFirst "ORI = '" & strUserInput & "'"

 

[pnmng49]

After the adjustment to rs, I still get an "Invalid use of null" error message.
The ORI is of text data type in the table. User input would be like 0010300.

 

[gemma-the-husky]

strSQL = strSQL + "'" + strUserInput + "';"

might be this line that is the problem

the concatenation character should be & , not +

is it failing on this line, or on the findfirst line

 

[pnmng49]

I changed the + to & and it still produces error message. The error message pops up as soon as I click the command button>

 

[gemma-the-husky]

before the input box?

put a breakpoint in, and see how far it runs

 

[pnmng49]

I put a break point at
If .NoMatch = True Then.
But it does not run at all. The error pops up as soon as I click
cmdFindByORI

 

[pnmng49]

I set the breakpoint at With rs.
It now runs to that line.

 

[gemma-the-husky]

so thats the line that's wrong

what do you actually have now, in the .findfirst line

[edited
IT SHOULD BE, I thnik

.FindFirst "ORI = " & chr(34) & strUserInput & chr(34)

 

[pnmng49]

Thanks for your patience.
.FindFirst ORI = strUserInput
I also tried .FindFirst "ORI = '" & strUserInput & "'"
but either way it breaks at that line

 

[pnmng49]

I tried .FindFirst "ORI = " & chr(34) & strUserInput & chr(34)
but it still breaks at that line.

 

[gemma-the-husky]

well struserinput must be null then, for some reason

is that possible?

 

[pnmng49]

Dave, thanks so much. It runs and will update the underlying table. However, the form(record source is tblValidationListMaster) on which cmdFindByORI is placed also has all ORI's displayed with a check box next to each. After the update to the table it takes a very long time for the check mark to appear. Any way to speed that up?

Also my IF THEN ELSE construct does not work correctly. If user enters Quit, I want to exit the sub(it currently displays msg box).
If user enters an incorrect ORI, I want to notify them and continue.

Code as currently running is below

Private Sub cmdFindByORI_Click()
On Error GoTo Err_cmdFindORI_Click
    Dim strUserInput As String
    Dim strSQL As String
    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Set db = CurrentDb()
    Set rs = db.OpenRecordset("tblValidationListMaster", dbOpenDynaset)
    
    Do
        strUserInput = InputBox("Enter ORI or QUIT")
        Debug.Print strUserInput
        With rs
            '.FindFirst ORI = strUserInput
            '.FindFirst "ORI = '" & strUserInput & "'"
            .FindFirst "ORI = " & Chr(34) & strUserInput & Chr(34)

                If .NoMatch = True Then
                    MsgBox ("That ORI is not in table tblValidationListMaster")
                Else
                    strSQL = "UPDATE tblValidationListMaster SET MailFlag = True WHERE ORI = "
                    strSQL = strSQL & "'" & strUserInput & "';"
                    Debug.Print strSQL
                    DoCmd.RunSQL strSQL
                    DoCmd.RunCommand acCmdSaveRecord
            
                End If
        End With
        

    Loop While strUserInput <> "Quit"
    
    rs.Close

Exit_cmdFindORI_Click:
    Set rs = Nothing
    Set db = Nothing
    Exit Sub

Err_cmdFindORI_Click:
    MsgBox Err.Description
    Resume Exit_cmdFindORI_Click
    
End Sub

 

[gemma-the-husky]

i noticed that entering quit would still run the code, and entering nothing might produce the error

so you need to test for these before doing the rs loop

i am not sure exactly what your code is doing, and why it is taking so long, though

 

[pnmng49]

Hooray!! I did as you said, tested for user input = quit before the rs loop--- working great. Also added Me.Requery after the DoCmd to SaveRecord. Refreshes form to show the check marks instantly.
Thank you very much, Dave.