List-based Filter Criteria Derived from a (Sub)Form

[Ellpee]

I have a select query to grab records from a table, but ONLY those records that match the customer numbers involved in my current transaction. Problem is, this starts from a form that has some generic header data, and a subform that contains the customer numbers I need. Picture it: I need to order 1000 more widgets (main form), because the following customer(s) (subform) want xxx widgets each.



So I need to first grab all those customer numbers (could be one to many), then tell my query to use only the numbers in that list. Scratching my head for a simple way to do both parts of that -- first capture a list of the numbers, then filter my query with some kind of "only if it's in this list" criterion. Any thoughts? (As far as I can tell, data in the subform is not stored anywhere until AFTER this process runs. I suppose I could put it in a temporary table or query, but hoping there's a simpler way than adding more objects.)

[ranman256]

base the combo box qry on the query in the subform. (or the key in the master form)
like:
select distinct widgets from qsSubFormQry

[Gicu]

Show us the select query that you have and the main form in design view with the subform's control properties window so we could see the Child\Master linking between subform and main form.

Cheers,

[Ellpee]

Sorry, should have been clearer. Subform is not a combo box; separate lines that say customer 12345 needs #### widgets, customer 67890 needs 50 widgets, etc. If saved somewhere, this data would be separate records in a table, but I'm hoping to avoid that and capture the customer numbers right off the subform.

[June7]

How are you producing these "separate lines" on subform?

Are forms bound to tables? Data entered to subform should be feeding directly to table. Data is committed to table when: 1) close table/query/bound form or 2) move to another record or 3) run code to save.

Use customer numbers to filter what data?

"Simple" is subjective.

Code can physically loop through records or controls on form or loop recordset object or reference form's filter criteria.

If you want to provide db for analysis, follow instructions at bottom of my post.

[Gicu]

We got it that is not a combo . Basically you need to use in your new Select query the same criteria that produces the subform's records, so look either at the subforms record source or the linking between subform and main form as I suggested in my previous post. Not much more advice possible if you do not show us something to illustrate your issue.

Cheers,

[Ellpee]

Best I can tell the subform is a "stand-alone,' not linked to a table and not connected to the parent form. (Hey, I didn't build this, just trying to help them with a problem!) But the subform is a "continuous form," meaning they can enter multiple rows each containing a customer ID and a quantity. That information eventually gets dumped into a table via embedded SQL in the VBA procedure, but only if the user clicks the "GO" button. So June7's post about code looping through controls on a subform offers a glimmer of hope. The Customer numbers, however, are not in a list box or combo box or any other single control, they are in one or many rows of a continuous (sub)form that, until "GO" is clicked, lives only in that subform. If there's a way to loop through those rows BEFORE they exist anywhere else, TaDa. If not, starting to look like I might need to add a temp table to hold that data so it can be further manipulated as needed. Since I don't own the database, I'm always reluctant to make those kinds of changes.

[Gicu]

What is the subform's record source property? You can always loop through a (sub)form's record via its associated Recordset(Clone), but you are not giving us much to go with.

Dim rs As DAO.Recordset, lCustID as Long
Set rs=Forms!frmMain!sfrmContinuous.Form.RecordsetClone
Do until rs.EOF
    lCustID=rs("CustomerID") 
 'now do something with this ID like use it to build a comma-delimited list that you can then use in your select query using the IN clause
rs.MoveNext
Loop

Cheers,

[Ellpee]

That recordset thing looks promising, will look into it more. Thanks!

[June7]

If the form is not linked to table and data "lives only in that subform", sounds like an UNBOUND form with "rows" created by multiple sets of textboxes, which means a limited number of "records" can be entered. Again, suggest you provide db for analysis.