I'm pretty new to Access and VB and also know fairly little (more like nothing) about SQL.
I've got three queries that shows similar fields, the main ones being:
FIRST QUERY
Name
ID
Active member (criteria: yes)
Division in which a person belongs
Preliminary First Aid (PFA) due in 30 days
PFA due in 60 days
PFA due in 90 days
(the last 3 which are calculated fields which is based on when they actually sat the course)
SECOND QUERY
Name
ID
Active member (criteria: yes)
Division in which a person belongs
Senior First Aid (SFA) due in 30 days
SFA due in 60 days
SFA due in 90 days
(the last 3 which are calculated fields which is based on when they actually sat the course)
THIRD QUERY
Name
ID
Active member (criteria: yes)
Division in which a person belongs
Advanced First Aid (AFA) due in 30 days
AFA due in 60 days
AFA due in 90 days
(the last 3 which are calculated fields which is based on when they actually sat the course)
I've got a form that lets me select which report i want (PFA, SFA or AFA) and i want one report layout with unbound text boxes that is sorted by Division.
The problem is, how do i program the unbound text boxes in the report to refer to the relevant fields as i dont know how to link the control source either through SQL or through VB.
Thanks
9 2707 MMcCarthy 14,534
Recognized Expert Moderator MVP
What you are trying to do is a coding nightmare commonly known as dynamic reporting. Unless you are a very experienced programmer then don't even try it.
You would not just have to set the textbox values you would also have to set the labels and toggle textboxes to invisible if no data. Then there are formatting issues.
It's a lot of code and if you are trying to save time on creating three different reports this is not the way to do it.
The only way this works is if you have the same number of fields and the same labels for each field and the same datatype for each field. In that case you would only need to change the record source of the report each time.
I'm pretty new to Access and VB and also know fairly little (more like nothing) about SQL.
I've got three queries that shows similar fields, the main ones being:
FIRST QUERY
Name
ID
Active member (criteria: yes)
Division in which a person belongs
Preliminary First Aid (PFA) due in 30 days
PFA due in 60 days
PFA due in 90 days
(the last 3 which are calculated fields which is based on when they actually sat the course)
SECOND QUERY
Name
ID
Active member (criteria: yes)
Division in which a person belongs
Senior First Aid (SFA) due in 30 days
SFA due in 60 days
SFA due in 90 days
(the last 3 which are calculated fields which is based on when they actually sat the course)
THIRD QUERY
Name
ID
Active member (criteria: yes)
Division in which a person belongs
Advanced First Aid (AFA) due in 30 days
AFA due in 60 days
AFA due in 90 days
(the last 3 which are calculated fields which is based on when they actually sat the course)
I've got a form that lets me select which report i want (PFA, SFA or AFA) and i want one report layout with unbound text boxes that is sorted by Division.
The problem is, how do i program the unbound text boxes in the report to refer to the relevant fields as i dont know how to link the control source either through SQL or through VB.
Thanks
Thanks for the quick response mmccarthy.
Yes, the three queries do have the same number of fields, and same datatypes for each field. And i can make it so that all of their field name is the same in each query too.
But I dont know how to change the control sources to refer to each query.
I am doing this so i dont have to amend all three report layout if i need the report layout changed in the future. i could have easily done three reports but that means that if i change the look of one report, i have to do so with other reports.
What you are trying to do is a coding nightmare commonly known as dynamic reporting. Unless you are a very experienced programmer then don't even try it.
You would not just have to set the textbox values you would also have to set the labels and toggle textboxes to invisible if no data. Then there are formatting issues.
It's a lot of code and if you are trying to save time on creating three different reports this is not the way to do it.
The only way this works is if you have the same number of fields and the same labels for each field and the same datatype for each field. In that case you would only need to change the record source of the report each time.
MMcCarthy 14,534
Recognized Expert Moderator MVP
Thanks for the quick response mmccarthy.
Yes, the three queries do have the same number of fields, and same datatypes for each field. And i can make it so that all of their field name is the same in each query too.
But I dont know how to change the control sources to refer to each query.
I am doing this so i dont have to amend all three report layout if i need the report layout changed in the future. i could have easily done three reports but that means that if i change the look of one report, i have to do so with other reports.
Set up the report using one of the queries.
Then change the recordsource using the following code in the report open event. -
-
Private Sub Report_Open(Cancel As Integer)
-
Me.RecordSource = "QueryName"
-
End Sub
The queryname will have to be based on a value in your form. how are you choosing the report type. Option group, dropdown list ?
THANKS, i'll try it tonight. :) I've been pulling my hair about this aspect of the the database for about two weeks.
I'm choosing the report by command button.
Set up the report using one of the queries.
Then change the recordsource using the following code in the report open event. -
-
Private Sub Report_Open(Cancel As Integer)
-
Me.RecordSource = "QueryName"
-
End Sub
The queryname will have to be based on a value in your form. how are you choosing the report type. Option group, dropdown list ?
MMcCarthy 14,534
Recognized Expert Moderator MVP
THANKS, i'll try it tonight. :) I've been pulling my hair about this aspect of the the database for about two weeks.
I'm choosing the report by command button.
A different command button for each report would complicate the process. With just one command button.
Create a drop down list of the three choices.
Then in the Report Open event you could use something like this: - Private Sub Report_Open(Cancel As Integer)
-
Dim rptType As String
-
-
rptType = Forms![FormName]![ComboBoxName]
-
-
If rptType = "XXX" Then
-
Me.RecordSource = "QueryName1"
-
ElseIf rptType = "YYY" Then
-
Me.RecordSource = "QueryName2"
-
ElseIf rptType = "ZZZ" Then
-
Me.RecordSource = "QueryName3"
-
End If
-
-
End Sub
-
NeoPa 32,572
Recognized Expert Moderator MVP - Private Sub Report_Open(Cancel As Integer)
-
Select Case Forms![FormName]![ComboBoxName]
-
Case "XXX"
-
Me.RecordSource = "QueryName1"
-
Case "YYY"
-
Me.RecordSource = "QueryName2"
-
Case "ZZZ"
-
Me.RecordSource = "QueryName3"
-
End Select
-
End Sub
-'Select Evangelist' ;)
(I'll convert Mary yet!)
MMcCarthy 14,534
Recognized Expert Moderator MVP - Private Sub Report_Open(Cancel As Integer)
-
Select Case Forms![FormName]![ComboBoxName]
-
Case "XXX"
-
Me.RecordSource = "QueryName1"
-
Case "YYY"
-
Me.RecordSource = "QueryName2"
-
Case "ZZZ"
-
Me.RecordSource = "QueryName3"
-
End Select
-
End Sub
-'Select Evangelist' ;)
(I'll convert Mary yet!)
I knew as soon as I'd posted it you'd be in here mouthing off.
Have to leave you something to do. Keep trying to convert me.
Mary
Thanks for the answer,
I tried it last night and it worked like a gem. :)
MMcCarthy 14,534
Recognized Expert Moderator MVP
Thanks for the answer,
I tried it last night and it worked like a gem. :)
You're welcome.
Sign in to post your reply or Sign up for a free account.
Similar topics |
by: mark |
last post by:
How do I get all fields on one page of a report?
I have a report that has a column for each day of the week and 6 records for
each day. I need each weekday's records returned on only one detail page.
Instead I am getting a new table layout for each day of the week. I have tried
grouping on every record and combination I can, manipulating the Group
Properties but can't get it right. I have the entire report in the Detail
Section, with...
|
by: Nathan Bloomfield |
last post by:
Does anyone know if there is any documentation which relates to Access2k + ?
or can anyone help adjust the code?
I am having trouble converting the DAO references.
TITLE :INF: How to Create a Dynamic Crosstab Report
PRODUCT :Microsoft Access
PROD/VER:1.00 1.10
OPER/SYS:WINDOWS
|
by: teresaalmond |
last post by:
Ok.. I have a table where people add and delete records when machines
are sold or new ones brought into inventory. I know.. this isn't a good
business practice, but thats how they do it.
What I need is snapshots of the table on a specified day each month,
and queries comparing the 2 in order to get a list of Removed and Added
Records.
the queries are easy enough, but I have to remember to copy the latest
table as NewPopulation and the...
|
by: geronimo_me |
last post by:
I have 20 queries that compare fields in one table with fields in
another table - the query results are the records that do not match in
Table1 and Table2. ie
Table1
DOB 28/02/78
Table2
DOB 27/02/78
|
by: prabhukalyan |
last post by:
Hi all,
I am not so good in queries. here is my problem
2 tables to store the received items (fabric)-- inwardmaster,
inwarddetails
and after some processing (Dyeing)
the items were deliverd and stored in tables -- deliverymaster,
deliverydetails
| |
by: john |
last post by:
I have a report to print envelopes. The report is based on a query. Now I
need to make 10 more queries to make different selections of addresses.
Every query has the same output fields as the already existing query. I know
I can copy the report and base it on another query but then I would have to
make 10 extra reports. How can I use just one report for all of the queries?
At the moment I use the button wizard in my forms to make buttons...
|
by: Jana |
last post by:
Hi!
I have an Access 97 report based on tables linked to an SQL back end.
The report has 5 subreports in it (unfortunately unavoidable due to the
nature of the report) and performance is quite slow. The query runs
quickly and the report displays the first page in a short amount of
time, but takes quite a while to format each page. This means it takes
a long time to print the report (up to 50 mins to print around 360
pages!) as it...
|
by: creative1 |
last post by:
Here is how you create a complex data report that involves parent and child commands and you can update information at runtime. Its pretty straight forward to work with simple queries; however, working with complex reports is tricky
Assumption: Reader of this article have basic knowledge of creating data reports.
Creating a Parent-Child Command and create a DataReport
Suppose we have a database called company with two tables
...
|
by: magickarle |
last post by:
Hi, I have a database in access with the following columns:
Day
AgentID
ManagerID
Grade
They got information about agents and some grades.
I would like to have ONE form with several grouping (yearly quarterly
and weekly) with the respective average.
|
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: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it.
Here is my compilation command:
g++-12 -std=c++20 -Wnarrowing bit_field.cpp
Here is the code in...
| |
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: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert into image.
Globals.ThisAddIn.Application.ActiveDocument.Select();...
|
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: 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
| |
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| |