Form and Subform 1

[Thomas5000]

I have a form and subform. They are both linked.

In the main form there is a combo box with departments. In the subform is a datasheet of employee related info such as vacation time, sick time, etc.

I want to use the combo box to display all the employees and their info in that department. Currently, when i change the combo box it changes the department all these employees are in. that is not what i want. The combo is supposed to be the navigational tool. When the department change the info in the subform changes.

Are there any online links for this? Also there is the typical recordset arrows at the bottom. Since the combo box will be the navigational instrument, i don't see a need for it.

I have a query in the subform Record Source:

SELECT Employee.SSN, Employee.HireDate, Employee.DepartmentID,
sum(sicktime.sicktime) AS Total_SickTime, sum(personaltime.personaltime) AS
Total_PersonalTime, sum(vacationtime.vacationtime) AS Total_VacationTime
FROM ((Employee INNER JOIN personaltime ON
personaltime.employeeid=employee.employeeid) INNER JOIN vacationtime ON
Employee.EmployeeID=vacationTime.EmployeeID) INNER JOIN SickTime ON
Employee.EmployeeID=SickTime.EmployeeID
WHERE employee.employed=true
GROUP BY employee.SSN, Employee.hiredate, Employee.DepartmentID;

In the main form i have this query:

the other query is found here.

SELECT department, departmentid
FROM Department
ORDER BY department;

They are linked through DepartmentID

Thanks!

 

[ThomasLafferty]

Your combo box sounds like its data source is the table. From the form design view, delete the combo box. On the forms toolbar, make sure that your wizard button is depressed. Select the combo box control, and when the wizard prompts you, do not change the default which reads, "I want the combo box to look up values in a table or query."

In the next page of your wizard, click the Query button and select the name of the query which feeds your subform.

Click Next.

In the Available Fields list, select DepartmentID and Department.

Click Next.

When prompted, hide your key code, and give your combo an appropriate label like "Select Department".

Hope this helps!

Tom

Live once die twice; live twice die once.

 

[Thomas5000]

Ok, i did what you said, but i ended up with two columns in my combo box. One side has numbers and the otehr side has numbers and departments.

Also, there were duplicates.

What i did differently was tie the combo box to another query, which displays the departments. this is distinct.

To prevent the two columns, of course, when it asked which columns to display, i simply decided to move only departments over instead of departmentid.

HERE IS THE PROBLEM:
Now the only problem i have is that the combo box still doesn't do its job. IF i change the combo box, department changes for those employees.

The only way i can navigate is using the recordset arrow at the end of the main form. it will move through the departments and display departmental info. But i want the user to use the combo box and go DIRECTLY to the department and not risk them unnecessarily looking at other departments info. Besides i don't think users will know what the arrow means at the bottom of the main form. They may not even notice it.

Can this be done? thanks so much!

 

[ThomasLafferty]

Oops! I had you pick this option:

[quote
"I want the combo box to look up values in a table or query."
[/quote]

I should have had you chose this option:

Find a record on my form based on the value I selected in my combo box."

Check the AfterUpdate procedure on the control properties for your combo box's event tab. Access should have generated some code like this:

Private Sub [COLOR=red][b]NameOfYourComboBox[/b][/color]_AfterUpdate()
    ' Find the record that matches the control.
    Dim rs As Object

    Set rs = Me.Recordset.Clone
    rs.FindFirst "[DepartmentID] = " & Str(Nz(Me![COLOR=red][b][NameOfYourCombobox][/b][/color], 0))
    If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub

The items in red will need to be changed to match your control names. As for seeing 2 columns, when using the wizard, go ahead and bring over the DepartmentID as well as the Department, just make sure you select the option labeled, "Hide Key Column (recommended)."

Hope this helps.

Tom

Live once die twice; live twice die once.

 

[Thomas5000]

Thanks for the information. Sorry for taking so long to reply.

I'm getting closer. Thanks very much.

Find a record on my form based on the value I selected in my combo box.

Helps me achieve my main goal.


Here are some problems I'm having

rs.FindFirst "[DepartmentID] = " & Str(Nz(Me![Combo11], 0))

This line gives me an error message. It says type mismatch. Why do i need this code anyway? Just wondering.

As for seeing 2 columns, when using the wizard, go ahead and bring over the DepartmentID as well as the Department, just make sure you select the option labeled, "Hide Key Column (recommended)."

I didn't see "Hide key column" in either the wizard or the properties for the combo box. What i did was set the column width to 0 for the second column so that it doesn't show.

The last problem appears to be data changes.
- When i use the arrow keys (I'm assuming this is the problem) at the bottom of the main form, some of my data gets mixed up. People end up in the wrong department. I now have a combo box with several duplicate departments. Also, departments can be added to the list. by typing at the end of the record set.

Sorry. But I'm getting close and i appreciate your help and patience.

 

[ThomasLafferty]

Probable reason for the data type mismatch error:
You may have only brought over the Department, which is a string.

To prevent the two columns, of course, when it asked which columns to display, i simply decided to move only departments over instead of departmentid.

The code is expecting DepartmentID, which is numeric. In order to prevent an error, you have to deal with the possibility for null values propagating through your combobox. Nz converts nulls to zero length strings. Using your query with SELECT DISTINCT should take care of duplicate departments.

Could you clarify what is meant by people ending up in the wrong department? The combo dropdown as I have envisioned you using it is as a navigational device and does not in any way write to your table on your main form. If you are using it to move from record to record, and then scroll to a new record, the combo will stay display whatever the last selected value was, but this does not change what's in your table. If you want to prevent users from adding departments from the combo, set its Limit to List property to Yes. In order to add a new department, they have to write it directly to the table, or through another form which writes to the table. Am I tracking with you so far?

Tom

Live once die twice; live twice die once.