Pass selection in pop-up to subform

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am trying to figure out how to use a pop-up form to present a series of
choices. When the user clicks on one of the choices, that choice is passed to
a subform to populate a field. For example, on an order form there is a
subform to list all of the items ordered. I would like to be able to use a
pop-up form to display all the possible items that can be ordered. When the
user clicks on one item, that item number is passed to the respective field
in the items ordered subform. Can anyone help with this? Is there another way
to do this? Is there a way to populate the pop-up form with a dynamic list
based on the items most recently used?
 
Why use a popup form? Why not use a combo box on the subform and do it
directly?

It's certainly possible to do this with a popup form, but it'll take a bit
of programming to set this up, so I usually don't do that unless I have some
reason why I cannot just use a combo box on the original form.
 
Thanks very much Ken. I was trying to get away from a combo box because the
list I would like to use is about 70 items long...a bit long for a combo box.

CM
 
To flesh out my problem a bit better...I have a list several thousand
"products" long. Only about 100 or so are likely to be used, but the user
still needs access to the whole list just in case. I would like to make it
easy for the user to choose from among the "products" that have already been
used, while maintaining the option of selecting a new one. When I base the
field on a combo box of the whole "product" list, the list is way too long to
be useful. If I base it on a query derived from the linking table that
conatins all the "orders", and use the display unique values option, I
exclued the ones that haven't been used.

Is there a way to prioritize the list in the combo box based on the
"products" that have been used, then after a separator bar, all the products?
 
To flesh out my problem a bit better...I have a list several thousand
"products" long. Only about 100 or so are likely to be used, but the user
still needs access to the whole list just in case. I would like to make it
easy for the user to choose from among the "products" that have already been
used, while maintaining the option of selecting a new one. When I base the
field on a combo box of the whole "product" list, the list is way too long to
be useful. If I base it on a query derived from the linking table that
conatins all the "orders", and use the display unique values option, I
exclued the ones that haven't been used.

Is there a way to prioritize the list in the combo box based on the
"products" that have been used, then after a separator bar, all the products?
 
What is considered "too long" for a combo box (or a list box) is certainly a
matter of preference, and depends upon the situation where it'll be used. I
actually use combo boxes in one of my applications for a list that exceeds
20,000 items -- because the users begin typing the part number in the combo
box and it immediately "moves" to the first record matching what's been
typed, and then the user can drop down the list to refine the choice. Very
much a customer-specific issue < g >.

So you want to present the 70+ items as a continuous forms view in a popup?
How would that be different from using a list box? You won't be able to
display all 70+ items in a single screen without having the user use the
scroll bar.

Just curious what the popup form would give you compared to a list box or
combo box?
 
There is no direct way to "prioritize" and then use a separator bar... but,
you might be able to design a query that would put sorting order values in a
calculated field that would allow you to "sort" the high-priority items to
the top of the list and the others to the bottom of the list, and to
"insert" a separator item of some type via the use of a union query that
gets all the records.

But, quite honestly, this would be a lot of tricky design work for little
return on your time investment. If you want to let the user see a priority
list and then a low-priority list, perhaps you could show the two lists in
separate combo boxes or separate list boxes -- let the user see the
high-priority ones in one list, and if the user wants, he/she can look in
the 'low-priority' combo/list box for the other entries. You'd have to use
programming to "write" the correct value from either of the boxes to where
you want it to go, but this would still be less work than the programming
that you'd need for a popup form for displaying and then writing the chosen
value to the first form.
 
Thanks Ken. That answers my question. I think the best solution will be to
use the combo box for all the products and an adjacent command button that
opens a form for filtering the "product" numbers based on keywords. If the
user then filters the products and is shown an abbreviated list, is there a
way for the user to select one of the items and have that item passed to the
combo box?
 
Sure... see this article at The ACCESS Web for some info -- the article
discusses cascading combo boxes, but the setup would be essentially the same
for what you want to do -- just think of your combo box as the "second" or
"dependent" combo box in this article:

http://www.mvps.org/access/forms/frm0028.htm
 
I'm not sure if the answer I just posted actually is answering your
question... let me know if it's not (apologies in advance if not).
 
Thanks again Ken. I appreciate you're staying with me on this.
Well....no...it didn't really answer my question unless I am not
understanding you clearly (which is a distinct possibility). I don't see how
cascading combo boxes would help.

To stay with the order entry model, I have a subform in which the user can
select as many product numbers as they want for any given order. There are
thousands of products, but only about 100 or so are used with any frequency.
I was thinking that maybe the best way to do this is to use command buttons
to open a form that displays the frequently used products so the user can
click on the one they want. But if the product is not one of the more
frequently used ones, then the user can click on a different command button
to open a form that will enable the user to filter all the products based on
a keyword. Either way I need to pass the product that the user clicks on to
the combo box in the subform. Does that make sense?

Thanks again, and I apologize if I'm not getting it.

CM
 
Yep, I missed on my reply! < g >

So what you want to do is to let the user select a value on the second form,
and then you want to pass that value to the combo box on the first form --
so that the combo box would then show that value in its display? Will the
value from the second form be one that is in the combo box's RowSource
records? If not, then the code may need to reset the combo box's RowSource
query too.

Passing a value from one open form to another can be done in various ways,
but the method that I prefer involves opening the second form in dialog
mode, letting the user select the value, and the making that form invisible
when the user clicks an "ok" button on the second form. The first form then
reads the value from the second form's control and then closes the second
form. I usally also give the user a "cancel" button on the second form so
that he/she can return to the first form without doing anything.

Here is some basic code, assuming that the user clicks a button to make the
second form appear:


Private Sub cmdButtonName_Click()
Const strFormName As String = "NameOfSecondForm"
Const strFormName_ControlName As String = "NameOfControlOnSecondForm"
DoCmd.OpenForm strFormName, , , , acDialog
If IsTheFormOpen(strFormName) = True Then
Me.ComboBoxName.Value = _
Forms(strFormName).Controls(strFormName_ControlName).Value
DoCmd.Close acForm, strFormName, acSaveNo
End If
End Sub


Note that this code uses the following function (put it in a regular
module):




' *********************************
' ** Function IsTheFormOpen **
' *********************************

Public Function IsTheFormOpen(ByVal xstrFormName As String) As Boolean
'Returns False if form is not open or is open in design view; returns
' True if it is open in form or datasheet view
On Error Resume Next
' Set default value for the function
IsTheFormOpen = False
If SysCmd(acSysCmdGetObjectState, acForm, xstrFormName) <> 0 Then
If Forms(xstrFormName).CurrentView <> 0 Then IsTheFormOpen = True
End If
Exit Function
End Function



The code in the second form for the two buttons ("ok" and "Cancel") would be
this:

Private Sub cmdOK_Click()
Me.Visible = False
End Sub

Private Sub cmdCancel_Click()
DoCmd.Close acForm, Me.Name, acSaveNo
End Sub
 
Thanks Ken. This sounds like what I am trying to do. I'm using this project
as a way to learn how to program for Access, so your guidance is really
helpful. I will work on it.

CM
 
Back
Top