Display Question

[jo15765]

I have a combo box that is set to display Employee Names. What I want is when an employee name is selected from the combo box, I want a (I assume this is the best control to use) a subform/subreport to display on the form, that will select about 6 fields from a table and display them visually to the user.

How would I go about setting this up? And is a subform/subreport the best way to handle this?

[Micron]

The idea seems fine to me. Typically this would be an unbound combo, rowsource set to a query or sql statement that retrieves the emplID and the names (in that order). User chooses a name, the combo afterUpdate event checks that a value was picked, then that code loads the subform with records that match the combo's first column - the employee id. You can find lots of written directions and vid tutorials on this if you need to.

[jo15765]

Ohhh, it should be an unbound combo box?

What search terms would I use to find this video? I've looked but maybe it's wrong search terms

[Micron]

ms access filter subform from main form combobox

gave me 51 results, which is less than I expected. Yes, it should be unbound. Most of the time, search parameter combos should be; otherwise each time you select a new value, you overwrite the field that the combo is bound to.

[jo15765]

Geez, so simple when you know what search terms to use!

Do I need employeename to be visible in the sub-form in order to filter on it?

[Micron]

No, but I might to begin with just so I knew I had constructed this correctly, then perhaps just hide the control in case I changed my mind later. However, I think you will need to at least have the employee id field on the subform, assuming you have such a thing.

I would think that the tutorials would reveal the answer to your question, so maybe do the review, try it, and ask questions if you get stuck?

[jo15765]

What setting do I need to change so the form auto-expands to the data?

I've got auto-reize = Yes

[Micron]

Even with section CanGrow, subform control CanGrow and subform CanGrow and subform is datasheet I can't recall getting that to work if the subform is in the header or footer. IIRC, I've never got it to work with the subform in the detail section either. Maybe someone else who has will chime in.

[jo15765]

Okay, I'm missing something. I have this code in the AfterUpdate event of my Unbound ComboBox

Private Sub Combo4_AfterUpdate()
    
    [Forms]![frmEmployeesToSales]![sbfrmEmployeesToSales].Form.Filter = "[Field] = '" & Me.cboEmployeeName & "'"
    [Forms]![frmEmployeesToSales]![sbfrmEmployeesToSales].Form.FilterOn = True
End Sub

But when I select an employee name from the combo box the sub form is not filtered?

[usfarang]

Field ? Is that the real name of a field on your form? If so I would change it as it's likely to cause problems.
What does happen exactly? Anything, Errors?

[jo15765]

Hah, I missed that. I changed it to be

EmployeeName

but still no error, and still not filtering based off the selection of the combo box

And EmployeeName is the field name from the sub form that I want to map to the Employee Name selected from the combo box of the parent form

******EDIT*******
I found the issue, I did a

Debug.Print Me.cboEmployeeName

And instead of printing the EmployeeName it's printing the EmployeeId

How can I change this to capture the EmployeeName!

[Micron]

I addressed that in my first post - said employee id, not name. Name will almost never be unique but ID (usually autonumber for the record) will be. Your empl table should have a unique record id and you should be using that. You show the name(s) in the combo but pass the id for the chosen name to your forms/reports/queries.

[jo15765]

AH yes, that did it.

I had to add it as a hidden field to the sub-form.

Geez, way harder than it should have been.

[jo15765]

Is there a way for me to remove the whitespace between my last column and the scrollbar?

[Micron]

Is that a blank column? Columns you don't want to see need the column width property set to 0.
Or your control width exceeds your list width and/or the total of your column widths.