Subform fields auto-filled based on Main form combo box value

[Sunflower]

I am not even sure if this is possible, but here goes…

I have a main form named frmWORKORDER, |
which has a combo box selecting Department from tblDEPTS

The main form has a subform named subfrmAPPROVALS,
which has a combo box selecting Names from tblCONTACTS

What I need is…
If a certain Department “Marketing, Special Events” is selected in
Department combo box on the main form , to have 5 specific names
automatically selected in the subform , Name combo Box with an ability
to select more if needed,
as well as, if “Marketing, Special Events” is not selected in
Department combo box on the main form, to have 3 specific names
automatically selected in the subform, Name combo Box with an ability
to select more if needed.

I am somewhat of a newbie so coding is not my strong suit.

Any and all help is greatly appreciated.

 

[June7]

Use the AfterUpdate event of the combobox selecting Department. Build code to
set value of the other combobox. But don't think combobox allows
multi-selection. Or do you mean you want to modify the list of selectable
items. Then code would modify the RowSource query statement and then execute
a Requery of the combobox.

 

[Sunflower]

Use the AfterUpdate event of the combobox selecting Department. Build code to
set value of the other combobox. But don't think combobox allows
multi-selection. Or do you mean you want to modify the list of selectable
items. Then code would modify the RowSource query statement and then execute
a Requery of the combobox.










- Show quoted text -

My "Names" Combobox in the subform is linked to a table [tblCONTACTS]
so I dont want to modify the list,
What I need it to do is automatically add rows of either 3 specifc
names or 5 specific names.

Is this do-able?

 

[June7]

'Names' Combobox is bound to table in the ControlSource property, so that
whatever you select is directly saved to table? You want users to have a list
of items they can select by setting the RowSource property? This list can be
created by a query or by a value list. The RowSourceType must be set to
reflect the method. You can define the RowSource property in code and with
code modify the RowSource property then Requery the combobox. You can set the
property either to a query statement or a value list but the Type setting
must be compatible, which you can change with code. Am I getting close?
Examples:
Me.Names.RowSourceType = "Value List"
Me.Names.RowSource = "Smith;Jones;Carter"
Me.Names.RowSourceType = "Table/Query"
Me.Names.RowSource = "SELECT DISTINCT names FROM tblContacts;"

Use the AfterUpdate event of the combobox selecting Department. Build code to
set value of the other combobox. But don't think combobox allows
multi-selection. Or do you mean you want to modify the list of selectable
items. Then code would modify the RowSource query statement and then execute
a Requery of the combobox.










- Show quoted text -

My "Names" Combobox in the subform is linked to a table [tblCONTACTS]
so I dont want to modify the list,
What I need it to do is automatically add rows of either 3 specifc
names or 5 specific names.

Is this do-able?

 

[Sunflower]

'Names' Combobox is bound to table in the ControlSource property, so that
whatever you select is directly saved to table? You want users to have a list
of items they can select by setting the RowSource property? This list canbe
created by a query or by a value list. The RowSourceType must be set to
reflect the method. You can define the RowSource property in code and with
code modify the RowSource property then Requery the combobox. You can setthe
property either to a query statement or a value list but the Type setting
must be compatible, which you can change with code.  Am I getting close?
Examples:
Me.Names.RowSourceType = "Value List"
Me.Names.RowSource = "Smith;Jones;Carter"
Me.Names.RowSourceType = "Table/Query"
Me.Names.RowSource = "SELECT DISTINCT names FROM tblContacts;"

My "Names" Combobox in the subform is linked to a table [tblCONTACTS]
so I dont want to modify the list,
What I need it to do is automatically add rows of either 3 specifc
names or 5 specific names.

Is this do-able?- Hide quoted text -

- Show quoted text -

Yes, the 'Names' Combobox is bound to table [tblCONTACTS]
this table is linked to several comboboxes


On the MAIN form [frmWOEDIT] I have
[Requestor] combobox
[Departments] combobox

When the user chooses "Marketing, Special Events" department
I need the SUBFORM [subfrmAPPROVALS] to auto fill records

like so:

APPROVER NAME CHECK OFF APPROVED ON
------------- --------- -----------
[Requester]
Emily
Venissa
Carol
Cleave
Kelli



When the user does not choose "Marketing, Special Events" department
I need the subform to auto fill records

like so:

APPROVER NAME CHECK OFF APPROVED ON
------------- --------- -----------
[Requester]
Carol
Cleave
Kelli


I dont want to make my user have to select each "MUST HAVE" names from
a listing,
However, I want them to be able to add more, so I dont wnat to limit
their choices.

Hope that made sense

 

[June7]

Is there a table that stores names of all people who can be approvers? Is
there a field in that table that can be used as a filter condition?
Me.Names.RowSource = "SELECT names FROM tablename WHERE fieldname =
fieldvalue;"
Set the combobox LimitToList property to No. If you want to update the
approvers table with the user's new entry, can use ListItemsInputForm
property and/orOnNotInList event . I have not done this before, just know
they are there to use.

'Names' Combobox is bound to table in the ControlSource property, so that
whatever you select is directly saved to table? You want users to have a list
of items they can select by setting the RowSource property? This list can be
created by a query or by a value list. The RowSourceType must be set to
reflect the method. You can define the RowSource property in code and with
code modify the RowSource property then Requery the combobox. You can set the
property either to a query statement or a value list but the Type setting
must be compatible, which you can change with code. Am I getting close?
Examples:
Me.Names.RowSourceType = "Value List"
Me.Names.RowSource = "Smith;Jones;Carter"
Me.Names.RowSourceType = "Table/Query"
Me.Names.RowSource = "SELECT DISTINCT names FROM tblContacts;"

Use the AfterUpdate event of the combobox selecting Department. Build code to
set value of the other combobox. But don't think combobox allows
multi-selection. Or do you mean you want to modify the list of selectable
items. Then code would modify the RowSource query statement and then execute
a Requery of the combobox.

:
I am not even sure if this is possible, but here goes…

I have a main form named frmWORKORDER, |
which has a combo box selecting Department from tblDEPTS

The main form has a subform named subfrmAPPROVALS,
which has a combo box selecting Names from tblCONTACTS

What I need is…
If a certain Department “Marketing, Special Events†is selected in
Department combo box on the main form , to have 5 specific names
automatically selected in the subform , Name combo Box with an ability
to select more if needed,
as well as, if “Marketing, Special Events†is not selected in
Department combo box on the main form, to have 3 specific names
automatically selected in the subform, Name combo Box with an ability
to select more if needed.

I am somewhat of a newbie so coding is not my strong suit.

Any and all help is greatly appreciated.- Hide quoted text -

- Show quoted text -

My "Names" Combobox in the subform is linked to a table [tblCONTACTS]
so I dont want to modify the list,
What I need it to do is automatically add rows of either 3 specifc
names or 5 specific names.

Is this do-able?- Hide quoted text -

- Show quoted text -

Yes, the 'Names' Combobox is bound to table [tblCONTACTS]
this table is linked to several comboboxes


On the MAIN form [frmWOEDIT] I have
[Requestor] combobox
[Departments] combobox

When the user chooses "Marketing, Special Events" department
I need the SUBFORM [subfrmAPPROVALS] to auto fill records

like so:

APPROVER NAME CHECK OFF APPROVED ON
------------- --------- -----------
[Requester]
Emily
Venissa
Carol
Cleave
Kelli



When the user does not choose "Marketing, Special Events" department
I need the subform to auto fill records

like so:

APPROVER NAME CHECK OFF APPROVED ON
------------- --------- -----------
[Requester]
Carol
Cleave
Kelli


I dont want to make my user have to select each "MUST HAVE" names from
a listing,
However, I want them to be able to add more, so I dont wnat to limit
their choices.

Hope that made sense

 

[June7]

Correction: to use the OnNotInList event the LimitToList property must be set
to Yes. I have done this. I have also used the BeforeUpdate event to check if
entry is valid according to some criteria and if it is new use code to add a
new record then requery the combobox.

Is there a table that stores names of all people who can be approvers? Is
there a field in that table that can be used as a filter condition?
Me.Names.RowSource = "SELECT names FROM tablename WHERE fieldname =
fieldvalue;"
Set the combobox LimitToList property to No. If you want to update the
approvers table with the user's new entry, can use ListItemsInputForm
property and/orOnNotInList event . I have not done this before, just know
they are there to use.

'Names' Combobox is bound to table in the ControlSource property, so that
whatever you select is directly saved to table? You want users to have a list
of items they can select by setting the RowSource property? This list can be
created by a query or by a value list. The RowSourceType must be set to
reflect the method. You can define the RowSource property in code and with
code modify the RowSource property then Requery the combobox. You can set the
property either to a query statement or a value list but the Type setting
must be compatible, which you can change with code. Am I getting close?
Examples:
Me.Names.RowSourceType = "Value List"
Me.Names.RowSource = "Smith;Jones;Carter"
Me.Names.RowSourceType = "Table/Query"
Me.Names.RowSource = "SELECT DISTINCT names FROM tblContacts;"



:
Use the AfterUpdate event of the combobox selecting Department. Build code to
set value of the other combobox. But don't think combobox allows
multi-selection. Or do you mean you want to modify the list of selectable
items. Then code would modify the RowSource query statement and then execute
a Requery of the combobox.

:
I am not even sure if this is possible, but here goes…

I have a main form named frmWORKORDER, |
which has a combo box selecting Department from tblDEPTS

The main form has a subform named subfrmAPPROVALS,
which has a combo box selecting Names from tblCONTACTS

What I need is…
If a certain Department “Marketing, Special Events†is selected in
Department combo box on the main form , to have 5 specific names
automatically selected in the subform , Name combo Box with an ability
to select more if needed,
as well as, if “Marketing, Special Events†is not selected in
Department combo box on the main form, to have 3 specific names
automatically selected in the subform, Name combo Box with an ability
to select more if needed.

I am somewhat of a newbie so coding is not my strong suit.

Any and all help is greatly appreciated.- Hide quoted text -

- Show quoted text -

My "Names" Combobox in the subform is linked to a table [tblCONTACTS]
so I dont want to modify the list,
What I need it to do is automatically add rows of either 3 specifc
names or 5 specific names.

Is this do-able?- Hide quoted text -

- Show quoted text -

Yes, the 'Names' Combobox is bound to table [tblCONTACTS]
this table is linked to several comboboxes


On the MAIN form [frmWOEDIT] I have
[Requestor] combobox
[Departments] combobox

When the user chooses "Marketing, Special Events" department
I need the SUBFORM [subfrmAPPROVALS] to auto fill records

like so:

APPROVER NAME CHECK OFF APPROVED ON
------------- --------- -----------
[Requester]
Emily
Venissa
Carol
Cleave
Kelli



When the user does not choose "Marketing, Special Events" department
I need the subform to auto fill records

like so:

APPROVER NAME CHECK OFF APPROVED ON
------------- --------- -----------
[Requester]
Carol
Cleave
Kelli


I dont want to make my user have to select each "MUST HAVE" names from
a listing,
However, I want them to be able to add more, so I dont wnat to limit
their choices.

Hope that made sense

 

[June7]

Correction: to use the OnNotInList event the LimitToList property must be set
to Yes. I have done this. I have also used the BeforeUpdate event to check if
entry is valid according to some criteria and if it is new use code to add a
new record then requery the combobox.

Is there a table that stores names of all people who can be approvers? Is
there a field in that table that can be used as a filter condition?
Me.Names.RowSource = "SELECT names FROM tablename WHERE fieldname =
fieldvalue;"
Set the combobox LimitToList property to No. If you want to update the
approvers table with the user's new entry, can use ListItemsInputForm
property and/orOnNotInList event . I have not done this before, just know
they are there to use.

'Names' Combobox is bound to table in the ControlSource property, so that
whatever you select is directly saved to table? You want users to have a list
of items they can select by setting the RowSource property? This list can be
created by a query or by a value list. The RowSourceType must be set to
reflect the method. You can define the RowSource property in code and with
code modify the RowSource property then Requery the combobox. You can set the
property either to a query statement or a value list but the Type setting
must be compatible, which you can change with code. Am I getting close?
Examples:
Me.Names.RowSourceType = "Value List"
Me.Names.RowSource = "Smith;Jones;Carter"
Me.Names.RowSourceType = "Table/Query"
Me.Names.RowSource = "SELECT DISTINCT names FROM tblContacts;"



:
Use the AfterUpdate event of the combobox selecting Department. Build code to
set value of the other combobox. But don't think combobox allows
multi-selection. Or do you mean you want to modify the list of selectable
items. Then code would modify the RowSource query statement and then execute
a Requery of the combobox.

:
I am not even sure if this is possible, but here goes…

I have a main form named frmWORKORDER, |
which has a combo box selecting Department from tblDEPTS

The main form has a subform named subfrmAPPROVALS,
which has a combo box selecting Names from tblCONTACTS

What I need is…
If a certain Department “Marketing, Special Events†is selected in
Department combo box on the main form , to have 5 specific names
automatically selected in the subform , Name combo Box with an ability
to select more if needed,
as well as, if “Marketing, Special Events†is not selected in
Department combo box on the main form, to have 3 specific names
automatically selected in the subform, Name combo Box with an ability
to select more if needed.

I am somewhat of a newbie so coding is not my strong suit.

Any and all help is greatly appreciated.- Hide quoted text -

- Show quoted text -

My "Names" Combobox in the subform is linked to a table [tblCONTACTS]
so I dont want to modify the list,
What I need it to do is automatically add rows of either 3 specifc
names or 5 specific names.

Is this do-able?- Hide quoted text -

- Show quoted text -

Yes, the 'Names' Combobox is bound to table [tblCONTACTS]
this table is linked to several comboboxes


On the MAIN form [frmWOEDIT] I have
[Requestor] combobox
[Departments] combobox

When the user chooses "Marketing, Special Events" department
I need the SUBFORM [subfrmAPPROVALS] to auto fill records

like so:

APPROVER NAME CHECK OFF APPROVED ON
------------- --------- -----------
[Requester]
Emily
Venissa
Carol
Cleave
Kelli



When the user does not choose "Marketing, Special Events" department
I need the subform to auto fill records

like so:

APPROVER NAME CHECK OFF APPROVED ON
------------- --------- -----------
[Requester]
Carol
Cleave
Kelli


I dont want to make my user have to select each "MUST HAVE" names from
a listing,
However, I want them to be able to add more, so I dont wnat to limit
their choices.

Hope that made sense

 

[Sunflower]

Correction: to use the OnNotInList event the LimitToList property must beset
to Yes. I have done this. I have also used the BeforeUpdate event to check if
entry is valid according to some criteria and if it is new use code to add a
new record then requery the combobox.

Is there a table that stores names of all people who can be approvers? Is
there a field in that table that can be used as a filter condition?
Me.Names.RowSource = "SELECT names FROM tablename WHERE fieldname =
fieldvalue;"
Set the combobox LimitToList property to No. If you want to update the
approvers table with the user's new entry, can use ListItemsInputForm
property and/orOnNotInList event . I have not done this before, just know
they are there to use.

'Names' Combobox is bound to table in the ControlSource property, so that
whatever you select is directly saved to table? You want users to have a list
of items they can select by setting the RowSource property? This list can be
created by a query or by a value list. The RowSourceType must be set to
reflect the method. You can define the RowSource property in code and with
code modify the RowSource property then Requery the combobox. You can set the
property either to a query statement or a value list but the Type setting
must be compatible, which you can change with code.  Am I gettingclose?
Examples:
Me.Names.RowSourceType = "Value List"
Me.Names.RowSource = "Smith;Jones;Carter"
Me.Names.RowSourceType = "Table/Query"
Me.Names.RowSource = "SELECT DISTINCT names FROM tblContacts;"
:
Use the AfterUpdate event of the combobox selecting Department.Build code to
set value of the other combobox. But don't think combobox allows
multi-selection. Or do you mean you want to modify the list of selectable
items. Then code would modify the RowSource query statement andthen execute
a Requery of the combobox.
:
I am not even sure if this is possible, but here goes…
I have a main form named frmWORKORDER, |
which has a combo box selecting Department from tblDEPTS
The main form has a subform named subfrmAPPROVALS,
which has a combo box selecting Names from tblCONTACTS
What I need is…
If a certain Department “Marketing, Special Events” is selected in
Department combo box on the main form , to have 5 specific names
automatically selected in the subform , Name combo Box with an ability
to select more if needed,
as well as, if “Marketing, Special Events”  is not selected in
Department combo box on the main form, to have 3 specific names
automatically selected in the subform, Name combo Box with anability
to select more if needed.
I am somewhat of a newbie so coding is not my strong suit.
Any and all help is greatly appreciated.- Hide quoted text -
- Show quoted text -
My "Names" Combobox in the subform is linked to a table [tblCONTACTS]
so I dont want to modify the list,
What I need it to do is automatically add rows of either 3 specifc
names or 5 specific names.
Is this do-able?- Hide quoted text -
- Show quoted text -
Yes, the 'Names' Combobox is bound to table [tblCONTACTS]
this table is linked to several comboboxes
On the MAIN form [frmWOEDIT] I have
      [Requestor] combobox
      [Departments] combobox
When the user chooses "Marketing, Special Events" department
I need the SUBFORM [subfrmAPPROVALS] to auto fill records
like so:
APPROVER NAME    CHECK OFF   APPROVED ON
-------------    ---------   -----------
[Requester]
Emily
Venissa
Carol
Cleave
Kelli
When the user does not choose "Marketing, Special Events" department
I need the subform to auto fill records
like so:
APPROVER NAME    CHECK OFF   APPROVED ON
-------------    ---------   -----------
[Requester]
Carol
Cleave
Kelli
I dont want to make my user have to select each "MUST HAVE" names from
a listing,
However, I want them to be able to add more, so I dont wnat to limit
their choices.
Hope that made sense- Hide quoted text -

- Show quoted text -

I am not sure I am following your suggestion...

I have a table with all names [tblCONTACTS}
there are 200 names and counting...
I can certainly put a filter condition on the 5 names that are needed
everytime a new record is created
However, the [Requestor] can be any one of those 200 names and
counting...

So I am not sure how to apply the Row Source and use the OnNotInList
event the LimitToList property ???