Query by form, help with adding subform

[ljungers]

Hi to all and hope someone may have an answer for me.

I have a Form named Cust_lukup_Form that has 3 text boxes and a click button that uses a OnClick to call Cust_lukup_Macro that runs an OpenQuery named Cust_lukup_Query using the text box(s) input data running in print view mode. The Cust_lukup_Query has the code setup to query the 3 fields using 1, 2 or 3 of the boxes or selecting all if there blank.

What I need to do is add another level of form selection in between the 1st form and the print preview. Would like to show a Cust_select_Form that has 3 combo boxes that display the results from the 1st query and allow selection of desired data (click on/select item or check boxes). When the Button is clicked OnClick runs Cust_select_Macro and then presents a print view or even a worksheet for futher uses like mail merge or what ever.

I found "How to use the Query by Form (QBF) technique in Access" on the MS site but can not find anything that walks me through the process of adding a next level in this query/selection process.

Can this be done (a Form calling a Macro that uses a Query that in turn uses a Form for display that allows data items to be selected and calls a Macro that runs a Query using that selected data to retrive the final data for use in a Form/Report/Worksheet etc.)

Thanks in advance for any help, advice or pointing me in the right direction

[MMcCarthy]

Hi to all and hope someone may have an answer for me.

I have a Form named Cust_lukup_Form that has 3 text boxes and a click button that uses a OnClick to call Cust_lukup_Macro that runs an OpenQuery named Cust_lukup_Query using the text box(s) input data running in print view mode. The Cust_lukup_Query has the code setup to query the 3 fields using 1, 2 or 3 of the boxes or selecting all if there blank.

What I need to do is add another level of form selection in between the 1st form and the print preview. Would like to show a Cust_select_Form that has 3 combo boxes that display the results from the 1st query and allow selection of desired data (click on/select item or check boxes). When the Button is clicked OnClick runs Cust_select_Macro and then presents a print view or even a worksheet for futher uses like mail merge or what ever.

I found "How to use the Query by Form (QBF) technique in Access" on the MS site but can not find anything that walks me through the process of adding a next level in this query/selection process.

Can this be done (a Form calling a Macro that uses a Query that in turn uses a Form for display that allows data items to be selected and calls a Macro that runs a Query using that selected data to retrive the final data for use in a Form/Report/Worksheet etc.)

Thanks in advance for any help, advice or pointing me in the right direction

From what I understand of your question you need to open a form based on the query at the end of the first macro then call a new macro from the second form.

Using macros to do this kind of complicated routine is probably not possible. One of the reasons vba was invented.

Forgetting the macros can you explain in english what you want to do and why.

Mary

[ljungers]

What I am trying to accomplish is the following:

1) using a form with 3 input text boxes (customer name, customer number, customer city) any combination of data values can be entered or all three left blank.

2) clicking on the search button will call/present a new form showing the results of the 1st query.

3) The second Form allows the selection (using click or ctrl + click or check boxes) of item(s) that came from the 1st query.

3) using the selected data on the second form, perform a query that will produce a tempory worksheet that saves the final query results (could be for 1 row or multi rows from the customer table) that can be used for printing the results.

I plan on using a module that I found and want to use it. This module calls Word and passes the results from a worksheet/parameters to Word and Word performs a mail merge using that data on a merge template doc. That doc gets it's info from the customer table. All of this is using one table only.

Hope this helps and is not to confussing. Thanks

[MMcCarthy]

What I am trying to accomplish is the following:

1) using a form with 3 input text boxes (customer name, customer number, customer city) any combination of data values can be entered or all three left blank.

2) clicking on the search button will call/present a new form showing the results of the 1st query.

3) The second Form allows the selection (using click or ctrl + click or check boxes) of item(s) that came from the 1st query.

3) using the selected data on the second form, perform a query that will produce a tempory worksheet that saves the final query results (could be for 1 row or multi rows from the customer table) that can be used for printing the results.

I plan on using a module that I found and want to use it. This module calls Word and passes the results from a worksheet/parameters to Word and Word performs a mail merge using that data on a merge template doc. That doc gets it's info from the customer table. All of this is using one table only.

Hope this helps and is not to confussing. Thanks

To be honest it sounds like you are taking too many steps. Why not have all the selections on the first form. If you can explain in english how you would like to restrict the criteria for the final query we can suggest something maybe using a series of combo boxes and lists. Include the original table structure.

I'm genuinely not trying to be difficult but the model you're following doesn't make sense to me and I can't get my head around trying to follow it.

Mary

[ljungers]

Hope this helps!

Have a customer invoice history table. In that table there are fields named "Cust_name, Cust_No, Cust_City and Cust_inv_no". Want to show a form that allows the person to enter any of the fields mentioned above. If they inter the Cust_name for example, there could be several invoices for that customer in one or several cities with many invoices for that customer, plus there could be multi customer numbers as well.

After a person enters the starting query info and clicks the search button that query presents a different form that uses combo boxes or some type of means to allow a narrowing down of the matching search data. There could be 1 entry showing if the first query had enought information supplied. If there are many rows returned I would like to show them and allow the selection of 1 or more of those rows from that customer invoice history table.

The next step I'm want to do is print the final selected items (reprint invoice(s) from the history). No editing is allowed to any of this data.

Example:
Form 1:
Cust Name _________
and/or Cust Numb _________
and/or Cust City ____________
and.or Invoice # __________

Search Button
=========================================
Form 2:
Cust Name, Cust Numb, Cust City, Invoice #

Combo | Combo | Combo | Combo
- Box - | - Box -- | -- Box - | - Box --
______ | ______ | ______ | ______

Click on above item(s) for Invoice reprint

Print Button
================================================

In short, a person enters search field(s) that show the list of matches and allows them to futher narrow down the search to 1 or more for reprint.

[PEB]

Why you don't do a query with this information?

There you can present this information and when the user want to see the respective invoice, clicking on a buton, opens the form with invoices on the respective one!

So you will be able to use the form filter that is used mainly for queries and tables /also forms, but not very good way for the users/

For my programs i'm doing the same thing, coz i've invoices and when the user is positionned on the respective invoice in the query and presses a bouton this bouton opens the respective invoice!

[MMcCarthy]

Have a combo box based on a Value List called cboSearchField as follows:

Column Count 2
Column Widths 0cm; 5 cm
Bound Column 1
Row Source Type "Value List"
Row Source 1;"Customer Name";2;"Customer Number";3;"City";4;"Invoice Number"

Create a textbox to enter the value called txtSearch.

Create a list box called listInvoices as follows:

Column Count 4
Column Widths 3cm; 3cm; 3cm; 3cm; (adjust these as required)
Bound Column 1
Row Source Type "Table/Query"
Row Source (Leave this blank)
Multi Select "Simple"

Create a Search Button called cmdSearch

In the code put ...

Private Sub cmdSearch_Click()
Dim strWhere As String
 
   SELECT CASE cboSearchField
   CASE 1
      strWhere = "[Cust_name] LIKE '" & Me.txtSearch & "*'"
   CASE 2
      strWhere = "[Cust_No]=" & Me.txtSearch 
   CASE 3
      strWhere = "[Cust_city] LIKE '" & Me.txtSearch & "*'" 
   CASE 4
      strWhere = "[Cust_inv_no]="  & Me.txtSearch
   End Select
 
   Me.listInvoices.RowSource = "SELECT   Cust_name,  Cust_No,  Cust_city,  Cust_inv_no " & _
      "FROM [Invoice Table Name] WHERE " & strWhere
 
   Me.listInvoices.Requery
 
End Sub
 

Users can hold down the control button to select multiple values from the list.

Create a print Button called cmdPrint

This code assumes that Invoice Number is a unique value in the table.

In the code put ...

Private Sub cmdPrint_Click()
Dim valSelect As Variant
 
   For Each valSelect In Me.listInvoices.ItemsSelected
      DoCmd.OpenReport "Invoice Report Name", acViewNormal, , "[Invoice#]=" & Me.listInvoices.ItemData(valSelect)
   Next valSelect
 
End Sub
 

This will print each of the selected invoices.

Mary