Odd problem on form open button

[Ebes1099]

I have a form with a search feature on it. It searches by a field called Code. Codes are in the form D****, the stars are all numbers. So D0240, D9820, D6513 are all valid codes. Instead of having to type the D in every time, I have the search feature so you can just type in the 4 digit number and in the code it appends the D and pulls up the correct record.

Then I have a button on the form to open another form where you can associate rules to the code. It opens the Rules form with the correct code pulled up based on what Code you were on when you clicked the button on the Codes form. This works fine if you open the form and don't use the search feature and just scroll the records with the scroll on the bottom. Also, I had the search before where you had to type the "D" and it worked fine.

Now when I changed it to where the "D" was appended in the code, that button no longer works. If I open the codes form and do a search to pull up a Code, then click the Rules button for that code, it doesn't open the rules form at all, it just goes to a blank record on the Codes form. I can't understand why it's doing that. Here's the code for the Rules button.

Private Sub Rules22_Click()
On Error GoTo Err_Rules22_Click

    Dim stDocName As String
    Dim stLinkCriteria As String

    stDocName = "Code_Rules"
    stLinkCriteria = "[Code]=" & "'" & Me![CodeNumber] & "'"
    
    DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_Rules22_Click:
    Exit Sub

Err_Rules22_Click:
    MsgBox Err.Description
    Resume Exit_Rules22_Click
    
End Sub

Then in my search I have this...

Private Sub FindButton_Click()
    If IsNull(Me.FindCode) Then
        MsgBox "Enter a Code"
    Else
        Call CodeSearch(Me.FindCode.Value)
    End If
End Sub

Function CodeSearch(CodeNmbr As String)
    Dim answer As String

    answer = "D" & CodeNmbr
    If answer <> "" Then
       DoCmd.OpenForm "Codes", , , "[Code]='" & answer & "' "
    End If
    [Forms]![Codes].FindCode.Value = ""
End Function

Anyone have any idea why this happens?

 

[dhookom]

Learn how to troubleshoot faq705-7148.

Duane
Hook'D on Access
MS Access MVP

 

[Ebes1099]

I've done that. I put a breakpoint on the first line in my Rules22 function and went through it step by step. I did this for when I knew the button would function properly and it worked as expected.

But then I tried it again after I performed a search and it never executed any of the code because it just brought up the blank form right away, never getting to my break point. And I tried the break point on the OnError line and the Dim line.

 

[dhookom]

If the code doesn't run then you need to make sure the event procedure is attached to the event property.

Next time, you shouldn't wait to a later post to tell us you already discovered something when troubleshooting.

Duane
Hook'D on Access
MS Access MVP

 

[Ebes1099]

The code does run, as long as I open the form and don't do a search. If I just scroll to the record using the arrows on the bottom and click the button it works fine.

I don't change anything on the Event Procedure of the button when I do the search. But if I open the form and do a search for a code, then try and click the button I get the problem I described earlier.

Nothing in the search function does anything to the button. The only difference between the two search options I had is one appends the "D" in the back end code, and the other one the user has to type the "D" in when they search.

Example...

Option 1

Private Sub FindButton_Click()
    If IsNull(Me.FindCode) Then
        MsgBox "Enter a Code"
    Else
        Call CodeSearch(Me.FindCode.Value)
    End If
End Sub

Function CodeSearch(CodeNmbr As String)
    Dim answer As String

    [b]answer = "D" & CodeNmbr[/b]
    If answer <> "" Then
       DoCmd.OpenForm "Codes", , , "[Code]='" & answer & "' "
    End If
    [Forms]![Codes].FindCode.Value = ""
End Function

Option 2...

Private Sub FindButton_Click()
    If IsNull(Me.FindCode) Then
        MsgBox "Enter a Code"
    Else
        Call CodeSearch(Me.FindCode.Value)
    End If
End Sub

Function CodeSearch(CodeNmbr As String)
    Dim answer As String

    [b]answer = CodeNmbr[/b]
    If answer <> "" Then
       DoCmd.OpenForm "Codes", , , "[Code]='" & answer & "' "
    End If
    [Forms]![Codes].FindCode.Value = ""
End Function

 

[randy700]

What happens if you pass code with the D all ready added?

Call CodeSearch("D" & Me.FindCode)

Randy

 

[Ebes1099]

I get the same result.

I took out the Error handling in the code for the Rules22 button click and put in a MsgBox to try and check what the value of [CodeNumber] was when I clicked the button. Just like before I would get the right CodeNumber value when I hadn't performed a search.

But if i had performed a search, the MsgBox wouldn't come up. And since I took out the error handling the MsgBox is the first line of Code in that Sub.

So I'm convinced that this code is not being run on the click. I don't understand why. What could be happening in that search function that changes the EventProcedure for that button click?

 

[dhookom]

I'm not sure I understand all your logic but this code opens a form with a subset of the records. If you attempt to find other codes in your form Codes, they won't be there since the form record source is filtered.

Function CodeSearch(CodeNmbr As String)
    Dim answer As String

    answer = "D" & CodeNmbr
    [green]'If answer <> "" Then don't need this[/green]
       DoCmd.OpenForm "Codes", , , "[Code]='" & answer & "' "
    [green]'End If don't need this[/green]

    [Forms]![Codes].FindCode.Value = ""
End Function

Duane
Hook'D on Access
MS Access MVP

 

[Ebes1099]

Yea I understand that. It opens the form to only the record of that code. That's fine, but when I try and click the Rules button after I do the search. It just makes the form blank and the Event code never gets run. It should look at the Code it's on, and open the Code_Rules form to that code.

Also, is there a way for me to do that search without filtering the form?

 

[dhookom]

Is the FindCode text box bound to a field in your record source?

Duane
Hook'D on Access
MS Access MVP

 

[Ebes1099]

Nope, it is unbound. I just use it so the user can enter a value and I grab it to find the matching record.

 

[Ebes1099]

I found a way to do the search without the filter. This is how I'm doing it now...

Private Sub FindButton_Click()
    Dim rs As Object
    Dim answer As String
    
    Set rs = Me.Recordset.Clone
    answer = "D" & Me.FindCode.Value
    
    rs.FindFirst "[Code]=" & "'" & answer & "'"
    
    If rs.EOF Then
        DoCmd.GoToRecord , , acNewRec
    Else
        Me.Bookmark = rs.Bookmark
    End If
    Me.FindCode.Value = ""
End Sub

In that code above, I just type the number portion of the code in and it will take me to that record. But then again, the Rules button won't work. I click the rules button and it takes me back to first record. I still have no idea why this happens and the EventPrpcedure doesn't get executed for that button. If I click the button after I get taken back to the first record then it opens the form correctly. Very strange.

Also, if I use this code...

Private Sub FindButton_Click()
    Dim rs As Object
    
    Set rs = Me.Recordset.Clone
    
    rs.FindFirst "[Code]=" & "'" & Me.FindCode.Value & "'"
    
    If rs.EOF Then
        DoCmd.GoToRecord , , acNewRec
    Else
        Me.Bookmark = rs.Bookmark
    End If
    Me.FindCode.Value = ""
End Sub

and force the user to type in the entire Code (with the preceding "D") then I can do the search and the button works fine.

Something about having the search without having to type the leading "D" in screws everything up.

 

[dhookom]

Is there any other code running in either of your forms? Have you tried littering your code with Debug.Print statements to see what is being run when?

Duane
Hook'D on Access
MS Access MVP

 

[Ebes1099]

I figured out what the problem was. I will share in case anyone was wondering or checks this thread later with the same problem.

In the Text Box where the user typed in the Code to search for, I had an event on Lost Focus. (I now see I didn't even show that part of the code in this thread which explains why nobody knew what was going on, I apologize). The issue was that after a person searched for a code, the focus was still in that Text box. So after your search when you went to click the Rules button, it was executing the Lost Focus event procedure instead of the Rules button event procedure. I ended up adding a line of code on my lost focus event to change the focus to another field on the form and everything now works.