dynamic combo box on subform- show full list Box on main form

[babs]

I have a combo box on the subform cbomanname its row source is based off of
queries that only show employees Not sched(ie. available) for that Given
Workdate the combo box is updated for each workdate as emplyees are sched -
names taken off of combo box. I would like to DISPLAY this combo box as a
List box on the Main Form - I copied and pasted the combo box onto the Main
Form and changed it into a ListBox(just reallywant to see all of employees
available for a given day(workdate) that sitting on in subform). but for
some reason the new copied ListBox on the Main form shows ALL of the
employees - not what is inthe combo box)

any thoughts???
thanks,
barb

 

[Marshall Barton]

I have a combo box on the subform cbomanname its row source is based off of
queries that only show employees Not sched(ie. available) for that Given
Workdate the combo box is updated for each workdate as emplyees are sched -
names taken off of combo box. I would like to DISPLAY this combo box as a
List box on the Main Form - I copied and pasted the combo box onto the Main
Form and changed it into a ListBox(just reallywant to see all of employees
available for a given day(workdate) that sitting on in subform). but for
some reason the new copied ListBox on the Main form shows ALL of the
employees - not what is inthe combo box)

any thoughts???
thanks,
barb

What is the list box's RowSource?

It sounds like you forgot to copy some code that limits the
combo box to the available employees.

 

[babs]

Marshall,
Here is code for both list box and combo box
SELECT [Jeff Employee Combo Box Time Card].[man name] FROM [Jeff Employee
Combo Box Time Card] LEFT JOIN JeffDropDownDelete ON [Jeff Employee Combo Box
Time Card].[man name]=JeffDropDownDelete.[man name] WHERE
(((JeffDropDownDelete.[man name]) Is Null)) ORDER BY [Jeff Employee Combo Box
Time Card].[man name];

the Code on the list box is IDENTICAL to the code on the row source for the
combo box.- the only difference is the combo box resides on the subform
Jefflookaheadgenerate and the list box is on the main form jeffmaingenerate.

???

thanks,
barb

 

[Marshall Barton]

That's not code, it's an SQL statement, probably used as the
list box's RowSource.

The VBA code I was referring to is probably a
Me.cbomanname.Requery
somewhere in a subform event procedure. I'm guessing that
line would be in an event related to scheduling an employee.
You will need an analogous line to also requery the main
form list box. If all my guesses are on the right track you
should add a second requery wherever you requery the combo
box:
Parent.lstmanname.Requery
--
Marsh
MVP [MS Access]

Here is code for both list box and combo box
SELECT [Jeff Employee Combo Box Time Card].[man name] FROM [Jeff Employee
Combo Box Time Card] LEFT JOIN JeffDropDownDelete ON [Jeff Employee Combo Box
Time Card].[man name]=JeffDropDownDelete.[man name] WHERE
(((JeffDropDownDelete.[man name]) Is Null)) ORDER BY [Jeff Employee Combo Box
Time Card].[man name];

the Code on the list box is IDENTICAL to the code on the row source for the
combo box.- the only difference is the combo box resides on the subform
Jefflookaheadgenerate and the list box is on the main form jeffmaingenerate.

What is the list box's RowSource?

It sounds like you forgot to copy some code that limits the
combo box to the available employees.

 

[babs]

You got it - I am just not sure where - on what control to put the requery
of the list box

the combobox on subform is on the afterupdate event of the subform
Me!cbomanname.Requery

but I would like the list box on the main form to requery after each time
the user Clicks ON a different record in the subform to - represent what
available people - would be in that man name combo box - but don't want them
to have to Click On the combo box - just want them to See it in the list box-

do I use some sort of got focus on different record on the subform - not
sure what Event that would be.

thanks soooo much for helping!
Barb

That's not code, it's an SQL statement, probably used as the
list box's RowSource.

The VBA code I was referring to is probably a
Me.cbomanname.Requery
somewhere in a subform event procedure. I'm guessing that
line would be in an event related to scheduling an employee.
You will need an analogous line to also requery the main
form list box. If all my guesses are on the right track you
should add a second requery wherever you requery the combo
box:
Parent.lstmanname.Requery
--
Marsh
MVP [MS Access]

Here is code for both list box and combo box
SELECT [Jeff Employee Combo Box Time Card].[man name] FROM [Jeff Employee
Combo Box Time Card] LEFT JOIN JeffDropDownDelete ON [Jeff Employee Combo Box
Time Card].[man name]=JeffDropDownDelete.[man name] WHERE
(((JeffDropDownDelete.[man name]) Is Null)) ORDER BY [Jeff Employee Combo Box
Time Card].[man name];

the Code on the list box is IDENTICAL to the code on the row source for the
combo box.- the only difference is the combo box resides on the subform
Jefflookaheadgenerate and the list box is on the main form jeffmaingenerate.

What is the list box's RowSource?

It sounds like you forgot to copy some code that limits the
combo box to the available employees.


babs wrote:
I have a combo box on the subform cbomanname its row source is based off of
queries that only show employees Not sched(ie. available) for that Given
Workdate the combo box is updated for each workdate as emplyees are sched -
names taken off of combo box. I would like to DISPLAY this combo box as a
List box on the Main Form - I copied and pasted the combo box onto the Main
Form and changed it into a ListBox(just reallywant to see all of employees
available for a given day(workdate) that sitting on in subform). but for
some reason the new copied ListBox on the Main form shows ALL of the
employees - not what is inthe combo box)

.

 

[Marshall Barton]

There is no click event for a record in a (sub)form. What
are you clicking on?

If it's a particular control (text box?), use the control's
Click event.

If it's the record selector, you would use the form's click
event.
--
Marsh
MVP [MS Access]

You got it - I am just not sure where - on what control to put the requery
of the list box

the combobox on subform is on the afterupdate event of the subform
Me!cbomanname.Requery

but I would like the list box on the main form to requery after each time
the user Clicks ON a different record in the subform to - represent what
available people - would be in that man name combo box - but don't want them
to have to Click On the combo box - just want them to See it in the list box-

do I use some sort of got focus on different record on the subform - not
sure what Event that would be.

That's not code, it's an SQL statement, probably used as the
list box's RowSource.

The VBA code I was referring to is probably a
Me.cbomanname.Requery
somewhere in a subform event procedure. I'm guessing that
line would be in an event related to scheduling an employee.
You will need an analogous line to also requery the main
form list box. If all my guesses are on the right track you
should add a second requery wherever you requery the combo
box:
Parent.lstmanname.Requery

Here is code for both list box and combo box
SELECT [Jeff Employee Combo Box Time Card].[man name] FROM [Jeff Employee
Combo Box Time Card] LEFT JOIN JeffDropDownDelete ON [Jeff Employee Combo Box
Time Card].[man name]=JeffDropDownDelete.[man name] WHERE
(((JeffDropDownDelete.[man name]) Is Null)) ORDER BY [Jeff Employee Combo Box
Time Card].[man name];

the Code on the list box is IDENTICAL to the code on the row source for the
combo box.- the only difference is the combo box resides on the subform
Jefflookaheadgenerate and the list box is on the main form jeffmaingenerate.


:
What is the list box's RowSource?

It sounds like you forgot to copy some code that limits the
combo box to the available employees.


babs wrote:
I have a combo box on the subform cbomanname its row source is based off of
queries that only show employees Not sched(ie. available) for that Given
Workdate the combo box is updated for each workdate as emplyees are sched -
names taken off of combo box. I would like to DISPLAY this combo box as a
List box on the Main Form - I copied and pasted the combo box onto the Main
Form and changed it into a ListBox(just reallywant to see all of employees
available for a given day(workdate) that sitting on in subform). but for
some reason the new copied ListBox on the Main form shows ALL of the
employees - not what is inthe combo box)

 

[babs]

It works great when I add it to the on click event of the subform- which
updates the list box when clicking on the record selector- however, I really
don't want the user to Have to click on the record selector - just want the
list box updated when click on Any text box in a given record to update the
listbox. Would I need to add the requery to EACH control(textbox) on the
subform or is there a better way to do it.

thanks again!!!!
Barb

There is no click event for a record in a (sub)form. What
are you clicking on?

If it's a particular control (text box?), use the control's
Click event.

If it's the record selector, you would use the form's click
event.
--
Marsh
MVP [MS Access]

You got it - I am just not sure where - on what control to put the requery
of the list box

the combobox on subform is on the afterupdate event of the subform
Me!cbomanname.Requery

but I would like the list box on the main form to requery after each time
the user Clicks ON a different record in the subform to - represent what
available people - would be in that man name combo box - but don't want them
to have to Click On the combo box - just want them to See it in the list box-

do I use some sort of got focus on different record on the subform - not
sure what Event that would be.

That's not code, it's an SQL statement, probably used as the
list box's RowSource.

The VBA code I was referring to is probably a
Me.cbomanname.Requery
somewhere in a subform event procedure. I'm guessing that
line would be in an event related to scheduling an employee.
You will need an analogous line to also requery the main
form list box. If all my guesses are on the right track you
should add a second requery wherever you requery the combo
box:
Parent.lstmanname.Requery


babs wrote:
Here is code for both list box and combo box
SELECT [Jeff Employee Combo Box Time Card].[man name] FROM [Jeff Employee
Combo Box Time Card] LEFT JOIN JeffDropDownDelete ON [Jeff Employee Combo Box
Time Card].[man name]=JeffDropDownDelete.[man name] WHERE
(((JeffDropDownDelete.[man name]) Is Null)) ORDER BY [Jeff Employee Combo Box
Time Card].[man name];

the Code on the list box is IDENTICAL to the code on the row source for the
combo box.- the only difference is the combo box resides on the subform
Jefflookaheadgenerate and the list box is on the main form jeffmaingenerate.


:
What is the list box's RowSource?

It sounds like you forgot to copy some code that limits the
combo box to the available employees.


babs wrote:
I have a combo box on the subform cbomanname its row source is based off of
queries that only show employees Not sched(ie. available) for that Given
Workdate the combo box is updated for each workdate as emplyees are sched -
names taken off of combo box. I would like to DISPLAY this combo box as a
List box on the Main Form - I copied and pasted the combo box onto the Main
Form and changed it into a ListBox(just reallywant to see all of employees
available for a given day(workdate) that sitting on in subform). but for
some reason the new copied ListBox on the Main form shows ALL of the
employees - not what is inthe combo box)

.

 

[Marshall Barton]

Yes, you would have to set the click event for each text box
and, if there is a significant amount of space between the
text boxes, the same for the detail section too.

BUT, you do not have to duplicate the code. You can create
a function in the subform's module that contains the code:
Function SyncList()
Parent.listmanname.Requery
End Function

Then, for each object that you want to do that, set its
OnClick *property* to =SyncList()

You should not even have to enter that more than once to do
all the text boxes. Hold down the shift key and select each
text box, then enter it in the property. You will have to
Copy/Paste it to the form's (and section's?) OnClick
property.

 

[babs]

Marshall,
It is working great for all of the fields except for the two that are combo
Boxes. the listbox on the main form does not requery even though I set the
On click event to =SyncList(). Should I put it on a different event -I don't
want the user to have to update the dropdown list - but if they just click on
it I want the list box to show - update for who is availble - like the other
fields on the subform.

not sure why this is happening?
thanks,
barb

Yes, you would have to set the click event for each text box
and, if there is a significant amount of space between the
text boxes, the same for the detail section too.

BUT, you do not have to duplicate the code. You can create
a function in the subform's module that contains the code:
Function SyncList()
Parent.listmanname.Requery
End Function

Then, for each object that you want to do that, set its
OnClick *property* to =SyncList()

You should not even have to enter that more than once to do
all the text boxes. Hold down the shift key and select each
text box, then enter it in the property. You will have to
Copy/Paste it to the form's (and section's?) OnClick
property.
--
Marsh
MVP [MS Access]

It works great when I add it to the on click event of the subform- which
updates the list box when clicking on the record selector- however, I really
don't want the user to Have to click on the record selector - just want the
list box updated when click on Any text box in a given record to update the
listbox. Would I need to add the requery to EACH control(textbox) on the
subform or is there a better way to do it.

.

 

[Marshall Barton]

It is working great for all of the fields except for the two that are combo
Boxes. the listbox on the main form does not requery even though I set the
On click event to =SyncList(). Should I put it on a different event -I don't
want the user to have to update the dropdown list - but if they just click on
it I want the list box to show - update for who is availble - like the other
fields on the subform.

I guess that says something about how often I've used a
combo box's Click event :-( It appears the combo box Click
event doesn't occur until the combo box loses the focus (or
an item is selected from the list).

Let's try using the MouseUp event instead of Click for the
combo boxes. Note, the MouseUp event will also occur when
an item is selected and even when the list the drop list
arrow is clicked.