Dropdown to select employee (1 Viewer)

tjnichols · Apr 27, 2012

Good morning. I have a dropdown that I want employees to use to select their name to add time. The following is what I have:

Private Sub Combo18_AfterUpdate()
Dim rs As DAO.Recordset

Set rs = Me.RecordsetClone
rs.FindFirst "[Employe Name] = '" & Me.cboName & "'"
Me.Bookmark = rs.Bookmark
Set rs = Nothing
Me.Requery

End Sub

I am uploading the database so you can see the whole thing. This is located in the Employees Extended Form. Essentially, I want the data in both the main and subforms to change based on the employee that's selected in the dropdown.

Thanks!

jdraw · Apr 27, 2012

A suggestion
Don't use a naming convention that allows spaces and/or special characters(*&^%$#@! etc) in field or object names.

I have acc2003 and can not open accdb files.

tjnichols · Apr 27, 2012

The code I have above works to select the employee name. It doesn't change the record that has been selected for either the main or sub forms. It the space in my field name stopping this from working properly?

Thanks.

jdraw · Apr 27, 2012

No, the space in the name is not preventing anything.
What do you mean "change"?
You haven't asked for anything to be updated/permanently changed.
What do you think should have changed?

Can you save a copy of the database in mdb format?

tjnichols · Apr 27, 2012

I want the rest of the record to update when I select different employees. The change should happen in both the main AND the sub form.

I have saved this as an mdb.

jdraw · Apr 27, 2012

I downloaded your mdb. I'm not clear on what exactly I'm looking for.
I did open your Employees Extended Form. I got an immediate error as shown in the attached jpg. It highlights cboName

Also, I noted in your code
"[Employe Name] but I don't see a field like that. I see Employee Name.
But you said this code worked????

I looked at the recordsource for the Form in the Detail section. It is
qryWeekEnding, but when I run the recordsource/query there is NO data.

Perhaps you could describe exactly what you are trying to do. What is this
Employees Extended Form intended to do?

I am confused with your set up. Please explain/describe what you expect to happen.

tjnichols · Apr 27, 2012

Yes- I have been getting that error too. Just accept it and open the Employee Extended form. Click the dropdown for Employee Name to select a different employee. Notice when the information changes - the information in the fields around it do not. Most specifically the EmpID field.

jdraw · Apr 27, 2012

I think you should describe what you are trying to do in detail. Then make sure your Tables and relationships support your business.

As I mentioned, there is no data in query qryWeekEnding.

I am attaching 2 jpgs

1 is the values in your EmployeeName table, the other is the Tables and Relationships.

sparks80 · Apr 27, 2012

Hi,

Firstly I suggest you use the primary key of the table (EmpID) to search for new records, rather than the "Employee Name" field, which is a string.

One problem is that you have a combobox with a default name like "Combo18", but you refer to it in the code as "cboName".

The next problem is that it is impossible to tell from the code if no match is found. Ideally you need use rst.NoMatch after using FindFirst, this way you display a message to the user if no matching record is found.

The last line of code where you requery the form is preventing the code from working. After moving to the desired record, Me.Requery is moving back to the original record.

The modified code would look something like this:

Code:

Private Sub cboName_AfterUpdate()
      Dim rs As DAO.Recordset
      Set rs = Me.RecordsetClone
      rs.FindFirst "[EmpID]=" & Me.cboName
      If rs.NoMatch Then
        MsgBox "No entry found"
      Else
        Me.Form.Bookmark = rs.Bookmark
      End If
      Set rs = Nothing
      DoCmd.GoToControl "cboName"
      'Me.Requery
End Sub

I have modified your database and attached the revised copy here.

Note that the combobox now has two columns, the first being EmpID, which is hidden by changing the columnwidth to 0. I have changed the combobox name to "cboName".

tjnichols · Apr 27, 2012

Sparks80 - YOU ROCK! The problem is I want the EmpID Field in both the Employees Extended form and the Hours subform to match the employee that is selected in the Employee Name field you changed my code on. Does this make any sense?

For instance, if Mickey Mouse is selected, the EmpID would be 17. If Scooby Doo is selected, the EmpID would be 19. Does this help?

Essentially, I want the records in the subform to be filtered based on the employee that is selected in the main form. The information for the main form should match the employee that is selected though.

Thanks!

tjnichols · Apr 27, 2012

OK Sparks80 - I was totally wrong! This works!! Thank you SOOO MUCH!!!

sparks80 · Apr 28, 2012

Hi,

I'm really glad I could help. Here is some more information that might help.

You have set up the table correctly with an Autonumber primary key called EmpID.

This field is very useful because of several things:
- it can be used as a unique ID for each employee in the database. To ensure that no duplicate employee details are entered into the table you may have to create an additional index, but this is very easy to do
- The ID can be included in other tables that link to the employee details table. Access is more efficient at matching two numbers compared with two text fields
- For the same reason the FindFirst method for searching for a record should be faster as well.

By the way you do not have to display the ID field on your forms - in fact it is better not to. You can leave the ID field "behind the scenes" as it were, to help locate records, and link your main form with the subform. The mechanics of the form will still work.

Dropdown to select employee (1 Viewer)

tjnichols

Registered User.

Attachments

jdraw

Super Moderator

tjnichols

Registered User.

jdraw

Super Moderator

tjnichols

Registered User.

Attachments

jdraw

Super Moderator

Attachments

tjnichols

Registered User.

jdraw

Super Moderator

Attachments

sparks80

Physicist

Attachments

tjnichols

Registered User.

tjnichols

Registered User.

sparks80

Physicist

Similar threads

Users who are viewing this thread