By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,495 Members | 1,002 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,495 IT Pros & Developers. It's quick & easy.

Query by form, help with adding subform

100+
P: 114
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
Dec 16 '06 #1
Share this Question
Share on Google+
6 Replies


MMcCarthy
Expert Mod 10K+
P: 14,534
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
Dec 16 '06 #2

100+
P: 114
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
Dec 16 '06 #3

MMcCarthy
Expert Mod 10K+
P: 14,534
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
Dec 16 '06 #4

100+
P: 114
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.
Dec 16 '06 #5

PEB
Expert 100+
P: 1,418
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!
Dec 17 '06 #6

MMcCarthy
Expert Mod 10K+
P: 14,534
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 ...

Expand|Select|Wrap|Line Numbers
  1. Private Sub cmdSearch_Click()
  2. Dim strWhere As String
  3.  
  4.    SELECT CASE cboSearchField
  5.    CASE 1
  6.       strWhere = "[Cust_name] LIKE '" & Me.txtSearch & "*'"
  7.    CASE 2
  8.       strWhere = "[Cust_No]=" & Me.txtSearch 
  9.    CASE 3
  10.       strWhere = "[Cust_city] LIKE '" & Me.txtSearch & "*'" 
  11.    CASE 4
  12.       strWhere = "[Cust_inv_no]="  & Me.txtSearch
  13.    End Select
  14.  
  15.    Me.listInvoices.RowSource = "SELECT   Cust_name,  Cust_No,  Cust_city,  Cust_inv_no " & _
  16.       "FROM [Invoice Table Name] WHERE " & strWhere
  17.  
  18.    Me.listInvoices.Requery
  19.  
  20. End Sub
  21.  
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 ...

Expand|Select|Wrap|Line Numbers
  1. Private Sub cmdPrint_Click()
  2. Dim valSelect As Variant
  3.  
  4.    For Each valSelect In Me.listInvoices.ItemsSelected
  5.       DoCmd.OpenReport "Invoice Report Name", acViewNormal, , "[Invoice#]=" & Me.listInvoices.ItemData(valSelect)
  6.    Next valSelect
  7.  
  8. End Sub
  9.  
This will print each of the selected invoices.

Mary
Dec 18 '06 #7

Post your reply

Sign in to post your reply or Sign up for a free account.