By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,998 Members | 2,837 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,998 IT Pros & Developers. It's quick & easy.

Codes for FIND button in MS Access Programming

P: 25
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:-)

Expand|Select|Wrap|Line Numbers
  1. Private Sub cmdFindEmployee_Click()
  3. Dim curDatabase As Object
  4. Dim rsttblUser As Object
  5. Dim fldEmployeeID As Object
  7. Set rsttblUser = curDatabase.OpenRecordset("tblUser")
  8. Set rsttblUser = curDatabase.OpenRecordset("SELECT EmployeeID, Lastname, Firstname, Role from tblUser ")
  9. With rsttblUser
  10.     Do Until .EOF
  11.         For Each fldEmployeeID In .Fields
  12.             If rsttblUser("EmployeeID").Value = txtEmployeeID Then
  13.             Me.txtLastName = CStr(rsttblUser("Lastname").Value)
  14.         Exit For
  16.             If fldN_Number.Name = "EmployeeID" Then
  17.             If fldN_Number.Value = txtEmployeeID Then
  18.             If Me.txtEmployeeID.Value = DLookup("EmployeeID", "tblUser", "[EmployeeID]='" & Me.txtEmployeeID.Value & "'") Then
  19.             txtEmployeeID = rsttblUser("EmployeeID").Value
  20.             Me.txtLastName = .Fields("Lastname").Value
  21.             Me.txtFirstName.Value = DLookup("Firstname", "tblUser", "[EmployeeID]='" & Me.txtEmployeeID.Value & "'")
  22.             Me.cboUserRole = .Fields("Role").Value
  23.             MsgBox "OK"
  24.             .Edit
  25.             .Fields("Role").Value = cboUserRole
  26.             .Fields("LastName").Value = txtLastName
  27.             .Fields("FirstName").Value = txtFirstName
  28.             .Update
  29.             Exit For
  30.         End If
  31.         End If
  32.     Next
  33. .MoveNext
  34. Loop
  36. End With
  38. End Sub
Feb 2 '12 #1
Share this Question
Share on Google+
5 Replies

P: 759
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.
Feb 2 '12 #2

P: 25
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.
Feb 2 '12 #3

Expert Mod 100+
P: 2,321
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.

Expand|Select|Wrap|Line Numbers
  1.        For Each fldEmployeeID In .Fields 
  2.             If rsttblUser("EmployeeID").Value = txtEmployeeID Then 
  3.             Me.txtLastName = CStr(rsttblUser("Lastname").Value) 
  4.         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.
Feb 2 '12 #4

P: 25
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.

Expand|Select|Wrap|Line Numbers
  1. Private Sub cmdFindEmployee_Click()
  2. On Error GoTo cmdFindEmployee_Error
  4. Dim curDatabase As Object
  5. Dim rstSIMMEMP As Object
  6. Dim fldN_Number As Object
  9. If IsNull(txtEmployeeID) Then
  10.     MsgBox "You must enter an N_Number.", vbOKOnly Or vbInformation
  11.     Exit Sub
  12. End If
  14. If Not IsNull(DLookup("N_Number", "SIMMEMP", "N_Number='" & txtEmployeeID & "'")) Then
  15.     txtFirstName = DLookup("Firstname", "SIMMEMP", "N_Number='" & txtEmployeeID & "'")
  16.     txtLastName = DLookup("Lastname", "SIMMEMP", "N_Number='" & txtEmployeeID & "'")
  17.     cboUserRole = DLookup("Role", "SIMMEMP", "N_Number='" & txtEmployeeID & "'")
  18. Else
  19.     MsgBox "The N_Number doesn't exist! Please try again"
  21. End If
  23. cmdFindEmployee_Error:
  24. End Sub
Feb 2 '12 #5

Expert Mod 100+
P: 2,321
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.
Feb 2 '12 #6

Post your reply

Sign in to post your reply or Sign up for a free account.