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_Macr o that runs an OpenQuery named Cust_lukup_Quer y using the text box(s) input data running in print view mode. The Cust_lukup_Quer y 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_For m 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_Mac ro 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
6 5257 MMcCarthy 14,534
Recognized Expert Moderator MVP
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_Macr o that runs an OpenQuery named Cust_lukup_Quer y using the text box(s) input data running in print view mode. The Cust_lukup_Quer y 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_For m 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_Mac ro 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
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 14,534
Recognized Expert Moderator MVP
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
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 1,418
Recognized Expert Top Contributor
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 14,534
Recognized Expert Moderator MVP
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;"Custom er Number";3;"City ";4;"Invoic e 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
Sign in to post your reply or Sign up for a free account.
Similar topics |
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 product sold in that transaction. I
have a subtransaction table that maintains record of each subtransaction,
with data such as: ProductID, Quantity, TransactionID etc.
The Products table has fields: ProductID, UnitsSold, UnitsOrdered,...
|
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 receive an
error every time I attempt to create a new record on the main form.
However, there are no errors received when accessing existing records.
It is a Run-Time Error 3075 - Synax error (missing operator) in query
expression 'IDRef=' . And...
|
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 date
and have the append query create a new record for each BondID, using
the input date and the most recent price - which the user can then
update on a tabular form. I have created a totals query
(qryMostRecentPrice) which selects the most...
|
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 last record
on any related subform is deleted, then it generates a Runtime Error
3075 - Syntax error (missing operator) in query expression 'IDRef =
'-- of course, because there are no more records left with the same
IDRef number, or none...
|
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 make a query that selects from a table as
desribed below ..
I have a table (Volunteer) that has a member field (memnumber)
and a number of fields that are headed in various categories and are
yes/no formated
| |
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 form
by field named TestID. The subform is used just for displaying site
address data, data which is stored in another table named
Total_Site_Address. In the Total_Site_Address table there are
numerous fields that form the site addresses...
|
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 doing what I want it to do.
What I have so far:
Fields to enter search terms
Subform bound to a query
|
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 SerialNumber. In the criteria for the
query, I have !! which links the query to the
textbox in the main form.
Code for the form:
Private Sub cmdShow2_Click()
|
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
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look !
Part I. Meaning of...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it.
First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
| |
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth.
The Art of Business Website Design
Your website is...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms.
Adolph will...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
| |
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
| | |