Codes for FIND button in MS Access Programming

[Midzie]

Hi everyone, anyone could help me with my codes. I have here Find button, txtEmployeeID, txtFirstName, txtLastName, cboUserRole. The user should type an employeeid to be searched in txtEmployeeID then press Find button. If txtEmployeeID is in tblUser then txtLastName, txtFirstName and cboUserRole will be populated with a value assoaciated with the employeeid and it will be enabled for editing. If txtEmployeeID is blank message should appear "Please enter an employeeID" and if txtEmployeeID value is not found in tblUser then "EmployeeID doesn't exist.". Please help me revise my codes, thanks in advance:-)

Private Sub cmdFindEmployee_Click()
 
Dim curDatabase As Object
Dim rsttblUser As Object
Dim fldEmployeeID As Object
 
Set rsttblUser = curDatabase.OpenRecordset("tblUser")
Set rsttblUser = curDatabase.OpenRecordset("SELECT EmployeeID, Lastname, Firstname, Role from tblUser ")
With rsttblUser
    Do Until .EOF
        For Each fldEmployeeID In .Fields
            If rsttblUser("EmployeeID").Value = txtEmployeeID Then
            Me.txtLastName = CStr(rsttblUser("Lastname").Value)
        Exit For
 
            If fldN_Number.Name = "EmployeeID" Then
            If fldN_Number.Value = txtEmployeeID Then
            If Me.txtEmployeeID.Value = DLookup("EmployeeID", "tblUser", "[EmployeeID]='" & Me.txtEmployeeID.Value & "'") Then
            txtEmployeeID = rsttblUser("EmployeeID").Value
            Me.txtLastName = .Fields("Lastname").Value
            Me.txtFirstName.Value = DLookup("Firstname", "tblUser", "[EmployeeID]='" & Me.txtEmployeeID.Value & "'")
            Me.cboUserRole = .Fields("Role").Value
            MsgBox "OK"
            .Edit
            .Fields("Role").Value = cboUserRole
            .Fields("LastName").Value = txtLastName
            .Fields("FirstName").Value = txtFirstName
            .Update
            Exit For
        End If
        End If
    Next
.MoveNext
Loop
 
End With
 
End Sub

[Mihail]

I think you have a tables of employes, isn't it ? So why you not design a simple combo box from where the user can select the employe ? Sort the combo box by names to make the user's life easier.

[Midzie]

Hi Mihail, thanks for the reply. I am using one form for maintaining user information. So I have additional button as ADD, SAVE, EDIT, DELETE so when user select the add button the text box controls will be used also. When user wants to edit or delete, they will also use the txtEmployeeID to find for user to be deleted or edited.

[TheSmileyCoder]

Please bear with me as I ask this question. It is not meant to be offensive though it may be perceived as such. It is not my intention.

Am I right in assumming that you have little to none knowledge of how the code works and it is simply something you copy/pasted together?

Its quite important information for us to know, in order to best help you.


Example:

       For Each fldEmployeeID In .Fields 
            If rsttblUser("EmployeeID").Value = txtEmployeeID Then 
            Me.txtLastName = CStr(rsttblUser("Lastname").Value) 
        Exit For 

This loops over a collection of fields, and for each field found, it looks in a specific field in a recordset . Its kinda like saying: For each apple hanging on the tree look through the box on the ground to find a pear. There is no correlation between your loop and your actions.

[Midzie]

Yes, it's ok. I'm new to programming but I understand a little logic:-) I find it hard doing MS Access VBA that's why I joined here. Anyway, I'm done with my Find button. Here's my code to help beginners like me:-) Thanks everyone for helping us.

Private Sub cmdFindEmployee_Click()
On Error GoTo cmdFindEmployee_Error
 
Dim curDatabase As Object
Dim rstSIMMEMP As Object
Dim fldN_Number As Object
 
 
If IsNull(txtEmployeeID) Then
    MsgBox "You must enter an N_Number.", vbOKOnly Or vbInformation
    Exit Sub
End If
 
If Not IsNull(DLookup("N_Number", "SIMMEMP", "N_Number='" & txtEmployeeID & "'")) Then
    txtFirstName = DLookup("Firstname", "SIMMEMP", "N_Number='" & txtEmployeeID & "'")
    txtLastName = DLookup("Lastname", "SIMMEMP", "N_Number='" & txtEmployeeID & "'")
    cboUserRole = DLookup("Role", "SIMMEMP", "N_Number='" & txtEmployeeID & "'")
Else
    MsgBox "The N_Number doesn't exist! Please try again"
 
End If
 
cmdFindEmployee_Error:
End Sub

[TheSmileyCoder]

Its fine to be new in Access. We have all been there at some point. Its just easier to give the correct help if we are familiar with your level.

While your solution will probably give you what you want it is quite in-effective. Having to use 4 Dlookups is one thing when your in the testing phase and probably have around 10 or 100 records in your database. Once it goes live and you maybe have 1000 or 30.000 or more records, having to do 4 dlookups after another can have a quite severe impact on your performance.



I need to ask why you are not using a bound form? Access is based on using bound forms, and it is what makes access a good tool for alot of projects. But if your not using that feature, its kinda look grabbing a good hammer and then using to wrong end to hit the nail with.