Enter Key & Search

[Gaddy]

Hi All,

I have a simple search form that has a two text boxes and two search buttons. One of the search buttons and text boxes is as below:

txtPayrollIDSearch and cmdPayrollIDSearch

I have the following code for the search button:

Private Sub cmdPayrollIDSearch_Click()

    If IsNull(Me.txtPayrollIDSearch.Value) Then
        MsgBox "Please enter a value", 48, "Unable to search!"
    Else
    
    Dim sWHERE As String
    
    sWHERE = "[PayrollID] = " & Me.txtPayrollIDSearch
    sWHERE = "[PayrollID] = '" & Me.txtPayrollIDSearch & "'"

    DoCmd.OpenForm "frmUser", acNormal, , sWHERE, acFormEdit

    Forms!frmUser.Header0.Caption = "Edit User"
    Forms!frmUser.txtPayrollID.Enabled = False
    Forms!frmUser.txtForename.Enabled = False
    Forms!frmUser.txtSurname.SetFocus
    
    End If
    
End Sub

When the button is clicked it will bring up a message box if the text box is empty, if it's not then it will open frmUser, edit the caption of the header in the form, disable a couple of fields and set the focus to the Forename field.

What I want to be able to do is rather than having to click the button, I want to just type in my search criteria and press the Enter key. Currently, I have to press enter twice... Once to set focus to the button and then the second time launches the code.

I've tried setting the above code to On Focus too, but I am trapped on the Forename field and can't close down the form.

I can also assign the same code to the On Exit event for either text box but if I click into one and do not type anything into it and click into the other search button (because I've changed my mind and want to use the other search criteria) it brings up my message box which is a little irritating.

How do I solve this conundrum?

Many thanks

EDIT: Solved: Use After Update.

 

[pbaldy]

Try the after update event of the textbox. You can trap for the tab key, but I'm not sure you want to. The user could tab out of the textbox instead of using the enter key.

 

[Gaddy]

Hi,

I've tried after update - it doesn't do anything other than move to the search button.

EDIT: Me being an idiot I had fiddled with the code just before this post and had removed the 'Else' bit from the code.

 

[pbaldy]

That code in the after update event of the textbox should execute when the user hits enter after changing the contents.

 

[Gaddy]

Disregard.

 

[pbaldy]

Can you post the db here? I wonder if the code is even running.

 

[Gaddy]

Disregard; I thought it might have not been working because the text box had an input mask.

Unrelated question: Furthermore, if I search for something that doesn't exist, it opens up the form anyway with a blank record - how can I prevent that?

 

[pbaldy]

An input mask shouldn't affect this. You can use a DCount() with the same criteria to test for records before opening the form.

 

[Gaddy]

I've stripped down the code a bit and now it thinks the text box is empty because the message box keeps coming up. I've removed the message box section of the code and it works fine.

Previously it didn't work properly because I had forgot to change "[Surname]" to "[PayrollID]" and "Me.txtSurnameSearch" to "Me.txtPayrollIDSearch", etc.

sWHERE = "[Surname] = " & Me.txtSurnameSearch
    sWHERE = "[Surname] = '" & Me.txtSurnameSearch & "'"

It doesn't work properly with the this bit though:

If IsNull(Me.txtSurnameSearch.Value) Then
        MsgBox "Please enter a value", 48, "Unable to search!"
End If

The message comes up even though it isn't null.

 

[Gaddy]

Please delete this thread.

 

[pbaldy]

If you can't post the db, I can only guess that the textbox name is wrong or something. I've never seen the Null test fail like that.

 

[Gaddy]

Somewhere along the line I had removed 'Else' from the 'If' statement - what a tool.

Please delete thread.