473,322 Members | 1,493 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,322 software developers and data experts.

How to use data from a form's unbound control in the query & report?

sueb
379 256MB
So I'm following Steward Ross's good advice (see thread http://bytes.com/topic/access/answer...ab-query-field) about making a form that allows my users to select a FY, and then have buttons that each run one of a handful of summary reports.

I made a little table that contains fiscal year strings ("FY08-09", etc.) with associated begin and end dates, and a form with a combo box to allow the user to select a fiscal year and some buttons to select among the reports, but how, exactly, do I give the dates (for the filter) and the string (for printing) to the query/report?

Here is the query's SQL as it currently stands (it asks for input from the user, but that will change, right?):

Expand|Select|Wrap|Line Numbers
  1. TRANSFORM Count(DataTable.Number) AS CountOfNumber
  2. SELECT DataTable.Service, Count(DataTable.Number) AS [Total Of Number]
  3. FROM DataTable
  4. WHERE (((DataTable.[Date of Surgery]) Between [FY Begin] And [FY End]))
  5. GROUP BY DataTable.Service
  6. PIVOT Format([date of surgery],"mmm") In ("Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec");
and here is the code for the button that opens the report:
Expand|Select|Wrap|Line Numbers
  1. Private Sub VCMC_FY_Case_Count_Click()
  2. On Error GoTo Err_VCMC_FY_Case_Count_Click
  3.  
  4.     Dim stDocName As String
  5.  
  6.     stDocName = "VCMC FY Case Count"
  7.     DoCmd.OpenReport stDocName, acPreview
  8.  
  9. Exit_VCMC_FY_Case_Count_Click:
  10.     Exit Sub
  11.  
  12. Err_VCMC_FY_Case_Count_Click:
  13.     MsgBox Err.Description
  14.     Resume Exit_VCMC_FY_Case_Count_Click
  15.  
  16. End Sub
Of course, I'm wanting to change how the query gets its filter values, and I'm assuming that I'm going to have to pass the string value into the query so that the report can access it.
May 3 '11 #1
2 1723
Mihail
759 512MB
As far as I know (and is not too far) it is no way to do that (or it is not an easy way or I can not find it). So, I transform the query in a MakeTable query. I copy the SQL from query views and I paste it in VBA module. Then I work around (something like this:)

Expand|Select|Wrap|Line Numbers
  1. Sub MakeNewTable(TheValueToPass)
  2. On Error ResumeNext
  3.  
  4.    'Remove the previous created table (if exist)
  5.    DoCmd.DeleteObject acTable, TableName
  6.  
  7.    'Create new table
  8.    SQL = FirstPartOfQuerySQL & TheValueToPass & EndPartOfQuerySQL
  9.    DoCmd.RunSQL (SQL) 'Create table
  10. End Sub
After that you can use the new created table as source for your report.
Hope this is a help for you.
Good luck !
May 7 '11 #2
NeoPa
32,556 Expert Mod 16PB
I'm not sure if this answers your particular question Sue, but DoCmd.OpenReport() has a WhereCondition parameter (3rd) which you can use to pass your filtering for the report. This means that there is no need generally to keep updating the RecordSource of the report simply to filter it.
May 10 '11 #3

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

Similar topics

13
by: MLH | last post by:
I have a form with two controls: !! - combo box !! - text box A button on the form tries to run this SQL when clicked... INSERT INTO BodyMsgsSent (ToWhom, BodyText) SELECT DISTINCTROW !! AS...
3
by: Pat | last post by:
Hello, I've used Sum() to total bound fields on a continuous form with no problem. However, I now have a continuous form, on which I use an unbound field to calculate the number of hours between...
3
by: Trevor Hughes | last post by:
I am trying to resolve a problem I'm experiencing in Access 2000. I have an unbound control which is set be code on the open event of a form. However when I try to subsequently run some code...
1
by: Roger | last post by:
I've got a continuous form based on a query the first 2 controls on the form are bound to columns in the query a third control (paymentAmount) is unbound I have a total control in the footer,...
1
by: Jim M | last post by:
To prevent data corruption I have replaced a memo field on my form with an unbound control on my form that I populate with a function that fills it from a memo field from my table. When the form is...
3
by: duncedunce | last post by:
Hi Have 'googled' and no luck (after 2 hours!) - hope someone here can help. Access 2003 I am looking (for a visually impaired student of mine) at how to insert unbound controls in to a...
0
by: Barbara Schmidt | last post by:
Hello, if I get the data from db with sqldatasource-control, how to show this data to an unbound control, so for instance: label1.text=sqldatasourceWHAT-TO-DO-NOW Thanks Barbara
3
by: Paulo | last post by:
Hi everyone and thanks in advance for helping me. I´ve a form (continuous form) based on a query, wich several data fields are limited for severel fields (unbound text fields) build in footer...
1
by: Vijayakumar .Y | last post by:
Hello, I need to create label designer project. Anyone have sample code with control add, drag, drop and resize features to start to? Otherwise a good sample for idesignerhost is enough Regards,...
5
by: John McAtee | last post by:
I am using Access 2007. Created a contribution database for a non-profit. Created parameter query that displays contributions for a specified time period using the "Between And " parameter. The...
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
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
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: 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...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
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....
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 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 former...

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.