Search Form & Error Messages (1 Viewer)

Gaddy

Wookie
Joined
Dec 16, 2012
Messages
46
SOLVED: Search Form & Error Messages

Hi All,

I have two unbound text boxes and a search button adjacent each that allows me to search for a user via two methods:

1) Payroll ID
2) Surname

They have the following code:

txtPayrollIDSearch

Code:
Private Sub txtPayrollIDSearch_AfterUpdate()

    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 Sub
cmdPayrollIDSearch

Code:
Private Sub cmdPayrollIDSearch_Click()

    If IsNull(Me.txtPayrollIDSearch.Value) Then
        MsgBox "Please enter a value", 48, "Search Failed"
    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
Then minor differences between the above and the Surname search.

I have two questions:

1) How do I make the search more friendly by allowing it to find partial matches, i.e., a user has a surname of 'Smith' but I want to search for 'Smi'?

2) How can I display further error messages if there aren't any matches?
 
Last edited:

pbaldy

Wino Moderator
Staff member
Joined
Aug 30, 2003
Messages
33,537
1) Use Like instead of = plus wildcard(s):

sWHERE = "[PayrollID] Like '" & Me.txtPayrollIDSearch & "*'"

2) As mentioned on your other thread, you can use DCount() with the same criteria to test before opening the form.
 

Gaddy

Wookie
Joined
Dec 16, 2012
Messages
46
It works on the Surname search but just not in the PayrollID search.

An example Payroll ID number is JD12345 with an input mask of 'LL00000'.

I'm really new to all this so don't know what you meant by the DCount() bit...
 

Gaddy

Wookie
Joined
Dec 16, 2012
Messages
46
I'm not sure that's going to be of any use to me as it returns all 11 users in the table, none of which have the same combination of letters or numbers.

For reference, this is what I have in my code now:

Code:
Private Sub txtPayrollIDSearch_AfterUpdate()
    
    If DCount("*", "tblUser") > 0 Then

    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
    
    Else
    
    MsgBox "Blah blah"
    End If
    
End Sub
 

Gaddy

Wookie
Joined
Dec 16, 2012
Messages
46
Did you miss:
I did - thanks.

Code now reads:

Code:
Private Sub txtPayrollIDSearch_AfterUpdate()
    
    If DCount("*", "tblUser") > 0 Then
    
    Dim sWHERE As String
  
    sWHERE = "[PayrollID] = " & Me.txtPayrollIDSearch
    sWHERE = "[PayrollID] LIKE '" & 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
    
    Else
    
    MsgBox "Blah blah"
    End If
    
End Sub
I've now removed my input mask on the form too because it would only return the one record this time.

I still want to prevent these types of searches:

JD123456789
JackDaniels12345

(A valid Payroll ID is JD12345.)

EDIT: I've just searched 'BB' of which there is nothing similar and it doesn't display my error message, it just opens the form with no records. :confused:
 

Gaddy

Wookie
Joined
Dec 16, 2012
Messages
46
I think I'm over-complicating it.

I'm sticking to two search boxes. One for if the user knows the Payroll ID and the other (Surname) search if they don't.

The Payroll ID will find an exact match only, but I still need it to not display anything if there aren't any matching records found.

How do I fix this?
 

SOS

Registered Lunatic
Joined
Aug 27, 2008
Messages
3,517
I did - thanks.

Code now reads:

Private Sub txtPayrollIDSearch_AfterUpdate()

If DCount("*", "tblUser") > 0 Then
You still didn't get it.

Code:
If DCount("*", "tblUser","[PayrollID] LIKE '" & Me.txtPayrollIDSearch & "*'") > 0 Then
 

Gaddy

Wookie
Joined
Dec 16, 2012
Messages
46
I didn't get it no. I still don't to be honest.

It returns 11 results if I enter in a specific ID, none of the others are even similar.
 

SOS

Registered Lunatic
Joined
Aug 27, 2008
Messages
3,517
I suggest uploading a copy of your database (with bogus data) so we can see what is happening. If you do remember to run Compact and Repair first and then ZIP the file before uploading.
 

Gaddy

Wookie
Joined
Dec 16, 2012
Messages
46
Please find attached - try not to laugh, it's my first attempt and it's obviously no where near finished.

I've taken it back prior to this DCount code.
 

Attachments

SOS

Registered Lunatic
Joined
Aug 27, 2008
Messages
3,517
I've fixed it and have combined the two searches into one. Post back if you have questions. I took the input mask and validaton off. Those aren't good for searches.
 

Attachments

SOS

Registered Lunatic
Joined
Aug 27, 2008
Messages
3,517
Oops, sorry. Remove the Exit Sub from each of the Case statements. I forgot and left those in there.
 

Gaddy

Wookie
Joined
Dec 16, 2012
Messages
46
Will do - thanks. (EDIT: It worked.)

Also if you search for 'Doe', the second record's Manager does not appear...(Actually, first, you'll need to go into enter a manager ID next to each John Doe - set 2 for the first one and 3 for the second.

EDIT: Where a user is registered to Location ID 2 or 3, the Manager does not appear. That said it doesn't on my other versions.
 
Last edited:

boblarson

Smeghead
Joined
Jan 12, 2001
Messages
32,068
Will do - thanks. (EDIT: It worked.)

Also if you search for 'Doe', the second record's Manager does not appear...
I just took a look at that file and it showed two records for me when you put in Doe. You have to navigate to the next record.
 

Gaddy

Wookie
Joined
Dec 16, 2012
Messages
46
I just took a look at that file and it showed two records for me when you put in Doe. You have to navigate to the next record.
I've just realised that the two 'John Does' don't have a manager ID assigned to them. If you assign them one, then search for 'Doe', it won't return the Manager name if the Location ID for that manager is anything other than 1.

EDIT: If you enter the manager ID of say 3 for the second John Doe Record, and search for that unique payroll ID for the second, the Manager name will show up. It only dissapears when you search and it displays both records... It dissapears and re-saves the record without a Manager ID...:confused:
 
Last edited:

boblarson

Smeghead
Joined
Jan 12, 2001
Messages
32,068
I don't understand what you are doing with the code:
Private Sub cboLocationID_Change()

Me.cboManagerID.Requery
Me.cboManagerID = ""

End Sub

Private Sub cboManagerID_GotFocus()

Me.cboManagerID.Requery
Me.cboManagerID = ""


What is the purpose of that? Why would you set the ManagerID to "" when you just move into that combo?
 

Gaddy

Wookie
Joined
Dec 16, 2012
Messages
46
Hi Bob,

The form, 'frmUser', I am using to add a new user as well as edit an existing one.

The Location and Manager comboboxes are cascading combo boxes, so I only get a relevant selection of Managers based on a certain Location.

That code is in there because if I am adding a new user and have selected a Location and Manager, and then swap the Location out, it will remove the Manager and update the list so it doesn't allow the user to save with an invalid Location/Manager combo.

I've tried it without the code and it still does the same thing.


EDIT: I think it might be to do with the row source and my query which I think picks up the location ID from the form which is number 1 and doesn't update for the other locations.
 
Last edited:

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

Top