Solved Capture Query Data (2 Viewers)

[Juett]

Hi All,

I have a query (qrypackage1) that collects individual records from a table (tblproducts). I use this query to display certain products together as they form a final packaged product. The result of said query can then be displayed on reports etc.

The thing is...tblproducts consists of many individual products that make up many individual final, packaged products, so I have many queries for each packaged product. All good so far.

But...what I'd like to accomplish is the following:

Have a combo box (combopackages) on a form (frmquote) that contains the name of each final, packaged product, that when selected, runs the corresponding package query and then transfers the query results into a set of text boxes on that open from.

I know how to display a single query (bound to a combo box) and copy out data to a text box on a form. But.. what I'd like to do is choose from a selection of queries. This is where I'm stuck.

I assume I need to use combopackages to select my desired query from a list, then use VBA to run that chosen query and store/display it temporarily in another combo box, and then copy out from THAT into the form text fields? This is what I cannot figure out how to do.

Any help would be greatly appreciated.

 

[Gasman]

Use another combo with all the queries to set the value for your existing combo and the rest of your process?

 

[Juett]

This is, I think, what I can't seem to figure out how to do.

 

[Gasman]

Should be easy enough.
Create a new combo that selects all the query names from your DB, or restrict on naming convention, if there are several you do not want to run?
I did something similar in a DB of mine, but I had one form that processed Forms,Queries and Reports.

Private Sub Form_Load()
Dim strSQL As String
' Object type gets passed in as OpenArgs
If IsNull(Me.OpenArgs) Then
    MsgBox "Missing parameter for form"
    DoCmd.Close
End If
strSQL = "SELECT * from tblObject WHERE ObjectType = '" & Me.OpenArgs & "'"
Me.RecordSource = strSQL
Me.Auto_Title0.Caption = " Open " & Me.OpenArgs
Me.txtObjectType = Me.OpenArgs
If Me.OpenArgs <> "Report" Then
    Me.chkPreview.Visible = False
    Me.lblPreview.Visible = False
End If
TempVars("ObjectType") = Me.OpenArgs
End Sub

That ran from the switchboard some code

Function OpenCommonForm(pOpenArgs As String)
' Open a form for Reports & Queries
DoCmd.OpenForm "frmOpenObject", , , , , , pOpenArgs
End Function

POpenArgs could be Form,Query or Report.

Then in the afterupdate event of that combo, set your existing combo to it's value, then from then on process should be the same?
You could probably initiate the process as well, so all in one step?

 

[plog]

The thing is...tblproducts consists of many individual products that make up many individual final, packaged products, so I have many queries for each packaged product. All good so far.

I don't think that is the correct way to achieve this. Instead of multiple queries where you store the product in the name of the query you just need one query with an additional field in which the product appears. That way you simply need to have the form filter the query to the product you need and it then holds the data you want. Further, when new products get added it won't require a new query, more code to handle that query, etc.

Have a combo box (combopackages) on a form (frmquote) that contains the name of each final, packaged product, that when selected, runs the corresponding package query and then transfers the query results into a set of text boxes on that open from.

This become really simple with just one query. Build your selection form (SelectionForm) and then build your display form (DisplayForm). DisplayForm will be based on that one query I described above and will show all the data from that query if opened by itself. But you won't be opening it just by itself, you will be opening it from the SelectionForm. The user selects a product, they click a button and the button has code on it that uses DoCmd.OpenForm:

DoCmd.OpenForm method (Access)

Office VBA reference topic

learn.microsoft.com

One of the arguments is the name of the form to open ("DisplayForm") and another is a string that is filter criteria. You construct that filter string with data from the product combo box on SelectionForm and then you are able to open DisplayForm to just the records applicable to the product selected.

 

[Juett]

I think I might suggest that the form is redesigned on this occasion as per plog's suggestion. It seems more sensible. Thanks very much.

 

[Juett]

Update - I managed to achieve this by running a query from a combo box and storing the results in another combo box, then from this second combo box I copied and inserted the results into the form datasheet using a loop.

Dim i As Integer
For i = 0 To Me.Combobox2.ListCount - 1
Me!Datasheet1.Form![Qty] = Me.[Combobox2].Column(0)
Me!Datasheet1.Form![Product Code] = Me.[Combobox2].Column(1)
Me!Datasheet1.Form![Product Name] = Me.[Combobox2].Column(2)
Me!Datasheet1.Form![Price] = Me.[Combobox2].Column(3)
Me.Combobox2.SetFocus
Me!Combobox2.Value = Me.Combobox2.ListIndex + 1
Me.Combobox2.Requery
Datasheet1.SetFocus
DoCmd.GoToRecord , , acNewRec
Next i

Its worth nothing that I did not build this database and do not recommend using such a workaround as there are other, better methods to pull the data required, as detailed above. But - this code did do the job initially needed it to do.