combo box issue

[Guest]

Perhaps I'm going about this the wrong way but here's what I'm trying to do:

I have a form called OrderAcceptance. The user will designate the ordered
product(s) in the OADetails subform. The Products field is a combo box
displaying either "Parts Order" (which could be anything -- and a detail of
that is entered later in another form) or a specific Assembly number. The
list in the combo box for products (which is a union query so I can add the
"parts order" option) displays only those assemblies that are available for
shipment. When the order acceptance is printed, I have code which updates
the assembly status to show it has been reserved, therefore no longer
available. (now it won't appear in the drop list on the next order)

My problem is when viewing existing orders. Because the assembly is no
longer available, the field appears blank. The combo box is LimitToList=Yes.
The first (bound) column of the combo box is a number (0 for parts order or
2-5 digits for the assembly) but the second column is what displays, which is
the text "parts order" or the full assembly number (3-4 letters plus the 2-5
digits).

I've tried modifying my union query to include the selected assembly (if
there is one) but may be doing something wrong because it still won't appear
in the field.

I've thought of having another form pop-up to display the available
assemblies and then the user picks one which, then code inserts it in the
products field. OR I have an adding/editing setup so the combo box is only
there when I'm adding new records, otherwise it's a textbox. I just keep
thinking that what I'm trying to do must work and I'm just an inch away from
making it work.

Thanks in advance!

 

[Guest]

What you need is two queries. One that filters for new orders and one that
filters for existing orders where assemblies that are not available are
included in the list. Then you can use the Current Event of the form to
determine which of the queries yo need to use for the rowsource of your combo:

If Me.NewRecord Then
Me.MyCombo.Rowsource = "qselFilteredList"
Else
Me.MyCombo.Rowsource = "qselUnFilteredList"
End If

Now, there is one issue to be considered, but I don't know if it is
relevant. If a user is allowed to either add or remove line items on an
existing order, it may be necessary to have a column in your combo that would
tell you whether the assembly or part is unavailable so if the user tried to
add that item to the order, you could present a message box to alert the user
and disallow the addition.

 

[Guest]

Thanks! I should have thought of that but I got too bogged down to think
clearly. It certainly makes sense to me now.

Thanks again!

 

[Guest]

Glad to help.

Thanks! I should have thought of that but I got too bogged down to think
clearly. It certainly makes sense to me now.

Thanks again!