On Click, Open a form, choose a selection then have it populate fi

[Iram]

Hello.
I have a form with a subform (datasheet view). In the subform I have a few
fields, "Category", "SubCategory", "Amount", "Comments".

Upon clicking in the Category field I would like a form called
"frm_Categories" to open so that I can choose only one of many selections (
these selections would come from a table) so that upon choosing one of them
the form would grab what ever I selected and populate the "Category" field
with it.

Does anyone know how to do this?
I have seen something similar with a form calendar connected to a date
field. The form would populate the field with the selection from the calendar.

In this case I don't want to use a Combox because a form will allow you to
place the fields in a nice formatted way where as the Combox will make users
scroll for ever trying to find a selection.

Your help is greatly appreciated!

Iram/mcp

 

[fredg]

Regarding ... In this case I don't want to use a Combox because a form will
allow you to
place the fields in a nice formatted way where as the Combox will make users
scroll for ever trying to find a selection. ...

Actually, a combo box on a form is exactly the control that you want.
Set it's AutoExpand property to Yes and as the user starts to enter a name
it will jump ahead to the next value with that combination. There is no need
to scroll.

 

[Iram]

There are too many categories to memorize that is why we want a form pop out.
Can you help me?



Thanks.
Iram

 

[John W. Vinson]

There are too many categories to memorize that is why we want a form pop out.
Can you help me?

In what way will having 812 categories displayed on a Subform (which does
*not* have an autocomplete property) be better than having the same 812
categories displayed in a combo box? If the combo is properly designed no
memorization and no scrolling are needed: you would setfocus to the combo,
type the first letter of the category (which is presumably meaningful and
relatively easy to remember), and the combo will jump to the first row
starting with that letter, and display the categories right there on the
screen.

If that isn't acceptable, could you explain what WOULD be acceptable?

 

[Iram]

The category field is used by 10 groups of people. The combobox has 2 columns
which is sorted by TeamName, Category. Each team has to scroll through the
lis to get to their stuff. If I had a form pop-out (seperate form) where I
had all 100 fields grouped by team in a nice pretty form with descriptions
and colors, etc, staff could just click in the category field, a popup form
would appear, they would select 1 selection, the form would go away and there
selection would be populated in the category field.
I kind of thought this would be cooler than jus the combobox and staff loved
this idea. Is it possible? I know that the calendar form pop out works fine,
which populates a field after selecting a date.

Iram

 

[John W. Vinson]

The category field is used by 10 groups of people. The combobox has 2 columns
which is sorted by TeamName, Category. Each team has to scroll through the
lis to get to their stuff. If I had a form pop-out (seperate form) where I
had all 100 fields grouped by team in a nice pretty form with descriptions
and colors, etc, staff could just click in the category field, a popup form
would appear, they would select 1 selection, the form would go away and there
selection would be populated in the category field.
I kind of thought this would be cooler than jus the combobox and staff loved
this idea. Is it possible? I know that the calendar form pop out works fine,
which populates a field after selecting a date.

It's certainly possible. It would be quite a bit of work. What I'd do for
starters is have two combo boxes - one for team name, and the second for
category. The user would pick their own teamname from the first combo; the
second combo would use that choice as a criterion to display only the
categories pertinant to their team.

If you want the popup form you'll need some VBA code to pop up the form, and
more code in its AfterUpdate or command button events to copy the user's
selection back into a control on the main form. Lots more code than I'm
comfortable writing here on the forum!

And if you're talking about 100 *FIELDS* - or even 100 textboxes! - on a form,
you're digging yourself a monstrous ongoing maintenance hole, unless the list
of categories is never going to change!

 

[fredg]

Your original question was generic. John and I have both given you the best
solution. Next time, when you post, please give us more specifics so that the
answer can be more specific.

So, if I understand you correctly, let's assume each record has a TeamID,
and you wish the combo box categories to be specific to that team?

I don't know your exact form set up, so, guessing, code the form's Current
event:

Me!ComboName.Rowsource = "Select TableName.[Category] from TableName Where
TableName.TeamID = " & Me.TeamID & " Order By [Category]"

The above assumes TeamID is a Number datatype.
Look up (in VBA help)
"Restrict data to a subset of records"
to learn how to write that where clause for different datatypes.

Set the Combo box AutoExpand to yes.
As you cycle through your records on the form, the combo will show the
appropriate categories for that team. As the user starts to enter the first,
second, etc. letters the Combo will jump to those categories. Only the
categories specific to the team shown on the form will be available.

 

[fredg]

I meant to add, in my second reply;

Place the same Current event code in the TeamID AfterUpdate event.

Me!ComboName.Rowsource = "Select TableName.[Category] from TableName Where
TableName.TeamID = " & Me.TeamID & " Order By [Category]"

--
Fred
Please respond only to this newsgroup.
I do not reply to personal e-mail

Your original question was generic. John and I have both given you the best
solution. Next time, when you post, please give us more specifics so that the
answer can be more specific.

So, if I understand you correctly, let's assume each record has a TeamID,
and you wish the combo box categories to be specific to that team?

I don't know your exact form set up, so, guessing, code the form's Current
event:

Me!ComboName.Rowsource = "Select TableName.[Category] from TableName Where
TableName.TeamID = " & Me.TeamID & " Order By [Category]"

The above assumes TeamID is a Number datatype.
Look up (in VBA help)
"Restrict data to a subset of records"
to learn how to write that where clause for different datatypes.

Set the Combo box AutoExpand to yes.
As you cycle through your records on the form, the combo will show the
appropriate categories for that team. As the user starts to enter the first,
second, etc. letters the Combo will jump to those categories. Only the
categories specific to the team shown on the form will be available.
--
Fred
Please respond only to this newsgroup.
I do not reply to personal e-mail

There are too many categories to memorize that is why we want a form pop out.
Can you help me?



Thanks.
Iram

 

[Iram]

Thanks John W. Vinson and fredg for your responses!

For now I am going to skip this feature that we need and use combobox's as
both of you have suggested. At some point in the future I am going to tackle
the monster job of creating a populating form.



Thanks you guys are awesome!
Iram/mcp

I meant to add, in my second reply;

Place the same Current event code in the TeamID AfterUpdate event.

Me!ComboName.Rowsource = "Select TableName.[Category] from TableName Where
TableName.TeamID = " & Me.TeamID & " Order By [Category]"

--
Fred
Please respond only to this newsgroup.
I do not reply to personal e-mail

Your original question was generic. John and I have both given you the best
solution. Next time, when you post, please give us more specifics so that the
answer can be more specific.

So, if I understand you correctly, let's assume each record has a TeamID,
and you wish the combo box categories to be specific to that team?

I don't know your exact form set up, so, guessing, code the form's Current
event:

Me!ComboName.Rowsource = "Select TableName.[Category] from TableName Where
TableName.TeamID = " & Me.TeamID & " Order By [Category]"

The above assumes TeamID is a Number datatype.
Look up (in VBA help)
"Restrict data to a subset of records"
to learn how to write that where clause for different datatypes.

Set the Combo box AutoExpand to yes.
As you cycle through your records on the form, the combo will show the
appropriate categories for that team. As the user starts to enter the first,
second, etc. letters the Combo will jump to those categories. Only the
categories specific to the team shown on the form will be available.
--
Fred
Please respond only to this newsgroup.
I do not reply to personal e-mail

There are too many categories to memorize that is why we want a form pop out.
Can you help me?



Thanks.
Iram



:

Regarding ... In this case I don't want to use a Combox because a form will
allow you to
place the fields in a nice formatted way where as the Combox will make users
scroll for ever trying to find a selection. ...

Actually, a combo box on a form is exactly the control that you want.
Set it's AutoExpand property to Yes and as the user starts to enter a name
it will jump ahead to the next value with that combination. There is no need
to scroll.
--
Fred
Please respond only to this newsgroup.
I do not reply to personal e-mail



:

Hello.
I have a form with a subform (datasheet view). In the subform I have a few
fields, "Category", "SubCategory", "Amount", "Comments".

Upon clicking in the Category field I would like a form called
"frm_Categories" to open so that I can choose only one of many selections (
these selections would come from a table) so that upon choosing one of them
the form would grab what ever I selected and populate the "Category" field
with it.

Does anyone know how to do this?
I have seen something similar with a form calendar connected to a date
field. The form would populate the field with the selection from the calendar.

In this case I don't want to use a Combox because a form will allow you to
place the fields in a nice formatted way where as the Combox will make users
scroll for ever trying to find a selection.

Your help is greatly appreciated!

Iram/mcp