473,320 Members | 1,820 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

Query by form, help with adding subform

114 100+
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
6 5236
MMcCarthy
14,534 Expert Mod 8TB
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
ljungers
114 100+
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
14,534 Expert Mod 8TB
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
ljungers
114 100+
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
1,418 Expert 1GB
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
14,534 Expert Mod 8TB
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

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

Similar topics

1
by: Kunal | last post by:
Hi, I need some help on writing an update query to update "UnitsSold" field in Products Table whenever I save a transaction. The transaction may contain several "Subtransactions", one for each...
1
by: Rolan | last post by:
I created a function module base on a query to provide a group record total for a subform and which is posted to a field on the main form. Everything seems to work fine except that I consistently...
2
by: John | last post by:
Hi - I am trying to perform a simple append query, with no luck. I have a table (MktPrices) that has the following fields: BondID, PriceDate, Price. The objective is to allow the user to input a...
1
by: Dalan | last post by:
I'm experiencing a Query Syntax Error with an Access 97 Db. Actually, the query performs as expected when adding any new records or editing existing ones and even deleting records, EXCEPT when the...
3
by: John young | last post by:
I have been looking for an answer to a problem and have found this group and hope you can assist . I have been re doing a data base I have made for a car club I am with and have been trying to...
0
by: Jason | last post by:
I have a primary form which is used to enter/edit data in a table named Test_Results. On this primary form there is a subform which displays site addresses. This subform is linked to the primary...
2
by: Smartin | last post by:
Using Access 97 I am trying to create a form that allows for user entry of search terms and displays a table of results. I thought I could accomplish this using a subform but it isn't quite...
0
by: Stephen D Cook | last post by:
I have a form linked to a table. In the form I have a textbox, a command button, and a query subform. I dragged the query onto the form to create the query subform. The query has a field named...
4
by: Deus402 | last post by:
Here is my table design: tblEmployers EmployerID autonum (primary key) EmployerName text tblLocations LocationID autonum (primary key) EmployerID longint (foreign key) LocationAdress text
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
0
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.